Re: After 11.2.0.4 upgradge getting ORA-12012, ORA-04021, ORA-06512 errors on ETL job
Date: Sun, 9 Mar 2014 13:25:56 +0800
Message-ID: <CAMNBsZvuuiZeGrkbFsJzdxkGN-AbMcuLVMsMYW6Z8f+_zjG_qw_at_mail.gmail.com>
That shouldn't be a result of the upgrade. Possibly a performance change (better or worse) caused operations that used to be fired at different times now happening to occur at the same time. OR there was some other code / application / setup change causing this concurrency. Essentially a design bug that was "hidden" earlier. You can't have two EXCHANGE PARTITIONs succeeding concurrently against the same table.
Hemant K Chitale
http://hemantoracledba.blogspot.com
On Mar 8, 2014 3:46 AM, "Sandra Becker" <sbecker6925_at_gmail.com> wrote:
> Mark,
>
> 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-lReceived on Sun Mar 09 2014 - 06:25:56 CET