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

Multiple failed login attempts can cause high number of row cache lock wait events in 11g

From Oracle 11g, there is a new security enhancement that has potential of creating huge performance issues due to failed login attempts through multiple sessions.

Yesterday we faced this issue on production database, all application users were complaining about slow performance especially while making new connections with database.

After having quick check with v$session, I found there are huge number of sessions waiting on event : row cache lock.


 

select event,count(0) from v$session
where username is not null
and status='ACTIVE'
group by event
order by 2 desc;
EVENT COUNT(0)
---------------------------------------------------------------- ----------
row cache lock 137
class slave wait 4
direct path read 3
db file sequential read 2
db file scattered read 1
SQL*Net message to client 1

 

Row cache lock ???

After searching on metalink I found following note :

Bug 7715339 – Logon failures causes “row cache lock” waits – Allow disable of logon delay (Doc ID 7715339.8).

As per this bug: In 11g there is an intentional delay between allowing failed logon attempts to retry. For some specific application types this can cause a problem as the row cache entry is locked for the duration of the delay . This can lead to excessive row cache lock waits for DC_USERS for specific users / schemas .

After 3 successive failures a sleep delay is introduced starting at 3 seconds and extending to 10 seconds max. During each delay the user X row cache lock is held in exclusive mode preventing any concurrent logon attempt as user X (and preventing any other operation which would need the row cache lock for user X).

This can severely impact applications as any new connection would not be possible. All existing sessions will continie to work without issue.

In AUD$ we found that , one of the application schema was with high number of failed login attempts , all of which were done through database link from other database.

After troubleshooting issues with database link, database performance was back to normal.

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Be careful !! ORA-01536 after revoking DBA role from user

Many of the times we DBA get requests from AUDITORS  to apply security policies or DB hardening checklists on databases so that all schemas/users will be with least required privileges/roles.

Now here we will discuss about one of the most critical – UNILIMITED TABLESPACE privilege – is the one which allows – grantee to have unlimited quota on ALL tablespaces in the database.

Yesterday I faced very weird issue on one of the production databaase. Application owner raised a S1 call with us that his application is throwing following error:

ORA-1536: space quota exceeded for tablespace ‘JVX_DATA’.

I quickly checked all privileges/roles that were assigned to application schema & granted  UNILIMITED TABLESPACE system privilege to resolve the issue.

Issue resolved!! but why this happened now 😦  What was the reason behind this error. Definitely something must have gone wrong.

Luckly we were having audit_sys_operations parameter set to TRUE, which helped us for thorough investigation of the issue.

after going through in audit trace files, we found following audit records:

Mon Sep 1 12:19:08 2014 +02:00
LENGTH : '179'
ACTION :[24] 'revoke dba from jvx'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[7] 'JVXDB'
CLIENT TERMINAL:[5] 'pts/3'
STATUS:[3] '604'
DBID:[9] '494762650'

So in morning someone had revoked DBA role from application schema which was assigned to it earlier (This was done by one of our team member while applying security policies from auditors). IS THIS THE REASON BEHIND ORA-01536??

After searching through metalink we found note ORA-01536 After Revoking DBA Role (Doc ID 465737.1)

“We need to be very careful while revoking any grants/roles especially roles like DBA as revoking such roles will subsequently revoke all privileges which were explicitly assigned to schema along with role itself.”

“In our case UNLIMITED TABLESPACE privilege – which was granted to JVX schema explicitly – was revoked along with revoking DBA role.”

“Also we need to take an extra care while revoking UNILIMITED TABLESPACE, as this will revoke all granted quotas on any individual tablespace from the user.”

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath