Debugging Hung Database Using oradebug With prelim Connection

I accidentally found a great article from Arup Nanda about this over here then decided to give this a test on my database.

When trying the steps in Arup’s article, which are as follow:

sqlplus -prelim / as sysdba
oradebug setmypid
oradebug hanganalyze 12

I am getting this error in my trace file.

ERROR: Can not perform hang analysis dump without a process
 state object and a session state object.
 ( process=0x0, sess=0x0 )

Apparently the way to use oradebug with prelim has changed in Oracle 11.2.0.2 as written by Tanel in his article here. You need to attached your oradebug “session” with an active and fully logged on server process.

Here are the steps I did and the trace output shows the hanging session which I created by updating the same record using two different sessions without committing.

oracle@oracle-dev:DEV$ ps -ef|grep oracleDEV
 oracle 237 4235 0 14:17:38 pts/2 0:00 grep oracleDEV
 oracle 22305 2426 0 14:42:24 ? 0:00 oracleDEV (LOCAL=NO)
 oracle 9291 2426 0 15:31:57 ? 0:06 oracleDEV (LOCAL=NO)
 oracle 4304 2426 0 18:09:41 ? 0:13 oracleDEV (LOCAL=NO)
 oracle 22092 2426 0 14:41:52 ? 0:01 oracleDEV (LOCAL=NO)

Choose any of the process

sqlplus -prelim / as sysdba
oradebug setospid <strong>4304</strong>
oradebug dump hanganalyze 12

Here is the content of my trace file that shows the hanging

Chains most likely to have caused the hang:

[a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
 Chain 1 Signature Hash: 0x38c48850

====snipped====

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
 Oracle session identified by:
 {
 instance: 1 (dev.dev)
 os id: 21709
 process id: 78, oracle@lasoracle-dev2z (TNS V1-V3)
 session id: 2399
 session serial #: 5391
 }
 is waiting for 'enq: TX - row lock contention' with wait info:
 {
 p1: 'name|mode'=0x54580006
 p2: 'usn<<16 | slot'=0x340011
 p3: 'sequence'=0x57
 time in wait: 6 min 5 sec
 timeout after: never
 wait id: 56
 blocking: 0 sessions
 wait history:
 * time between current wait and wait #1: 0.000906 sec
 1. event: 'db file sequential read'
 time waited: 0.003456 sec
 wait id: 55 p1: 'file#'=0x14
 p2: 'block#'=0x19472
 p3: 'blocks'=0x1
 * time between wait #1 and #2: 0.000131 sec
 2. event: 'db file sequential read'
 time waited: 0.007404 sec
 wait id: 54 p1: 'file#'=0x14
 p2: 'block#'=0x16fa6
 p3: 'blocks'=0x1
 * time between wait #2 and #3: 0.000095 sec
 3. event: 'db file sequential read'
 time waited: 0.004728 sec
 wait id: 53 p1: 'file#'=0x14
 p2: 'block#'=0xa61b
 p3: 'blocks'=0x1
 }
 and is blocked by
 => Oracle session identified by:
 {
 instance: 1 (dev.dev)
 os id: 21839
 process id: 88, oracle@lasoracle-dev2z (TNS V1-V3)
 session id: 1384
 session serial #: 13075
 }
 which is waiting for 'SQL*Net message from client' with wait info:
 {
 p1: 'driver id'=0x62657100
 p2: '#bytes'=0x1
 time in wait: 6 min 12 sec
 timeout after: never
 wait id: 23
 blocking: 1 session
 wait history:
 * time between current wait and wait #1: 0.000014 sec
 1. event: 'SQL*Net message to client'
 time waited: 0.000003 sec
 wait id: 22 p1: 'driver id'=0x62657100
 p2: '#bytes'=0x1
 * time between wait #1 and #2: 0.008904 sec
 2. event: 'SQL*Net message from client'
 time waited: 51.527820 sec
 wait id: 21 p1: 'driver id'=0x62657100
 p2: '#bytes'=0x1
 * time between wait #2 and #3: 0.000002 sec
 3. event: 'SQL*Net message to client'
 time waited: 0.000001 sec
 wait id: 20 p1: 'driver id'=0x62657100
 p2: '#bytes'=0x1
 }

Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850

====snipped====