如果ORACLE已經連線不上如果產生一個資料庫級別的systemstate dump檔案

背景

  如果當你已經無法正常連線到oracle時，可能資料庫此時已經hang,透過sqlplus -prelim '/as sysdba'也無法登陸資料庫，可以透過作業系統級別的工具
產生oracle systemstate dump檔案，進行資料庫的全面分析與診斷  

結論

  1,資料庫無法進入或HANG時，可能過gdb產生ORACLE的SYSTEMSTATE DUMP檔案
  2,LINUX作業系統，ORACLE11G
  3,在unix請使用dbx

測試

1,產生一個測試遠端會話
[oracle@seconary ~]$ sqlplus scott/system@guowang




[oracle@seconary admin]$ ps -ef|grep LOCAL
oracle   17446     1  0 04:28 ?        00:00:00 oracleguowang (LOCAL=NO)
oracle   17545  8032  0 04:28 pts/0    00:00:00 grep LOCAL


2，用GDB跟蹤這個遠端會話
[oracle@seconary ~]$ gdb $ORACLE_HOME/bin/oracle 17446
GNU gdb Fedora (6.8-37.el5)
Copyright (C) 2008 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later < /> This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu"...
(no debugging symbols found)
Attaching to program: /oracle/product/11.2.0/db_1/bin/oracle, process 17446
Reading symbols from /oracle/product/11.2.0/db_1/lib/libodm11.so...(no debugging symbols found)...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libodm11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libcell11.so...(no debugging symbols found)...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libcell11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libskgxp11.so...(no debugging symbols found)...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libskgxp11.so
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /oracle/product/11.2.0/db_1/lib/libnnz11.so...(no debugging symbols found)...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libnnz11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libclsra11.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libclsra11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libdbcfg11.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libdbcfg11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libhasgen11.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libhasgen11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libskgxn2.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libskgxn2.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libocr11.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libocr11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libocrb11.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libocrb11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libocrutl11.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libocrutl11.so
Reading symbols from /oracle/product/11.2.0/db_1/lib/libasmclnt11.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libasmclnt11.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 0x2ab13e44ae80 (LWP 17446)]
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...print 
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
Reading symbols from /lib64/libnss_files.so.2...done.
Loaded symbols for /lib64/libnss_files.so.2
Reading symbols from /oracle/product/11.2.0/db_1/lib/libnque11.so...done.
Loaded symbols for /oracle/product/11.2.0/db_1/lib/libnque11.so
Reading symbols from /lib64/libnss_dns.so.2...done.
Loaded symbols for /lib64/libnss_dns.so.2
Reading symbols from /lib64/libresolv.so.2...done.
Loaded symbols for /lib64/libresolv.so.2


0x0000003d5660d590 in __read_nocancel () from /lib64/libpthread.so.0
(gdb) 
(gdb) 
(gdb) print 
The history is empty.




3，在GDB介面輸入如下命令，即print ksudss(10)
(gdb) print ksudss(10)
$1 = 0


4,退出GDB介面，先輸入QUIT後輸入Y
(gdb) quit
The program is running.  Quit anyway (and detach it)? (y or n) y
Detaching from program: /oracle/product/11.2.0/db_1/bin/oracle, process 17446
[oracle@seconary ~]$ 


5，在USER_DUMP_DEST獲取上述產生的SYSTEMSTATE DUMP檔案
SQL> show parameter user_dump


NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
user_dump_dest                       string                 /oracle/diag/rdbms/guowang/guo
                                                            wang/trace


[oracle@seconary ~]$ cd /oracle/diag/rdbms/guowang/guowang/trace
[oracle@seconary trace]$ ls -l *17446*
-rw-r----- 1 oracle oinstall 3036048 Oct 17 04:31 guowang_ora_17446.trc


[oracle@seconary trace]$ more guowang_ora_17446.trc
Trace file /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_17446.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/db_1
System name:    Linux
Node name:      seconary
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: guowang
Redo thread mounted by this instance: 1
Oracle process number: 74
Unix process pid: 17446, image: oracle@seconary




*** 2015-10-17 04:30:54.862
*** SESSION ID:(211.52312) 2015-10-17 04:30:54.862
*** CLIENT ID:() 2015-10-17 04:30:54.862
*** SERVICE NAME:(guowang) 2015-10-17 04:30:54.862
*** MODULE NAME:(SQL*Plus) 2015-10-17 04:30:54.862
*** ACTION NAME:() 2015-10-17 04:30:54.862
 
===================================================
SYSTEM STATE (level=10)
------------
System global information:
     processes: base 0xdd51ae00, size 1000, cleanup 0xdd59fc40
     allocation: free sessions 0xdd9b40d0, free calls (nil)
     control alloc errors: 0 (process), 0 (session), 0 (call)
     PMON latch cleanup depth: 0
     seconds since PMON's last scan for dead processes: 25
     system statistics:
0 OS CPU Qt wait time
--More--(0%)