Chinaunix首页 | 论坛 | 博客
  • 博客访问: 86487
  • 博文数量: 44
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 431
  • 用 户 组: 普通用户
  • 注册时间: 2013-09-13 14:21
文章分类
文章存档

2014年(2)

2013年(42)

我的朋友

分类: Python/Ruby

2013-11-27 09:22:10

Oracle oradebug命令使用说明
 
当数据库出现一些挂起状态时,如果sqlplus仍然可以连接,可能视图查询没有相应,但是可以通过oradebug工具来进行进程及系统状态信息的转储,从而可以进行Hang分析。
 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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
~~~~~~~~~~~~~~~~
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阻塞

 

阅读(798) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~