If you find yourself in the middle of a hung database, you can do what follows (see Metalink for additional information)
Can login to the database
alter session set events 'immediate trace name SYSTEMSTATE level 10'; --three time waiting five minutes between
Can’t login to the database
Either…
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10
…or from the command line in linux…
ps aux | awk '{ printf $3;$3 = "" ;printf " "$0"\n" }' | sort -nr | head -10 | grep oracle | awk '{print $1,$3,$11}'
gdb
attach one_of_the_processes_listed_above
print ksudss(10)
quit
Sample output from 2 is below…
13:19:49 oracle@racperf01 /u02/admin/perfrac/udump >gdb
GNU gdb 6.6
Copyright (C) 2006 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-suse-linux".
(gdb) attach 23124
Attaching to process 23124
Reading symbols from /u01/app/oracle/product/10.2.0/bin/oracle...done.
Using host libthread_db library "/lib64/libthread_db.so.1".
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libskgxp10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libskgxp10.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libhasgen10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libhasgen10.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libskgxn2.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libskgxn2.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libocr10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libocr10.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libocrb10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libocrb10.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libocrutl10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libocrutl10.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libjox10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libjox10.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libclsra10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libclsra10.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libdbcfg10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libdbcfg10.so
Reading symbols from /u01/app/oracle/product/10.2.0/lib/libnnz10.so...done.
Loaded symbols for /u01/app/oracle/product/10.2.0/lib/libnnz10.so
Reading symbols from /usr/lib64/libaio.so.1...done.
Loaded symbols for /usr/lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...done.
[Thread debugging using libthread_db enabled]
[New Thread 47057305337440 (LWP 23124)]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
0x00002acc61b1b900 in __read_nocancel () from /lib64/libpthread.so.0
(gdb) print ksudss(10)
[Switching to Thread 47057305337440 (LWP 23124)]
$1 = 1256237080
(gdb)
The output will be under bdump or udump (or the correct diag directory in 11G and later).