It's great that Oracle have introduced an ever richer set of auditing management functions in 11g release 2. I was particularly interested in auditing to the filesystem, thereby keeping the db clean and allowing standard operating system methods to archive audit data to a backup server. The solution being neatly wrapped up by purging old audit files on a regular basis, all controlled by the db scheduler.
There is a great article how to do so here. It is, however, more focussed on purging audit records in the database, not the os. Furthermore, an additional scheduler job is required to update the timestamp against which Oracle will delete old records (see end of article).
BUG: Hands up how many folk out there use upper-case SID names? Yeah, me too. Sorry folks, o/s purging does not work if you SID is upper case unless you apply patch 9438890! Before I discovered this patch, I tried creating lower case SID db's, thinking this might be the way forward. I was left feeling somewhat uncomfortable when DBCA left with a mixture of upper and lower uses of the SID (lower case in the spfile, upper case in the flash recovery folder name). The opposite cannot be said of specifying upper-case SID's to DBCA. Perhaps it is an unspoken wisdom of being a DBA, but I say better stick with upper case SID's.
I will keep you posted on the patch which is yet to be installed.
Update: 4 Oct 2011. Sid's are now being generated in upper case and therefore being identified by purging.
Update: 31 Oct 2011. So it seems a job needs also to be scheduled to update the otherwise static LAST_ARCHIVE_TS. The following code should take care of it:
create or replace procedure set_archive_retention
(retention in number default 7) as
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type =>DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => sysdate - retention);
end;
/
begin
DBMS_SCHEDULER.disable('advance_archive_timestamp');
DBMS_SCHEDULER.drop_job('advance_archive_timestamp');
exception
when others then
null;
end;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'advance_archive_timestamp',
job_type => 'STORED_PROCEDURE',
job_action => 'SET_ARCHIVE_RETENTION',
number_of_arguments => 1,
start_date => SYSDATE,
repeat_interval => 'freq=daily' ,
enabled => false,
auto_drop => FALSE);
dbms_scheduler.set_job_argument_value
(job_name =>'advance_archive_timestamp',
argument_position =>1,
-- one week, you can customize this line:
argument_value => 7);
DBMS_SCHEDULER.ENABLE('advance_archive_timestamp');
End;
/
BEGIN
DBMS_SCHEDULER.run_job (job_name => 'advance_archive_timestamp',
use_current_session => FALSE);
END;
/
