eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 在上海,在Radisson,在Oracle的路上 | Blog首页 | 顺利结束上海站 - OU 深入解析Oracle 培训课程 »

使用 oradebug 转储 systemstate / processstate

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

DUMP进程状态可以使用:
  alter sessions set events 'immediate trace name processstate level <level>';
或者使用:
  oradebug setmypid
  oradebug ulimit
  oradebug dump processstate<level>
当诊断数据库挂起条件时可以DUMP系统状态:
  alter sessions set events 'immediate trace name systemstate level <level>';
  或:
  oradebug setmypid
  oradebug ulimit
  oradebug dump systemstate <level>

如果为了获取全面一点的信息,可以使用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   

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

-The End-


历史上的今天...
    >> 2015-11-23文章:
    >> 2012-11-23文章:
    >> 2011-11-23文章:
    >> 2010-11-23文章:
    >> 2008-11-23文章:
    >> 2007-11-23文章:
           奥运语录:开始与结束
    >> 2006-11-23文章:
    >> 2005-11-23文章:

无觅

By eygle on 2009-11-23 18:30 | Comments (1) | Case | 2464 |

1 Comment

当sga很大,比如超过50g的时候,然后如果刚好是rac,然后系统hang了,这个时候,system dump可能做不出来。


另外,nnd,归档满了,用sysdba登录sqlplus的时候,有个错误提示仅限内部操作的吧,无需做dump这么杀鸡用牛刀的吧。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com