Removing those pesky .aud files

For as long as i can remember, by default, oracle creates a .aud file every time a user connects to the database with the sysdba privilege. The content of this file is similar to that shown below

Personally I've never really found the files to be that useful and they just tend to be an annoyance. Primarily when the filesystem hosting the files (specified by audit_file_dest - with a default of $ORACLE_HOME/rdbms/audit) fills up and then prevents you from logging on to the database with an error something along the lines of 'I can't write the .aud file to say you are logging on so you can't log on'

Housekeeping these files has always been a pain and generally we've done that with shell scripts run on a regular interval from cron. There must be a better way though surely?

Turns out there is, something that was developed primarily for use with the audit vault product is now available for general use in 11.2 (for earlier versions it still seems unclear what the position is from oracle - you can get the package but is a license required? I'd check with your sales rep before you start using it in earlier versions)

So this new and improved method then - how does it work?

It's actually pretty simple, though there seem to be a few quirks/bugs - in the simplest form you can just run these two blocks of plsql)

begin
DBMS_AUDIT_MGMT.INIT_CLEANUP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,24*7);
end;
/

begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,sysdate-30);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS);
end;
/

The first just sets up some stuff and is a one time activity, the second then needs to be run regularly  to delete anything older than a month.

Confusingly the initial setup procedure 'INIT_CLEANUP' has a parameter which seems to imply some kind of default time when things will be done - this parameter does nothing and can be ignored (thats the one i set to 24*7 in the code above)

There is also a procedure that will create a 'job' for you, but this job will only ever delete files older than the first date you supply to 'SET_LAST_ARCHIVE_TIMESTAMP' - this procedure just creates a value that does not change - you have to call it again if you want to move the clock forward - using a function such as sysdate-30 does not work - it's evaluated and used as the actual value - it does not change over time.

To run things correctly, ignore creating the 'automatic' job and create your own with the contents of the plsql block above.

The other thing to note (and this cause me a lot of confusion when initially testing this) is that prior to 11.2.0.3 there is a bug where the process above does not work if the database has an uppercase SID! We always use uppercase SIDS as it makes then easier to spot in ps commands and helps the SID stand out in trace/log files. There are patches for this if you have anything prior to 11.2.0.3.

In summary - there is a better way in 11.2 and as long as you know about the 'features' of it it seems to work fine. Having it central in the database is great rather than relying on external jobs - it also means when you copy a database you get the cleanup by default in wherever you copy it to.