Oracle AWR快照管理与常见问题

1、手动创建Snapshots

exec dbms_workload_repository.create_snapshot();
–或者
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;

2、手动删除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;

这种方法删除是通过delete的会产生大量redo，建议直接删除相关分区

3、修改快照设置

——查看快照策略
set linesize 1000 pagesize 500
col SNAP_INTERVAL for a20
col RETENTION for a30
select * from dba_hist_wr_control;

——生成修改快照策略的语句
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生成常用脚本

–生成单实例 AWR 报告：
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

–生成 Oracle RAC AWR 报告：
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

–生成 RAC 环境中特定数据库实例的 AWR 报告:
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

–生成 Oracle RAC 环境中多个数据库实例的 AWR 报告的方法：
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql

–生成 SQL 语句的 AWR 报告：
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

–生成特定数据库实例上某个 SQL 语句的 AWR 报告：
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

–生成单实例 AWR 时段对比报告
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

–生成 Oracle RAC AWR 时段对比报告
@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

–生成特定数据库实例的 AWR 时段对比报告
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

–生成 Oracle RAC 环境下特定（多个）数据库实例的 AWR 时段对比报告
@$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 *       

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),
gv$instance ins, v$database 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 ');    

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 ;   
/');  

end loop;
dbms_output.put_line(‘spool off’);
end;

6、常见问题
6.1、AWR快照数据和ASH数据未正常产生，一般是由于Oracle mman和mmnl进程异常导致的，可以尝试下列方法解决

1)、重启一下mmon的刷新
alter system set “_swrf_mmon_flush”=false;
alter system set “_swrf_mmon_flush”=true;

2)、或者，找到mmon进程杀掉，让数据库自动重启一个新的mmon进程，或者重启下实例。
ps -ef|grep mmon
kill -9 xxxxxx

MMON负责执行与AWR相关的任务。包括收集数据库统计信息，收集AWR快照，启动各种自动维护作业JOB，生成超过阀值告警信息。
MMNL负责执行与ASH相关的任务。

6.2、生成awr报告时报错，抛出ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小

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、截断sql_text
update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 1000); commit; 2、补丁13527323 Download Patch 13527323 3、 Use following workaround: sqlplus / as sysdba @$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’;

–修改参数手动出发AWR表的新分区生成
alter session set “_swrf_test_action” = 72;

–通过下列脚本获取AWR分区对应的快照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;
/
通过DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE包删除快照信息，但是会产生大量的redo

直接删除相关分区更为便捷和迅速恢复业务

alter table WRH$_ACTIVE_SESSION_HISTORY drop partition WRH$_ACTIVE_692952975_0;

附：申请阿里云服务器等产品时，可以使用1000元阿里云代金券，领取网址： http://aliyun.jinre.com
可以领用阿里云代金券。 申请阿里云服务器时，都可以领取代金券，新用户和老用户都可以用。