如果Oracle資料庫hang住了，對Oracle做system dump，或做 hang analyze，是研究和解決問題的有效辦法。如果能夠連線資料庫，並能夠進行操作，那麼用oradebug是簡單快捷的辦法。

SYS@anqing1(rac1)> oradebug setmypid

Statement processed.

SYS@anqing1(rac1)> oradebug dump systemstate 10

Statement processed.

SYS@anqing1(rac1)> oradebug tracefile_name

/u01/app/oracle/admin/anqing/udump/anqing1_ora_8725.trc

SYS@anqing1(rac1)> oradebug close_trace

Statement processed.

在系統hung的時候，systemstate基本等同於hanganalyze，可以用於診斷system hung。但是如果直接來檢視這個trace file，比較麻煩，這時候可以使用ass.awk 指令碼來幫助我們檢視trace file。

Ass.Awk 指令碼在LTOM的ltom431\ltom\tom_base\tom\src目錄下，其名稱是：ass109.awk。這個是目前的最新版本，ass.awk 指令碼可以幫助我們分析和格式化system state dump。

LTOM的MOS說明：

LTOM - TheOn-Board Monitor User Guide [ID 352363.1]

LTOM 的下載地址：

在看AWK, awk是一種程式語言，用於在linux/unix下對文字和資料進行處理。資料可以來自標準輸入、一個或多個檔案，或其它命令的輸出。

關於AWK的更多內容參考：

Linux awk 命令說明

http://blog.csdn.net/tianlesoftware/article/details/6278273

我們這裡使用的是AWR命令的 -fscripfile 引數，即從指令碼檔案中讀取awk命令。這個指令碼就是我們LTOM中的ass109.awk。

示例：

[oracle@rac1 src]$ awk -f ass109.awk /u01/app/oracle/admin/anqing/udump/anqing1_ora_8725.trc

Starting Systemstate 1

...................................

Ass.Awk Version 1.0.9 - Processing/u01/app/oracle/admin/anqing/udump/anqing1_ora_8725.trc

System State 1

~~~~~~~~~~~~~~~~

1:

2: waiting for 'pmon timer' wait

3: waiting for 'DIAG idle wait' wait

4: waiting for 'rdbms ipc message' wait

5: waiting for 'rdbms ipc message' wait

6: waiting for 'ges remote message' wait

7: waiting for 'gcs remote message' wait

8: waiting for 'rdbms ipc message' wait

9: waiting for 'rdbms ipc message' wait

10: waiting for 'rdbms ipc message' wait

11: waiting for 'rdbms ipc message' wait

12: waiting for 'buffer busy waits'(2,89,21) wait

13: waiting for 'rdbms ipc message' wait

14: waiting for 'enq: JS - queue lock'[Enqueue JS-00000000-00000001] wait

15: waiting for 'buffer busy waits'(2,89,21) wait

16: waiting for 'rdbms ipc message' wait

17:

18:

19: waiting for 'rdbms ipc message' wait

20: waiting for 'log file switch (archivingneeded)' wait

21: waiting for 'ASM background timer' wait

22: waiting for 'rdbms ipc message' wait

23: waiting for 'log file switch (archivingneeded)' wait

24: last wait for 'SQL*Net message fromclient'

25: for 'Streams AQ: waiting for messagesin the queue' wait

26: waiting for 'Streams AQ: qmn slave idlewait' wait

27: waiting for 'rdbms ipc message' wait

28: waiting for 'rdbms ipc message' wait

29: waiting for 'class slave wait' wait

30:

31: waiting for 'Streams AQ: qmncoordinator idle wait' wait

32: waiting for 'SQL*Net message fromclient' wait

34: waiting for 'SQL*Net message fromclient' wait

36: waiting for 'jobq slave wait' wait

37: for 'Streams AQ: waiting for timemanagement or cleanup tasks' wait

Blockers

~~~~~~~~

Above is a list of all the processes. If they are waiting for a resource

then it will be given in square brackets. Below is a summary of the

waited upon resources, together with the holder of that resource.

Notes:

~~~~~

o A process id of '???' implies that the holder was not found in the

systemstate.

Resource Holder State

Enqueue JS-00000000-00000001 23: waiting for 'log file switch (archivingneeded)'

Object Names

~~~~~~~~~~~~

Enqueue JS-00000000-00000001

48768 Lines Processed.

[oracle@rac1 src]$

這個使用ass.awk 處理之後的結果就直接檢視trace 直觀很多：

[oracle@rac1 src]$ head -100/u01/app/oracle/admin/anqing/udump/anqing1_ora_8725.trc

/u01/app/oracle/admin/anqing/udump/anqing1_ora_8725.trc

Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - Production

With the Partitioning, Real ApplicationClusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1

System name: Linux

Node name: rac1

Release: 2.6.18-194.el5

Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010

Machine: i686

Instance name: anqing1

Redo thread mounted by this instance: 1

Oracle process number: 24

Unix process pid: 8725, image: oracle@rac1(TNS V1-V3)

*** 2012-02-06 20:32:20.758

*** ACTION NAME:() 2012-02-06 20:32:20.755

*** MODULE NAME:(sqlplus@rac1 (TNS V1-V3))2012-02-06 20:32:20.755

*** SERVICE NAME:(SYS$USERS) 2012-02-0620:32:20.755

*** SESSION ID:(118.34295) 2012-02-0620:32:20.755

===================================================

SYSTEM STATE

------------

System global information:

processes: base 0x30e0ec78, size 150, cleanup 0x30e1dff8

allocation: free sessions 0x30e5a6c4, 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: 64

system statistics:

313621 logons cumulative

30 logons current

14842838 opened cursors cumulative

….

Oracle 使用 ass.awk 工具檢視 system state dump 說明

相關文章