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.