ORA-00060: Deadlock detected While DBUA is running post-upgrade step of Utlrp.sql
(Doc ID 2643750.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 18.3.0.0.0 and laterInformation in this document applies to any platform.
Symptoms
When upgrading database from 12.1.0.2 to 18.6.0 RU 6 (2 node RAC) , during the post-upgrade step while utlr.sqlp was running as part of DBUA , alert log of the database shows numerous ORA-00060: Deadlock detected and after upgrade numerous application objects remained invalid.
After upgrade, again executed utlrp several times and each time once utlrp started, started getting GES deadlock in the alert log .
Even when DB was started in restricted mode on single node, same issue is faced.
During upgrade no custom DDL trigger was enabled neither any custom job or mview remained enabled.
From Database Alert log , following can be seen :
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file <oracle_base>/diag/rdbms/<dbname>/<sid>/trace/<sid>_j003_19144.trc. : Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file <oracle_base>/diag/rdbms/<dbname>/<sid>/trace/<sid>_j003_19144.trc.
2019-09-28T01:29:04.073931+05:30
Errors in file <oracle_base>/diag/rdbms/<dbname>/<sid>/trace/<sid>_j001_19140.trc:
2019-09-28T01:29:04.505491+05:30
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file <oracle_base>/diag/rdbms/<dbname>/<sid>/trace/<sid>_j001_19140.trc. : Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file <oracle_base>/diag/rdbms/<dbname>/<sid>/trace/<sid>_j001_19140.trc.
2019-09-28T01:29:07.520976+05:30
Errors in file <oracle_base>/diag/rdbms/<dbname>/<sid>/trace/<sid>_j005_19148.trc:
2019-09-28T01:29:07.959617+05:30
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file <oracle_base>/diag/rdbms/<dbname>/<sid>/trace/<sid>_j005_19148.trc. : Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file <oracle_base>/diag/rdbms/<dbname>/<sid>/trace/<sid>_j005_19148.trc.
<sid>_j006_19231.trc
<time>Sep 28 01:30:57. 2019</time>28593:2019-09-28 01:30:57.998*:ksq.c@13053:ksqdld_hdr_dump():
DEADLOCK DETECTED ( ORA-00060 ) )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors Errors
[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 serial process session holds waits serial
TX-004D001F-0014BDDC-00000000-00000000 90 399 X 65276 78 1157 X 23410
TX-006B0004-00091851-00000000-00000000 78 1157 X 23410 90 399 X 65276
----- Information for waiting sessions -----
Session 399:
sid: 399 ser: 65276 audsid: 257573130 user: 0/SYS
flags: (0x8210041) USR/- flags2: (0x40409) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 90 O/S info: user: oracle, term: UNKNOWN, ospid: 19231
image: oracle@rac1 (J006)
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 19231
machine: rac1 program: oracle@rac1 (J006)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: UTL_RECOMP_SLAVE_5, hash value=13280109
current SQL:
delete from plscope_identifier$ where obj#=:1
Session 1157:
sid: 1157 ser: 23410 audsid: 257573131 user: 0/SYS
flags: (0x8210041) USR/- flags2: (0x40409) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 78 O/S info: user: oracle, term: UNKNOWN, ospid: 19140
image: oracle@rac1 (J001)
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 19140
machine: rac1 program: oracle@rac1 (J001)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: UTL_RECOMP_SLAVE_2, hash value=1250098927
current SQL:
delete from plscope_identifier$ where obj#=:1
----- End of information for waiting sessions -----
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |