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=>4);

execute dbms_stats.gather_table_stats(ownname => 'sys',tabname => 'WRH$_SQLSTAT',partname => 'WRH$_SQLSTA_338469376_0',DEGREE=>4);

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的新特性意思是：兩個審計（標準審計和細粒度審計合併成一個一種審計）同放在一個表中