Re: After 11.2.0.4 upgradge getting ORA-12012, ORA-04021, ORA-06512 errors on ETL job
Date: Fri, 7 Mar 2014 11:23:46 -0700
Message-ID: <CAJzM94BNpERr_KjniNUaQyhvbWk-e7VUG-9cyv-ED=8HSjfEpw_at_mail.gmail.com>
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_at_proquest.com> 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_at_burgess-consulting.com.au>
> Date: Friday, March 7, 2014 at 12:28 PM
> To: "sbecker6925_at_gmail.com" <sbecker6925_at_gmail.com>
> Cc: Mark Bobak <Mark.Bobak_at_ProQuest.com>, oracle-l <oracle-l_at_freelists.org
> >
> 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_at_gmail.com> 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_at_proquest.com>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_at_gmail.com>
>> Reply-To: "sbecker6925_at_gmail.com" <sbecker6925_at_gmail.com>
>> Date: Friday, March 7, 2014 at 10:30 AM
>> To: oracle-l <oracle-l_at_freelists.org>
>>
>> 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. -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 07 2014 - 19:23:46 CET