使用 oradebug 转储 systemstate / processstate -- 转

作者： eygle | English Version 【转载时请以超链接形式标明文章 出处和作者信息及 本声明】
链接： http://www.eygle.com/archives/2009/11/oradebug_systemstate_dump.html

站内相关文章|Related Articles

工具: ass109.awk 分析 Oracle 的跟踪文件
Oracle的异构数据迁移工具 - OMW及ODI
Quest 的 Spotlight On Unix 漂亮的展现
使用spawrrac.sql收集RAC数据库对比信息
使用Linux下script工具记录Oracle输出

当数据库出现一些挂起状态时，如果sqlplus仍然可以连接，可能视图查询没有相应，但是可以通过oradebug工具来进行进程及系统状态信息的转储，从而可以进行Hang分析。[@more@]

DUMP进程状态可以使用：

　　alter sessions set events 'immediate trace name processstate level '；
或者使用：
　　oradebug setmypid
　　oradebug ulimit
　　oradebug dump processstate

当诊断数据库挂起条件时可以DUMP系统状态：
　　alter sessions set events 'immediate trace name systemstate level '；
　　或：
　　oradebug setmypid
　　oradebug ulimit
　　oradebug dump systemstate

如果为了获取全面一点的信息，可以使用Level 10。
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10

今天一个朋友的请求帮助，获得的系统状态如下：

System State 1
~~~~~~~~~~~~~~~~
1:
2: waiting for 'pmon timer' wait
3: waiting for 'rdbms ipc message' wait
4: waiting for 'rdbms ipc message' wait
5: waiting for 'rdbms ipc message' wait
6: waiting for 'rdbms ipc message' wait
7: waiting for 'rdbms ipc message' wait
8: waiting for 'buffer busy waits' (2,9,11) wait
9: waiting for 'rdbms ipc message' wait
10: waiting for 'rdbms ipc message' wait
11: waiting for 'log file switch (archiving needed)' wait
12: waiting for 'rdbms ipc message' wait
13:
14:
15: waiting for 'log file switch (archiving needed)' wait
16: waiting for 'library cache lock' [LOCK: handle=20e750ca8] wait
17: waiting for 'rdbms ipc message' wait
18: waiting for 'rdbms ipc message' wait
19: waiting for 'buffer busy waits' (2,59,1b) wait
Cmd: Select
20: waiting for 'log file switch (archiving needed)' wait
21: waiting for 'Streams AQ: qmn coordinator idle wait' wait
22: waiting for 'SQL*Net message from client' wait
23: waiting for 'SQL*Net message from client' wait
24: waiting for 'library cache lock' [LOCK: handle=20e750ca8] wait
25: waiting for 'buffer busy waits' (1,2ba2,1) wait
26: last wait for 'SQL*Net message from client'
130:waiting for 'SQL*Net message from client' wait
131:waiting for 'SQL*Net message from client' wait
132:waiting for 'SQL*Net message from client' wait
133:waiting for 'SQL*Net message from client' wait
134:waiting for 'SQL*Net message from client' wait
135:waiting for 'SQL*Net message from client' wait
136:waiting for 'SQL*Net message from client' wait
137:waiting for 'SQL*Net message from client' wait
138:waiting for 'SQL*Net message from client' wait
139:waiting for 'SQL*Net message from client' wait
140:waiting for 'SQL*Net message from client' wait
141:waiting for 'SQL*Net message from client' wait
142:waiting for 'SQL*Net message from client' wait
143:waiting for 'SQL*Net message from client' wait
145:waiting for 'Streams AQ: qmn slave idle wait' wait
146:waiting for 'SQL*Net message from client' wait
148:waiting for 'SQL*Net message from client' wait
149:waiting for 'SQL*Net message from client' wait
150:waiting for 'SQL*Net message from client' wait
151:waiting for 'SQL*Net message from client' wait
152:waiting for 'SQL*Net message from client' wait
175:waiting for 'log file switch (archiving needed)' wait
Cmd: Insert
177:waiting for 'buffer busy waits' (6,ac78b,1) wait
Cmd: Delete
179:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
182:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
184:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
185:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
186:waiting for 'log file switch (archiving needed)' wait
Cmd: Delete
188:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
195:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
196:waiting for 'log file switch (archiving needed)' wait
Cmd: Insert
199:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
204:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
205:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
213:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
221:waiting for 'log file switch (archiving needed)' wait
Cmd: Insert
228:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
232:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
235:waiting for 'SQL*Net message from client' wait
243:waiting for 'log file switch (archiving needed)' wait
Cmd: Select
248:waiting for 'SQL*Net message from client' wait
249:waiting for 'SQL*Net message from client' wait
250:waiting for 'SQL*Net message from client' wait
251:waiting for 'SQL*Net message from client' wait
252:waiting for 'log file switch (archiving needed)' wait
253:waiting for 'SQL*Net message from client' wait
254:waiting for 'SQL*Net message from client' wait
255:waiting for 'SQL*Net message from client' wait
259:waiting for 'SQL*Net message from client' wait
260:waiting for 'SQL*Net message from client' wait
262:waiting for 'SQL*Net message from client' wait
263:waiting for 'SQL*Net message from client' wait
264:waiting for 'SQL*Net message from client' wait
265:waiting for 'SQL*Net message from client' wait
266:waiting for 'SQL*Net message from client' wait
267:waiting for 'SQL*Net message from client' wait
268:waiting for 'SQL*Net message from client' wait
269:waiting for 'SQL*Net message from client' wait
270:waiting for 'SQL*Net message from client' wait
273:waiting for 'log file switch (archiving needed)' wait
Cmd: Insert
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
LOCK: handle=20e750ca8 15: waiting for 'log file switch (archiving needed)'

Object Names
~~~~~~~~~~~~
LOCK: handle=20e750ca8 TABL:EXFSYS.RLM$EVTCLEANUP

显然是归档满了，导致了系统挂起。

转：http://www.eygle.com/archives/2009/11/oradebug_systemstate_dump.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/789833/viewspace-1039969/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/789833/viewspace-1039969/