[20170518]11G审计日志清除3.txt
--//以前写的链接:http://blog.itpub.net/267265/viewspace-2133145/
--//我当时写存在许多问题,仅仅清除sys.aud$内容,参数audit_file_dest目录的文件不清除,而这里参数大量的aud文件,重新测试看看.
1.环境:
SYS@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
2.清理工作:
http://www.oracle-base.com/articles/11g/AuditingEnchancements_11gR2.php
======================================================
-- Constants 注意重叠问题.
-- Audit Trail types
--
AUDIT_TRAIL_AUD_STD CONSTANT NUMBER := 1;
AUDIT_TRAIL_FGA_STD CONSTANT NUMBER := 2;
--
-- Both AUDIT_TRAIL_AUD_STD and AUDIT_TRAIL_FGA_STD
AUDIT_TRAIL_DB_STD CONSTANT NUMBER := 3;
--
AUDIT_TRAIL_OS CONSTANT NUMBER := 4;
AUDIT_TRAIL_XML CONSTANT NUMBER := 8;
--
-- Both AUDIT_TRAIL_OS and AUDIT_TRAIL_XML
AUDIT_TRAIL_FILES CONSTANT NUMBER := 12;
--
-- All above audit trail types
AUDIT_TRAIL_ALL CONSTANT NUMBER := 15;
======================================================
--//注:我当时选择AUDIT_TRAIL_AUD_STD,现在选择全部(AUDIT_TRAIL_ALL).直接使用数字15应该也可以.
--//首先清除前面的设置
exec DBMS_AUDIT_MGMT.DEINIT_CLEANUP(AUDIT_TRAIL_TYPE=>SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD);
exec dbms_scheduler.drop_job( job_name => 'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP' , force => TRUE);
exec SYS.DBMS_AUDIT_MGMT.DROP_PURGE_JOB( AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job');
--//这些参考链接http://blog.itpub.net/267265/viewspace-2133145/,感叹一下看文档不认真.
--//这些步骤都是前面没有配置好,大家可以不看这些内容.我仅仅做一个记录.
3.初始化工作:
BEGIN
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 24 /* hours */);
END;
/
SET SERVEROUTPUT ON
BEGIN
IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/
YES
PL/SQL procedure successfully completed.
--//返回YES,说明已经做清理的初始化工作.
--//这样设计对全部audit才有效.
SYS@xxxxx> column PARAMETER_VALUE format a30
SYS@xxxxx> column PARAMETER_name format a30
SYS@xxxxx> SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ --------------- ----------------------------
DB AUDIT TABLESPACE TSP_AUDIT STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE TSP_AUDIT 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 FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24 OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24 STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24 XML AUDIT TRAIL
14 rows selected.
--//看最后4行DEFAULT CLEAN UP INTERVAL,DEFAULT CLEAN UP INTERVAL,DEFAULT CLEAN UP INTERVAL,DEFAULT CLEAN UP INTERVAL
--//已经定义了清理间隔时间.
4.建立schedule,清理日志:
--//oracle处理这些问题的机制有点繁琐,首先是标记那些需要clean,执行SET_LAST_ARCHIVE_TIMESTAMP然后交由清除程序处理.
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);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,LAST_ARCHIVE_TIME => SYSDATE-30);
END;',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
enabled => TRUE,
comments => 'Create an archive timestamp'
);
END;
/
--//注:这里AUDIT_TRAIL_TYPE 不能使用 DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,必须1个1个写.
SYS@xxxxx> BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,LAST_ARCHIVE_TIME => SYSDATE-1); END;
2 /
BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,LAST_ARCHIVE_TIME => SYSDATE-1); END;
*
ERROR at line 1:
ORA-46250: Invalid value for argument 'AUDIT_TRAIL_TYPE'
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 2233
ORA-06512: at line 1
--//每个小时检查1次,标记30天前的日志.一些参数可以根据需要自己调整.我自己的测试保留30天.建议生产系统保留100天或者半年.
--//建立清理程序:
BEGIN
SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
4.相关视图查询:
SYS@xxxxx> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;
JOB_NAME JOB_STAT AUDIT_TRAIL JOB_FREQUENCY
------------------------------ -------- ---------------------------- -----------------------
DAILY_AUDIT_PURGE_JOB ENABLED ALL AUDIT TRAILS FREQ=HOURLY;INTERVAL=24
SYS@xxxxx> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;
AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL 0 2017-04-18 15:25:37.000000 +00:00
OS AUDIT TRAIL 1 2017-04-18 15:25:37.000000 +08:00
SYS@xxxxx> select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;
AUDIT_TRAIL RAC_INSTANCE CLEANUP_TIME DELETE_COUNT WAS
-------------------- ------------ --------------------------------- ------------ ---
STANDARD AUDIT TRAIL 0 2017-05-17 00:52:47.741345 +00:00 87 NO
STANDARD AUDIT TRAIL 0 2017-05-18 00:52:47.701398 +00:00 1 NO
OS AUDIT TRAIL 1 2017-05-18 07:25:13.354238 +00:00 285 NO
SYS@xxxxx> @ &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%'''
ROW_NUM COL_NUM COL_NAME COL_VALUE
---------- ---------- ---------------- ----------------------------------------------------------------------------------------------------
1 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(15, TRUE); END;
7 START_DATE 2017-05-18 12:03:39.838620 +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'
2 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_AUD
7 START_DATE 2017-05-18 15:21: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
26 rows selected.