SYSAUX表空間滿對(duì)數(shù)據(jù)庫(kù)的影響以及解決措施是什么

SYSAUX表空間滿對(duì)數(shù)據(jù)庫(kù)的影響以及解決措施是什么，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

1.概要

SYSAUX表空間滿了，會(huì)影響登錄嘛？會(huì)影響數(shù)據(jù)庫(kù)正常運(yùn)行嗎？怎么處理呢？容易的想到，增加空間，刪除被耗的空間，首先恢復(fù)生產(chǎn)業(yè)務(wù)為重。對(duì)于排查消耗SYSAUX空間大的對(duì)象，無(wú)論哪個(gè)版本，都一樣，查看是否存放較大的業(yè)務(wù)表，或者個(gè)人的中間表。對(duì)于12C來(lái)說(shuō)，更加關(guān)注的一個(gè)就是：數(shù)據(jù)庫(kù)的audit_trail審計(jì)參數(shù)是否為開(kāi)啟的，如果該參數(shù)值為DB，則關(guān)注ausdsys模式下的audsys組件，即audsys.CLI_SWP$459d3b9$1$1表的lob段SYS_LOB0000091784C00014$$的大小。如果為NONE，則和10G與11G的排查一樣的思路去排查SYSAUX里的大對(duì)象。

2.案例分析

1、備份檢查

在日常的備份維護(hù)當(dāng)中，從備份檢查集中采集的結(jié)果發(fā)現(xiàn)，多個(gè)庫(kù)的歸檔備份失敗。

2、檢查歸檔備份或者全庫(kù)的備份的日志輸出

3、檢查catalog庫(kù)的audit_trail參數(shù)values=’DB’.

4、查看該庫(kù)的alert日志：

5、查看消耗SYSAUX表空間大的對(duì)象（段）：

其中，audsys組件的lob段占了31.5G。

6、嘗試使用sys用戶對(duì)該lob段對(duì)應(yīng)的表進(jìn)行truncate：

發(fā)現(xiàn)sys用戶也沒(méi)有權(quán)限。

7、使用存儲(chǔ)過(guò)程執(zhí)行清理：

begin

   dbms_audit_mgmt.clean_audit_trail(

       audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

        use_last_arch_timestamp  =>  FALSE);

   end;

/

附：oracle官網(wǎng)提供的清理方法，查看：

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表空間釋放，調(diào)整審計(jì)參數(shù)audit_trail參數(shù)values=’NONE’。

9、重啟數(shù)據(jù)庫(kù)，多個(gè)庫(kù)的歸檔備份恢復(fù)正常。

3.SYSAUX表空間描述

3.1SYSAUX表空間官網(wǎng)的描述

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表空間中文簡(jiǎn)述

Oracle從10G 開(kāi)始引入的,以前一些使用獨(dú)立表空間或系統(tǒng)表空間的數(shù)據(jù)庫(kù)組件現(xiàn)在在SYSAUX表空間中創(chuàng)建.通過(guò)分離這些組件和功能，SYSTEM表空間的負(fù)荷得以減輕.反復(fù)創(chuàng)建一些相關(guān)對(duì)象及組件引起SYSTEM表空間的碎片問(wèn)題得以避免。如果SYSAUX表空間不可用,數(shù)據(jù)庫(kù)核心功能將保持有效;使用SYSAUX表空間的特點(diǎn)將會(huì)失敗或功能受限.

3.3SYSAUX表空間的主要組件

查看SYSAUX表空間組件的信息語(yǔ)句：

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;

這個(gè)腳本執(zhí)行出來(lái)的結(jié)果包括有：組件名、組件消耗的空間大小、對(duì)象模式和組件移動(dòng)使用的存儲(chǔ)過(guò)程。

3.3.1 11G的組件

3.3.2 12C的組件

4.防范SYSAUX表空間滿的措施

根據(jù)sysaux表空間的使用情況，在10G和11G，如果在建庫(kù)后給sysaux補(bǔ)充一次數(shù)據(jù)文件，沒(méi)有生產(chǎn)業(yè)務(wù)表或者個(gè)人中間表放進(jìn)來(lái)，改表空間增長(zhǎng)較為緩慢，相對(duì)穩(wěn)定。

4.1權(quán)限防范

權(quán)限上，防止生產(chǎn)用戶或者個(gè)人用把表放到SYSAUX表空間。

1、創(chuàng)建賬號(hào)時(shí)，禁止配置用戶的DEFAUL TABLESPACE 為SYSAUX;

2、不管生產(chǎn)賬號(hào)或者個(gè)人賬號(hào)，盡量少授權(quán)UNLIMITED TABLESPACE給到這些用戶，或者quota unlimited on SYSAUX,當(dāng)然根據(jù)實(shí)際生產(chǎn)業(yè)務(wù)定。

4.2 參數(shù)防范

對(duì)于12C而言，如果沒(méi)有特殊需求，調(diào)整參數(shù)audit_trail為NONE并重啟數(shù)據(jù)庫(kù)生效。

4.3 存儲(chǔ)防范

適當(dāng)增大SYSAUX表空間。

4.4快照和統(tǒng)計(jì)信息保留策略防范

4.4.1調(diào)整數(shù)據(jù)統(tǒng)計(jì)信息和快照的保存策略

1、調(diào)整歷史統(tǒng)計(jì)信息保留時(shí)間：

修改統(tǒng)計(jì)信息的保持時(shí)間，默認(rèn)為31天，這里修改為7天，過(guò)期的統(tǒng)計(jì)信息會(huì)自動(dòng)被刪除。

sys@PROD>select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31

2、修改統(tǒng)計(jì)信息保留時(shí)間：

sys@PROD>exec dbms_stats.alter_stats_history_retention(10);

PL/SQL procedure successfully completed.

3、再次查看歷史統(tǒng)計(jì)信息保留的天數(shù)：

sys@PROD>select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         10

4.4.2調(diào)整AWR快照的保存時(shí)間

例如改為7天(7*24*60),每小時(shí)收集一次，默認(rèn)情況下當(dāng)前系統(tǒng)的保留時(shí)間為8天,1小時(shí)采樣一次。

1、查看當(dāng)前的快照保留時(shí)間和采集周期：

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、查看系統(tǒng)的當(dāng)前的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、嘗試調(diào)整AWR的快照的收集時(shí)間60分鐘一次和保留時(shí)間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、先調(diào)整小于當(dāng)前保留的時(shí)間窗口：

sys@PROD>exec dbms_workload_repository.modify_baseline_window_size(7);

PL/SQL procedure successfully completed.

5、再次調(diào)整AWR的快照的收集時(shí)間60分鐘一次和保留時(shí)間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、調(diào)整后，確認(rèn)AWR的快照的保留時(shí)間：

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消耗空間大的組件內(nèi)容

5.1通過(guò)刪除AWR快照清理空間

1、查看當(dāng)前保留的快照的SNAP_ID：

sys@PROD>select min(snap_id),max(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)

------------ ------------

        2561         2570

2、嘗試刪除較早的幾個(gè)快照，例如刪除最前的5個(gè)快照：

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、查看當(dāng)前保留的快照的SNAP_ID：

sys@PROD>select min(snap_id),max(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)

------------ ------------

        2566         2570

如果快照保留得比較多的話，在刪除快照的過(guò)程，比較消耗CPU資源，比較慢，當(dāng)中會(huì)對(duì)以WRH$_開(kāi)頭的分區(qū)表進(jìn)行delete操作。所以當(dāng)我們通過(guò)查看SNAP_ID，舊的快照已經(jīng)被刪除，但是sysaux里面的組件大小沒(méi)有改變，依然原來(lái)大小，sysaux表空間沒(méi)有釋放。接著就是對(duì)WRH$_相關(guān)表進(jìn)行高水位回收，收完就是收集相關(guān)表的統(tǒng)計(jì)信息。

4、刪除舊的快照后進(jìn)行高水位回收，例如：

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、或者對(duì)更多的表進(jìn)行高水位回收：

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、收集回收高水位之后的表的統(tǒng)計(jì)信息：

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通過(guò)刪除過(guò)舊的統(tǒng)計(jì)信息清理空間

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刪除部分歷史記錄表清理空間

針對(duì)部分歷史統(tǒng)計(jì)信息表進(jìn)行刪除收縮，例如刪除無(wú)效的ASH歷史會(huì)話記錄表wrh$_active_session_history

1、查看無(wú)效的歷史會(huì)話記錄：

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、進(jìn)行刪除無(wú)效的歷史會(huì)話記錄：

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;

收完之后，就是重新收集該表的統(tǒng)計(jì)信息。

5.4通過(guò)移動(dòng)部分組件內(nèi)容到其他表空間清理空間

從上面查看SYSAUX表空間的組件的結(jié)果中，可以看到，結(jié)果中的MoveProcedure字段的內(nèi)容，提供了移動(dòng)該組件內(nèi)容的存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程后接目標(biāo)表空間的參數(shù)，就可以實(shí)現(xiàn)移動(dòng)了，當(dāng)然也可以重新移動(dòng)回SYSAUX表空間，前提是確保目標(biāo)表空間大小足夠。例如，對(duì)Logminer 從SYSAUX 表空間，遷移到users表空間，在還原回來(lái)：

1、查看移動(dòng)前的表空間：

2、移動(dòng)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.

補(bǔ)充：

再補(bǔ)充一下第5部分：sysaux消耗空間大的處理，也是以前經(jīng)常遇到的案例：

1. 理解sysaux表空間各組件的用途，最容易遇到空間增長(zhǎng)的應(yīng)該是下面這三個(gè)組件：

   SM/ADVISOR：指的是數(shù)據(jù)庫(kù)中的sql tuning advisor、sql access advisor、ADDM等自動(dòng)維護(hù)任務(wù)產(chǎn)生的數(shù)據(jù)

   SM/AWR：AWR的快照信息，空間大小取決于產(chǎn)生快照的頻率和保留時(shí)間

   SM/OPTSTAT：用于存儲(chǔ)優(yōu)化器統(tǒng)計(jì)信息的歷史版本數(shù)據(jù)。

2. 為什么有定期清理任務(wù)，還是會(huì)出現(xiàn)空間異常增長(zhǎng)？

   情況一，數(shù)據(jù)存在保留期，例如快照保留時(shí)間默認(rèn)為31天，過(guò)期的數(shù)據(jù)將會(huì)被數(shù)據(jù)庫(kù)的MMON進(jìn)程定期自動(dòng)刪除,但是這個(gè)進(jìn)程每次只運(yùn)行5分鐘，超過(guò)5分鐘清理任務(wù)就會(huì)中斷，所以數(shù)據(jù)清理可能是不成功或不徹底的。這個(gè)問(wèn)題oracle在新版本中提供了補(bǔ)丁，把大表改成了按天的分區(qū)表，清理方式變成truncate就不會(huì)出現(xiàn)清不掉的情況了。

   情況二，默認(rèn)的保留期太長(zhǎng)，例如dba_scheduler_job_classes中任務(wù)調(diào)度的運(yùn)行日志默認(rèn)保留期就是1000000天。

3. 如何處理？

   一般不使用delete，然后shrink space這樣的方式去清理空間，而是調(diào)用oracle自己實(shí)現(xiàn)的清理函數(shù)，例如清理任務(wù)運(yùn)行的日志使用 DBMS_SCHEDULER.PURGE_LOG();清理所有統(tǒng)計(jì)信息使用dbms_stats.purge_stats(dbms_stats.PURGE_ALL);

總結(jié)

對(duì)于SYSAUX滿了，數(shù)據(jù)庫(kù)受到哪些影響，從目前遇到的案例來(lái)看，就是在12C數(shù)據(jù)庫(kù)審計(jì)參數(shù)audit_trail為DB的時(shí)候，該表空間滿了，直接影響用戶登錄數(shù)據(jù)庫(kù)，10G和11G未遇到受影響的情況。經(jīng)過(guò)一些模擬實(shí)驗(yàn)測(cè)試，在12C和11G的版本里面，當(dāng)audit_trail為NONE的時(shí)候,SYSAUX滿了，都不影響數(shù)據(jù)庫(kù)的可用性。對(duì)于SYSAUX表空間的其他組件，則需要更多的測(cè)試實(shí)驗(yàn)區(qū)探究，各組件的內(nèi)容清理與移動(dòng)，大致一樣。

以上是個(gè)人的親身經(jīng)歷的一個(gè)案例、測(cè)試效果和一些看法，如果還有哪些紕漏或者錯(cuò)誤，希望眾大神指出并賜教，在往后的時(shí)間，加以補(bǔ)充，改進(jìn)與學(xué)習(xí)。

附：

12C安全審計(jì)的新特性：

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的新特性意思是：兩個(gè)審計(jì)（標(biāo)準(zhǔn)審計(jì)和細(xì)粒度審計(jì)合并成一個(gè)一種審計(jì)）同放在一個(gè)表中

看完上述內(nèi)容，你們掌握SYSAUX表空間滿對(duì)數(shù)據(jù)庫(kù)的影響以及解決措施是什么的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！