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