Parsing hanganalyze output for blocking sessions

The hanganalyze command was introduced in Oracle 8i. It is very useful, especially to mere mortals like us. It is formatted in a human readable format. In contrast, a systemstate dump normally contains a lot of memory addresses and offsets, which is useful to Oracle support, but not so much to us.

hanganalyze is *mandatory* to be generated in a cluster issue. It can be obtained by issuing the following from any node in the cluster which you can access.

sqlplus / as sysbda
oradebug setmypid
oradebug –g all hanganalyze 3
oradebug tracefile_name
exit

Go to $ORACLE_BASE/udump and review the output by using the following python script:

#!/home/oracle/local/bin/python

import re, string, fileinput, sys

before = []

#------------------------------------------------------------------------------------

class blocker:
  def __init__(self, value):
    self.blockedIds = []
    self.blockerId = value
  def addBlocked(self,value):
    self.blockedIds.append(value)
  def getBlockerId(self):
    return self.blockerId

#------------------------------------------------------------------------------------

def parseFile(f, a):
  START_STATS=False
  FOUND_STATS=False
  for line in fileinput.input(f):
    if START_STATS == False:
      if re.match('^State of nodes', line):
        START_STATS = True
    elif FOUND_STATS == False:
      if re.match('^[[0-9]', line):
        FOUND_STATS = True
        a.append(line)
    elif FOUND_STATS == True:
      if re.match('^session', line):
        break
      else:
        a.append(line)
  fileinput.close()

#------------------------------------------------------------------------------------

parseFile(sys.argv[1], before)

blockers = []
arr = -1
found = False
HANG = False
for e in before:
  found = False
  j = 0
  BLOCKED=string.split(e,"/")[0]
  INST=int(string.split(e,"/")[1]) + 1
  SID=string.split(e,"/")[2]
  SERIAL=string.split(e,"/")[3]
  OSPID=string.split(e,"/")[5]
  STATE=string.split(e,"/")[6]
  BLOCKER=string.split(e,"/")[9]
  if string.find(STATE,"HANG") > -1:
    HANG = True
  if string.find(BLOCKER,"[") != -1:
    for i in range(len(blockers)):
      if blockers[i].getBlockerId() == BLOCKER:
        arr = i
        found = True
        break
      else:
        found = False
    if found != True:
      arr = arr + 1
      a = blocker(BLOCKER)
      a.addBlocked("SID = " + str(SID) + " SERIAL# = " + str(SERIAL) + " on INST " + str(INST) + " (OSPID = " + str(OSPID) + ")")
      blockers.append(a)
    else:
      a = blockers[arr]
      a.addBlocked("SID = " + str(SID) + " SERIAL# = " + str(SERIAL) + " on INST " + str(INST) + " (OSPID = " + str(OSPID) + ")")

if HANG == True:
  print "------------------------------------------------------------------------"
  print "\n\nW A R N I N G ! ! !  Possible True hang found\n\n"
  print "Search for the case sensitive word HANG in " + sys.argv[1] + "\n\n"


print "------------------------------------------------------------------------"
print "BLOCKING SESSION ANALYSIS:"
print "------------------------------------------------------------------------"

for e3 in blockers:
  for e in before:
    BLOCKED=string.split(e,"/")[0]
    if BLOCKED == e3.blockerId:
      INST=int(string.split(e,"/")[1]) + 1
      SID=string.split(e,"/")[2]
      SERIAL=string.split(e,"/")[3]
      OSPID=string.split(e,"/")[5]  
      BLOCKER=string.split(e,"/")[9]
      if string.find(BLOCKER,"[") != -1:
        ALSO_BLOCKED = True
      else:
        ALSO_BLOCKED = False
      break
  print "------------------------------------------------------------------------"
  if ALSO_BLOCKED:
    print "SID = " + str(SID) + " SERIAL# = " + str(SERIAL) + " on INST " + str(INST) + " (OSPID = " + str(OSPID) + ") - Also blocked"
  else:
    print "SID = " + str(SID) + " SERIAL# = " + str(SERIAL) + " on INST " + str(INST) + " (OSPID = " + str(OSPID) + ")"
  print "...is blocking..."
  for e4 in e3.blockedIds:
    print "\t" + e4

This will generate output similar to the following:

C:\>parseHang.py wcprod1_diag_23622.trc
------------------------------------------------------------------------
BLOCKING SESSION ANALYSIS:
------------------------------------------------------------------------
------------------------------------------------------------------------
SID = 1533 SERIAL# = 35901 on INST 1 (OSPID = 28371)
...is blocking...
        SID = 1524 SERIAL# = 252 on INST 1 (OSPID = 28407)
------------------------------------------------------------------------
SID = 1524 SERIAL# = 252 on INST 1 (OSPID = 28407) - Also blocked
...is blocking...
        SID = 1504 SERIAL# = 33 on INST 2 (OSPID = )
        SID = 1507 SERIAL# = 81 on INST 2 (OSPID = )
        SID = 1511 SERIAL# = 37 on INST 2 (OSPID = )
        SID = 1516 SERIAL# = 44536 on INST 2 (OSPID = )
        SID = 1517 SERIAL# = 9722 on INST 2 (OSPID = )
        SID = 1525 SERIAL# = 32 on INST 2 (OSPID = )
        SID = 1532 SERIAL# = 372 on INST 2 (OSPID = )
        SID = 1534 SERIAL# = 45 on INST 2 (OSPID = )
        SID = 1535 SERIAL# = 61522 on INST 2 (OSPID = )
        SID = 1539 SERIAL# = 59 on INST 2 (OSPID = )
        SID = 1543 SERIAL# = 8 on INST 2 (OSPID = )
        SID = 1547 SERIAL# = 37 on INST 2 (OSPID = )
        SID = 1555 SERIAL# = 45 on INST 2 (OSPID = )
        SID = 1556 SERIAL# = 34 on INST 2 (OSPID = )
        SID = 1557 SERIAL# = 37 on INST 2 (OSPID = )
        SID = 1562 SERIAL# = 20988 on INST 2 (OSPID = )
        SID = 1581 SERIAL# = 35597 on INST 2 (OSPID = )
        SID = 1584 SERIAL# = 63056 on INST 2 (OSPID = )
        SID = 1589 SERIAL# = 47551 on INST 2 (OSPID = )
        SID = 1614 SERIAL# = 60597 on INST 2 (OSPID = )

C:\>

You may be able to identify the session that is causing the issue using output similar to what is above. It may be a background process on one of the instances, in which case you will have to terminate that instance.