Thursday, October 22, 2015

ORA-00600 [ktfbns_update_ilmstat2] during impdp

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