Tuesday, December 09, 2014

ORA-00600 [kdiRelocateCommitted:DBAupd]

This error recently showed up in a 11.2.0.3.0 Enterprise Edition RDBMS on Oracle Enterprise Linux: ORA-00600: internal error code, arguments: [kdiRelocateCommitted:DBAupd], [2126573183], [], [], [], [], [], [], [], [],
[], []
Incident details in: /u01/app/oracle/diag/rdbms/ ....._ora_5642_i10412.trc

After the trace file I was able to identify which operation produced the error and identify the specific partition that had problems.  The error was triggered during a partitioned index maintenance operation:

alter index INDEX_OWNER.INDEX_NAME modify partition PART_NAME shrink space
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdiRelocateCommitted:DBAupd], [2063617647], [], [], [], [], [], [], [], [], [], []

The error is not documented, and so far Oracle Support doesn't have information about this error

The workaround was to rebuild the index.  After rebuilding it the same operation produced no error.

Wednesday, January 22, 2014

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

Note: This particular scenario happened on an Oracle 11g Rel.2 (11.2.0.3.0) RAC on OEL6 x64 with Active Dataguard.

The following error shows on a scheduled basis

ORCL1:
Sat Jan 18 23:00:06 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_j008_6097.trc:
ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1

Reason:
The underlying table required as part of one of the schedule maintenance tasks has invalid values.
The related table is DBSNMP.BSLN_BASELINES. 

After taking a look at the job log we see the failed execution attempts:

SQL> select log_date,status from dba_scheduler_job_run_details 
     where job_name='BSLN_MAINTAIN_STATS_JOB';

LOG_DATE                              STATUS
------------------------------------- ------------------------------
11-JAN-14 11.00.07.315077 PM -06:00   FAILED
04-JAN-14 11.00.05.595559 PM -06:00   FAILED
18-JAN-14 11.00.06.554385 PM -06:00   FAILED
28-DEC-13 11.01.05.966337 PM -06:00   FAILED
22-JAN-14 03.36.50.995888 PM -06:00   FAILED

5 rows selected.

FIX Procedure:
Look for the values on the table, detect the invalid values and remove them.

SQL> select * from DBSNMP.BSLN_BASELINES;

      DBID INSTANCE_NAME    BASELINE_ID BSLN_GUID                        TI A STATUS           LAST_COMPUT
---------- ---------------- ----------- -------------------------------- -- - ---------------- -----------
3038864366 orcl                       0 0A03A0424F06F7E6B3841C6CC0999F7C NW N ACTIVE           131027:0000
3038864366 ORCL2                      0 A396EEB2AB3A39AF477DC4A1AEE70CC9 ND Y ACTIVE           131228:2300
3038864366 ORCL1                      0 176C36D62D62855075AD0BBD90B2DA30 ND Y ACTIVE           131228:2300

You must know exactly which row you're deleting, in my very particular case I know which rows do not make sense since this database was recently involved in a fail over scenario and the former name is still being held in the table.


The corrupt row is removed with a simple DELETE command:

SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='orcl';

1 row deleted.

SQL> commit;

Commit complete.

Manually re-execute the job and check the execution log, it must show the job executed successfully.  It takes a couple of minutes after execution to show the results in the log table.

SQL> exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);
PL/SQL procedure successfully completed.


The issue was fixed, validate it by querying he DBA_SCHEDULER_JOB_RUN_DETAILS view.

SQL> select log_date,status from dba_scheduler_job_run_details 
     where job_name='BSLN_MAINTAIN_STATS_JOB';

LOG_DATE                              STATUS
------------------------------------- ------------------------------
11-JAN-14 11.00.07.315077 PM -06:00   FAILED
04-JAN-14 11.00.05.595559 PM -06:00   FAILED
18-JAN-14 11.00.06.554385 PM -06:00   FAILED
28-DEC-13 11.01.05.966337 PM -06:00   FAILED
22-JAN-14 03.36.50.995888 PM -06:00   FAILED
22-JAN-14 03.41.20.714453 PM -06:00   SUCCEEDED