Oracle AWR快照管理与常见问题

  • 时间:
  • 浏览:1
  • 来源:uu快3官方邀请码_uu快3app赚钱_彩神8

或多或少依据删除是通过delete的会产生少量redo,建议直接删除相关分区

3、修改快照设置
------查看快照策略  
set linesize 60

0 pagesize 60

0  
col SNAP_INTERVAL for a20  
col RETENTION for a60

  
select * from dba_hist_wr_control;  
  
------生成修改快照策略的语句  
set linesize 60

0 pagesize 60

00  
col cmd for a60

  
SELECT 'exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 16*24*60
,INTERVAL => 60

,dbid => ' || DBID ||  
       ' );' cmd  
  FROM dba_hist_wr_control;  
  
------修改快照策略  
BEGIN  
   DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 18*24*60
,  
                                                     INTERVAL => 60

,  
                                                     dbid => 1328382724);  
 END;  
 /  

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(60

);   
query2 varchar2(60

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

6、常见疑问图片

6.1、AWR快照数据和ASH数据未正常产生,一般是原因分析 Oracle mman和mmnl进程异常原因分析 的,可不时要尝试下列依据外理

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

Getting ORA-0660 2 and ORA-06512 Errors When Running AWR Report in HTML Format (Doc ID 2231743.1)

AWR Diagnostic Collection Script (Doc ID 733655.1)

直接删除相关分区更为便捷和比较慢恢复业务

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;  

How To Avoid the AWR Usage Without Having Diagnostic Pack License (Doc ID 2276199.1)

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)

参考文档:

外理依据:

1、截断sql_text  
update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 60

0);  
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  

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

MMNL负责执行与ASH相关的任务。