[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;