10 Replies Latest reply on Sep 8, 2014 3:33 PM by spur230

    deadlock occurred but  not logged in alert log

    spur230

      We are using Oracle 11.2.0.3. We have a nightly job that drops package through dynamic sql. We sometime get following error. I have not yet been able to reproduce the error.

      "

      DECLARE

      *

      ERROR at line 1:

      ORA-00060: deadlock detected while waiting for resource

      ORA-06512: at line 24 "

       

      I checked alert log  to find more information on deadlock but could not find anything.

       

      more alert_db1.log| grep ORA-00060 returns nothings. Please suggest what else can I try.

        • 1. Re: deadlock occurred but  not logged in alert log

          spur230 wrote:

           

          We are using Oracle 11.2.0.3. We have a nightly job that drops package through dynamic sql. We sometime get following error. I have not yet been able to reproduce the error.

          "

          DECLARE

          *

          ERROR at line 1:

          ORA-00060: deadlock detected while waiting for resource

          ORA-06512: at line 24 "

           

          I checked alert log  to find more information on deadlock but could not find anything.

           

          more alert_db1.log| grep ORA-00060 returns nothings. Please suggest what else can I try.

           

           

          DROP PACKAGE  won't result in ORA-00060

           

          does batch job ever do @REMOTE; use DBLINK?

           

          does batch job run on  RAC?

          • 2. Re: deadlock occurred but  not logged in alert log
            spur230

            We do not have RAC and no it does not use dblink.

            • 3. Re: deadlock occurred but  not logged in alert log

              post full results from SQL below

               

              SELECT * FROM V$VERSION;

              • 4. Re: deadlock occurred but  not logged in alert log
                spur230

                Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                PL/SQL Release 11.2.0.3.0 - Production

                CORE 11.2.0.3.0 Production

                TNS for Linux: Version 11.2.0.3.0 - Production

                NLSRTL Version 11.2.0.3.0 - Production

                • 5. Re: deadlock occurred but  not logged in alert log

                  You have a mystery & we have no clues.

                   

                  submit a Service Request since nobody here can resolve what you report

                   

                  what results when you do as below

                   

                   

                  grep ORA-00060  alert_db1.log

                  1 person found this helpful
                  • 6. Re: deadlock occurred but  not logged in alert log
                    Amar_Singh

                    You should definitely open SR to get it checked. In the mean time you can also try setting the system event for 00060 for deadlocks explicitly, that should create dumps in the DB when the ORA-00060 deadlock event occurs. You can use syntax like 'alter system/session set events '60 context name...';

                     

                    Thanks

                    Amar

                    1 person found this helpful
                    • 7. Re: deadlock occurred but  not logged in alert log
                      Jonathan Lewis

                      a) Check that there isn't a cron job or something similar that renames and archives the alert log at the end of each batch.

                      b) Search the trace files:  "grep ORA-00060 db1_ora_*.trc" if db1 is the instance name and you're in the right directory.

                       

                      Regards

                      Jonathan Lewis

                      1 person found this helpful
                      • 8. Re: deadlock occurred but  not logged in alert log
                        spur230

                        Thanks Jonathan. I was able to get to trace information. It had following. Not sure what is the issue

                         

                         

                         

                        *** 2014-09-01 20:55:59.586

                        *** SESSION ID:(201.1) 2014-09-01 20:55:59.586

                        *** SERVICE NAME:(SYS$BACKGROUND) 2014-09-01 20:55:59.586

                         

                         

                        -------------------------------------------------------------------------------

                         

                         

                        DEADLOCK DETECTED (id=0xacab1043)

                         

                         

                        Chain Signature: 'library cache lock'<='library cache pin' (cycle)

                        Chain Signature Hash: 0xe850dcb0

                         

                         

                        The following deadlock is not an Oracle error. Deadlocks of

                        this type can be expected if certain SQL statements are

                        issued. The following information may aid in determining the

                        cause of the deadlock.

                         

                         

                        Resolving deadlock by signaling ORA-00060 to 'instance: 1, os id: 8210, session

                        id: 678'

                          dump location: /usr/local/oracle/diag/rdbms/db1/DB1/trace/DB1_ora_

                        8210.trc

                         

                         

                        Performing diagnostic dump on 'instance: 1, os id: 16093, session id: 776'

                          dump location: /usr/local/oracle/diag/rdbms/db1/DB1/trace/DB1_ora_

                        16093.trc

                         

                         

                        -------------------------------------------------------------------------------

                            Oracle session identified by:

                            {

                                        instance: 1 (db1.db1)

                                           os id: 8210

                                      process id: 59, oracle@d002

                                      session id: 678

                                session serial #: 59

                            }

                            is waiting for 'library cache pin' with wait info:

                            {

                                              p1: 'handle address'=0x131caf410

                                              p2: 'pin address'=0xd5c1e6b0

                                              p3: '100*mode+namespace'=0x18f10000010003

                                    time in wait: 1.002537 sec

                                   timeout after: 14 min 58 sec

                                         wait id: 659

                                        blocking: 1 session

                                     current sql: <none: error encountered - unable to get kgl lock no-w

                        ait>

                                    wait history:

                                      * time between current wait and wait #1: 0.384600 sec

                                      1.       event: 'db file sequential read'

                                         time waited: 0.002142 sec

                                             wait id: 658             p1: 'file#'=0x1

                                                                      p2: 'block#'=0x8da8

                                                                      p3: 'blocks'=0x1

                                      * time between wait #1 and #2: 0.003012 sec

                                      2.       event: 'db file sequential read'

                                         time waited: 0.019133 sec

                                             wait id: 657             p1: 'file#'=0x1

                                                                      p2: 'block#'=0x1599

                                                                      p3: 'blocks'=0x1

                                      * time between wait #2 and #3: 0.000529 sec

                                      3.       event: 'db file sequential read'

                                         time waited: 0.007083 sec

                                             wait id: 656             p1: 'file#'=0x1

                                                                      p2: 'block#'=0x69fd

                                                                      p3: 'blocks'=0x1

                            }

                            and is blocked by

                        => Oracle session identified by:

                            {

                                        instance: 1 (db1.db1)

                                           os id: 16093

                                      process id: 31, oracle@d002

                                      session id: 776

                                session serial #: 3335

                            }

                            which is waiting for 'library cache lock' with wait info:

                            {

                                              p1: 'handle address'=0x131caf410

                                              p2: 'lock address'=0x1324308e0

                                              p3: '100*mode+namespace'=0x18f10000010002

                                    time in wait: 1.002421 sec

                                   timeout after: never

                                         wait id: 343

                                        blocking: 1 session

                                     current sql: Begin PK_RPT.sp_SetReportStatus(:v0, :v1); End;

                                    wait history:

                                      * time between current wait and wait #1: 0.000189 sec

                                      1.       event: 'db file sequential read'

                                         time waited: 0.000020 sec

                                             wait id: 342             p1: 'file#'=0x1

                                                                      p2: 'block#'=0xb682

                                                                      p3: 'blocks'=0x1

                                      * time between wait #1 and #2: 0.004864 sec

                                      2.       event: 'db file sequential read'

                                         time waited: 0.000099 sec

                                             wait id: 341             p1: 'file#'=0x1

                                                                      p2: 'block#'=0x92b2

                                                                      p3: 'blocks'=0x1

                                      * time between wait #2 and #3: 0.000010 sec

                                      3.       event: 'Disk file operations I/O'

                                         time waited: 0.000078 sec

                                             wait id: 340             p1: 'FileOperation'=0x2

                                                                      p2: 'fileno'=0x1

                                                                      p3: 'filetype'=0x2

                            }

                            and is blocked by the session at the start of the chain.

                         

                         

                        -------------------------------------------------------------------------------

                        • 9. Re: deadlock occurred but  not logged in alert log
                          Jonathan Lewis

                          The bits you've shown (if they' are actually the things underlying your problem and not an unlucky coincidence - did you crosscheck timestamps) suggest that you're seeing a problem that ought to be reported as ORA-04020.  (viz: you're deadlocking on the library cache, not on data).  Perhaps this unexpected error number is a side effect of the problem code being called as dynamic SQL from within PL/SQL.  I think you should pass your trace file to Oracle support in an SR, they may be able to diagnose the full content more rapidly than we could.

                           

                          You shouldn't be dropping and creating packages as part of the production run, of course, so the ideal strategy is to find out why you're doing that and work out a better way of achieving the same result.  In the short term, though, you may be able to identify what concurrent actions might be causing this problem.  Here's a hypothesis I would be looking at based on just the information received so far.

                           

                          a) are there several different packages that could be dropped/recreated in the course of the batch

                          b) is it possible for concurrent sessions to be doing this type of work

                          c) is it possible that one session could running package A which includes a line to drop package B, while another session is running package B which includes a line to drop package A

                           

                          In passing, you could repeat the search of the alert logs and trace files but look for ORA-04020 as this might be something that is appearing in the traces, but gets masked by the PL/SQL.

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: deadlock occurred but  not logged in alert log
                            spur230

                            Thanks Jonathan. Looking at the log it looks like we were calling the package from another session while  it is being dropped.  I search for ORA-04020 but did not find it. Anyways ,  I think we can resolve this problem now.