11gr2 rac WRH$_ACTIVE_SESSION_HISTORY未自動清理導致SYSAUX空間過度增長
遇到11gr2 rac WRH$_ACTIVE_SESSION_HISTORY未自動清理導致SYSAUX空間過度增長;但是和網上其它的不太一樣,查詢SNAP快照資訊,和select a.snap_interval,a.retention,a.topnsql from dba_hist_wr_control a; 中配置未發生變化,分割槽和SNAP對應資訊也是最近7天的,沒問題;查詢基線的資訊,也沒問題。
只有WRH$_ACTIVE_SESSION_HISTORY的一個WRH$_ACTIVE_SES_MXDB_MXSN分割槽異常,是6GB。但是查不出此分割槽對應的SNAP資訊。
使用MOS文件:WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文件 ID 387914.1)
中的方法alter session set "_swrf_test_action" = 72;解決了此問題。
附一下此MOS文件的解決方法:
Cause
Oracle decides what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables where we store the snapshot data in partitions. One method of purging data from these tables is by removing partitions that only contain rows that have exceeded the retention criteria. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn't be removed, then the partition won't be dropped and as such the table will contain old data.
If partition splits do not occur (for whatever reason), then we can end up with a situation where we have to wait for the latest entries to expire before the partition that they sit in can be removed. This can mean that some of the older entries can be retained significantly past their expiry date. The result of this is that the data is not purged as expected.
Solution
A potential solution to this issue is to manually split the partitions of the partitioned AWR objects such that there is more chance of the split partition being purged.You will still have to wait for all the rows in the new partitions to reach their retention time but with split partitions there is more chance of this happening. you can manually split the partitions using the following undocumented command:
To perform a single split of all the AWR partitions.
- Check the partition details for the offending table before the split:
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY'; - Split the partitions so that there is more chance of the smaller partition being purged:
alter session set "_swrf_test_action" = 72;NOTE: This command will split partitions for ALL partitioned AWR objects. It also initiates a single split; it does not need to be disabled and will need to be repeated if multiple splits are required.
- Check the partition details for the offending table after the split:
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
With smaller partitions it is expected that some will be automatically removed when the retention period of all the rows within each partition is reached.
As an alternative, you could purge data based upon a snapshot range. Depending on the snapshots chosen, this may remove data that has not yet reached the retention limit so this may not be suitable for all cases. The following output shows the min and max snapshot_id
in each partition.
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;
/
Once you have split the partitions and identified a partition with a range of snap ids that can be deleted, you can free up the memory by dropping a snapshot range than matches the high and low snap_ids for the partition:
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);
相關文章
- ORACLE 10g SYSAUX表空間快速增長之WRH$_ACTIVE_SESSION_HISTORY篇Oracle 10gUXSession
- Clean WRH$_ACTIVE_SESSION_HISTORY in SYSAUXSessionUX
- oracle 10g SYSAUX表空間快速增長之WRH$_SQL_PLAN篇Oracle 10gUXSQL
- AWR資料導致SYSAUX表空間一直增長的問題UX
- AWR不自動刪除導致SYSAUX表空間滿UX
- Oracle清理SYSAUX表空間OracleUX
- SYSAUX表空間清理之SM/OPTSTATUX
- Oracle目錄由於TFA觸發bug導致jdb檔案未自動清理引起空間不足Oracle
- OGG相關的CPATURE導致SYSAUX表空間異常暴增處理UX
- 10g ORACLE_HOME空間滿導致SYSAUX表空間離線OracleUX
- WRH$_ACTIVE_SESSION_HISTORYSession
- TRUNCATE模式SQLLDR導致SECUREFILE的LOB空間不斷增長模式SQL
- Oracle SYSAUX 表空間使用率100% 導致的DB 故障OracleUX
- Win10硬碟空間如何自動清理Win10硬碟
- 【AWR】該怎樣清理SYSAUX表空間相關資料UX
- oracle10g的sysaux空間暴增與空間回收-轉載OracleUX
- 32、SYSAUX表空間UX
- Oracle10g以上sysaux表空間的維護和清理OracleUX
- oracle 10g SYSAUX表空間快速增長之WRI$_OPTSTAT_HISTGRM_HISTORY篇Oracle 10gUX
- oracle 表空間關閉自增長 autoextend offOracle
- ORACLE的SYSAUX 表空間OracleUX
- RAC因為localhost磁碟空間不夠導致has程式掛起localhost
- 10g 新特性 Recyclebin 空間的自動清理(zt)
- oracle 10g SYSAUX表空間快速增長之STREAMS$_APPLY_SPILL_MESSAGES篇Oracle 10gUXAPP
- 【RAC】因清理不完整導致RAC ASM例項建立失敗ASM
- 2.5.4.1 關於SYSAUX表空間UX
- 認識 SYSAUX 表空間(zt)UX
- 計算sysaux中各主件對sysaux空間的使用!UX
- 【AWR】清理SYSAUX表空間資料(第三季非暴力不合作)UX
- Fast Recovery Area空間用滿後的自動清理機制AST
- SYSAUX 表空間歷史統計資料過大purgeUX
- Oracle SYSAUX表空間使用率超過警戒閥值OracleUX
- 一次WRH$_ACTIVE_SESSION_HISTORY問題處理Session
- AWR佔用sysaux表空間太大UX
- oracle之 SYSAUX表空間維護OracleUX
- SYSAUX表空間管理及恢復UX
- 【AWR】該怎樣清理SYSAUX表空間相關資料(第二季)UX
- win10系統怎麼設定自動清理磁碟空間Win10