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不一致。 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30327022/viewspace-2221760/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30327022/viewspace-2221760/