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.

5 comments:

  1. Replies
    1. Thanks for your comment and glad it helped to resolve your issue.

      Delete
  2. Still I am getting the issues :(

    SQL> select OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,BASE_OBJECT,PURGE_OBJECT ,CAN_PURGE from dba_recyclebin where OBJECT_NAME like '%BIN$71yQGVgDADzgQwqBJgKp1A%';

    OWNER OBJECT_NAME ORIGINAL_NAME OPERATION BASE_OBJECT PURGE_OBJECT CAN
    ------------------------------ ------------------------------ -------------------------------- --------- ----------- ------------ ---
    CAS BIN$71yQGVgDADzgQwqBJgKp1A==$0 AR_PH_ARC_UNDO_DT DROP 20108 30668 NO

    SQL> insert into sys.objerror$ values(20108);

    1 row created.

    SQL> insert into sys.objerror$ values(30668);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> purge dba_recyclebin;
    purge dba_recyclebin
    *
    ERROR at line 1:
    ORA-00603: ORACLE server session terminated by fatal error
    ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [49], [BIN$71yQGVgLADzgQwqBJgKp1A==$0], [], [], [], [], [], [], []
    Process ID: 8781984
    Session ID: 92 Serial number: 29

    ReplyDelete
  3. Above error has been resolved with the same steps mentioned in the article. there was multiple object in recycle bin to whose entry was required to add into sys.objerror$ . Later on it was resolved. Thanks for useful information.

    SQL> purge dba_recyclebin;
    purge dba_recyclebin
    *
    ERROR at line 1:
    ORA-00603: ORACLE server session terminated by fatal error
    ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [49], [BIN$71yQGVbBADzgQwqBJgKp1A==$0], [], [], [], [], [], [], []
    Process ID: 39190750
    Session ID: 12 Serial number: 13


    SQL> conn / as sysdba
    Connected.
    SQL> select OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,BASE_OBJECT,PURGE_OBJECT ,CAN_PURGE from dba_recyclebin where OBJECT_NAME like '%BIN$71yQGVbBADzgQwqBJgKp1A%';

    OWNER OBJECT_NAME ORIGINAL_NAME OPERATION BASE_OBJECT PURGE_OBJECT CAN
    ------------------------------ ------------------------------ -------------------------------- --------- ----------- ------------ ---
    CAS BIN$71yQGVbBADzgQwqBJgKp1A==$0 AR_PDI_UNDO_DT DROP 20070 30672 NO

    SQL> insert into sys.objerror$ values(20070);

    insert into sys.objerror$ values(30672);
    1 row created.

    SQL> SQL>

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> purge dba_recyclebin;

    DBA Recyclebin purged.

    SQL> exit

    ReplyDelete
  4. Suggesting that anyone directly update any data dictionary tables is, in my view, irresponsible as such action would be cause for Oracle Support to invalidate the service agreement. At the very LEAST this post should include a strongly worded disclaimer to NOT follow the path you took to prevent losing support services the enterprise has paid for. UNLESS ORACLE SUPPORT PROVIDES SUCH INSTRUCTION THE DATA DICTIONARY SHOULD NOT BE TAMPERED WITH.

    ReplyDelete