11G设置审计日志保留策略
环境:
Os:Centos 7
DB:11.2.0.4
1.初始化清理Audit的功能,该命令只有在第一次执行时需要运行,default_cleanup_interval =>72 代表清理周期为72小时(3天).
BEGIN
sys.DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 72);
END;
/
2.查看清除Audit功能是否开启,yes为开启
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;
/
3.设置需要清理的天数，最后一个数字3代表清理3天前的数据(归档时间大于等于清除时间)
BEGIN
sys.DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-3);
END;
/
4.执行清除,时间长短受数据量大小影响
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
执行改命令看到后台是使用delete的方式删除的
DELETE FROM SYS.AUD$ WHERE DBID = 1510119012 AND NTIMESTAMP# < to_timestamp('2023-10-06 14:08:13', 'YYYY-MM-DD HH24:MI:SS.FF') AND ROWNUM <= 10000;
5.删除一天前的审计日志
BEGIN
sys.DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-1);
END;
/
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
说明：
相关参数配置
SQL> set linesize 1000;
SQL> column PARAMETER_NAME format a32;
SQL> column PARAMETER_VALUE format a16;
SQL> column AUDIT_TRAIL format a32;
SQL> select PARAMETER_NAME,PARAMETER_VALUE,AUDIT_TRAIL from dba_audit_mgmt_config_params;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
-------------------------------- ---------------- --------------------------------
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX 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 72 STANDARD AUDIT TRAIL
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
-------------------------------- ---------------- --------------------------------
DEFAULT CLEAN UP INTERVAL 72 FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 72 OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 72 XML AUDIT TRAIL
14 rows selected.
aud$所在的表空间
SQL> select owner,table_name,tablespace_name from dba_tables
2 where table_name = 'AUD$';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYSTEM AUD$ SYSAUX
