Short dump DBIF_RSQL_SQL_ERROR

[Former Member]

Hello,

We are facing a short dump in the ERP Production system - DBIF_RSQL_SQL_ERROR.

We don't understand why this sometimes happens!

I sent to you attached to this message the short dump (DBIF_RSQL_SQL_ERROR.txt).

The error text in the database is this: ORA-00060: deadlock detected while waiting for resource

Can you help us please in way to understand how can we solve this error?

Thank you,

samid raif

DBIF_RSQL_SQL_ERROR.txt.zip

[Former Member]

I have more information about this error.

I checked the alert.log and I see the following lines:

...

Incremental checkpoint up to RBA [0x19729.e122.0], current log tail at RBA [0x1972b.399d.0]

Thu Apr 10 02:15:36 2014

ORA-00060: Deadlock detected. More info in file /oracle/AZP/sapdata/saptrace/diag/rdbms/azp/AZP/trace/AZP_ora_5741.trc.

Thu Apr 10 02:16:51 2014

Completed checkpoint up to RBA [0x1972a.2.10], SCN: 1460463379

Thu Apr 10 02:17:29 2014

...

So, I also checked the AZP_ora_5741.trc trace file:

Trace file /oracle/AZP/sapdata/saptrace/diag/rdbms/azp/AZP/trace/AZP_ora_5741.trc

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /oracle/AZP/112_64

System name:    SunOS

Node name:    liszgpora001

Release:    5.10

Version:    Generic_147440-16

Machine:    sun4v

Instance name: AZP

Redo thread mounted by this instance: 1

Oracle process number: 18

Unix process pid: 5741, image: oracle@liszgpora001

*** 2014-04-10 02:15:36.770

*** SESSION ID:(75.12339) 2014-04-10 02:15:36.770

*** CLIENT ID:(ADMINSAP) 2014-04-10 02:15:36.770

*** SERVICE NAME:(SYS$USERS) 2014-04-10 02:15:36.770

*** MODULE NAME:(CL_ESH_ADM_CONNECTIVITY=======CP) 2014-04-10 02:15:36.770

*** ACTION NAME:(3030) 2014-04-10 02:15:36.770

*** 2014-04-10 02:15:36.771

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-0001000e-0002dc99        18      75     X             26     357           X

TX-000c0000-00039011        26     357     X             18      75           X

session 75: DID 0001-0012-000001D7    session 357: DID 0001-001A-00000280

session 357: DID 0001-001A-00000280    session 75: DID 0001-0012-000001D7

Rows waited on:

  Session 75: obj - rowid = 00037127 - AAA3EnAAuAADEPtABB

  (dictionary objn - 225575, file - 46, block - 803821, slot - 65)

  Session 357: obj - rowid = 00037127 - AAA3EnAApAAI5j1AAP

  (dictionary objn - 225575, file - 41, block - 2332917, slot - 15)

----- Information for the OTHER waiting sessions -----

Session 357:

  sid: 357 ser: 11629 audsid: 804662 user: 34/SAPSR3

    flags: (0x1000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

    flags2: (0x40008) -/-

  pid: 26 O/S info: user: oraazp, term: UNKNOWN, ospid: 4515

    image: oracle@liszgpora001

  client details:

    O/S info: user: azpadm, term: , ospid: 11445

    machine: liszgpsap003 program: dw.sapAZP_DVEBMGS00@liszgpsap003 (TNS V1-V3)

    client info: 0::ZGLR_ASTREIN_EXPANSAO_MAT

    application name: SAPLSCVU, hash value=3354201086

    action name: 2246, hash value=2124259505

  current SQL:

  UPDATE "ZGLT_REG_MAT" SET "STAT_ACCAO"=:A0,"STAT_EXP_CENTRO"=:A1,"STAT_EXP_ORGVEND"=:A2,"DATUM"=:A3 WHERE "MANDT"=:A4 AND "WEBSERVICE"=:A5 AND "CODIGO_MATERIAL"=:A6

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=4f35vv3kp93vh) -----

UPDATE "ZGLT_REG_MAT" SET "STAT_ACCAO"=:A0,"STAT_EXP_CENTRO"=:A1,"STAT_EXP_ORGVEND"=:A2,"DATUM"=:A3 WHERE "MANDT"=:A4 AND "WEBSERVICE"=:A5 AND "CODIGO_MATERIAL"=:A6

*** 2014-04-10 02:15:36.785

Attempting to break deadlock by signaling ORA-00060

Can you help me please to understand this and how to solve this errors?

Thank you,

samid raif

[Former Member]

Hello,

the crucial is this part:

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-0001000e-0002dc99        18      75     X             26     357           X

TX-000c0000-00039011        26     357     X             18      75           X

session 75: DID 0001-0012-000001D7    session 357: DID 0001-001A-00000280

session 357: DID 0001-001A-00000280    session 75: DID 0001-0012-000001D7

Rows waited on:

  Session 75: obj - rowid = 00037127 - AAA3EnAAuAADEPtABB

  (dictionary objn - 225575, file - 46, block - 803821, slot - 65)

  Session 357: obj - rowid = 00037127 - AAA3EnAApAAI5j1AAP

  (dictionary objn - 225575, file - 41, block - 2332917, slot - 15)

Classic deadlock situation. In your case 75 wating 357 to be finished and wise versa. So it is not possible to move further.

And it seems that the program/application only sometimes comes to this state and then falls.

Kind regards

[Former Member]

Hello Kamil,

Ok... this happened several times, but what conclusions I can take? This is normal... and it will happen again?

Thank you,

samid raif

[Former Member]

Yes, probably it will happen again. Until you correct the program/application. Finally the system can recover from that :c)

It should be program with this name I think: ZGLR_ASTREIN_EXPANSAO_MAT.

If you are not able to correct the program, you should contact the responsible person (probably somebody from developer team) in your company.

[Former Member]

Hello Kamil and the others,

Ok, but this error ("ORA-00060: deadlock detected while waiting for resource") also happened with other programs, SAP* programs like:

Program SAPLMG65 --> SQL error 60 when accessing table "MSTA".

Program SAPLSTXD --> SQL error 60 when accessing table "STXH".

Program SAPLMG31 --> SQL error 60 when accessing table "MLAN".

Yesterday, we had these three short-dumps with SAP programs.

I searched for some error details in alert.log:

...

ORA-00060: Deadlock detected. More info in file /oracle/AZP/sapdata/saptrace/diag/rdbms/azp/AZP/trace/AZP_ora_20576.trc.

...

I also checked the trace file AZP_ora_20576.trc:

Trace file /oracle/AZP/sapdata/saptrace/diag/rdbms/azp/AZP/trace/AZP_ora_20576.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/AZP/112_64
System name: SunOS
Node name: liszgpora001
Release: 5.10
Version: Generic_147440-16
Machine: sun4v
Instance name: AZP
Redo thread mounted by this instance: 1
Oracle process number: 49
Unix process pid: 20576, image: oracle@liszgpora001

*** 2014-04-10 21:04:09.105
*** SESSION ID:(38.30307) 2014-04-10 21:04:09.105
*** CLIENT ID:(ADMINSAP) 2014-04-10 21:04:09.105
*** SERVICE NAME:(SYS$USERS) 2014-04-10 21:04:09.105
*** MODULE NAME:(ZGLR_ASTREIN_EXPANSAO_MAT) 2014-04-10 21:04:09.105
*** ACTION NAME:(953) 2014-04-10 21:04:09.105

*** 2014-04-10 21:04:09.106
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000b0017-00074644        49      38     X             44     428           X
TX-0005001c-0002d681        44     428     X             49      38           X

session 38: DID 0001-0031-000002EF session 428: DID 0001-002C-000000F5
session 428: DID 0001-002C-000000F5 session 38: DID 0001-0031-000002EF

Rows waited on:
  Session 38: obj - rowid = 0002A519 - AAAqUZAApAAAYctAAD
  (dictionary objn - 173337, file - 41, block - 100141, slot - 3)
  Session 428: obj - rowid = 00026254 - AAAmJUAAjAAEyViAAp
  (dictionary objn - 156244, file - 35, block - 1254754, slot - 41)

----- Information for the OTHER waiting sessions -----
Session 428:
  sid: 428 ser: 17101 audsid: 804240 user: 34/SAPSR3
    flags: (0x1000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 44 O/S info: user: oraazp, term: UNKNOWN, ospid: 18717
    image: oracle@liszgpora001
  client details:
    O/S info: user: azpadm, term: , ospid: 26744
    machine: liszgpsap003 program: dw.sapAZP_DVEBMGS00@liszgpsap003 (TNS V1-V3)
    client info: 0::ZGLR_ASTREIN_EXPANSAO_MAT
    application name: SAPLMG46, hash value=1647133455
    action name: 552, hash value=2314872509
  current SQL:
  UPDATE "MSTA" SET "ERSDA"=:A0,"ERNAM"=:A1,"LAEDA"=:A2,"AENAM"=:A3,"WERKS"=:A4,"LGORT"=:A5,"LGNUM"=:A6,"LGTYP"=:A7,"VKORG"=:A8,"VTWEG"=:A9,"BWKEY"=:A10,"BWTAR"=:A11 WHERE "MANDT"=:A12 AND "MATNR"=:A13 AND "STATM"=:A14 AND "ZHLER"=:A15

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=b9yvdzmxd3r4m) -----
UPDATE "STXH" SET "TDTITLE"=:A0,"TDFRELES"=:A1,"TDFUSER"=:A2,"TDFDATE"=:A3,"TDFTIME"=:A4,"TDLRELES"=:A5,"TDLUSER"=:A6,"TDLDATE"=:A7,"TDLTIME"=:A8,"TDVERSION"=:A9,"TDSTYLE"=:A10,"TDFORM"=:A11,"TDHYPHENAT"=:A12,"TDTRANSTAT"=:A13,"TDOSPRAS"=:A14,"TDMACODE1"=:A15,"TDMACODE2"=:A16,"TDTXTLINES"=:A17,"TDREF"=:A18,"TDREFOBJ"=:A19,"TDREFNAME"=:A20,"TDREFID"=:A21,"TDTEXTTYPE"=:A22,"TDCOMPRESS"=:A23,"TDOCLASS"=:A24,"LOGSYS"=:A25 WHERE "MANDT"=:A26 AND "TDOBJECT"=:A27 AND "TDNAME"=:A28 AND "TDID"=:A29 AND "TDSPRAS"=:A30

*** 2014-04-10 21:04:09.119
Attempting to break deadlock by signaling ORA-00060

*** 2014-04-10 21:04:12.134

*** 2014-04-10 21:04:12.134
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000b0017-00074644        49      38     X             44     428           X
TX-0005001c-0002d681        44     428     X             49      38           X

session 38: DID 0001-0031-000002EF session 428: DID 0001-002C-000000F5
session 428: DID 0001-002C-000000F5 session 38: DID 0001-0031-000002EF

Rows waited on:
  Session 38: obj - rowid = 0002A519 - AAAqUZAApAAAYctAAF
  (dictionary objn - 173337, file - 41, block - 100141, slot - 5)
  Session 428: obj - rowid = 00026254 - AAAmJUAAjAAEyViAAq
  (dictionary objn - 156244, file - 35, block - 1254754, slot - 42)

----- Information for the OTHER waiting sessions -----
Session 428:
  sid: 428 ser: 17101 audsid: 804240 user: 34/SAPSR3
    flags: (0x1000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 44 O/S info: user: oraazp, term: UNKNOWN, ospid: 18717
    image: oracle@liszgpora001
  client details:
    O/S info: user: azpadm, term: , ospid: 26744
    machine: liszgpsap003 program: dw.sapAZP_DVEBMGS00@liszgpsap003 (TNS V1-V3)
    client info: 0::ZGLR_ASTREIN_EXPANSAO_MAT
    application name: SAPLMG46, hash value=1647133455
    action name: 552, hash value=2314872509
  current SQL:
  UPDATE "MSTA" SET "ERSDA"=:A0,"ERNAM"=:A1,"LAEDA"=:A2,"AENAM"=:A3,"WERKS"=:A4,"LGORT"=:A5,"LGNUM"=:A6,"LGTYP"=:A7,"VKORG"=:A8,"VTWEG"=:A9,"BWKEY"=:A10,"BWTAR"=:A11 WHERE "MANDT"=:A12 AND "MATNR"=:A13 AND "STATM"=:A14 AND "ZHLER"=:A15

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=b9yvdzmxd3r4m) -----
UPDATE "STXH" SET "TDTITLE"=:A0,"TDFRELES"=:A1,"TDFUSER"=:A2,"TDFDATE"=:A3,"TDFTIME"=:A4,"TDLRELES"=:A5,"TDLUSER"=:A6,"TDLDATE"=:A7,"TDLTIME"=:A8,"TDVERSION"=:A9,"TDSTYLE"=:A10,"TDFORM"=:A11,"TDHYPHENAT"=:A12,"TDTRANSTAT"=:A13,"TDOSPRAS"=:A14,"TDMACODE1"=:A15,"TDMACODE2"=:A16,"TDTXTLINES"=:A17,"TDREF"=:A18,"TDREFOBJ"=:A19,"TDREFNAME"=:A20,"TDREFID"=:A21,"TDTEXTTYPE"=:A22,"TDCOMPRESS"=:A23,"TDOCLASS"=:A24,"LOGSYS"=:A25 WHERE "MANDT"=:A26 AND "TDOBJECT"=:A27 AND "TDNAME"=:A28 AND "TDID"=:A29 AND "TDSPRAS"=:A30

*** 2014-04-10 21:04:12.138
Attempting to break deadlock by signaling ORA-00060

*** 2014-04-10 21:04:15.139

*** 2014-04-10 21:04:15.139
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000b0017-00074644        49      38     X             44     428           X
TX-0005001c-0002d681        44     428     X             49      38           X

session 38: DID 0001-0031-000002EF session 428: DID 0001-002C-000000F5
session 428: DID 0001-002C-000000F5 session 38: DID 0001-0031-000002EF

Rows waited on:
  Session 38: obj - rowid = 0002A519 - AAAqUZAApAAAYctAAG
  (dictionary objn - 173337, file - 41, block - 100141, slot - 6)
  Session 428: obj - rowid = 00026254 - AAAmJUAAjAAEyViAAs
  (dictionary objn - 156244, file - 35, block - 1254754, slot - 44)

----- Information for the OTHER waiting sessions -----
Session 428:
  sid: 428 ser: 17101 audsid: 804240 user: 34/SAPSR3
    flags: (0x1000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 44 O/S info: user: oraazp, term: UNKNOWN, ospid: 18717
    image: oracle@liszgpora001
  client details:
    O/S info: user: azpadm, term: , ospid: 26744
    machine: liszgpsap003 program: dw.sapAZP_DVEBMGS00@liszgpsap003 (TNS V1-V3)
    client info: 0::ZGLR_ASTREIN_EXPANSAO_MAT
    application name: SAPLMG46, hash value=1647133455
    action name: 552, hash value=2314872509
  current SQL:
  UPDATE "MSTA" SET "ERSDA"=:A0,"ERNAM"=:A1,"LAEDA"=:A2,"AENAM"=:A3,"WERKS"=:A4,"LGORT"=:A5,"LGNUM"=:A6,"LGTYP"=:A7,"VKORG"=:A8,"VTWEG"=:A9,"BWKEY"=:A10,"BWTAR"=:A11 WHERE "MANDT"=:A12 AND "MATNR"=:A13 AND "STATM"=:A14 AND "ZHLER"=:A15

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=b9yvdzmxd3r4m) -----
UPDATE "STXH" SET "TDTITLE"=:A0,"TDFRELES"=:A1,"TDFUSER"=:A2,"TDFDATE"=:A3,"TDFTIME"=:A4,"TDLRELES"=:A5,"TDLUSER"=:A6,"TDLDATE"=:A7,"TDLTIME"=:A8,"TDVERSION"=:A9,"TDSTYLE"=:A10,"TDFORM"=:A11,"TDHYPHENAT"=:A12,"TDTRANSTAT"=:A13,"TDOSPRAS"=:A14,"TDMACODE1"=:A15,"TDMACODE2"=:A16,"TDTXTLINES"=:A17,"TDREF"=:A18,"TDREFOBJ"=:A19,"TDREFNAME"=:A20,"TDREFID"=:A21,"TDTEXTTYPE"=:A22,"TDCOMPRESS"=:A23,"TDOCLASS"=:A24,"LOGSYS"=:A25 WHERE "MANDT"=:A26 AND "TDOBJECT"=:A27 AND "TDNAME"=:A28 AND "TDID"=:A29 AND "TDSPRAS"=:A30

*** 2014-04-10 21:04:15.143
Attempting to break deadlock by signaling ORA-00060

*** 2014-04-10 21:05:04.228
TTX id1: 5001c id2: 2d681 ret: 0 waitopt: 2

Any tips?

Kind regards,

samid raif