當數據庫出現嚴重的性能問題或者hang了的時候，服務器端sqlplus也無法連接時，此時如果想獲取數據庫當前的狀態信息，以便事後診斷，那麼我們非常需要通過systemstate dump來知道進程在做什麼，在等待什麼，誰是資源的持有者，誰阻塞了別人。在出現上述問題時，及時收集systemstate dump非常有助於問題原因的分析。ORACLE 10g 開始，sqlplus提供了這麼一個功能參數-prelim,在sqlplus無法連接的情況下，連接登錄到數據庫。下面關於這些知識點的一個總結
There are two ways to connect to sqlplus using a preliminary connection.
sqlplus -prelim / as sysdba
sqlplus /nolog
set _prelim on
connect / as sysdba
用sysdba登錄到數據庫上：
$sqlplus / as sysdba
或者
$sqlplus -prelim / as sysdba <==當數據庫已經很慢或者hang到無法連接
Collection commands for Hanganalyze and Systemstate: Non-RAC:
Sometimes, database may actually just be very slow and not actually hanging. It is therefore recommended, where possible to get 2 hanganalyze and 2 systemstate dumps in order to determine whether processes are moving at all or whether they are "frozen".
Hanganalyze
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- Wait one minute before getting the second hanganalyze
oradebug hanganalyze 3
oradebug tracefile_name
exit
Systemstate
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug dump systemstate 266
oradebug tracefile_name
exit
Collection commands for Hanganalyze and Systemstate: RAC
There are 2 bugs affecting RAC that without the relevant patches being applied on your system, make using level 266 or 267 very costly. Therefore without these fixes in place it highly unadvisable to use these level
For information on these patches see:
Document 11800959.8 Bug 11800959 - A SYSTEMSTATE dump with level >= 10 in RAC dumps huge BUSY GLOBAL CACHE ELEMENTS - can hang/crash instances
Document 11827088.8 Bug 11827088 - Latch 'gc element' contention, LMHB terminates the instance
Note: both bugs are fixed in 11.2.0.3.
Collection commands for Hanganalyze and Systemstate: RAC with fixes for bug 11800959 and bug 11827088
For 11g:
sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
oradebug -g all dump systemstate 266
exit
Collection commands for Hanganalyze and Systemstate: RAC without fixes for Bug 11800959 and Bug 11827088
sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
exit
For 10g, run oradebug setmypid instead of oradebug setorapname reco:
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
exit
In RAC environment, a dump will be created for all RAC instances in the DIAG trace file for each instance.
那麼我們現在來看一個例子吧：
[oracle@DB-Server ~]$ sqlplus -prelim / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 2 16:31:03 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/SCM2/udump/scm2_ora_13598.trc
SQL> exit
Disconnected from ORACLE
告警日志裡面會看到類似這樣的信息：
Wed Mar 02 16:32:08 CST 2016
System State dumped to trace file
Wed Mar 02 16:32:48 CST 2016
System State dumped to trace file /u01/app/oracle/admin/xxx/udump/scm2_ora_13598.trc
$ORACLE_BASE/admin/ORACLE_SID/udump/ 下找到對應的trc文件，如下所示，你會看到類似下面的一些信息.
systemstate dump有多個級別：
2: dump (不包括lock element)
10: dump
11: dump + global cache of RAC
256: short stack （函數堆棧）
258: 256+2 -->short stack +dump(不包括lock element)
266: 256+10 -->short stack+ dump
267: 256+11 -->short stack+ dump + global cache of RAC
level 11和 267會 dump global cache, 會生成較大的trace 文件，一般情況下不推薦。一般情況下，如果進程不是太多，推薦用266，因為這樣可以dump出來進程的函數堆棧，可以用來分析進程在執行什麼操作。但是生成short stack比較耗時，如果進程非常多，比如2000個進程，那麼可能耗時30分鐘以上。這種情況下，可以生成level 10 或者 level 258， level 258 比 level 10會多收集short short stack, 但比level 10少收集一些lock element data.
雖然通過system state dump收集了進程的相關，但是如何有效的解讀相關信息，並診斷分析問題是一個不小的難題和挑戰！
參考資料：
https://blogs.oracle.com/Database4CN/entry/systemstate_dump_%E4%BB%8B%E7%BB%8D
http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=352993211736965&parent=DOCUMENT&sourceId=68738.1&id=452358.1&_afrWindowMode=0&_adf.ctrl-state=z7hwh19s9_319
