DBMS_AUDIT_MGMT represents package which you can use to manage audit trail records. It enables for database administrators to define different settings related to audit trail records such as maximum size, cleanup operations. job for periodical deletion etc.
In this post I’ll show the steps that I used to setup automatic job that deletes OS audit trail records older than 90 days.
Here you can find nice overview of the DBMS_AUDIT_MGMT package http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_audit_mgmt.htm#ARPLS241
As first we have to initialize the cleanup process by invoking INIT_CLEANUP procedure from DBMS_AUDIT_MGMT package.
This procedure needs to be invoked only once. You have to specify for which audit trail type you want to init the cleanup and set the default cleanup interval (in my case every 24 hours).
BEGIN DBMS_AUDIT_MGMT.INIT_CLEANUP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, DEFAULT_CLEANUP_INTERVAL => 24); END; SQL> / PL/SQL procedure successfully completed.
Let’s confirm the initialization.
We can access that information in DBA_AUDIT_MGMT_CONFIG_PARAMS dictionary view.
SQL> col parameter_name format a40 SQL> col parameter_value format a40 SQL> set linesize 140 SQL> select * from dba_audit_mgmt_config_params; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL -------------------------- ----------------- ---------------------------- .... DEFAULT CLEAN UP INTERVAL 24 OS AUDIT TRAIL 11 rows selected.
Because I don’t want to delete all files, just those (modified/created) which are older than 90 days I’ve set the last archive timestamp parameter.
When CLEAN_AUDIT_TRAIL procedure is called by the purge job (define later) if USE_LAST_ARCH_TIMESTAMP is set to true the clean up will delete all files modified before last archive timestamp.
BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,LAST_ARCHIVE_TIME => trunc(systimestamp - 90)); END; SQL> / PL/SQL procedure successfully completed.
We can confirm this by looking in DBA_AUDIT_MGMT_LAST_ARCH_TS dictionary view.
SQL> select audit_trail, last_archive_ts from DBA_AUDIT_MGMT_LAST_ARCH_TS; AUDIT_TRAIL LAST_ARCHIVE_TS --------------- ------------------------------------ OS AUDIT TRAIL 06-AUG-14 12.00.00.000000 AM +02:00
Next we have to create the purge job which will automatically invoke CLEAN_AUDIT_TRAIL procedure.
SQL> BEGIN 2 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( 3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, 4 AUDIT_TRAIL_PURGE_INTERVAL => 24, 5 AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_OS', 6 USE_LAST_ARCH_TIMESTAMP => TRUE); 7* END; SQL> / PL/SQL procedure successfully completed.
As last step I’ve created scheduler job which will forward last archive timestamp parameter.
BEGIN DBMS_SCHEDULER.create_job ( job_name => 'update_audit_last_archive_time', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;', end_date => NULL, enabled => TRUE, auto_drop => FALSE, comments => 'Move forward audit last archive time.'); END; SQL> / PL/SQL procedure successfully completed.
* Audit records written to syslog are not deleted. Cleanup is performed only on *.aud files in directory specified by the AUDIT_FILE_DEST initialization parameter.
Regards,
Ivica