手動清理Oracle審計記錄

手動清理Oracle審計記錄

Oracle Database Audit功能非常強大，通常包括標準審計(包括使用者級審計和系統級審計)和細粒度審計。儘管如此，一不小心就容易造成效能問題。同時會把系統資料表空間給撐爆。下面的內容描述的是如何將審計從系統資料表空間剝離以及清理Oracle審計記錄，供大家參考。

一、審計的相關配置
--環境
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> show parameter audit

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /home/oraprod/app/product/11.2
                                                .0/dbhome_1/rdbms/audit
audit_sys_operations                boolean    FALSE
audit_syslog_level                  string
audit_trail                          string      DB  --此值為當前Oracle 11gR2預設配置

--從下面的查詢中可以看出，當前的審計位於system資料表空間
SQL> col segment_name FOR a10
SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name ='AUD$';

OWNER                          SEGMENT_NA TABLESPACE_NAME
------------------------------ ---------- ------------------------------
SYS                            AUD$      SYSTEM

二、修改審計儲存資料表空間
新增一個資料表空間用於儲存審計日誌
SQL> CREATE tablespace audit_data datafile '/home/oracle/app/oradata/orcl/audit01.dbf'
  2  SIZE 100M autoextend ON NEXT 50M;

SQL> @tbs_free.sql

TABLESPACE_NAME                USED (MB FREE (MB TOTAL (M PER_FR
------------------------------ -------- -------- -------- ------
AUDIT_DATA                            1    1,199    1,200  100 %
SYSAUX                            1,133      77    1,210    6 %
SYSTEM                            1,875      15    1,890    1 %

-- 設定審計資料存放資料表空間 
SQL> BEGIN
  2    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4      AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA'
  5    );
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-46267: Insufficient space in 'AUDIT_DATA' tablespace, cannot complete
operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1576
ORA-06512: at line 2
-- 錯誤提示，儘管我們使用了自動擴充資料表空間，依舊提示空間不夠

-- 查看當前審計資料大小，如下為1152MB
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

SEGMENT_NAME              BYTES/1024/1024
------------------------- ---------------
AUD$                                1152

-- 下面調整資料檔案大小
SQL> alter database datafile '/home/oracle/app/oradata/orcl/audit01.dbf' resize 1200m;

Database altered.

-- 再次設定審計資料存放資料表空間OK
SQL> BEGIN
  2    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4      AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA'
  5    );
  6  END;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:02:23.10
--整個過程花費了2m23s，主要是期間進行了資料搬遷

SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name ='AUD$';

OWNER                          SEGMENT_NAME                  TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            AUD$                          AUDIT_DATA

SQL> @tbs_free.sql

TABLESPACE_NAME                USED (MB FREE (MB TOTAL (M PER_FR
------------------------------ -------- -------- -------- ------
AUDIT_DATA                        1,153      47    1,200    4 %
SYSAUX                            1,143      67    1,210    6 %
SYSTEM                              724    1,166    1,890  62 %

-- 從上面的這個查詢可以看出，原來位於system資料表空間的AUD$被遷移到了AUDIT_DATA
-- 相應地AUDIT_DATA資料表空間已使用增加，而SYSTEM資料表空間使用率下降

-- 查看審計資料字典配置資訊
SQL> col PARAMETER_NAME FOR a30
SQL> col PARAMETER_VALUE FOR a15
SQL> col AUDIT_TRAIL FOR a20
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL
  2  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
  3  WHERE audit_trail = 'STANDARD AUDIT TRAIL';

PARAMETER_NAME                PARAMETER_VALUE AUDIT_TRAIL
------------------------------ --------------- --------------------
DB AUDIT TABLESPACE            AUDIT_DATA      STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000          STANDARD AUDIT TRAIL1
三、清除審計記錄
通過這個過程設定清除間隔
SQL> BEGIN
  2    DBMS_AUDIT_MGMT.init_cleanup(
  3      audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  4      default_cleanup_interval => 120 /* hours */);
  5  END;
  6  /

PL/SQL procedure successfully completed.

-- 下面嚴驗證審計日誌清除是否已開啟
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
  3      DBMS_OUTPUT.put_line('YES');
  4    ELSE
  5      DBMS_OUTPUT.put_line('NO');
  6    END IF;
  7  END;
  8  /
YES

PL/SQL procedure successfully completed.

SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

SEGMENT_NAME        BYTES/1024/1024
------------------- ---------------
AUD$                          1152

SQL> select 'Leshami' As author,'http://blog.csdn.net/leshami' as Blog from dual;

AUTHOR  BLOG
------- ----------------------------
Leshami http://blog.csdn.net/leshami

SQL> select count(*) from AUD$;

  COUNT(*)
----------
  5908086

SQL> select min(ntimestamp#) from aud$;

MIN(NTIMESTAMP#)
---------------------------------------------------------------------------
20-AUG-14 06.11.09.901253 AM

-- 設定歸檔間隔
SQL> BEGIN
  2  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
  3  audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4  last_archive_time => SYSTIMESTAMP-10);
  5  END;
  6  /

PL/SQL procedure successfully completed

--查看設定的歸檔間隔
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL            0 09-OCT-15 01.27.17.000000 PM +00:00

--通過調用DBMS_AUDIT_MGMT.clean_audit_trail進行手動清理審計日誌
BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
  audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  use_last_arch_timestamp => TRUE);
END;
/

DBMS_AUDIT_MGMT.clean_audit_trail
This procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the
SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.

--也可以通過建立一個purge Job來進行清理已歸檔的曆史審計記錄
SQL> BEGIN
  2    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
  3      AUDIT_TRAIL_TYPE          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4      AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
  5      AUDIT_TRAIL_PURGE_NAME    => 'Daily_Audit_Purge_Job',
  6      USE_LAST_ARCH_TIMESTAMP    => TRUE
  7    );
  8  END;
  9  /

PL/SQL procedure successfully completed.

-- 本次測試使用了job進行清理，注，上面的purge job 並非使用DBMS_SCHEDULER.CREATE_JOB建立
-- 執行job用於清理歸檔，通過觀察，由於redo log size為50MB，切換較為頻繁，花費了19分鐘
-- 同時伴隨有Checkpoint not complete等待事件，可見redo size過小
SQL> exec DBMS_SCHEDULER.RUN_JOB(job_name => 'SYS.DAILY_AUDIT_PURGE_JOB');

PL/SQL procedure successfully completed.

Elapsed: 00:19:26.38

SQL> select count(*) from AUD$;

  COUNT(*)
----------
        12

--經查看，清理後空間並沒有釋放       
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

SEGMENT_NAME                  BYTES/1024/1024
------------------------------ ---------------
AUD$                                      1152

SQL> alter table sys.aud$ shrink space cascade;
alter table sys.aud$ shrink space cascade
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table sys.aud$ enable row movement;

Table altered.

SQL> alter table sys.aud$ shrink space cascade;

Table altered.

SQL> alter table sys.aud$ disable row movement;

Table altered.

-- 下面的查詢可以看到，空間已經被釋放
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

SEGMENT_NAME        BYTES/1024/1024
-------------------- ---------------
AUD$                          .0625

四、小結
a、對於Oracle 11g，審計功能預設被開啟，因此如果在必須啟用的情況下應考慮效能影響；
b、開啟審計的情況下，建議將審計從system或sysaux資料表空間剝離，使用單獨的資料表空間；
c、對於曆史審計日誌的清除，應考慮清除期間所帶來的效能影響；
d、調用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION這個過程已經開始了搬遷過程，如果審計日誌很龐大，應考慮IO影響；
e、審計日誌的清除需要先設定歸檔，已歸檔的審計日誌會被清理;
f、也可以通過trunate table aud$ reuse storage以及deallocate非常規方式來處理。