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

Thanks again for your script. I identified the sql the blocking sessions were running. All session were running the ALTER TABLE tname EXCHANGE PARTITION... It appears the code kicks multiple sessions that try to exchange partitions on the same table. I'm guessing different partitions, but I haven't nailed that down yet.

Sandy

On Fri, Mar 7, 2014 at 11:23 AM, Sandra Becker <sbecker6925_at_gmail.com>wrote:

> 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.
>

-- 
Sandy
Transzap, Inc.

--
http://www.freelists.org/webpage/oracle-l