11.2.0.4 awr快照无法正常生成

某日检查数据库性能，发现数据库awr快照没有正常生成

咨询 support sr

执行如下脚本

	
Dear Customer, 

1. Please provide the below script output on instance 3:<info.html> 

conn / as sysdba 
set pages 1000 
set num 20 
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff'; 
alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'; 
set mark html on 
spool info.html 
select * from (select t.*, rank() over(partition by dbid, instance_number order by begin_interval_time desc) r from dba_hist_snapshot t) where r < 100; 
select * from wrm$_wr_control where dbid = (select dbid from v$database); 
select * from gv$database; 
select * from gv$thread; 
show parameter statistics_level 
show parameter control_management_pack_access 
spool off 
exit 

2. Provide the "awrinfo" script output on instance 3: <awrinfo.txt> 
SQL> conn / as sysdba 
SQL> @?/rdbms/admin/awrinfo.sql 

3. Enable SQL tracing on MMON slaves to collect more information. (do this in all instances) 

begin 
dbms_monitor.serv_mod_act_trace_enable(service_name => 'SYS$BACKGROUND', 
module_name => 'MMON_SLAVE', 
action_name => 'Auto-Flush Slave Action', 
waits => true, 
binds => true); 
end; 
/ 
begin 
dbms_monitor.serv_mod_act_trace_enable(service_name => 'SYS$BACKGROUND', 
module_name => 'MMON_SLAVE', 
action_name => 'Remote-Flush Slave Action', 
waits => true, 
binds => true); 
end; 
/ 

Wait for 2 snapshots time then upload the MMON (xxxx_mmon_xxxx.trc) and the below mmon slave trace files. 

egrep -l "Auto-Flush Slave Action|Remote-Flush Slave Action" *.trc | xargs tar -cvf - | gzip > trace.tar.gz 
ls -l trace.tar.gz 

To disable the tracing later, use "dbms_monitor.serv_mod_act_trace_disable" instead. for example: 

begin 
dbms_monitor.serv_mod_act_trace_disable(service_name => 'SYS$BACKGROUND', 
module_name => 'MMON_SLAVE', 
action_name => 'Auto-Flush Slave Action'); 
end; 
/ 
begin 
dbms_monitor.serv_mod_act_trace_disable(service_name => 'SYS$BACKGROUND', 
module_name => 'MMON_SLAVE', 
action_name => 'Remote-Flush Slave Action'); 
end; 
/ 

4. Provide the alert logs in all instances. 

检查后台trace 并上传sr

egrep -l "Auto-Flush Slave Action|Remote-Flush Slave Action" *.trc | xargs tar -cvf - | gzip > trace.tar.gz 
ls -l trace.tar.gz 

检查trace并没有输出

执行 oradebug 命令 

conn / as sysdba 
oradebug unit_test kebm_dmp_slv_attrs kewrmafsa_ 
oradebug unit_test kebm_dmp_slv_attrs kewrmrfsa_ 

Unix process pid: 13823, image: oracle@zjhz-bjpaasb1 (TNS V1-V3) 


*** 2017-01-06 17:50:22.320 
*** SESSION ID:(5900.7047) 2017-01-06 17:50:22.320 
*** CLIENT ID:() 2017-01-06 17:50:22.320 
*** SERVICE NAME:(SYS$USERS) 2017-01-06 17:50:22.320 
*** MODULE NAME:(sqlplus@zjhz-bjpaasb1 (TNS V1-V3)) 2017-01-06 17:50:22.320 
*** ACTION NAME:() 2017-01-06 17:50:22.320 

Processing Oradebug command 'setmypid' 

*** 2017-01-06 17:50:22.320 
Oradebug command 'setmypid' console output: <none> 

*** 2017-01-06 17:50:39.146 
Processing Oradebug command 'tracefile_name' 

*** 2017-01-06 17:50:39.146 
Oradebug command 'tracefile_name' console output: 
/opt/oradiag/db/diag/rdbms/bjpaasb/bjpaasb1/trace/bjpaasb1_ora_13823.trc 

*** 2017-01-06 17:51:09.446 
Processing RESTRICTED Oradebug command 'unit_test kebm_dmp_slv_attrs kewrmafsa_' 

*** 2017-01-06 17:51:09.446 
Oradebug command 'unit_test kebm_dmp_slv_attrs kewrmafsa_' console output: 
Status: 3 
Flags: 0 
Runtime limit: 900 
CPU time limit: 300 
Violations: 8 
Suspended until: 1483721896 

*** 2017-01-06 17:51:09.459 
Processing RESTRICTED Oradebug command 'unit_test kebm_dmp_slv_attrs kewrmrfsa_' 

*** 2017-01-06 17:51:09.460 
Oradebug command 'unit_test kebm_dmp_slv_attrs kewrmrfsa_' console output: 
Status: 3 
Flags: 0 
Runtime limit: 900 
CPU time limit: 300 
Violations: 8 
Suspended until: 1483715997 

support 反馈

看来mmon slave的确被suspend了，参见 

Suspended until: 1483722947 

$ date -d @1483722947 "+%Y-%m-%d %H:%M:%S" 
2017-01-07 01:15:47 

第二天检查 awr 快照正常了