Question Re: Short dump DBIF_RSQL_SQL_ERROR in Technology Q&A

Short dump DBIF_RSQL_SQL_ERROR

Former Member — Thu, 10 Apr 2014 12:03:23 GMT

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

Re: Short dump DBIF_RSQL_SQL_ERROR

Former Member — Thu, 10 Apr 2014 12:06:04 GMT

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

Re: Short dump DBIF_RSQL_SQL_ERROR

Former Member — Thu, 10 Apr 2014 12:14:21 GMT

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

Re: Short dump DBIF_RSQL_SQL_ERROR

Former Member — Thu, 10 Apr 2014 12:52:37 GMT

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

Re: Short dump DBIF_RSQL_SQL_ERROR

Former Member — Thu, 10 Apr 2014 13:00:49 GMT

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.

Re: Short dump DBIF_RSQL_SQL_ERROR

Reagan — Thu, 10 Apr 2014 14:25:23 GMT

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

Re: Short dump DBIF_RSQL_SQL_ERROR

former_member182657 — Thu, 10 Apr 2014 15:10:22 GMT

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

Re: Short dump DBIF_RSQL_SQL_ERROR

Former Member — Fri, 11 Apr 2014 09:50:09 GMT

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

Re: Short dump DBIF_RSQL_SQL_ERROR

former_member182657 — Fri, 11 Apr 2014 09:56:49 GMT

Hi Samid,

Kindly check SAP Note 84348.

Regards,

Gaurav

Re: Short dump DBIF_RSQL_SQL_ERROR

former_member182657 — Fri, 11 Apr 2014 10:09:25 GMT


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

Re: Short dump DBIF_RSQL_SQL_ERROR

Former Member — Wed, 16 Apr 2014 04:43:41 GMT

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