Oracle清理SYSAUX表空間
11g的資料庫因為BUG，導致與AWR相關的分割槽表不會自動split，這樣一來，oracle自動清理策略不會生效，導致AWR資料越來越大，撐滿SYSAUX表空間。
12c不存在這個問題。
檢查快照實際保留時間與設定值的差異
14:51:28 SQL> col ash for a30
14:51:41 SQL> col SNAP for a30
14:51:46 SQL> col RETENTION for a30
14:51:51 SQL> select sysdate - a.sample_time ash,
14:51:53 2 sysdate - s.begin_interval_time snap,
14:51:53 3 c.RETENTION
14:51:53 4 from sys.wrm$_wr_control c,
14:51:53 5 (
14:51:53 6 select db.dbid,
14:51:53 7 min(w.sample_time) sample_time
14:51:53 8 from sys.v_$database db,
14:51:54 9 sys.Wrh$_active_session_history w
14:51:54 10 where w.dbid = db.dbid group by db.dbid
14:51:54 11 ) a,
14:51:54 12 (
14:51:54 13 select db.dbid,
14:51:54 14 min(r.begin_interval_time) begin_interval_time
14:51:54 15 from sys.v_$database db,
14:51:54 16 sys.wrm$_snapshot r
14:51:54 17 where r.dbid = db.dbid
14:51:54 18 group by db.dbid
14:51:54 19 ) s
14:51:54 20 where a.dbid = s.dbid
14:51:54 21 and c.dbid = a.dbid;
ASH SNAP RETENTION
------------------------------ ------------------------------ ------------------------------
+000000851 21:45:18.673 +000000008 17:01:35.550 +00008 00:00:00.0
1 row selected.
ASH保留了851天，但是設定值是8天。
SYSAUX表空間的使用情況
15:04:31 SQL> COL Item FOR A30
15:05:18 SQL> COL Schema FOR A30
15:05:49 SQL> SELECT occupant_name "Item",
15:05:50 2 space_usage_kbytes / 1048576 "Space Used (GB)",
15:05:50 3 schema_name "Schema",
15:05:50 4 move_procedure "Move Procedure"
15:05:51 5 FROM v$sysaux_occupants
15:05:51 6 ORDER BY 2 desc ;
Item Space Used (GB) Schema Move Procedure
------------------------------ --------------- ------------------------------ ----------------------------------------------------------------
SM/AWR 10.2494507 SYS
SM/OPTSTAT .566101074 SYS
XDB .123962402 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
LOGMNR .073669434 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
SDO .072509766 MDSYS MDSYS.MOVE_SDO
SM/ADVISOR .067810059 SYS
EM .047058105 SYSMAN emd_maintenance.move_em_tblspc
AO .03729248 SYS DBMS_AW.MOVE_AWMETA
XSOQHIST .03729248 SYS DBMS_XSOQ.OlapiMoveProc
JOB_SCHEDULER .02557373 SYS
ORDIM/ORDDATA .013244629 ORDDATA ordsys.ord_admin.move_ordim_tblspc
SM/OTHER .00769043 SYS
XSAMD .005004883 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
SMON_SCN_TIME .004150391 SYS
EXPRESSION_FILTER .003540039 EXFSYS
TEXT .003540039 CTXSYS DRI_MOVE_CTXSYS
WM .002380371 WMSYS DBMS_WM.move_proc
SQL_MANAGEMENT_BASE .001647949 SYS
PL/SCOPE .001525879 SYS
STREAMS .001342773 SYS
LOGSTDBY .001342773 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE
EM_MONITORING_USER .000854492 DBSNMP
ORDIM .000427246 ORDSYS ordsys.ord_admin.move_ordim_tblspc
AUTO_TASK .000305176 SYS
ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
TSM 0 TSMSYS
AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK
ULTRASEARCH 0 WKSYS MOVE_WK
ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
STATSPACK 0 PERFSTAT
31 rows selected.
Elapsed: 00:00:00.84
SM/AWR的含義是Server Manageability - Automatic Workload Repository，如果它的值大那麼表示AWR資訊容量大。
SM/OPTSTAT的含義是Server Manageability - Optimizer Statistics History，如果它的值大那麼表示優化器統計資訊容量大。
查詢具體哪些表佔用量大
15:06:00 SQL> select * from (
15:06:41 2 select segment_name,SEGMENT_TYPE,sum(bytes)/1024/1024 total_mb from dba_segments where tablespace_name =
15:06:41 3 'SYSAUX' group by segment_name,SEGMENT_TYPE order by 3 desc)
15:06:41 4 where rownum <=20;
SEGMENT_NAME SEGMENT_TYPE TOTAL_MB
--------------------------------------------------------------------------------- ------------------ ----------
WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 2951.0625
WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION 768.0625
WRH$_EVENT_HISTOGRAM TABLE PARTITION 736.0625
WRH$_LATCH TABLE PARTITION 640.0625
WRH$_SQLSTAT TABLE PARTITION 528.0625
WRH$_SYSSTAT_PK INDEX PARTITION 432.0625
WRH$_SYSSTAT TABLE PARTITION 408.0625
WRH$_PARAMETER_PK INDEX PARTITION 384.0625
WRH$_LATCH_PK INDEX PARTITION 376.0625
WRH$_LATCH_MISSES_SUMMARY TABLE PARTITION 336.0625
WRH$_PARAMETER TABLE PARTITION 320.0625
WRH$_LATCH_MISSES_SUMMARY_PK INDEX PARTITION 320.0625
WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 304.0625
WRH$_SEG_STAT TABLE PARTITION 246.0625
WRH$_SERVICE_STAT_PK INDEX PARTITION 168.0625
WRH$_SYSTEM_EVENT TABLE PARTITION 168.0625
WRH$_SQLSTAT_PK INDEX PARTITION 160.0625
SYS_LOB0000006409C00004$$ LOBSEGMENT 144
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 136
WRH$_SEG_STAT_PK INDEX PARTITION 112.0625
20 rows selected.
Elapsed: 00:00:00.09
查詢快照資訊：
15:06:41 SQL> SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
20147 20354
1 row selected.
Elapsed: 00:00:00.00
15:07:22 SQL> SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
1 20354
1 row selected.
Elapsed: 00:00:03.83
可以用下面的方法清理指定的快照資料。但是一般情況下，表空間告警的時候，WRH$_ACTIVE_SESSION_HISTORY已經很大了，下面的方法其實就是delete，奇慢無比。
EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>1, high_snap_id=>3);
上面的儲存過程最主要的其實是下面的語句，可以通過v$session和v$sql抓取出來。
delete from WRH$_ACTIVE_SESSION_HISTORY tab where (:beg_snap <=
tab.snap_id and tab.snap_id <= :end_snap and dbi
d = :dbid) and not exists (select 1 from WRM$_BASELINE b
where (tab.dbid = b.dbid) and
(tab.snap_id >= b.start_snap_id) and
(tab.snap_id <= b.end_snap_id))
下面的命令可以展示每個分割槽最大最小的snap id
15:29:35 SQL> set serveroutput on
15:29:43 SQL> declare
15:29:43 2 CURSOR cur_part IS
15:29:43 3 SELECT partition_name from dba_tab_partitions
15:29:43 4 WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
15:29:43 5
15:29:43 6 query1 varchar2(200);
15:29:43 7 query2 varchar2(200);
15:29:43 8
15:29:43 9 TYPE partrec IS RECORD (snapid number, dbid number);
15:29:43 10 TYPE partlist IS TABLE OF partrec;
15:29:43 11
15:29:43 12 Outlist partlist;
15:29:43 13 begin
15:29:43 14 dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
15:29:43 15 dbms_output.put_line('--------------------------- ------- ----------');
15:29:43 16
15:29:43 17 for part in cur_part loop
15:29:43 18 query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
15:29:43 19 execute immediate query1 bulk collect into OutList;
15:29:44 20
15:29:44 21 if OutList.count > 0 then
15:29:44 22 for i in OutList.first..OutList.last loop
15:29:44 23 dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
15:29:44 24 end loop;
15:29:44 25 end if;
15:29:44 26
15:29:44 27 query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
15:29:44 28 execute immediate query2 bulk collect into OutList;
15:29:44 29
15:29:44 30 if OutList.count > 0 then
15:29:44 31 for i in OutList.first..OutList.last loop
15:29:44 32 dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
15:29:44 33 dbms_output.put_line('---');
15:29:44 34 end loop;
15:29:44 35 end if;
15:29:44 36
15:29:44 37 end loop;
15:29:44 38 end;
15:29:44 39 /
PARTITION NAME SNAP_ID DBID
--------------------------- ------- ----------
WRH$_ACTIVE_3245728642_0 Min 1 3245728642
WRH$_ACTIVE_3245728642_0 Max 20354 3245728642
---
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.44
下面展示一種清理方法。總體來說，就是保留最近的資料，將表清空，再將資料插回去。
除此之外，前面提到過此問題最關鍵的問題是因為分割槽不能自動split，下面的命令可以將所有與AWR有關的分割槽表全部split出一個新的分割槽出來。
alter session set "_swrf_test_action" = 72;
這樣就可以自動等待上一個分割槽資料過期，自動清理，而且還會清理所有的相關分割槽表。下面我展示的方法，只會清理最大的那張表，其他表中的過期資料還是存在，但是佔用量不大，所以也可以接受。
1. 建立臨時表，儲存近8天的資料
create table mingshuo.ash_bak_20190610 as select * from sys.WRH$_ACTIVE_SESSION_HISTORY where SAMPLE_TIME>=sysdate-9;
SQL> select count(*) from mingshuo.ash_bak_20190610;
COUNT(*)
----------
12648
1 row selected.
Elapsed: 00:00:00.01
2. 禁用AWR
exec dbms_workload_repository.modify_snapshot_settings(interval => 0);
3. truncate WRH$_ACTIVE_SESSION_HISTORY
TRUNCATE TABLE sys.WRH$_ACTIVE_SESSION_HISTORY;
4. truncate 完成後，重建 WRH$_ACTIVE_SESSION_HISTORY表的索引，並從備份表恢復資料
insert into sys.WRH$_ACTIVE_SESSION_HISTORY select * from mingshuo.ash_bak_20190610;
commit;
5. 檢視索引狀態
set line 300 pages 200
col owner for a20
col index_name for a30
select index_owner,
index_name,
partition_name,
status,
tablespace_name,
last_analyzed
from dba_ind_partitions
where index_name in (select index_name
from dba_indexes
where table_name in ('WRH$_ACTIVE_SESSION_HISTORY')
and table_owner = 'SYS');
6. 如果索引失效重建索引（測試的時候是沒有失效的，索引為本地索引）
alter index sys.WRH$_ACTIVE_SESSION_HISTORY_PK rebuild parallel 8 nologging;
alter index sys.WRH$_ACTIVE_SESSION_HISTORY_PK noparallel;
7. 啟動AWR
exec dbms_workload_repository.modify_snapshot_settings(interval => 60);
8. 測試awr和ash可能正常生成
---手動生成snapshot
exec dbms_workload_repository.create_snapshot;
@?/rdbms/admin/awrrpt
@?/rdbms/admin/ashrpt
9. 刪除備份表
drop table mingshuo.ash_bak_20190610 purge;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31480688/viewspace-2647203/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SYSAUX表空間清理之SM/OPTSTATUX
- ORACLE的SYSAUX 表空間OracleUX
- Oracle10g以上sysaux表空間的維護和清理OracleUX
- oracle之 SYSAUX表空間維護OracleUX
- 32、SYSAUX表空間UX
- 【AWR】該怎樣清理SYSAUX表空間相關資料UX
- 2.5.4.1 關於SYSAUX表空間UX
- 認識 SYSAUX 表空間(zt)UX
- ORACLE臨時表空間的清理Oracle
- oracle sysaux表空間滿了處理辦法OracleUX
- AWR佔用sysaux表空間太大UX
- SYSAUX表空間管理及恢復UX
- oracle清理和重建臨時表空間Oracle
- 10g ORACLE_HOME空間滿導致SYSAUX表空間離線OracleUX
- 【AWR】清理SYSAUX表空間資料（第三季非暴力不合作）UX
- Oracle SYSAUX表空間使用率超過警戒閥值OracleUX
- 4.2.1.7 規劃 SYSTEM 和 SYSAUX 表空間UX
- sysaux 表空間爆滿處理方法UX
- sysaux 表空間不足問題處理UX
- 修復受損的SYSAUX表空間UX
- 【AWR】該怎樣清理SYSAUX表空間相關資料（第二季）UX
- 10G 新特性系列： SYSAUX 表空間UX
- 從system/sysaux空間轉移TABLE&Index到其它表空間UXIndex
- Oracle SYSAUX 表空間使用率100% 導致的DB 故障OracleUX
- 清理oracle資料庫空間Oracle資料庫
- Oracle案例08——xx.xx.xx.xx,表空間 SYSAUX 使用率>95%%OracleUX
- oracle10g的sysaux空間暴增與空間回收-轉載OracleUX
- AWR不自動刪除導致SYSAUX表空間滿UX
- 記一次sysaux表空間壞塊修復UX
- sysaux表空間檔案損壞的處理(zt)UX
- SYSAUX表空間使用率高問題處理UX
- oracle 10g SYSAUX表空間快速增長之WRH$_SQL_PLAN篇Oracle 10gUXSQL
- Oracle表空間Oracle
- oracle 表空間Oracle
- 計算sysaux中各主件對sysaux空間的使用！UX
- 2.5.4 為 SYSAUX 表空間指定資料檔案屬性UX
- SYSAUX 表空間歷史統計資料過大purgeUX
- oracle 11g sysaux表空間使用率非常高的問題解決OracleUX