AWR不能自動生成快照解決方法

問題描述：RAC歸檔滿后，出現(xiàn)AWR不能自動生成快照。經(jīng)原理應(yīng)該是MMON HANG住導(dǎo)致，所以嘗試重啟MMON后問題解決。； MMON HANG解決辦法

1)重啟一下mmon的刷新 
alter system set "_swrf_mmon_flush"=false; 
alter system set "_swrf_mmon_flush"=true;  2)或者，找到mmon進(jìn)程殺掉，讓數(shù)據(jù)庫自動重啟一個新的mmon進(jìn)程，或者重啟下實例。 
ps -ef|grep mmon 
kill -9 xxxxxx  Manageability Monitor Processes (MMON and MMNL)
The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects. The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full. See Also:
"Automatic Workload Repository (AWR)" and "Active Session History (ASH)"

AWR和ASH后臺運行進(jìn)程
AWR ==>MMON
ASH ==>MMNL MMON負(fù)責(zé)執(zhí)行與AWR相關(guān)的任務(wù)。包括收集數(shù)據(jù)庫統(tǒng)計信息，收集AWR快照，啟動各種自動維護(hù)作業(yè)JOB，生成超過閥值告警信息。
MMNL負(fù)責(zé)執(zhí)行與ASH相關(guān)的任務(wù)。

創(chuàng)新互聯(lián)公司服務(wù)項目包括建平網(wǎng)站建設(shè)、建平網(wǎng)站制作、建平網(wǎng)頁制作以及建平網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，建平網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到建平省份的部分城市，未來相信會繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

###之前MOS 專家咨詢的記錄

1. Please provide the below script output on failed instance:

conn / as sysdbaset pages 1000set num 20

alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';

alter session set nls_timestamp_format='yyyy-mm-dd hh34:mi:ss.ff';

alter session set nls_timestamp_tz_format='yyyy-mm-dd hh34:mi:ss.ff tzh:tzm';

set mark html onspool 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_accessspool off

exit

2. Provide the "awrinfo" script output on instance

3:

SQL> conn / as sysdbaSQL> @?/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.gzls -l trace.tar.gzTo 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;

/

########################################################################################
版權(quán)所有，文章允許轉(zhuǎn)載，但必須以鏈接方式注明源地址，否則追究法律責(zé)任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文鏈接： http://blog.itpub.net/26442936/viewspace-2125130/