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