Oracle oradebug命令使用说明

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

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

SQL>oradebug TRACEFILE_NAME

SQL>oradebug close_trace

。可以通过awk脚本对trc进分析。（简单，直接。源文件太大，分析起来费劲）

awk-f ass109.awk/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_9976.trc

Starting Systemstate 1

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

Ass.Awk Version 1.0.9 - Processing /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_9976.trc

System State 1

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

2: 0: waiting for 'pmon timer'

3: 0: waiting for 'rdbms ipc message'

4: 0: waiting for 'VKTM Logical Idle Wait'

5: 0: waiting for 'rdbms ipc message'

6: 0: waiting for 'DIAG idle wait'

7: 0: waiting for 'rdbms ipc message'

8: 0: waiting for 'DIAG idle wait'

9: 0: waiting for 'rdbms ipc message'

10: 0: waiting for 'rdbms ipc message'

11: 0: waiting for 'rdbms ipc message'

12: 0: waiting for 'rdbms ipc message'

13: 0: waiting for 'smon timer'

14: 0: waiting for 'rdbms ipc message'

15: 0: waiting for 'rdbms ipc message'

16: 0: waiting for 'rdbms ipc message'

17:

18:

19: 0: waiting for 'Space Manager: slave idle wait'

20: 0: waiting for 'SQL*Net message from client'

21: 0: waiting for 'enq: TX - row lock contention'[Enqueue TX-000A0020-0000024F]

Cmd: Delete

22: 0: waiting for 'rdbms ipc message'

23: 0: waiting for 'rdbms ipc message'

24: 0: waiting for 'rdbms ipc message'

25: 0: waiting for 'rdbms ipc message'

26: 0: waiting for 'Streams AQ: qmn coordinator idle wait'

27:

28:

30: 0: waiting for 'Streams AQ: qmn slave idle wait'

31: 0: waiting for 'rdbms ipc message'

33: 1: waited for 'Streams AQ: waiting for time management or cleanup tasks'

35: 0: waiting for 'rdbms ipc message'

41:

44:

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 TX-000A0020-0000024F 20: 0: waiting for 'SQL*Net message from client'

Object Names

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

Enqueue TX-000A0020-0000024F

30586 Lines Processed.

--从这里马上就可以知道pid 21 请求Enqueue TX被pid 20阻塞

海淀区搬家公司

冻豆腐132

Oracle oradebug命令使用说明