Saturday, May 11, 2013

Global Enqueue Services Deadlock detected – TX deadlock in Exclusive(x) mode


In single instance environment, when a deadlock happens, it often reports ORA-60: Detecting and Resolving Locking Conflicts and Ora-00060 errors. In a Real Application Cluster (RAC) environment, instead of ORA-60, one would see the following messages in database alert log:

Global Enqueue Services Deadlock detected. More info in file
/u01/oracle/diag/rdbms/node/node1/trace/node1_ora_763770.trc.
OR

Global Enqueue Services Deadlock detected. More info in file
/u01/oracle/diag/rdbms/node/node1/trace/node1_lmd0_87933.trc.



You can see similar below lines in the trace file.
**************
---------resource 0x2efbe8510----------------------
resname       : [0xae0003][0x5149ef],[TX]
Local node    : 3
dir_node      : 2
master_node   : 2
hv idx        : 70
hv last r.inc : 138
current inc   : 140
hv status     : 0
hv master     : 3
.
.
.
.
.
.
.
----------enqueue 0x30d34bd30------------------------
lock version     : 232019
Owner node       : 3
grant_level      : KJUSEREX
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x3170afe58
procp            : 0x30c334570
pid              : 13495
proc version     : 0

.
.
.
.
.
 

Global Wait-For-Graph(WFG) at ddTS[0.260b8] :

BLOCKED 0x30dcd0708 5 wq 2 cvtops x1001 TX 0xae0003.0x5149ef [8B000-0004-0036637E] 3
BLOCKER 0x30d29ce90 5 wq 1 cvtops x28   TX 0xae0003.0x5149ef [5D000-0003-001E8444] 2
BLOCKED 0x30e7999b0 5 wq 2 cvtops x1001 TX 0x270010.0x4c2def [5D000-0003-001E8444] 2
BLOCKER 0x30d34bd30 5 wq 1 cvtops x28   TX 0x270010.0x4c2def [8B000-0004-0036637E] 3
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0xae0003][0x5149ef],[TX]

  

**********

Here  mode 5 is exclusive lock and  instance# starts from 0

 

Above deadlock means two sessions involved in TX-0xae0003-0x5149ef and TX-0x270010-0x4c2def forms a deadlock, both sessions are from instance 4.

This is a typical application transaction TX enqueue lock, usually caused by SQL commit sequence and high concurrency. To avoid such deadlock, application code and logic need to be modified.
The application and SQL involved in the deadlock can be found in lmd0 or foreground trace (check all instances).

Friday, May 10, 2013

Displays LSNR SERVICE or STAT details

For example, if the sample script is saved to a file named test_lsnr, execute the following commands:
    chmod +x test_lsnr

If the sample script is saved to a file named test_lsnr, execute the following commands:
    ./test_lsnr -serv
 OR
    ./test_lsnr -stat


Script

 #!/bin/bash
 #
 #
 # Usage: test_lsnr [-stat||-serv]
 #
 if [ "$1" = "-serv" ]; then
 switch="service"
 elif [ "$1" = "-stat" ]; then
 switch="status"
 else
 echo "Usage: test_lsnr [-stat||-serv]"
 exit
 fi
 echo "Switch = $switch"
 # PS, GREP and AWK must be executable by the script user
 for lsnr in `ps -ef | grep tnslsnr | grep -v grep | awk '{ print $9}'`; do
 TEMP=`ps -ef | grep tnslsnr | grep " ${lsnr} " | grep -v grep | awk '{print $8}'`
 export ORACLE_HOME=`printf "%s" "${TEMP%\/bin\/tnslsnr}"`
 echo "Listener Name: ${lsnr}"
 echo "Home: ${ORACLE_HOME}"
 ${ORACLE_HOME}/bin/lsnrctl ${switch} ${lsnr}
 echo ""
 echo ""
 done

Sample output

>test_lsnr -stat


Switch = status
Listener Name: LISTENER_node1
Home: /home/oracle/oracle/product/11.1.0/asm
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 12-AUG-2011 01:54:15
Copyright (c) 1991, 2008, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.sftech.corp.biz)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_node1
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                12-JUN-2011 00:47:03
Uptime                    31 days 1 hr. 7 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/oracle/product/11.1.0/asm/network/admin/listener.ora
Listener Log File         /home/oracle/oracle/diag/tnslsnr/node1/listener_node1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX.XXX.XX.XXX)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX.XXX.XX.XXX)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "instance" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
  Instance "instance2", status READY, has 1 handler(s) for this service...
Service "instanceXDB" has 2 instance(s).
  Instance "instance1", status READY, has 1 handler(s) for this service...
  Instance "instance2", status READY, has 1 handler(s) for this service...
Service "instance_XPT" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
  Instance "instance2", status READY, has 1 handler(s) for this service...
Service "conn1" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
  Instance "instance2", status READY, has 1 handler(s) for this service...
The command completed successfully

> test_lsnr - serv


Switch = service
Listener Name: LISTENER_node1
Home: /home/oracle/oracle/product/11.1.0/asm
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 12-AUG-2011 01:57:45
Copyright (c) 1991, 2008, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.test.com)(PORT=1521)(IP=FIRST)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "instance" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.test.com)(PORT=1521))
      "DEDICATED" established:1097331 refused:0 state:ready
         LOCAL SERVER
  Instance "instance2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2201353 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.test.com)(PORT=1521))
Service "instanceXDB" has 2 instance(s).
  Instance "instance1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: node1.test.com, pid: 4904>
         (ADDRESS=(PROTOCOL=tcp)(HOST=node1.test.com)(PORT=3909))
  Instance "instance2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: node2.test.com, pid: 5389>
         (ADDRESS=(PROTOCOL=tcp)(HOST=node2.test.com)(PORT=54323))
Service "instance_XPT" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.test.com)(PORT=1521))
      "DEDICATED" established:1097331 refused:0 state:ready
         LOCAL SERVER
  Instance "instance2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2201353 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.test.com)(PORT=1521))
Service "conn1" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.test.com)(PORT=1521))
      "DEDICATED" established:1097331 refused:0 state:ready
         LOCAL SERVER
  Instance "instance2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2201353 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.test.com)(PORT=1521))
The command completed successfully