Re: After 11.2.0.4 upgradge getting ORA-12012, ORA-04021, ORA-06512 errors on ETL job

  • From: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx>
  • To: Mark Burgess <mark@xxxxxxxxxxxxxxxxxxxxxxxxx>, "sbecker6925@xxxxxxxxx" <sbecker6925@xxxxxxxxx>
  • Date: Fri, 7 Mar 2014 17:32:05 +0000

Sandra,

When the session is waiting on library cache lock, try running this script to 
see what the blocking session is:

liblock.sql:

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 
'CLUSTER',

                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',

                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

                      11, 'PACKAGE BODY', 12, 'TRIGGER',

                      13, 'TYPE', 14, 'TYPE BODY',

                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',

                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',

                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',

                      32, 'INDEXTYPE', 33, 'OPERATOR',

                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',

                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',

                      42, 'MATERIALIZED VIEW',

                      43, 'DIMENSION',

                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',

                      48, 'CONSUMER GROUP',

                      51, 'SUBSCRIPTION', 52, 'LOCATION',

                      55, 'XML SCHEMA', 56, 'JAVA DATA',

                      57, 'SECURITY PROFILE', 59, 'RULE',

                      62, 'EVALUATION CONTEXT',

                     'UNDEFINED') object_type,

       lob.KGLNAOBJ object_name,

       lk.KGLLKMOD lock_mode_held,

       lk.KGLLKREQ lock_mode_requested,

       ses.sid,

       ses.serial#,

       ses.username

  FROM

       x$kgllk lk,

       v$session ses,

       x$kglob lob,

       v$session_wait vsw

  WHERE

   lk.KGLLKUSE = ses.saddr and

   lk.KGLLKHDL = lob.KGLHDADR

   and lob.kglhdadr = vsw.p1raw

   and vsw.event = 'library cache lock'

order by lock_mode_held desc


Also, if you run into library cache pin waits, try this to see who the blocker 
is:
libpin.sql:

select ses.inst_id,decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 
'TABLE', 3, 'CLUSTER',

                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',

                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

                      11, 'PACKAGE BODY', 12, 'TRIGGER',

                      13, 'TYPE', 14, 'TYPE BODY',

                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',

                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',

                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',

                      32, 'INDEXTYPE', 33, 'OPERATOR',

                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',

                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',

                      42, 'MATERIALIZED VIEW',

                      43, 'DIMENSION',

                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',

                      48, 'CONSUMER GROUP',

                      51, 'SUBSCRIPTION', 52, 'LOCATION',

                      55, 'XML SCHEMA', 56, 'JAVA DATA',

                      57, 'SECURITY PROFILE', 59, 'RULE',

                      62, 'EVALUATION CONTEXT',

                     'UNDEFINED') object_type,

       lob.KGLNAOBJ object_name,

       pn.KGLpnMOD lock_mode_held,

       pn.KGLpnREQ lock_mode_requested,

       ses.inst_id,

       ses.sid,

       ses.serial#,

       ses.username

  FROM

       x$kglpn pn,

       gv$session ses,

       x$kglob lob,

       gv$session_wait vsw

  WHERE

   pn.KGLpnUSE = ses.saddr and

   pn.KGLpnHDL = lob.KGLHDADR

   and lob.kglhdadr = vsw.p1raw

   and vsw.event = 'library cache pin'

order by lock_mode_held desc

Hope that helps,

-Mark

From: Mark Burgess 
<mark@xxxxxxxxxxxxxxxxxxxxxxxxx<mailto:mark@xxxxxxxxxxxxxxxxxxxxxxxxx>>
Date: Friday, March 7, 2014 at 12:28 PM
To: "sbecker6925@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>" 
<sbecker6925@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>>
Cc: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>>, 
oracle-l <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: Re: After 11.2.0.4 upgradge getting ORA-12012, ORA-04021, ORA-06512 
errors on ETL job

Have a check through the network config files to make sure everything is 
working as expected (tnsnames.ora, listener.ora).

I have seen some strange problems in the past around library cache locks that 
turned out to be related to DB link problems. It's possible that with the new 
$ORACLE_HOME that the tns alias that the remote operation refers to is unable 
to be resolved.

Regards,

Mark

On 8 Mar 2014, at 4:17 am, Sandra Becker 
<sbecker6925@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>> wrote:

As I recall, it was waiting on a library cache lock.  I'll have them run it 
again and verify.

Sandy


On Fri, Mar 7, 2014 at 9:03 AM, Mark Bobak 
<Mark.Bobak@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>> wrote:
ORA-04021 is timeout occurred while waiting to lock object.  Before that 
occurs, the session in question will be waiting.  Is it waiting on ‘library 
cache lock’?  ‘library cache pin’?  Some type of mutex wait?  Something else?

-Mark

From: Sandra Becker <sbecker6925@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>>
Reply-To: "sbecker6925@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>" 
<sbecker6925@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>>
Date: Friday, March 7, 2014 at 10:30 AM
To: oracle-l <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>

Subject: After 11.2.0.4 upgradge getting ORA-12012, ORA-04021, ORA-06512 errors 
on ETL job

OS - Solaris Sparc 10
Oracle - EE 11.2.0.4

We upgraded a dev database from 11.2.0.2 to 11.2.0.4.  One of our ETL jobs 
started failing with errors ORA-12012, ORA-04021, ORA-06512.  We have 
identified the object it is waiting to lock, but have no idea why this is 
occurring.  The object is a partitioned table.  The code did not change.  I was 
told it creates a new table and does a partition exchange.  I am not familiar 
with partition exchanges, but wonder if this might be where the issue lies. It 
also does some of it's work over a dblink to another database.

 I've opened a ticket with oracle, but I've been give only until noon Monday to 
resolve or I have to downgrade the database.  Can anyone point me in the right 
direction.  My searches so far have told me only how to find the object in 
question.

Thank you.

--
Sandy
GHX, Inc.



--
Sandy
Transzap, Inc.

Other related posts: