Tuesday, September 4, 2012

ORA-00600: internal error code, arguments: [kqd-objerror$]

Database : 11.2.0.2
Problem   : ORA-00600: internal error code, arguments: [kqd-objerror$]
Reason    : This issue is due to inconsistency in data dictionary
SQL>alter TRIGGER ADDRESS_PREVENT_OVERLAP compile  ;
alter TRIGGER ADDRESS_PREVENT_OVERLAP compile
              *
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
PUBLIC.TRIGGER_OVERLAP_PKG
ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [369],
[EMAIL_PREVENT_OVERLAP_AFTER], [], [], [], [], [], [], []
SQL>alter TRIGGER CUSP.ADDRESS_PREVENT_OVERLAP compile  ;
alter TRIGGER ADDRESS_PREVENT_OVERLAP compile
              *
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
PUBLIC.TRIGGER_OVERLAP_PKG
ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [369],
[EMAIL_PREVENT_OVERLAP_AFTER], [], [], [], [], [], [], []
drop public synonym TRIGGER_OVERLAP_PKG
                    *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [369], [EMAIL_PREVENT_OVERLAP], [], [], [], [], [], [], []

Actions
1] Tried purge dba_recyclebin - Did not help
2] Tried recyclebin off and bounce Instance - Did not help
3] Tried dropping object EMAIL_PREVENT_OVERLAP_AFTER - Gives same ORA-600
3] Tried dropping public synonym TRIGGER_OVERLAP_PKG - Gives same ORA-600

Solution1] Find the object_id of the object giving problem for e.g. synonym TRIGGER_OVERLAP_PKG
 select object_id from dba_objects where object_name='TRIGGER_OVERLAP_PKG' and object_type='SYNONYM';
2] insert record in sys.objerror$
 insert into sys.objerror$ values(99601);
 commit;
3] Very important - Shutdown abort. Do not gracefully shutdown. Abort will not let rowcache to be flushed to dictionary
4] Start Instance
5] Drop public synonym TRIGGER_OVERLAP_PKG - Success
6] recreate public synonym TRIGGER_OVERLAP_PKG - Success
7] compile trigger CUSP.ADDRESS_PREVENT_OVERLAP - Success

Disclaimer : UNLESS ORACLE SUPPORT PROVIDES SUCH INSTRUCTION THE DATA DICTIONARY SHOULD NOT BE TAMPERED. RUN YOUR FINDINGS WITH ORACLE BEFORE EXECUTING ON YOUR ORACLE/VENDOR SUPPORTED ENVIRONMENT.