Hello Friends,
As we all know house keeping is very import either it may be in our home/surrounding or even in database/server. When we talk about Oracle Database the commonly growing files that needs an attention for housekeeping is audit and trace files. Even though these files may not consume more space but small files in more number can may create minor problems, even large number of files at OS level will be a difficult to delete. So we need to delete this file in a regular interval of time, but we don't do it either we don't remember or we find it's not really important to delete.
Every time deleting this files manually is a very unwanted work for a DBA, so what could be the solution?
The Solution very easy just automate it! Hmmm.., In this article I am going to share a script that will delete all the trace(.trc & trm) and audit(.aud) files older than 3 days( you can edit the days as per your requirement).
Just schedule the below script as and when you require and get relaxed from deleting files.
SCRIPT
*************
#!/bin/sh
#######################################################################################################################
############# SCRIPT TO DELETE AUDIT AND TRACE FILES ###########
############# SCRIPT CREATED AND TESTED BY AJEETH ###########
############# THIS SCRIPT WILL DELETE TRACE&AUDIT FROM ALL THE INSTANCE RUNNING ON SERVER ###########
############# WE CAN IGNORE THE ASM INSTANCE ERROR ###########
#######################################################################################################################
set -x
for ORACLE_SID in `cat /etc/oratab|egrep ':N|:Y'|grep -v \*|cut -f1 -d':'`
do
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -d":" -f2`
DAY_OLD=3; export DAY_OLD
OUTFILE=/apps/backup/house_keep.log ; export OUTFILE
#
echo `date '+%m/%d/%y %A %X'` > ${OUTFILE}
echo >> ${OUTFILE}
echo "SCRIPT NAME: $0" >> ${OUTFILE}
echo "SERVER: "`uname -n` >> ${OUTFILE}
echo "KEEP DURATION: $DAY_OLD days" >> ${OUTFILE}
echo >> ${OUTFILE}
echo >> ${OUTFILE}
#####################################
####Trace .trc and trm file deletion
#####################################
echo "Delete audit files owned by oracle..." >> ${OUTFILE}
echo >> ${OUTFILE}
echo >> ${OUTFILE}
# change directory to the audit file directory
cd ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
echo >> ${OUTFILE}
echo "Deleting from directory:" >> ${OUTFILE}
echo "[${ORACLE_BASE}/admin/${ORACLE_SID}/adump]" >> ${OUTFILE}
echo >> ${OUTFILE}
echo "The total number of audit files in directory is:" >> ${OUTFILE}
# output the total count of audit files to OUTFILEile
ls -al | wc -l >> ${OUTFILE}
echo >> ${OUTFILE}
echo "Total number of audit files to be deleted is:" >> ${OUTFILE}
# output the total number of audit files that will be deleted
find . -maxdepth 1 -type f -mtime +$DAY_OLD -name "*.aud" | wc -l >> ${OUTFILE}
echo >> ${OUTFILE}
# delete the audit files
find . -maxdepth 1 -type f -mtime +$DAY_OLD -name \*.aud -mtime +14 -exec rm {} \;
echo "Files successfully deleted." >> ${OUTFILE}
echo "Total number of files remaining:" >> ${OUTFILE}
# output the remaining count of audit files in the adump directory
ls -al | wc -l >> ${OUTFILE}
echo >> ${OUTFILE}
#####################################
####Trace .trc and trm file deletion
#####################################
# change directory to the trace file directory
cd ${ORACLE_BASE}/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace
echo >> ${OUTFILE}
echo "Deleting from directory:" >> ${OUTFILE}
echo "[${ORACLE_BASE}/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace]" >> ${OUTFILE}
echo >> ${OUTFILE}
echo "The total number of trace files in directory is:" >> ${OUTFILE}
# output the total count of trace files to OUTFILEile
ls -al | wc -l >> ${OUTFILE}
echo >> ${OUTFILE}
echo "Total number of trace files to be deleted is:" >> ${OUTFILE}
# output the total number of trace files that will be deleted
find . -maxdepth 1 -type f -mtime +$DAY_OLD -name "*.trc" | wc -l >> ${OUTFILE}
find . -maxdepth 1 -type f -mtime +$DAY_OLD -name "*.trm" | wc -l >> ${OUTFILE}
echo >> ${OUTFILE}
# delete the trace files
find . -maxdepth 1 -type f -mtime +$DAY_OLD -name \*.trc -mtime +14 -exec rm {} \;
find . -maxdepth 1 -type f -mtime +$DAY_OLD -name \*.trm -mtime +14 -exec rm {} \;
echo "Files successfully deleted." >> ${OUTFILE}
echo "Total number of files remaining:" >> ${OUTFILE}
# output the remaining count of trace files in the adump directory
ls -al | wc -l >> ${OUTFILE}
echo >> ${OUTFILE}
#
echo >> "Complete with delete." >> ${OUTFILE}
#
# Now email results
echo >> $OUTFILE
echo `date '+%m/%d/%y %A %X'` >> $OUTFILE
cat $OUTFILE | /bin/mailx -s "`uname -n` : delete old Oracle audit files" ajeet@abc.com
done
exit
* I Highly Recommend it to test it before implementing on production servers.
** Script has been tested by me on my test machine and I don't take any Responsibility of the above script working.
Please subscribe for latest updates.
