Oracle Audit Control part 2 – purging records

Now that you have got your audit table somewhere a little more sensible (i.e. not in the SYSTEM tablespace), there’s probably a policy about how many audit records should be kept.

Thoughtfully, the DBMS_AUDIT_MGMT package provides some of what you need to keep the audit records in check. However, a little more thought by Oracle would have helped. Lets see what I mean.

First we need to initialise for audit control. You can check to see if this has already been done as follows:

SET SERVEROUTPUT ON
 BEGIN
 IF
  DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
 THEN
  DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
 ELSE
  DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
 END IF;
END;
/

NOTE: To do this for Fine-Grained auditing, you need to use the constant DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD instead, and check on table FGA_LOG$.

If cleanup is not initialised, you need to set it up as follows:

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP
 (AUDIT_TRAIL_TYPE         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  DEFAULT_CLEANUP_INTERVAL => 999 );
END;
/

Some VERY important things to note here:

1. The DEFAULT_CLEANUP_INTERVAL doesn’t do anything (up to and including Rel 12.1). It’s for “future use”, apparently. However, if it is not specified, it has been associated with bugs in relation to cleanup to last timestamp not working and not cleaning anything up.
2. If you have not already moved the audit tables AUD$ / FGA_LOG$ out of the SYSTEM tablespace, to any other tablespace this will move them for you, right now, whether desired or not, into SYSAUX.
3. If you DEINIT_CLEANUP, it does not move the tables back to SYSTEM.

OK, we are initialised. We could call the creation of a purge job, which will wipe out all of our audit records (every 24 hours in this example), but that would be an unlikely requirement.

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 => 'Purge_AUD$',
 USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

It’s MORE likely we want to wipe out the last N days worth of records. To do this we need to set the point (LAST_ARCHIVE_TIMESTAMP) from which we want to retain records and wipe out everything before that. So lets set for a 30 day retention.

BEGIN
 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 LAST_ARCHIVE_TIME => systimestamp-30,
 RAC_INSTANCE_NUMBER => 1 );
END;
/

And check it

select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL            RAC_INSTANCE LAST_ARCHIVE_TS
---------------------- ------------ ------------------------------------
STANDARD AUDIT TRAIL              0 17-MAY-14 11.00:01.000000 PM +00:00

Excellent. Now we create a job as before with “USE_LAST_ARCH_TIMESTAMP => TRUE” and all is good, EXCEPT that nothing is moving the timestamp forward.
The job will be called, purge the old records and that’s it. When it is next invoked, the timestamp will not have moved on. We therefore need another job to move the timestamp on… SO why bother setting up a job with these automatic routines if it doesn’t automate all of the requirement? Bit annoying that. I just create my own scheduled job with 2 calls, and forget the built-in (half a) job aspect of the management system:

BEGIN
 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 LAST_ARCHIVE_TIME => systimestamp-30,
 RAC_INSTANCE_NUMBER => 1 );

 DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

OK, the audit management system is pretty good; it deletes in batches, it works well, and it doesn’t need much from Oracle to make it much better. 7/10. Good but could do better.

You need to check out the associated views which show you the basic system config and what’s going on:

DBA_AUDIT_MGMT_CLEAN_EVENTS  Displays the cleanup event history
DBA_AUDIT_MGMT_CLEANUP_JOBS  Displays the currently configured audit trail purge jobs
DBA_AUDIT_MGMT_CONFIG_PARAMS Displays the currently configured audit trail properties
DBA_AUDIT_MGMT_LAST_ARCH_TS  Displays the last archive timestamps set for the audit trails

OK – that should keep things nice and tidy in the database. What about the audit files on the OS? .
Find out about that in Part 3