[20170207]11G審計日志清除.txt
--//11G預設打開了許多審計,比如登入審計(我個人建議僅僅審計不成功的登入,特别對登入密集的系統),如果系統上線時沒有關閉或者取
--//消一些審計,sys.aud$在system表空間,會導緻空間異常增加,而且占用system表空間不是很合理.必須建立良好的監測清理機制.
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> show parameter audit
NAME TYPE VALUE
--------------------- -------- --------------------------------
audit_file_dest string /u01/app/oracle/admin/book/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB, EXTENDED
--//這是安裝完成後預設設定.
SYS@book> select count(*) from sys.aud$;
COUNT(*)
----------
1049
--//我的測試環境,已經關閉登入審計,僅僅審計不成功的登入.
2.移動SYS.AUD$ ,SYS.FGA_LOG$到别的表空間:
SYS@book> SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('AUD$', 'FGA_LOG$');
OWNER SEGMENT_NAME TABLESPACE_NAME
------ -------------------- ------------------------------
SYS FGA_LOG$ SYSTEM
SYS AUD$ SYSTEM
--以sys使用者執行:
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'users');
END;
/
--//注意如果aud$占用空間很大,執行上述指令很慢.可以先在執行前做一些清理工作.
--//我這裡是測試環境,僅僅移動到users,個人建議建立單獨的表空間來保持這些審計資訊.
ALTER USER SYS QUOTA UNLIMITED ON users;
--說明:
The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.
SYS FGA_LOG$ USERS
SYS AUD$ USERS
3.設定清理的初始化工作:
--//任由日志檔案增加不是很合理,必須建立合理的清除機制,而oracle預設并沒有做清理的初始化工作:
SET SERVEROUTPUT ON
IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
NO
PL/SQL procedure successfully completed.
--//傳回NO,說明沒有做清理的初始化工作.
SYS@book> SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ -------------------- ----------------------------
DB AUDIT TABLESPACE USERS STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE USERS FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
10 rows selected.
--//定義24小時執行1次清理.
SYS.dbms_audit_mgmt.init_cleanup(
audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
default_cleanup_interval => 24 /* hours */); -- PARAMETER NOT USED ON 11GR2 (FUTURE USE)
YES
--//傳回YES,說明已經做清理的初始化工作.
DEFAULT CLEAN UP INTERVAL 24 STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24 FGA AUDIT TRAIL
12 rows selected.
--//看最後2行DEFAULT CLEAN UP INTERVAL,DEFAULT CLEAN UP INTERVAL.已經定義了清理間隔時間.
4.建立schedule,清理日志:
--//oracle處理這些問題的機制有點繁瑣,首先是标記那些需要clean,然後交由清除程式處理.
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => SYSDATE-30); END;',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
enabled => TRUE,
comments => 'Create an archive timestamp'
);
--//每個小時檢查1次,标記30天前的日志.一些參數可以根據需要自己調整.我自己的測試保留30天.建議生産系統保留100天或者半年.
$ cd /u01/app/oracle/admin/book/adump
$ ls -ltr
total 388
-rw-r----- 1 oracle oinstall 795 2016-12-09 09:03:07 book_s000_64359_20161209090307799573143795.aud
-rw-r----- 1 oracle oinstall 795 2016-12-16 09:06:47 book_s000_49594_20161216090647626121143795.aud
-rw-r----- 1 oracle oinstall 795 2017-01-09 09:11:32 book_s000_57001_20170109091132668817143795.aud
-rw-r----- 1 oracle oinstall 784 2017-01-18 16:11:12 book1_ora_40227_20170118161112745444143795.aud
-rw-r----- 1 oracle oinstall 778 2017-01-18 16:11:12 book1_ora_40185_20170118161112689635143795.aud
-rw-r----- 1 oracle oinstall 981 2017-01-18 16:12:04 book1_ora_40237_20170118161156064475143795.aud
...
--//檢查schedule是否啟動.
SYS@book> @ &r/pt2 'SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE ''%AUDIT%'''
old 6: from table(xmlsequence(cursor( &1 )))
new 6: from table(xmlsequence(cursor( SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%' )))
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ---------- --------------- ----------------------------------------------------------------------------------------------------
1 1 OWNER SYS
2 JOB_NAME DAILY_AUDIT_ARCHIVE_TIMESTAMP
3 JOB_STYLE REGULAR
4 JOB_CREATOR SYS
5 JOB_TYPE PLSQL_BLOCK
6 JOB_ACTION BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD
7 START_DATE 2017-02-07 10:06:40.000000 +08:00
8 REPEAT_INTERVAL FREQ=HOURLY;INTERVAL=24
9 ENABLED TRUE
10 STATE SCHEDULED
11 SYSTEM TRUE
12 NLS_ENV NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUME
13 COMMENTS Create an archive timestamp
13 rows selected.)
--//建立清理程式:
SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE
--//檢查schedule是否建立.
SYS@book> SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
JOB_NAME JOB_STAT AUDIT_TRAIL JOB_FREQUENCY
--------------------- -------- --------------------- ------------------------
DAILY_AUDIT_PURGE_JOB ENABLED STANDARD AUDIT TRAIL FREQ=HOURLY;INTERVAL=24
2 1 OWNER SYS
2 JOB_NAME DAILY_AUDIT_PURGE_JOB
6 JOB_ACTION BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(1, TRUE); END;
7 START_DATE 2017-02-07 10:10:39.837281 +08:00
13 COMMENTS Audit clean job = 'Daily_Audit_Purge_Job'
26 rows selected.)
--//這樣就不必要困惑aud$異常增加,以及審計目錄下存在許多檔案.
513
--//現在已經删除了一部分資料.
5.補充關閉登入登入的指令:
NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;
![linux-svn解除安裝與安裝[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)