[20170207]11G審計日誌清除.txt
[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@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$ USERS
SYS AUD$ USERS
3.設定清理的初始化工作:
--//任由日誌檔案增加不是很合理,必須建立合理的清除機制,而oracle預設並沒有做清理的初始化工作:
SET SERVEROUTPUT ON
BEGIN
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;
END;
/
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次清理.
BEGIN
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)
END;
/
SET SERVEROUTPUT ON
BEGIN
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;
END;
/
YES
PL/SQL procedure successfully completed.
--//返回YES,說明已經做清理的初始化工作.
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
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,然後交由清除程式處理.
BEGIN
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'
);
END;
/
--//每個小時檢查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.)
--//建立清理程式:
BEGIN
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
);
END;
/
--//檢查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
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
2 1 OWNER SYS
2 JOB_NAME DAILY_AUDIT_PURGE_JOB
3 JOB_STYLE REGULAR
4 JOB_CREATOR SYS
5 JOB_TYPE PLSQL_BLOCK
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
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 Audit clean job = 'Daily_Audit_Purge_Job'
26 rows selected.)
--//這樣就不必要困惑aud$異常增加,以及審計目錄下存在許多檔案.
SYS@book> select count(*) from sys.aud$;
COUNT(*)
----------
513
--//現在已經刪除了一部分資料.
5.補充關閉登入登入的命令:
NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2133145/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20170518]11G審計日誌清除3.txt
- vertica審計日誌
- [20170207]11G mmon程式死掉.txt
- 日誌審計系統
- oracle 審計日誌清理Oracle
- mysql審計日誌-ProxySQLMySql
- 日誌審計是什麼？為什麼企業需要日誌審計？
- 清除SqlServer日誌SQLServer
- .Net Core 審計日誌實現
- 最全 Kubernetes 審計日誌方案
- wazuh日誌審計--定製規則
- 通過日誌審計追蹤外部***
- mysql清除binlog日誌MySql
- 清除MySQL Binlog日誌MySql
- [20211105]索引分裂 塊清除 日誌增加.txt索引
- [20160830]清除日誌與跟蹤檔案.txt
- 請問日誌審計什麼意思呢？
- mysql 系統審計日誌格式說明:MySql
- 利用 ELK 處理 Percona 審計日誌
- Oracle RMAN 清除歸檔日誌Oracle
- Oracle RMAN清除歸檔日誌Oracle
- AUDIT_TRAIL設定及審計日誌清理AI
- 清除SQL Server資料庫日誌SQLServer資料庫
- [20211105]索引分裂塊清除日誌增加(唯一索引).txt索引
- 日誌服務之敏感資訊脫敏與審計
- 深度解讀RDS for MySQL 審計日誌功能和原理MySql
- 什麼樣的日誌審計產品才能達到合規要求——日誌易
- 11g rac 日誌
- SQLServer資料庫日誌清理 清除sqlserver2005日誌SQLServer資料庫
- metaspolit下UAC提權以及日誌清除
- 清除SQL Server日誌的方法介紹SQLServer
- oracle 10g 歸檔日誌清除Oracle 10g
- mariadb審計日誌通過 logstash匯入 hiveHive
- Oracle 11g RAC檢視ASM日誌、grid日誌和DB日誌OracleASM
- RAC 11g的日誌
- windows10系統如何清除事件日誌Windows事件
- rman清除歸檔日誌經典資料
- 清除 SQL SERVER 2005 事務日誌SQLServer