Oracle AWR快照管理與常見問題

1、手動(dòng)創(chuàng)建Snapshots

exec dbms_workload_repository.create_snapshot();?

–或者?

BEGIN?

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();?

END;

2、手動(dòng)刪除Snapshots

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 6770,high_snap_id => 6774,dbid => 4059638244);?

OR?

BEGIN?

dbms_workload_repository.drop_snapshot_range(low_snap_id => 6770,?

high_snap_id => 6774,?

dbid => 4059638244);?

END;

這種方法刪除是通過(guò)delete的會(huì)產(chǎn)生大量redo，建議直接刪除相關(guān)分區(qū)

3、修改快照設(shè)置

——查看快照策略?

set linesize 1000 pagesize 500?

col SNAP_INTERVAL for a20?

col RETENTION for a30?

select * from dba_hist_wr_control;

——生成修改快照策略的語(yǔ)句?

set linesize 1000 pagesize 5000?

col cmd for a200?

SELECT ‘exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 16*24*60,INTERVAL => 30,dbid => ’ || DBID ||?

’ );’ cmd?

FROM dba_hist_wr_control;

——修改快照策略?

BEGIN?

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 18*24*60,?

INTERVAL => 30,?

dbid => 1328382724);?

END;?

/

4、AWR生成常用腳本

–生成單實(shí)例 AWR 報(bào)告：?

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

–生成 Oracle RAC AWR 報(bào)告：?

@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

–生成 RAC 環(huán)境中特定數(shù)據(jù)庫(kù)實(shí)例的 AWR 報(bào)告:?

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

–生成 Oracle RAC 環(huán)境中多個(gè)數(shù)據(jù)庫(kù)實(shí)例的 AWR 報(bào)告的方法：?

@$ORACLE_HOME/rdbms/admin/awrgrpti.sql

–生成 SQL 語(yǔ)句的 AWR 報(bào)告：?

@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

–生成特定數(shù)據(jù)庫(kù)實(shí)例上某個(gè) SQL 語(yǔ)句的 AWR 報(bào)告：?

@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

–生成單實(shí)例 AWR 時(shí)段對(duì)比報(bào)告?

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

–生成 Oracle RAC AWR 時(shí)段對(duì)比報(bào)告?

@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

–生成特定數(shù)據(jù)庫(kù)實(shí)例的 AWR 時(shí)段對(duì)比報(bào)告?

@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

–生成 Oracle RAC 環(huán)境下特定（多個(gè)）數(shù)據(jù)庫(kù)實(shí)例的 AWR 時(shí)段對(duì)比報(bào)告?

@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql

5、快速收集AWR/ASH/ADDM腳本

——-快速收集AWR?

declare?

begin_time_date date := to_date(‘20141130 23:00:00’, ‘yyyymmdd hh24:mi:ss’) ;?

end_time_date date := to_date(‘20141203 18:00:00’, ‘yyyymmdd hh24:mi:ss’) ;

begin?

dbms_output.put_line(‘set linesize 2000’);?

dbms_output.put_line(‘set pagesize 50000’);?

dbms_output.put_line(‘set head off’);?

for r in (select *?

from sys.WRM$_SNAPSHOT t?

where t.begin_interval_time >begin_time_date?

and t.begin_interval_time< end_time_date?

and t.dbid=3628304518?

order by t.snap_id, t.instance_number)

loop

dbms_output.put_line('spool awrrpt_' || r.instance_number || '_' ||? ? ?

? ? ? ? ? ? ? ? ? ? to_char(r.begin_interval_time+1/24,'mmdd_hh24mi') || '_' ||to_char(r.end_interval_time+1/24,'mmdd_hh24mi') || '.html');? ? ?

dbms_output.put_line('select *? ? ?

1

2

3

4

from table?

(dbms_workload_repository.awr_report_html(’ ||?

r.dbid || ‘,’ || r.instance_number || ‘,’ ||?

r.snap_id || ‘,’ || (r.snap_id + 1) || ‘,0));’);?

dbms_output.put_line(‘spool off’);?

end loop;?

end;

———快速收集ASH?

declare?

begin_time_date date := to_date(‘20141130 23:00:00’,?

‘yyyymmdd hh24:mi:ss’);?

end_time_date date := to_date(‘20141203 13:00:00’,?

‘yyyymmdd hh24:mi:ss’);?

interval_minutes number := 60;?

begin?

dbms_output.put_line(‘set linesize 2000’);?

dbms_output.put_line(‘set pagesize 50000’);?

dbms_output.put_line(‘set head off’);?

for r in (select db.DBID,?

ins.INSTANCE_NUMBER,?

begin_time_date + interval_minutes * (lv - 1) / 1440 begin_time_date,?

begin_time_date + interval_minutes * lv / 1440,?

‘yyyymmdd hh24:mi:ss’ end_time_date,?

to_char(begin_time_date + 60 * (lv - 1) / 1440,?

‘yyyymmddhh24mi’) begin_time,?

to_char(begin_time_date + 60 * lv / 1440,?

‘yyyymmddhh24miss’) end_time?

from (select /+ no_merge/?

level lv?

from dual?

connect by level <= (end_time_date - begin_time_date) * 60 * 24 /?

interval_minutes),?

gvinstanceins,vinstanceins,vdatabase db) loop?

dbms_output.put_line(‘spool ashrpt_’ || to_char(r.instance_number) || ‘_’ ||?

r.begin_time || ‘-’ || r.end_time || ‘.html’);

dbms_output.put_line('select output from table(dbms_workload_repository.ash_report_html( ' ||? ?

? ? ? ? ? ? ? ? ? ? r.dbid || ' , ' || r.instance_number || ' , ' ||? ?

? ? ? ? ? ? ? ? ? ? 'to_date(' || '''' || r.begin_time ||? ?

? ? ? ? ? ? ? ? ? ? ''', ''yyyymmddhh24miss''' || ')' || ' , ' ||? ?

? ? ? ? ? ? ? ? ? ? 'to_date(' || '''' || r.end_time ||? ?

? ? ? ? ? ? ? ? ? ? ''', ''yyyymmddhh24miss''' || ')));');? ?

dbms_output.put_line('spool off ');? ?

1

2

3

4

5

6

7

8

9

end loop;?

end;

—————快速收集ADDM?

declare?

begin_time_date date := to_date(‘20141109 15:00:00’, ‘yyyymmdd hh24:mi:ss’) ;?

end_time_date date := to_date(‘20141109 19:00:00’, ‘yyyymmdd hh24:mi:ss’) ;

begin?

dbms_output.put_line(‘set linesize 100’);?

dbms_output.put_line(‘set pagesize 50000’);?

dbms_output.put_line(‘set head off’);?

dbms_output.put_line(‘serveroutput ON SIZE UNLIMITED ‘);?

dbms_output.put_line(‘spool addm.txt’);?

for r in (select *?

from sys.WRM$_SNAPSHOT t?

where t.begin_interval_time >=begin_time_date?

and t.begin_interval_time<= end_time_date?

order by t.snap_id, t.instance_number)

loop?

dbms_output.put_line(‘declare?

id number ;?

name varchar2(100) ;?

v_dec varchar2(200) :=’||”’addm_’||to_char(r.snap_id)||’_’||to_char(r.snap_id+1)||”’;’);?

dbms_output.put_line( ‘begin ‘) ;?

dbms_output.put_line( ‘dbms_advisor.create_task(”ADDM”,id,name,v_dec,null);’);?

dbms_output.put_line( ‘dbms_advisor.set_task_parameter(name, ‘||”’START_SNAPSHOT”,’||r.snap_id||’);’) ;?

dbms_output.put_line( ‘dbms_advisor.set_task_parameter(name, ’ ||”’END_SNAPSHOT”,’||to_char(r.snap_id+1)||’);’) ;?

dbms_output.put_line( ‘dbms_advisor.set_task_parameter(name, ‘||”’INSTANCE”,’||r.instance_number||’);’) ;?

dbms_output.put_line( ‘dbms_advisor.set_task_parameter(name, ‘||”’DB_ID”,’||r.dbid||’);’) ;?

dbms_output.put_line( ‘dbms_advisor.execute_task(name); ‘) ;?

dbms_output.put_line(‘dbms_output.put_line(‘||”’#spool addm_’ || r.instance_number || ‘_’ ||?

r.snap_id || ‘_’ || (r.snap_id + 1) || ‘.txt”);’);?

dbms_output.put_line(‘dbms_output.put_line(‘||”’#select dbms_advisor.get_task_report(”””’||’||name||”””,’||””’TEXT””,””TYPICAL””) from dual ;”);’);

dbms_output.put_line('dbms_output.put_line(''#spool off'');');?

dbms_output.put_line('end ;?

/');?

1

2

3

4

end loop;?

dbms_output.put_line(‘spool off’);?

end;

6、常見問題?

6.1、AWR快照數(shù)據(jù)和ASH數(shù)據(jù)未正常產(chǎn)生，一般是由于Oracle mman和mmnl進(jìn)程異常導(dǎo)致的，可以嘗試下列方法解決

1)、重啟一下mmon的刷新?

alter system set “_swrf_mmon_flush”=false;?

alter system set “_swrf_mmon_flush”=true;

2)、或者，找到mmon進(jìn)程殺掉，讓數(shù)據(jù)庫(kù)自動(dòng)重啟一個(gè)新的mmon進(jìn)程，或者重啟下實(shí)例。?

ps -ef|grep mmon?

kill -9 xxxxxx

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

MMNL負(fù)責(zé)執(zhí)行與ASH相關(guān)的任務(wù)。

6.2、生成awr報(bào)告時(shí)報(bào)錯(cuò)，拋出ORA-06502: PL/SQL: 數(shù)字或值錯(cuò)誤 : 字符串緩沖區(qū)太小

ERROR:?

ORA-06502: PL/SQL: numeric or value error: character string buffer too small?

ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 919?

ORA-06512: at line 1

解決方法：

1、截?cái)鄐ql_text?

update WRHSQLTEXTsetsqltext=SUBSTR(sqltext,1,1000);commit;2、補(bǔ)丁13527323DownloadPatch135273233、Usefollowingworkaround:sqlplus/assysdba@SQLTEXTsetsqltext=SUBSTR(sqltext,1,1000);commit;2、補(bǔ)丁13527323DownloadPatch135273233、Usefollowingworkaround:sqlplus/assysdba@ORACLE_HOME/rdbms/admin/prvsawr.plb?

@$ORACLE_HOME/rdbms/admin/prvtawr.plb

6.3、SYSAUX表空間中WRH$_ACTIVE_SESSION_HISTORY占用大量空間

SELECT owner,?

segment_name,?

partition_name,?

segment_type,?

bytes/1024/1024/1024 Size_GB?

FROM dba_segments?

WHERE segment_name=’WRH$_ACTIVE_SESSION_HISTORY’;

–修改參數(shù)手動(dòng)出發(fā)AWR表的新分區(qū)生成?

alter session set “_swrf_test_action” = 72;

–通過(guò)下列腳本獲取AWR分區(qū)對(duì)應(yīng)的快照ID?

set serveroutput on?

declare?

CURSOR cur_part IS?

SELECT partition_name from dba_tab_partitions?

WHERE table_name = ‘WRH$_ACTIVE_SESSION_HISTORY’;

query1 varchar2(200);?

query2 varchar2(200);

TYPE partrec IS RECORD (snapid number, dbid number);?

TYPE partlist IS TABLE OF partrec;

Outlist partlist;?

begin?

dbms_output.put_line(‘PARTITION NAME SNAP_ID DBID’);?

dbms_output.put_line(‘————————— ——- ———-‘);

for part in cur_part loop?

query1 := ‘select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition (‘||part.partition_name||’) group by dbid’;?

execute immediate query1 bulk collect into OutList;

if OutList.count > 0 then?

for i in OutList.first..OutList.last loop?

dbms_output.put_line(part.partition_name||’ Min ‘||OutList(i).snapid||’ ‘||OutList(i).dbid);?

end loop;?

end if;

query2 := ‘select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition (‘||part.partition_name||’) group by dbid’;?

execute immediate query2 bulk collect into OutList;

if OutList.count > 0 then?

for i in OutList.first..OutList.last loop?

dbms_output.put_line(part.partition_name||’ Max ‘||OutList(i).snapid||’ ‘||OutList(i).dbid);?

dbms_output.put_line(‘—’);?

end loop;?

end if;

end loop;?

end;?

/?

通過(guò)DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE包刪除快照信息，但是會(huì)產(chǎn)生大量的redo

直接刪除相關(guān)分區(qū)更為便捷和迅速恢復(fù)業(yè)務(wù)

alter table WRHACTIVESESSIONHISTORYdroppartitionWRHACTIVESESSIONHISTORYdroppartitionWRH_ACTIVE_692952975_0;

附：申請(qǐng)阿里云服務(wù)器等產(chǎn)品時(shí)，可以使用1000元阿里云代金券，領(lǐng)取網(wǎng)址：?http://aliyun.jinre.com?

可以領(lǐng)用阿里云代金券。 申請(qǐng)阿里云服務(wù)器時(shí)，都可以領(lǐng)取代金券，新用戶和老用戶都可以用。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容