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敲街、常見(jiàn)問(wèn)題?
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)取代金券蔚携,新用戶和老用戶都可以用希太。