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
