共計 9149 個字符，預計需要花費 23 分鐘才能閱讀完成。
SYSAUX 表空間滿對數據庫的影響以及解決措施是什么，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。
1. 概要
SYSAUX 表空間滿了，會影響登錄嘛？會影響數據庫正常運行嗎？怎么處理呢？容易的想到，增加空間，刪除被耗的空間，首先恢復生產業務為重。對于排查消耗 SYSAUX 空間大的對象，無論哪個版本，都一樣，查看是否存放較大的業務表，或者個人的中間表。對于 12C 來說，更加關注的一個就是：數據庫的 audit_trail 審計參數是否為開啟的，如果該參數值為 DB，則關注 ausdsys 模式下的 audsys 組件，即 audsys.CLI_SWP$459d3b9$1$1 表的 lob 段 SYS_LOB0000091784C00014$$ 的大小。如果為 NONE，則和 10G 與 11G 的排查一樣的思路去排查 SYSAUX 里的大對象。
2. 案例分析
1、備份檢查
在日常的備份維護當中，從備份檢查集中采集的結果發現，多個庫的歸檔備份失敗。
2、檢查歸檔備份或者全庫的備份的日志輸出
3、檢查 catalog 庫的 audit_trail 參數 values=’DB’.
4、查看該庫的 alert 日志：
5、查看消耗 SYSAUX 表空間大的對象（段）：
其中，audsys 組件的 lob 段占了 31.5G。
6、嘗試使用 sys 用戶對該 lob 段對應的表進行 truncate：
發現 sys 用戶也沒有權限。
7、使用存儲過程執行清理：
begin
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type = DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp = FALSE);
end;
/
附：oracle 官網提供的清理方法，查看：
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_audit_mgmt.htm#ARPLS65414
1 .exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (–
AUDIT_TRAIL_TYPE = DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –
LAST_ARCHIVE_TIME = sysdate);
2 .exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(–
AUDIT_TRAIL_TYPE = DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, –
USE_LAST_ARCH_TIMESTAMP = TRUE);
8、檢查 sysaux 表空間釋放，調整審計參數 audit_trail 參數 values=’NONE’。
9、重啟數據庫，多個庫的歸檔備份恢復正常。
3.SYSAUX 表空間描述 3.1SYSAUX 表空間官網的描述
The SYSAUX tablespace is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEMtablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.
You can specify only datafile attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATE DATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include:
PERMANENT
READ WRITE
EXTENT MANAGMENT LOCAL
SEGMENT SPACE MANAGMENT AUTO
You cannot alter these attributes with an ALTER TABLESPACE statement, and any attempt to do so will result in an error. You cannot drop or rename the SYSAUX tablespace.
The size of the SYSAUX tablespace is determined by the size of the database components that occupy SYSAUX. See a list of allSYSAUX occupants. Based on the initial sizes of these components, the SYSAUX tablespace needs to be at least 240 MB at the time of database creation. The space requirements of the SYSAUX tablespace will increase after the database is fully deployed, depending on the nature of its use and workload. For more information on how to manage the space consumption of the SYSAUX tablespace on an ongoing basis, please refer to the Managing the SYSAUX Tablespace
3.2SYSAUX 表空間中文簡述
Oracle 從 10G 開始引入的, 以前一些使用獨立表空間或系統表空間的數據庫組件現在在 SYSAUX 表空間中創建. 通過分離這些組件和功能，SYSTEM 表空間的負荷得以減輕. 反復創建一些相關對象及組件引起 SYSTEM 表空間的碎片問題得以避免。如果 SYSAUX 表空間不可用, 數據庫核心功能將保持有效; 使用 SYSAUX 表空間的特點將會失敗或功能受限.
3.3SYSAUX 表空間的主要組件
查看 SYSAUX 表空間組件的信息語句：
col Item For a30
col Space Used(GB) For a10
col Schema For a20
col MoveProcedure For a40
SELECT occupant_name Item ,
round(space_usage_kbytes/1024,3) Space Used (MB) ,
schema_name Schema ,
move_procedure MoveProcedure
FROM v$sysaux_occupants
ORDER BY 2 Desc;
這個腳本執行出來的結果包括有：組件名、組件消耗的空間大小、對象模式和組件移動使用的存儲過程。
3.3.1 11G 的組件
3.3.2 12C 的組件
4. 防范 SYSAUX 表空間滿的措施
根據 sysaux 表空間的使用情況，在 10G 和 11G，如果在建庫后給 sysaux 補充一次數據文件，沒有生產業務表或者個人中間表放進來，改表空間增長較為緩慢，相對穩定。
4.1 權限防范
權限上，防止生產用戶或者個人用把表放到 SYSAUX 表空間。
1、創建賬號時，禁止配置用戶的 DEFAUL TABLESPACE 為 SYSAUX;
2、不管生產賬號或者個人賬號，盡量少授權 UNLIMITED TABLESPACE 給到這些用戶，或者 quota unlimited on SYSAUX, 當然根據實際生產業務定。
4.2 參數防范
對于 12C 而言，如果沒有特殊需求，調整參數 audit_trail 為 NONE 并重啟數據庫生效。
4.3 存儲防范
適當增大 SYSAUX 表空間。
4.4 快照和統計信息保留策略防范 4.4.1 調整數據統計信息和快照的保存策略
1、調整歷史統計信息保留時間：
修改統計信息的保持時間，默認為 31 天，這里修改為 7 天，過期的統計信息會自動被刪除。
sys@PROD select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
—————————
31
2、修改統計信息保留時間：
sys@PROD exec dbms_stats.alter_stats_history_retention(10);
PL/SQL procedure successfully completed.
3、再次查看歷史統計信息保留的天數：
sys@PROD select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
—————————
10
4.4.2 調整 AWR 快照的保存時間
例如改為 7 天 (7*24*60), 每小時收集一次，默認情況下當前系統的保留時間為 8 天,1 小時采樣一次。
1、查看當前的快照保留時間和采集周期：
sys@PROD col RETENTION for a20
sys@PROD col SNAP_INTERVAL for a25
sys@PROD select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
———- ————————- ——————– ———-
338469376 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
2、查看系統的當前的 MOVING_WINDOW_SIZE：
sys@PROD select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
———- —————————————————————- ————- ——————
338469376 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
3、嘗試調整 AWR 的快照的收集時間 60 分鐘一次和保留時間 7 天：
sys@PROD begin
2 dbms_workload_repository.modify_snapshot_settings(
3 interval = 60,
4 retention = 10080,
5 topnsql = 50
6 );
7 end;
8 /
begin
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (604800)
ORA-06512: at SYS.DBMS_WORKLOAD_REPOSITORY , line 174
ORA-06512: at SYS.DBMS_WORKLOAD_REPOSITORY , line 222
ORA-06512: at line 2
4、先調整小于當前保留的時間窗口：
sys@PROD exec dbms_workload_repository.modify_baseline_window_size(7);
PL/SQL procedure successfully completed.
5、再次調整 AWR 的快照的收集時間 60 分鐘一次和保留時間 7 天：
sys@PROD begin
2 dbms_workload_repository.modify_snapshot_settings(
3 interval = 60,
4 retention = 10080,
5 topnsql = 50
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
6、調整后，確認 AWR 的快照的保留時間：
sys@PROD select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
———- ————————- ——————– ———-
338469376 +00000 01:00:00.0 +00007 00:00:00.0 50
5. 清理 SYSAUX 消耗空間大的組件內容 5.1 通過刪除 AWR 快照清理空間
1、查看當前保留的快照的 SNAP_ID：
sys@PROD select min(snap_id),max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
2561 2570
2、嘗試刪除較早的幾個快照，例如刪除最前的 5 個快照：
sys@PROD exec dbms_workload_repository.drop_snapshot_range(low_snap_id = 2561,high_snap_id = 2561+4);
或者：
exec dbms_workload_repository.drop_snapshot_range(2561,2565,338469376)；
3、查看當前保留的快照的 SNAP_ID：
sys@PROD select min(snap_id),max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
2566 2570
如果快照保留得比較多的話，在刪除快照的過程，比較消耗 CPU 資源，比較慢，當中會對以 WRH$_開頭的分區表進行 delete 操作。所以當我們通過查看 SNAP_ID，舊的快照已經被刪除，但是 sysaux 里面的組件大小沒有改變，依然原來大小，sysaux 表空間沒有釋放。接著就是對 WRH$_相關表進行高水位回收，收完就是收集相關表的統計信息。
4、刪除舊的快照后進行高水位回收，例如：
11:40:03 sys@PROD ALTER TABLE SYS.WRH$_LATCH MODIFY PARTITION WRH$_LATCH_338469376_0 SHRINK SPACE;
Table altered.
Elapsed: 00:00:13.11
11:42:36 sys@PROD ALTER TABLE SYS.WRH$_SQLSTAT MODIFY PARTITION WRH$_SQLSTA_338469376_0 SHRINK SPACE;
Table altered.
Elapsed: 00:00:12.55
5、或者對更多的表進行高水位回收：
alter table WRH$_SQLSTAT shrink space;
alter table WRH$_SYSSTAT shrink space;
alter table WRH$_SEG_STAT shrink space;
alter table WRH$_LATCH shrink space;
… …
6、收集回收高水位之后的表的統計信息：
execute dbms_stats.gather_table_stats(ownname = sys ,tabname = WRH$_LATCH MODIFY ,partname = WRH$_LATCH_338469376_0 ,DEGREE=
execute dbms_stats.gather_table_stats(ownname = sys ,tabname = WRH$_SQLSTAT ,partname = WRH$_SQLSTA_338469376_0 ,DEGREE=
5.2 通過刪除過舊的統計信息清理空間
23:46:54 sys@PROD exec dbms_stats.purge_stats(sysdate-6);
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.70
alter table WRH$_SQLSTAT shrink space;
alter table WRH$_SYSSTAT shrink space;
alter table WRH$_SEG_STAT shrink space;
alter table WRH$_LATCH shrink space;
… …
5.3 刪除部分歷史記錄表清理空間
針對部分歷史統計信息表進行刪除收縮，例如刪除無效的 ASH 歷史會話記錄表 wrh$_active_session_history
1、查看無效的歷史會話記錄：
SELECT COUNT(1) Orphaned_ASH_Rows
FROM wrh$_active_session_history a
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
);
2、進行刪除無效的歷史會話記錄：
DELETE FROM wrh$_active_session_history a
WHERE NOT EXISTS (SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number);
23392228 rows deleted.
SQL commit;
3、回收高水位：
alter table wrh$_active_session_history shrink space;
收完之后，就是重新收集該表的統計信息。
5.4 通過移動部分組件內容到其他表空間清理空間
從上面查看 SYSAUX 表空間的組件的結果中，可以看到，結果中的 MoveProcedure 字段的內容，提供了移動該組件內容的存儲過程，存儲過程后接目標表空間的參數，就可以實現移動了，當然也可以重新移動回 SYSAUX 表空間，前提是確保目標表空間大小足夠。例如，對 Logminer 從 SYSAUX 表空間，遷移到 users 表空間，在還原回來：
1、查看移動前的表空間：
2、移動 Logminer 到 users 表空間：
21:39:40 sys@PROD exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE(USERS
PL/SQL procedure successfully completed.
3、回遷 Logminer 組件到 SYSAUX 表空間：
22:07:55 sys@PROD exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE(SYSAUX
PL/SQL procedure successfully completed.
補充：
再補充一下第 5 部分：sysaux 消耗空間大的處理，也是以前經常遇到的案例：
1. 理解 sysaux 表空間各組件的用途，最容易遇到空間增長的應該是下面這三個組件：
SM/ADVISOR：指的是數據庫中的 sql tuning advisor、sql access advisor、ADDM 等自動維護任務產生的數據
SM/AWR：AWR 的快照信息，空間大小取決于產生快照的頻率和保留時間
SM/OPTSTAT：用于存儲優化器統計信息的歷史版本數據。
2. 為什么有定期清理任務，還是會出現空間異常增長？
情況一，數據存在保留期，例如快照保留時間默認為 31 天，過期的數據將會被數據庫的 MMON 進程定期自動刪除, 但是這個進程每次只運行 5 分鐘，超過 5 分鐘清理任務就會中斷，所以數據清理可能是不成功或不徹底的。這個問題 oracle 在新版本中提供了補丁，把大表改成了按天的分區表，清理方式變成 truncate 就不會出現清不掉的情況了。
情況二，默認的保留期太長，例如 dba_scheduler_job_classes 中任務調度的運行日志默認保留期就是 1000000 天。
3. 如何處理？
一般不使用 delete，然后 shrink space 這樣的方式去清理空間，而是調用 oracle 自己實現的清理函數，例如清理任務運行的日志使用 DBMS_SCHEDULER.PURGE_LOG(); 清理所有統計信息使用 dbms_stats.purge_stats(dbms_stats.PURGE_ALL);
總結
對于 SYSAUX 滿了，數據庫受到哪些影響，從目前遇到的案例來看，就是在 12C 數據庫審計參數 audit_trail 為 DB 的時候，該表空間滿了，直接影響用戶登錄數據庫，10G 和 11G 未遇到受影響的情況。經過一些模擬實驗測試，在 12C 和 11G 的版本里面，當 audit_trail 為 NONE 的時候,SYSAUX 滿了，都不影響數據庫的可用性。對于 SYSAUX 表空間的其他組件，則需要更多的測試實驗區探究，各組件的內容清理與移動，大致一樣。
以上是個人的親身經歷的一個案例、測試效果和一些看法，如果還有哪些紕漏或者錯誤，希望眾大神指出并賜教，在往后的時間，加以補充，改進與學習。
附：
12C 安全審計的新特性：
Oracle Database 12c Security: New Unified Auditing
連接 1：https://blogs.oracle.com/imc/oracle-database-12c-security:-new-unified-auditing
連接 2：https://www.cnblogs.com/shenfeng/p/oracle_12c_sysaux_clean.html
## 這里 12C 的新特性意思是：兩個審計（標準審計和細粒度審計合并成一個一種審計）同放在一個表中
看完上述內容，你們掌握 SYSAUX 表空間滿對數據庫的影響以及解決措施是什么的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！
