I was asked to do an datapump import from a 11.2.0.4 BP16 to 12.1.0.2.7 from a couple of schema’s. everything went fine but all of a sudden during the import following happened
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00600: internal error code, arguments: [ktfbns_update_ilmstat2], [262161], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SQL", line 1185
ORA-06512: at "SYS.KUPW$WORKER", line 22791
ORA-06512: at "SYS.KUPW$WORKER", line 21996
ORA-06512: at "SYS.KUPW$WORKER", line 21553
ORA-06512: at "SYS.KUPW$WORKER", line 4516
ORA-06512: at "SYS.KUPW$WORKER", line 12063
ORA-06512: at "SYS.KUPW$WORKER", line 2081
ORA-06512: at line 2
ORA-0600 …. generally that is not a good sign, luckily the instance was still up. the ORA-0600 checker on metalink ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1) didn’t know this argument. I opened an SR in parallel with all the possible info and I started to have a look at the dump files but that didn’t help either. It occurred to me that ilm, Information Life Cycle Management or Heat Map is a new 12c feature. However this feature wasn’t enabled on our Target.
heat_map OFF
So this was a surprise.
Still the ORA-0600 seemed to give an error while calculating these stats, I quickly read some docs about this feature.
select * from dba_ilmparameters;
NAME VALUE
------------------------------ ----------
ENABLED 1
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS PERCENT FREE 25
POLICY TIME 0
I used the procedure to disable
DBMS_ILM_ADMIN
sys@WDWSI> begin
2 dbms_ilm_admin.DISABLE_ILM;
3 end;
4 /
PL/SQL procedure successfully completed.
sys@WDWSI> select * from dba_ilmparameters;
NAME VALUE
------------------------------ ----------
ENABLED 2
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS PERCENT FREE 25
POLICY TIME 0
8 rows selected.
Retried the import => NOK I checked which undocumented parameters where set with regards to ILM in the description
Parameter Instance Description
----------------------------- --------- -------------------------------------------
_ILM_FILTER_TIME 0 Upper filter time for ILM block compression
_ILM_FILTER_TIME_LOWER 0 Lower filter time for ILM block compression
_ILM_POLICY_NAME FALSE User specified ILM policy name
_create_stat_segment 0 create ilm statistics segment
_disable_12cbigfile FALSE DIsable Storing ILM Statistics in 12cBigFiles
_drop_stat_segment 0 drop ilm statistics segment
_enable_ilm_flush_stats TRUE Enable ILM Stats Flush
_enable_ilm_testflush_stats FALSE Enable Test ILM Stats Flush
_flush_ilm_stats 0 flush ilm stats
_ilmflush_stat_limit 0 ILM flush statistics limit - Internal testing only
_ilmset_stat_limit 0 ILM set statistics limit - Internal testing only
_ilmstat_memlimit 10 Percentage of shared pool for use by ILM Statistics
_ktilmsc_exp 600 expiration time of ktilm segment cache (in second)
_print_inmem_heatmap 0 print inmem ilm heatmap
_print_stat_segment 0 print ilm statistics segment
_trace_ktfs FALSE Trace ILM Stats Tracking
heat_map OFF ILM Heatmap Tracking
It occurred to me that BIGFILE TABLESPACES were used so because of the urgency of the IMPORT i decided to try to set the parameter _disable_12cbigfile to TRUE, since I didn’t get any update from support and the customer was waiting to do his tests …. And Bingo the import Worked as expected…. Big disclaimer : putting _ parameters is not something you do without the blessing of support !!!I provided support with my workaround and they are checking for further potential issues and impacts
