ORA-00001, ORA-00604 and ORA-600 [kqlidchg0] in IMPDP

Today I came across an error ORA-00600: internal error code, arguments: [kqlidchg0] while importing one of the schema from UAT database to DEV database using IMPDP.

IMPDP was failing after after importing TABLE_EXPORT/TABLE/TABLE_DATA with error : Job “SYS”.”SYS_IMPORT_FULL_01″ stopped due to fatal error at 10:28:53.

After having quick check in alert log, I found following errors:

Wed Sep 17 10:27:53 2014
Errors in file /u03/oracle/JPDATA/diag/diag/rdbms/JPDATA/JPDATA/trace/JPDATA_dw00_31597.trc (incident=50280):
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 3
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
Incident details in: /u03/oracle/JPDATA/diag/diag/rdbms/JPDATA/JPDATA/incident/incdir_50280/JPDATA_dw00_31597_i50280.trc
Wed Sep 17 10:28:26 2014
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

Generated trace file was having having following contents:

Dump continued from file: /u03/oracle/JPDATA/diag/diag/rdbms/JPDATA/JPDATA/trace/JPDATA_dw00_31597.trc
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 3
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG
========= Dump for incident 50281 (ORA 603) ========
*** 2014-09-17 10:28:26.536
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=5c6pdpmw2d72n) -----
ALTER PACKAGE "JPDATA"."AUDIT_HISTORIQUE_PKG" COMPILE SPECIFICATION PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:ALL' REUSE SETTINGS TIMESTAMP '2014-09-11 14:57:12'
----- PL/SQL Stack -----

So we were hitting ORA-600 on compilation of this package.


After searching on metalink, I found note ORA-1, ORA-604 and ORA-600 [kqlidchg0] on Datapump Import (Doc ID 1525771.1) which has provided number of solutions, which inludes following:

1. Upgrade to 12c. OR

2. Compile package with session level change in parameter PLSCOPE_SETTINGS to ‘identifiers:none’. OR

3. System level change in parameter PLSCOPE_SETTINGS to ‘identifiers:none’ and then drop & recreate object

After this u can rerun your IMPDP.


I tried for solution 2 and 3, but failed as I was not having any control on internal compilation done by IMPDP.

So as resolve this issue, I used following approach:


1. Continued from failed IMPDP, I compiled that package manually with session level change in parameter PLSCOPE_SETTINGS to ‘identifiers:none’.

2. Rerun import with option : exclude=package:\”in\(\’AUDIT_HISTORIQUE_PKG\’\)\”

3. Crosscheck object count from source to target schema. In my case many of the objects with object type – trigger, views & constraints were missing which I need to create manually.


Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Advertisements

4 thoughts on “ORA-00001, ORA-00604 and ORA-600 [kqlidchg0] in IMPDP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s