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

  • From: Sandra Becker <sbecker6925@xxxxxxxxx>
  • To: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx>
  • Date: Fri, 7 Mar 2014 11:23:46 -0700

I verified that it is a library cache lock.  Thanks for the scripts, Mark.
I will kick off another run and try them.

Sandy


On Fri, Mar 7, 2014 at 10:32 AM, Mark Bobak <Mark.Bobak@xxxxxxxxxxxx> wrote:

>  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>
> Date: Friday, March 7, 2014 at 12:28 PM
> To: "sbecker6925@xxxxxxxxx" <sbecker6925@xxxxxxxxx>
> Cc: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx>, oracle-l <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> 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>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>
>> Reply-To: "sbecker6925@xxxxxxxxx" <sbecker6925@xxxxxxxxx>
>> Date: Friday, March 7, 2014 at 10:30 AM
>> To: oracle-l <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.
>
>


-- 
Sandy
Transzap, Inc.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2014