Purging or deleting audit trail files in 11g

Whether you are using audit_trail = OS or DB there will be audit files accumulating in the audit_file_dest. Much more if audit_trail parameter is set to DB. This is the script I use to delete the files in the adump directory.

More on Oracle audit files.

Where are these audit files?

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE

--------------------------------------------------------------------------------

/app/oracle/admin/db251/adump

I run this file through corntab every night.
It tells me 

• how many audit trail files there are
• how many audit trail files are going to be deleted
• After purging, how many audit files are left

 Create a file in your common scripts or desired directory.

vi delete_aud_files.sh

Make it executable.

chmod 770 delete_aud_files.sh

Add it to your crontab to run every day.

## Delete Audit files 8:30pm every night

30 20 * * * /oracle/scripts/cleanup/delete_aud_files.sh

This is an example of the script I use.

NOTE: items in RED should be editing to your environment.

#!/bin/sh
ORACLE_BASE=/app/oracle; export ORACLE_BASE
ORACLE_SID=db251; export ORACLE_SID
DAY_AFTER=3; export DAY_AFTER
OUTF=/oracle/scripts/cleanup/cleanup_audit_files.log ; export OUTF
#
echo `date '+%m/%d/%y %A %X'` > ${OUTF}
echo >> ${OUTF}
echo "SCRIPT NAME:   $0" >> ${OUTF}
echo "SERVER:        "`uname -n` >> ${OUTF}
echo "KEEP DURATION: $DAY_AFTER days" >> ${OUTF}
echo >> ${OUTF}
echo >> ${OUTF}
#
echo "Delete audit files owned by oracle..." >> ${OUTF}
echo >> ${OUTF}
echo >> ${OUTF}

# change directory to the audit file directory
cd ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
echo >> ${OUTF}
echo "Deleting from directory:" >> ${OUTF}
echo "[${ORACLE_BASE}/admin/${ORACLE_SID}/adump]" >> ${OUTF}
echo >> ${OUTF}
echo "The total number of files in directory is:" >> ${OUTF}

# output the total count of audit files to outfile
ls -al | wc -l >> ${OUTF}
echo >> ${OUTF}
echo "Total number of files to be deleted is:" >> ${OUTF}

# output the total number of audit files that will be deleted
find . -maxdepth 1 -type f -mtime +$DAY_AFTER -name "*.aud" | wc -l >> ${OUTF}
echo >> ${OUTF}

# delete the audit files
find . -maxdepth 1 -type f -mtime +$DAY_AFTER -name "*.aud" | xargs rm
echo "Files successfully deleted." >> ${OUTF}
echo "Total number of files remaining:" >> ${OUTF}

# output the remaining count of audit files in the adump directory
ls -al | wc -l >> ${OUTF}
echo >> ${OUTF}
echo >> "Complete with delete." >> ${OUTF}
#
# Now email results
echo >> $OUTF
echo `date '+%m/%d/%y %A %X'` >> $OUTF
cat $OUTF | /bin/mailx -s "`uname -n` : delete old Oracle audit files" my_email@company.com
exit

This is what the email output looks like:

06/04/12 Monday 20:30:01

SCRIPT NAME:   /oracle/scripts/cleanup/delete_aud_files.sh
SERVER:        neutron
KEEP DURATION: 3 days


Delete audit files owned by oracle...

Deleting from directory:
[/app/oracle/admin/db251/adump]

The total number of files in directory is:
38677

Total number of files to be deleted is:
16589

Files successfully deleted.
Total number of files remaining:
22094

06/04/12 Monday 20:30:03

Oracle 11g Audit Trail

If you don't care about auditing the DML in your database then you will agree with me that Oracle should not have made auditing mandatory. Yes you can set this parameter to NONE, but you will still get some level of auditing. This is only a brief overview and I am not going into too much depth here.

The possible values for the audit_trail parameter are
 SQL> show parameters audit_trail

DB
DB_EXTENDED
OS
XML
NONE

If the value is DB you should set up a cron script to purge these files:
Script to purge audit trail files in Oracle 11g.

Where are the audit files

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE

--------------------------------------------

/app/oracle/admin/db251/adump


To update this parameter
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL>startup;
SQL> show parameter audit_trail;
AUDIT_TRAIL TYPE   VALUE
-----------------   ------    -------
audit_trail           string   DB

The default is OS. If the setting is OS then you should also periodically delete rows from the AUD$ table.

SQL> select count(*) from aud$;

COUNT(*)

----------

344273

SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;

 COUNT(*)

----------

53550

SQL> delete from aud$ where ntimestamp# > sysdate - 21;

53550 rows deleted.