在oracle10g及oracle11g使用oradebug生成systemstate dump文件系列二

背景

  数据库出现性能问题时，有时需要生成一个systemstate DUMP文件，便于进行全面的分析与诊断，本文了解如何生成此文件的命令语法以及
如何用AWK脚本格式化生成的DUMP文件，如此便于查阅与分析，可读性强。  

结论

1,oradebug生成SYSTEMSTATE DUMP文件
2,采用ass109.awk脚本格式化生成的DUMP文件
3,oracle10g及oracle11g方法命令相同

测试

SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


SQL> 


SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_27273.trc
SQL> 


[oracle@seconary format_tool]$ ll
total 36
-rwxrwxrwx 1 oracle oinstall 34549 Nov 24  2003 ass109.awk
[oracle@seconary format_tool]$ awk -f ass109.awk  /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_27273.trc


Starting Systemstate 1
..............................................................................
.........................................................
Ass.Awk Version 1.0.9 - Processing /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_27273.trc


System State 1
~~~~~~~~~~~~~~~~
1:                                      
2:  0: waiting for 'pmon timer'         
3:  0: waiting for 'VKTM Logical Idle Wait' 
4:  0: waiting for 'rdbms ipc message'  
5:  0: waiting for 'DIAG idle wait'     
6:  0: waiting for 'rdbms ipc message'  
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 'jobq slave wait'    
20: 0: waiting for 'Streams AQ: qmn coordinator idle wait' 
21: 0: waiting for 'Streams AQ: qmn slave idle wait' 
22: 0: waited for 'Streams AQ: waiting for time management or cleanup tasks' 
23: 0: waiting for 'jobq slave wait'    
24: 0: waiting for 'jobq slave wait'    
25: 0: waiting for 'jobq slave wait'    
26: 0: waiting for 'rdbms ipc message'  
27: 0: waiting for 'jobq slave wait'    
28: 0: waiting for 'jobq slave wait'    
29: 0: waiting for 'jobq slave wait'    
30: 0: waiting for 'jobq slave wait'    
31: 0: waiting for 'jobq slave wait'    
32: 0: waiting for 'jobq slave wait'    
33: 0: waiting for 'jobq slave wait'    
34: 0: waiting for 'jobq slave wait'    
35: 0: waiting for 'jobq slave wait'    
36: 0: waiting for 'jobq slave wait'    
37: 0: waiting for 'jobq slave wait'    
38: 0: waiting for 'jobq slave wait'    
41: 0: waiting for 'jobq slave wait'    
42: 0: waiting for 'jobq slave wait'    
43: 0: waiting for 'jobq slave wait'    
44: 0: waiting for 'jobq slave wait'    
46: 0: waiting for 'jobq slave wait'    
48: 0: waiting for 'jobq slave wait'    
49: 0: waiting for 'jobq slave wait'    
50: 0: waiting for 'jobq slave wait'    
52: 0: waiting for 'jobq slave wait'    
54: 0: waiting for 'jobq slave wait'    
55: 0: waiting for 'jobq slave wait'    
56: 0: waiting for 'jobq slave wait'    
57: 0: waiting for 'jobq slave wait'    
59: 0: waiting for 'jobq slave wait'    
60: 0: waiting for 'jobq slave wait'    
63: 0: waiting for 'jobq slave wait'    
64: 0: waiting for 'jobq slave wait'    
65: 0: waiting for 'jobq slave wait'    
66: 0: waiting for 'jobq slave wait'    
67:                                     
69: 0: waiting for 'jobq slave wait'    
70: 0: waiting for 'jobq slave wait'    
71: 0: waiting for 'jobq slave wait'    
72: 0: waiting for 'jobq slave wait'    
73: 0: waiting for 'jobq slave wait'    
74: 0: waiting for 'SQL*Net message from client' 
75: 0: waiting for 'jobq slave wait'    
76: 0: waiting for 'jobq slave wait'    
77: 0: waiting for 'jobq slave wait'    
78: 0: waiting for 'jobq slave wait'    
79: 0: waiting for 'jobq slave wait'    
80: 0: waiting for 'jobq slave wait'    
81: 0: waiting for 'jobq slave wait'    
82: 0: waiting for 'jobq slave wait'    
83: 0: waiting for 'jobq slave wait'    
85: 0: waiting for 'jobq slave wait'    
86: 0: waiting for 'jobq slave wait'    
87: 0: waiting for 'jobq slave wait'    
89: 0: waiting for 'jobq slave wait'    
90: 0: waiting for 'jobq slave wait'    
91: 0: waiting for 'jobq slave wait'    
92: 0: waiting for 'jobq slave wait'    
93: 0: waiting for 'jobq slave wait'    
94: 0: waiting for 'jobq slave wait'    
95: 0: waiting for 'jobq slave wait'    
96: 0: waiting for 'jobq slave wait'    
97: 0: waiting for 'jobq slave wait'    
98: 0: waiting for 'jobq slave wait'    
99: 0: waiting for 'jobq slave wait'    
100:0: waiting for 'jobq slave wait'    
101:0: waiting for 'jobq slave wait'    
102:0: waiting for 'jobq slave wait'    
103:0: waiting for 'jobq slave wait'    
104:0: waiting for 'jobq slave wait'    
105:0: waiting for 'jobq slave wait'    
106:0: waiting for 'jobq slave wait'    
107:0: waiting for 'jobq slave wait'    
108:0: waiting for 'jobq slave wait'    
109:0: waiting for 'jobq slave wait'    
110:0: waiting for 'jobq slave wait'    
114:0: waiting for 'jobq slave wait'    
116:0: waiting for 'jobq slave wait'    
117:0: waiting for 'jobq slave wait'    
118:0: waiting for 'jobq slave wait'    
119:0: waiting for 'jobq slave wait'    
120:0: waiting for 'jobq slave wait'    
123:0: waiting for 'jobq slave wait'    
134:0: waiting for 'jobq slave wait'    
136:0: waiting for 'jobq slave wait'    
137:0: waiting for 'jobq slave wait'    
138:0: waiting for 'jobq slave wait'    
139:0: waiting for 'rdbms ipc message'  
142:0: waiting for 'jobq slave wait'    
143:0: waiting for 'jobq slave wait'    
144:0: waiting for 'jobq slave wait'    
145:0: waiting for 'jobq slave wait'    
146:0: waiting for 'jobq slave wait'    
147:0: waiting for 'jobq slave wait'    
148:0: waiting for 'jobq slave wait'    
149:0: waiting for 'jobq slave wait'    
150:0: waiting for 'jobq slave wait'    
151:0: waiting for 'jobq slave wait'    
152:0: waiting for 'jobq slave wait'    
156:0: waiting for 'jobq slave wait'    
158:0: waiting for 'jobq slave wait'    
160:0: waiting for 'jobq slave wait'    
162:0: waiting for 'jobq slave wait'    
164:0: waiting for 'jobq slave wait'    
167:0: waiting for 'jobq slave wait'    
169:0: waiting for 'jobq slave wait'    
170:0: waiting for 'jobq slave wait'    
172:0: waiting for 'jobq slave wait'    
173:0: waiting for 'jobq slave wait'    
174:0: waiting for 'Space Manager: slave idle wait' 
176:0: waiting for 'jobq slave wait'    
178:0: waiting for 'jobq slave wait'    
179:0: waiting for 'jobq slave wait'    


NO BLOCKING PROCESSES FOUND




33572 Lines Processed.
[oracle@seconary format_tool]$ 

SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi




[ora10g@seconary format_tool]$ awk -f ass109.awk /home/ora10g/admin/ora10g/udump/ora10g_ora_2324.trc


Starting Systemstate 1
.............................
Ass.Awk Version 1.0.9 - Processing /home/ora10g/admin/ora10g/udump/ora10g_ora_2324.trc


System State 1
~~~~~~~~~~~~~~~~
1:                                      
2:  waiting for 'pmon timer'            
3:  waiting for 'rdbms ipc message'     
4:  waiting for 'rdbms ipc message'     
5:  waiting for 'rdbms ipc message'     
6:  waiting for 'rdbms ipc message'     
7:  waiting for 'rdbms ipc message'     
8:  waiting for 'smon timer'            
9:  waiting for 'rdbms ipc message'     
10: waiting for 'rdbms ipc message'     
11: waiting for 'rdbms ipc message'     
12: waiting for 'rdbms ipc message'     
13:                                     
14:                                     
15:                                     
16:                                     
17:                                     
18:                                     
19:                                     
20:                                     
21:                                     
22: last wait for 'SQL*Net message from client' 
23: waiting for 'rdbms ipc message'     
24: waiting for 'rdbms ipc message'     
25: waiting for 'i/o slave wait'        
26: waiting for 'i/o slave wait'        
27: waiting for 'Streams AQ: qmn coordinator idle wait' 
30: waiting for 'Streams AQ: waiting for time management or cleanup tasks' 
31: waiting for 'Streams AQ: qmn slave idle wait' 


NO BLOCKING PROCESSES FOUND




12430 Lines Processed.
[ora10g@seconary format_too

参考资料

awk格式脚本下载地址：http://www.eygle.com/archives/2009/11/awk_ass109.html

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

服务过的客户：

中国电信

中国移动

中国联通

中国电通

国家电网

四川达州商业银行

湖南老百姓大药房

山西省公安厅

中国邮政

北京302医院     

河北廊坊新奥集团公司

 项目经验:

中国电信3G项目AAA系统数据库部署及优化
      中国联通4G数据库性能分析与优化

中国联通CRM数据库性能优化

中国移动10086电商平台数据库部署及优化

湖南老百姓大药房ERR数据库sql优化项目

四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

北京高铁信号监控系统RAC数据库部署及优化

河南宇通客车数据库性能优化

中国电信电商平台核心采购模块表模型设计及优化

中国邮政储蓄系统数据库性能优化及sql优化

北京302医院数据库迁移实施

河北廊坊新奥data guard部署及优化

山西公安厅身份证审计数据库系统故障评估

国家电网上海灾备项目4 node rac+adg 
       贵州移动crm及客服数据库性能优化项目
       贵州移动crm及客服务数据库sql审核项目
       深圳穆迪软件有限公司数据库性能优化项目

联系方式：

手机：18201115468

qq   :   305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900    

itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1814923/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1814923/