EM Repository: ORA-00060: Deadlock detected error in alert log

Hello!
Some time ago I found some ORA-00060: Deadlock detected. errors in a client OEM database… Like this:

Thu Dec 22 09:01:55 2016
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1757.trc.
Thu Dec 22 09:02:07 2016
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1759.trc.
ORA-00060: Deadlock detected. More info in file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/oemdb_ora_1759.trc.

In summary, after investigating the trace (as per below), found that the issueis caused by the following command:

SELECT CURRENT_STATUS,TARGET_GUID FROM EM_CURRENT_AVAILABILITY WHERE TARGET_GUID IN (SELECT TARGET_GUID FROM MGMT_TARGETS WHERE EMD_URL = :B4 AND (DYNAMIC_PROPERTY_STATUS = :B3 OR TARGET_TYPE IN (:B2 , :B1 ))) ORDER BY TARGET_GUID FOR UPDATE;

I am just worndering WHY Oracle developers used this “FOR UPDATE“? Ok, now how to solve?
This is documented in Bug 18915413: DEADLOCK DETECTED IN MGMT_USER_CONTEXT and can be solved by applying Patch 18150714 on OMS.
It is also described in MOS: EM 12.1.0.4: ORA-60 Deadlocks on SELECT CURRENT_STATUS,TARGET_GUID FROM EM_CURRENT_AVAILABILITY in repository alert log (Doc ID 2063089.1)

Below the complete trace file:

Trace file /oracle/oemdb/diag/rdbms/oemdb/oemdb/trace/emdb_ora_1759.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/emdb/product/11.2.0.3
System name: Linux
Node name: server.
Release: 2.6.32-358.18.1.el6.x86_64
Version: #1 SMP Fri Aug 2 17:04:38 EDT 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: emdb
Redo thread mounted by this instance: 1
Oracle process number: 72
Unix process pid: 1759, image: oracle@server.
*** 2016-11-09 19:31:06.845
*** SESSION ID:(18.41411) 2016-11-09 19:31:06.845
*** CLIENT ID:() 2016-11-09 19:31:06.845
*** SERVICE NAME:(SYS$USERS) 2016-11-09 19:31:06.845
*** MODULE NAME:(OEM.PbsSystemPool) 2016-11-09 19:31:06.845
*** ACTION NAME:(PingHeartBeatWork::1) 2016-11-09 19:31:06.845

*** 2016-11-09 19:31:06.845
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-000a001b-000dd634 72 18 X 71 1926 S
TX-000e0000-0006a47a 71 1926 X 72 18 X

session 18: DID 0001-0048-00000007 session 1926: DID 0001-0047-0000000C
session 1926: DID 0001-0047-0000000C session 18: DID 0001-0048-00000007

Rows waited on:
Session 18: obj - rowid = 00014360 - AAAUNgAAGAABISoAAZ
(dictionary objn - 82784, file - 6, block - 296104, slot - 25)
Session 1926: no row

----- Information for the OTHER waiting sessions -----
Session 1926:
sid: 1926 ser: 61281 audsid: 34077302 user: 86/SYSMAN
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 71 O/S info: user: grid, term: UNKNOWN, ospid: 1757
image: oracle@server.
client details:
O/S info: user: oraemdb, term: unknown, ospid: 1234
machine: bwi-occoms-p01. program: OMS
client info: bwi-occoms-p01.:4889_Management_Service
application name: OEM.PbsSystemPool, hash value=3705687484
action name: PingHeartBeatWork::0, hash value=3185916132
current SQL:
SELECT CURRENT_STATUS FROM EM_CURRENT_AVAILABILITY WHERE TARGET_GUID IN (SELECT TARGET_GUID FROM MGMT_TARGETS WHERE EMD_URL = :B2 AND PROMOTE_STATUS = :B1 ) ORDER BY TARGET_GUID FOR UPDATE

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

Information for THIS session:
*** 2016-11-09 19:31:06.897
----- Current SQL Statement for this session (sql_id=b351w22ag16tg) -----
SELECT A.TARGET_GUID TARGET_GUID, T.EMD_URL EMD_URL, T.ENTITY_NAME TARGET_NAME, TT.TARGET_TYPE TARGET_TYPE, T.MONITORING_MODE MONITORING_MODE, TT.TYPE_RELATIONSHIP FROM MGMT_TARGET_AGENT_ASSOC A, EM_MANAGEABLE_ENTITIES T, MGMT_TARGET_TYPES TT WHERE A.AGENT_GUID = :B3 AND A.TARGET_GUID = T.ENTITY_GUID AND A.PROMOTE_STATUS >= :B2 AND T.MANAGE_STATUS = :B1 AND T.ENTITY_TYPE = TT.TARGET_TYPE ORDER BY TARGET_TYPE, TARGET_NAME FOR UPDATE OF T.MANAGE_STATUS
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x31c5aed80 750 package body SYSMAN.EM_MASTER_AGENT
0x31b49f940 3076 package body SYSMAN.EM_SEVERITY
0x31b49f940 4497 package body SYSMAN.EM_SEVERITY
0x31d832550 1414 SYSMAN.EM_VIOLATION_CHECKS
0x31d6458a0 229 package body SYSMAN.EM_PING
0x31d6458a0 532 package body SYSMAN.EM_PING
0x31d6458a0 940 package body SYSMAN.EM_PING
0x31d6458a0 1105 package body SYSMAN.EM_PING
0x31a421a88 1 anonymous block

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.