Using oradebug one can analyze if an instance is hung
$ sqlplus “/as sysdba”
SQL> oradebug mysetpid
Statement processed.
— no limit on trace file
SQL> oradebug unlimit
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc
— Wait for couple of minutes
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc
— Wait for couple of minutes and get systemstate dump
SQL> oradebug dump systemstate 10
Statement processed.
— Wait for couple of minutes and run systemstate dump
SQL> oradebug dump systemstate 10
Statement processed.
If one cannot connect using SQL*Plus then using PRELIM option one can connect without allocation of a session. Information on PRELIM
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Sun Dec 26 11:47:18 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
$ sqlplus /nolog
SQL> set _prelim on
SQL> connect /as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc
— Wait for couple of minutes
SQL> oradebug dump systemstate 10
Statement processed.
Attach using debugger and dump systemstate
$ gdb /u01/oracle/product/10.2.0/db_1/bin/oracle 22878
GNU gdb Red Hat Linux (6.3.0.0-1.153.el4_6.2rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type “show copying” to see the conditions.
There is absolutely no warranty for GDB. Type “show warranty” for details.
This GDB was configured as “x86_64-redhat-linux-gnu”…(no debugging symbols found)
Using host libthread_db library “/lib64/tls/libthread_db.so.1”.
Attaching to program: /u01/oracle/product/10.2.0/db_1/bin/oracle, process 22878
…
(gdb) print ksudss(10)
$1 = -1073762080
(gdb) print ksdhng(3)
$2 = 1
(gdb) detach
Detaching from program: /u01/oracle/product/10.2.0/db_1/bin/oracle, process 22878
(gdb) quit
alexlima.com 