AWR不自動刪除導致SYSAUX表空間滿
其主要是WRH$_ACTIVE_SESSION_HISTORY 的一個分割槽表佔了很多,沒遇到過的問題,總是感覺很緊張,尤其又是生產庫
於是乎,想了個笨方法開始手動刪WRH$_ACTIVE_SESSION_HISTORY的資料,刪的差不多了就開始收縮表,收縮完表空間有地了
可是過段時間又滿了,心想這笨方法可不行,開始找方法
在官方發現文件 387914.1就是說的這個問題
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:
alter session set "_swrf_test_action" = 72;
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';
其方法就是執行alter session set "_swrf_test_action" = 72; 讓AWR相關的分割槽表都建立一個新的分割槽,等待老分割槽的資料過期,oracle就會自動刪除。
可眼下表空間已經滿了,等不到老分割槽的資料過期,可咋辦
那麼可以先修改AWR的有效保留期,比如改成5天。等5天后oracle刪掉分割槽在把保留期改回來。如果老資料需要保留可以匯出AWR的資訊。
但是也有個問題,這次的資料已經刪了,那麼以後oracle會自動刪嗎?不會每次都要手動執行一下這個命了吧?希望有經驗的朋友告訴下,或者讓時間證明
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29339009/viewspace-2131604/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 10g ORACLE_HOME空間滿導致SYSAUX表空間離線OracleUX
- AWR資料導致SYSAUX表空間一直增長的問題UX
- AWR佔用sysaux表空間太大UX
- sysaux 表空間爆滿處理方法UX
- oracle sysaux表空間滿了處理辦法OracleUX
- Oracle SYSAUX 表空間使用率100% 導致的DB 故障OracleUX
- undo表空間滿導致的ogg discard檔案寫滿
- 【AWR】該怎樣清理SYSAUX表空間相關資料UX
- 32、SYSAUX表空間UX
- Oracle清理SYSAUX表空間OracleUX
- ORACLE的SYSAUX 表空間OracleUX
- Cancel 刪除 正在使用的臨時表空間的操作 將導致異常
- OGG相關的CPATURE導致SYSAUX表空間異常暴增處理UX
- 刪除表空間和表空間包含的檔案
- 2.5.4.1 關於SYSAUX表空間UX
- 認識 SYSAUX 表空間(zt)UX
- 刪除資料庫表空間資料庫
- MySQL 磁碟空間滿導致表空間相關資料檔案損壞故障處理MySql
- 【AWR】清理SYSAUX表空間資料(第三季非暴力不合作)UX
- SYSAUX表空間清理之SM/OPTSTATUX
- oracle之 SYSAUX表空間維護OracleUX
- SYSAUX表空間管理及恢復UX
- oracle級聯刪除使用者,刪除表空間Oracle
- ASM空間爆滿導致資料庫掛起ASM資料庫
- SYSAUX表空間滿對資料庫的影響以及解決措施UX資料庫
- ORACLE表空間的建立修改刪除Oracle
- SYSAUX表空間佔用過大情況下的處理(AWR資訊過多)UX
- 【AWR】該怎樣清理SYSAUX表空間相關資料(第二季)UX
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 4.2.1.7 規劃 SYSTEM 和 SYSAUX 表空間UX
- sysaux 表空間不足問題處理UX
- 修復受損的SYSAUX表空間UX
- oracle goldengate 目標端表空間滿導致程式abended處理過程OracleGo
- 2 Day DBA-管理Oracle例項-修改表空間-刪除表空間Oracle
- oracle 刪除表空間試驗面面觀Oracle
- 刪除臨時表空間hang處理
- 刪除表空間及所有指向關係
- 10G 新特性系列: SYSAUX 表空間UX