Tuesday, November 22, 2011

ORA-00600 [kqlindp-bad-user]

ORA-00600 [kqlindp-bad-user] error started to happen in a two node RAC running 11.1.0.7 on RHEL 64 bit system.

Effect on the system was both expdp and impdp was giving the following errors on the client side and ORA-00600 [kqlindp-bad-user] being logged on the alert.log. For import
UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2862
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4052
ORA-06512: at line 1
For export
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2862
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4052
ORA-06512: at line 1 and also any local PL/SQL package execution would a
The only metalink note (at the time of writing) related to this ora-600 was ORA-600 [kqlindp-bad-user] [ID 753007.1] which wasn't much help.

Other issue was execution of PL/SQL packages (even users own ones, not the pl/sql packages on a different schema) throw up the ORA-00600 [kqlindp-bad-user] error. The error was coming up on the client side (JDBC connections) as well as beign logged on the server side.

There were plenty of metalink notes for errors UDI-31623 and ORA-31623 but none of those solutions helped.

After raising a SR following steps were recommended which resolved the issue.

1. Execute the following query and examine the output
select owner#, name
from sys.obj$ where owner# not in (select user# from sys.user$) and type# != 10;
This returned set of PL/SQL objects with owner id in this case all the objects seem to be from one owner.

2. After submitting the above query output Oracle came back with The output of the previous query shows non-existent users referenced by table sys.obj$. If you run hcheck.full it may show some data dictionary inconsistency. The ORA-600 error is not directly a consequence of the orphan obj$ rows; it is caused by one or more of the bad obj# being present in the objerror$ table.

Offered solution was take a full backup and in order to fix the issue remove the row from objerror$:
startup restrict
delete from sys.objerror$ where obj# in (select obj# from sys.obj$ where owner# = owner_id from previous sql and owner# not in (select user# from sys.user$));
commit;
shutdown abort;
The shutdown abort is required to save changes to the data dictionary. Once the database is shutdown, you must then perform a startup and a clean shutdown before it can be used again.
startup restrict;
shutdown immediate;
startup;
3. This resolved the issue with ORA-00600 [kqlindp-bad-user] coming up when executing pl/sql packages but expdp and impdp still gave errors, but this time it was different error.
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 601
ORA-39080: failed to create queues "" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1550
ORA-01403: no data found
4. After refereing metalink notes
Impdp or Expdp Fails With ORA-31626 and ORA-31637 [ID 345198.1]
How To Reload Datapump Utility EXPDP/IMPDP [ID 430221.1] and
Invalid Objects After Installing a 10.2 Patchset [ID 361025.1]

@?/rdbms/admin/catproc.sql + steps in 361025.1 and @?/rdbms/admin/utlrp was executed which resolved the issue.