My Oracle Support Banner

ORA-00060: Deadlock detected While DBUA is running post-upgrade step of Utlrp.sql (Doc ID 2643750.1)

Last updated on AUGUST 05, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 18.3.0.0.0 and later
Information 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.

 

From Log file:
<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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.