Oracle 11.2.0.4 awr过期快照无法自动清理

事件：SYSAUX表空间项目存储比重较大 #检查占用空间最大的前20对象 SQL> select * from (select owner,segment_name,partition_name,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum <=20; #检查策略 SQL> select * from dba_hist_wr_control;        DBID SNAP_INTERVAL                                                               RETENTION                                                                   TOPNSQL ---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- 3560543255 +00000 01:00:00.0                                                           +00008 00:00:00.0                                                           DEFAULT SQL>  show parameter statistics; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_pending_statistics     boolean     FALSE statistics_level                     string      TYPICAL timed_os_statistics                  integer     0 timed_statistics                     boolean     TRUE SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ---------------------------                          15 #策略检查一切正常，修改统计信息保留10天                   SQL> exec dbms_stats.alter_stats_history_retention(10); PL/SQL procedure successfully completed. SQL> exec dbms_stats.purge_stats(systimestamp -11); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ---------------------------                          10 #创建新的分区 alter session set "_swrf_test_action" = 72;  #检查分区 SQL>  select table_name,partition_name,high_value from dba_tab_partitions where table_name='WRH$_SYSSTAT'; TABLE_NAME      PARTITION_NAME                 HIGH_VALUE --------------- ------------------------------ -------------------------------------------------------------------------------- WRH$_SYSSTAT    WRH$_SYSSTAT_MXDB_MXSN         MAXVALUE, MAXVALUE WRH$_SYSSTAT    WRH$_SYSSTA_3560543255_1322    3560543255, 30532 WRH$_SYSSTAT    WRH$_SYSSTA_3560543255_30532   3560543255, MAXVALUE #检查快照ID SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;  MIN(SNAP_ID) MAX(SNAP_ID)  ------------ ------------  30530 30531  SQL> select min(snap_id),max(snap_id) from wrh$_active_session_history;  MIN(SNAP_ID) MAX(SNAP_ID)  ------------ ------------  30530 30531  SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_PARAMETER;  MAX(SNAP_ID) MIN(SNAP_ID)  ------------ ------------  30531 1322  SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_SQLSTAT;  MAX(SNAP_ID) MIN(SNAP_ID)  ------------ ------------  30531 1322                    --检查awr占用情况，发现过期快照无法自动清理Bug 14084247 - ORA-1555 or ORA-12571 Failed AWR purge can lead to continued SYSAUX space use (文档 ID 14084247.8) @?/rdbms/admin/awrinfo.sql #统计各个WRH表的最大,最小snap_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;  / PARTITION NAME SNAP_ID DBID --------------------------- ------- ---------- WRH$_ACTIVE_3560543255_1322 Min 30530 3560543255 WRH$_ACTIVE_3560543255_1322 Max 30531 3560543255 --- WRH$_ACTIVE_3560543255_30532 Min 30532 3560543255 WRH$_ACTIVE_3560543255_30532 Max 30548 3560543255 --- PL/SQL procedure successfully completed. #再次清理历史分区的快照 SQL> begin dbms_workload_repository.drop_snapshot_range( low_snap_id =>30530, high_snap_id =>30531, dbid =>3560543255); end; / PL/SQL procedure successfully completed. #重启一下MMON刷新: SQL> alter system set "_swrf_mmon_flush"=false; System altered. SQL> alter system set "_swrf_mmon_flush"=true; System altered.      #过半小时左右，再次检查过期快照已清理完毕 SQL> select * from (select owner,segment_name,partition_name,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum <=20; SQL>  select max(SNAP_ID),min(SNAP_ID) from WRH$_PARAMETER;  MAX(SNAP_ID) MIN(SNAP_ID) ------------ ------------        30549        30532 SQL>  select max(SNAP_ID),min(SNAP_ID) from WRH$_SQLSTAT;  MAX(SNAP_ID) MIN(SNAP_ID) ------------ ------------        30549        30532 SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;  MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------        30532        30549 SQL> select min(snap_id),max(snap_id) from wrh$_active_session_history;  MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------        30532        30549 #检查分区已清理  SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name='WRH$_SYSSTAT'; TABLE_NAME      PARTITION_NAME                 HIGH_VALUE --------------- ------------------------------ -------------------------------------------------------------------------------- WRH$_SYSSTAT    WRH$_SYSSTAT_MXDB_MXSN         MAXVALUE, MAXVALUE WRH$_SYSSTAT    WRH$_SYSSTA_3560543255_30532   3560543255, MAXVALUE       #检查表空间使用率 注释： 1.关于_swrf_test_action参数，由于此参数属于隐含参数，没有明确的文档介绍此参数，只能确认此参数的部分的取值的作用，比如：  _swrf_test_action = 72;------------>强制分割AWR分区，以用于删除原有分区  _swrf_test_action = 82;------------->手工重新设置最后一次的AWR清理时间，从而触发MMON进程再次运行  _swrf_test_action=53;------------->手工刷新dba_feature_usage_statistics信息  --------------------------------  _swrf_test_action = 28;------------->启用MMON的trace  _swrf_test_action = 10; ------------->启用快照刷新 trace  ----------------------------------  _swrf_test_action = 11; ------------->关闭快照刷新 trace  _swrf_test_action = 29;------------->关闭MMON的trace  --------------------------------  2. dba_hist_snapshot， wrh$_active_session_history， WRH$_PARAMETER， WRH$_SQLSTAT：  dba_hist_snapsho视图：记录的是AWR快照的信息  wrh$_active_session_history表记录的是基于v$active_session_history的信息创建的表  WRH$_PARAMETER表记录的是基于v$parameter的信息创建的表  WRH$_SQLSTAT表记录的是v$sql和v$sqltext信息创建的表  通过分析以上视图和表的DDL语句，他们之间并没有关联，他们都有snap_id列，如果其中任何一个表中的信息清理失败，都会造成snap_id不一致。