Oracle Audit Control part 2 – purging records
20th June 2014 2 Comments
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:
- 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.
- 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.
- 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.