oracle process state,使用 oradebug 转储 systemstate / processstate

使用 oradebug 转储 systemstate / processstate

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

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

-The End-

By eygle on 2009-11-23 18:30 |

Comments (1) |

Case | 2464 |

1 Comment

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

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