cancel
Showing results for 
Search instead for 
Did you mean: 
Read only

Short dump DBIF_RSQL_SQL_ERROR

Former Member
0 Likes
1,423

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

Accepted Solutions (0)

Answers (6)

Answers (6)

Former Member
0 Likes

Check whether the table is consistent or not(Go Se11--> <table name>--> display--> Utilities--> database object --> Check). If it is inconsistent, solve the issue at first.

Asad

former_member182657
Active Contributor
0 Likes

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

As per your current log you are getting above error which i pointed earlier in my reply.So first please share hardware details with us & re check for what this ZGLR_ASTREIN_EXPANSAO_MAT ? (Already pointed by RB )

Regards,

Gaurav

former_member182657
Active Contributor
0 Likes

Hi Samid,

Kindly check SAP Note 84348.

Regards,

Gaurav

former_member182657
Active Contributor
0 Likes

Hi Samid,

Already suggested by others experts, this is completely related with developer team as the issue comes on customized Z table.And for mentioned error


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

I suggest you to work out for below at your end :

There are several causes for this error:


  • Too high activity - Re-running the job during a less busy period can fix this ORA-00060 deadlock error.
  • Poor application design - As noted in the trace file:  "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."



  • RAM shortage - In rare cases this ORA-00060 deadlock error can happen with a RAM shortage at the Java layer or Oracle layer.

Regards,

Gaurav

Reagan
Product and Topic Expert
Product and Topic Expert
0 Likes

This is basically an issue with the application.

The database is working in the way it is designed.


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

The deadlock happened during an update in the table ZGLT_REG_MAT.

Check what the program ZGLR_ASTREIN_EXPANSAO_MAT2 is for.

Regards

RB

Former Member
0 Likes

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
0 Likes

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
0 Likes

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
0 Likes

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
0 Likes

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