某日检查数据库性能,发现数据库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 快照正常了