Oracle oradebug 命令 使用说明

当数据库出现一些挂起状态时，如果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

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

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

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阻塞

 

评论

1 楼 andyniu 2013-11-07  

   内容是网上粘贴的，但我按照步骤拿生产环境做试验了。初学者，看不懂trc文件。不过用awk工具转化后，就知道结果了。不过。。