Oracle 數據庫審計功能非常強大,通常包括標准審計(包括用戶級審計和系統級審計)和細粒度審計。盡管如此,一不小心就容易造成性能問題。同時會把系統表空間給撐爆。下面的內容描述的是如何將審計從系統表空間剝離以及清理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 TRAIL
三、清除審計記錄
通過這個過程設定清除間隔
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非常規方式來處理。