DBMS_AUDIT_MGMT定期PURGE部分AUD$

使用DBMS_AUDIT_MGMT定期PURGE部分AUD$以及FGA_LOG$审计记录


Constant                               Value          Description
AUDIT_TRAIL_ALL                   15              All audit trail types. This includes the standard database audit trail (SYS.AUD$ and SYS.FGA_LOG$ tables), operating system (OS) audit trail, and XML audit trail.
AUDIT_TRAIL_AUD_STD         1              Standard database audit records in the SYS.AUD$ table
AUDIT_TRAIL_DB_STD            3              Both standard audit (SYS.AUD$) and FGA audit(SYS.FGA_LOG$) records
AUDIT_TRAIL_FGA_STD          2              Standard database fine-grained auditing (FGA) records in the SYS.FGA_LOG$ table
AUDIT_TRAIL_FILES                  12          Both operating system (OS) and XML audit trails
AUDIT_TRAIL_OS                      4            Operating system audit trail. This refers to the audit records stored in operating system files.
AUDIT_TRAIL_XML                  8              XML audit trail. This refers to the audit records stored in XML files.


在11gR2上执行.我的审计设置为db_extended,AUD$在表空间trff_aud 上.前期准备工作如下:

SQL> alter system set audit_trail=db_extended scope=spfile;

System altered.

SQL> create tablespace trff_aud datafile '/u01/oracle/trff_aud01.dbf' size 50m;

Tablespace created.

SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'trff_aud');
END;
/

PL/SQL procedure successfully completed.



SQL> select segment_name,tablespace_name from dba_segments where segment_name='AUD$';

SEGMENT_NAME TABLESPACE_NAME
-------------------- ------------------------------
AUD$ trff_aud 

--第一步针对AUDIT_TRAIL_AUD_STD设置init_cleanup每24小时执行一次.

SQL> BEGIN
IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED
(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');
DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 24);
else
dbms_output.put_line('Cleanup for STD was already initialized');
end if;
end;
/


--第二步 设置为超过14天审计记录标记时间戳.
--这里的时间戳需要注意,如果 AUDIT_TRAIL_TYPE为OS或者XML格式,则需要使用本地系统时间的格式,其他2种格式为UTC.

begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate - 14);
end;
/

--第三步 设置DBMS_AUDIT_MGMT过程中的purge_job

SQL> BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'AUD_PURGE',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

--第四步创建DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP定期推进审计记录的归档时间戳的存储过程.

create or replace procedure set_archive_timestamp (retention in number default 14) as
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,last_archive_time => sysdate – retention);
end;
/
--第五步 创建schedul,设置每6小时执行一次存储过程set_archive_timestamp

SQL> BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'PUSH_AUD_ARCH_TSTAMP',
job_type => 'STORED_PROCEDURE',
job_action => 'SET_ARCHIVE_TIMESTAMP',
number_of_arguments => 1,
start_date => SYSDATE,
repeat_interval => 'freq=hourly;interval=6',
enabled => false,
auto_drop => FALSE);
End;
/

SQL> BEGIN
dbms_scheduler.set_job_argument_value
(job_name =>'PUSH_AUD_ARCH_TSTAMP',
argument_position =>1,
argument_value => 7);
DBMS_SCHEDULER.ENABLE('PUSH_AUD_ARCH_TSTAMP');
End;
/

--设置完成,执行一次.

SQL> BEGIN
DBMS_SCHEDULER.run_job (job_name => 'PUSH_AUD_ARCH_TSTAMP',
use_current_session => FALSE);
END;
/

--到此为止一个定期purge清除aud$下的审计记录的job就设置好了.