Automatic purge of OS audit trail records with DBMS_AUDIT_MGMT

Posted by

0

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

Leave a Reply