Recently I have had to do work at a couple of client sites to set up scripts to clean up the many trace and log files that the Oracle database leaves all over the file system. Somewhere in the middle of the process I realized that the whole process would be much easier if I were to read the Oracle database itself to determine the location of the audit, trace, and log files.
So after scanning my memory, I realized that there are probably only two or three parameters that I need to look at to clean up the vast majority of trace files. These include audit_file_dest, background_dump_dest, and in 11gR2 only, diagnostic_dest. In addition, you will need the value for ORACLE_HOME.
Also, since you may have multiple Oracle Homes on a server, you want to make sure that you clean up files for all running instances on the server. So, the first thing I did was set up a loop to identify all the running instances on the server. In the bash shell, a loop to do this can be set up like this:
WORKDIR=”$1″
export WORKDIR
ORAENV_ASK=’NO’
for ORACLE_SID in `ps -ef | grep smon|grep -v grep|awk ‘{print $8}’| awk -F “_” ‘{print$3}’`
do
. oraenv
sqlplus / as sysdba <<EOF
select instance_name from v$instance;
exit;
EOF
<Rest of commands Here>
done
As you can see, we now have a loop set up that will roll us through each running Oracle instance on the server. If you want to exclude ASM, add a grep –v ASM.
From there, we need to extract the location of the trace files and audit file. In order to do this, you write some very short shell scripts, or you can embed the code in your script. The Short shell scripts are below:
#get_audit.ksh
#!/bin/sh
sqlplus -s “/ as sysdba”<<EOF
set feedback off heading off verify off
select value from v\$parameter where name=’audit_file_dest’;
exit
EOF
#get_diag_base.sh
#!/bin/sh
$ORACLE_HOME/bin/sqlplus -s “/ as sysdba”<<EOF
set feedback off heading off verify off
select value from v\$parameter where name=’diagnostic_dest’;
exit
EOF
#get_dump.sh
#!/bin/sh
sqlplus -s “/ as sysdba”<<EOF
set feedback off heading off verify off
select value from v\$parameter where name=’background_dump_dest’;
exit
EOF
The output for these shell scripts can be called from the calling script in this format:
AUDIT_DEST=`$WORKDIR/get_audit.sh`
In Oracle 9i, the audit file destination usually has a question mark in it (indicating Oracle home). This can be replaced using sed, like this:
finaud=`echo $AUDIT_DEST | sed -e ‘s|\?|’”$ORACLE_HOME”‘|g’`
Next, we need to figure out whether we have an Oracle 11gR2 type structure (uses diagnostic_dest) or pre-11g structure (uses background_dump_dest). To determine this, we check for the existence of the parameter diagnostic_dest, so after calling get_diag_base, we check for existence, then we use sed once again to clean up the values so that we can use them in a script:
if [[ -d "$DIAG" ]]; then
DIAG_BASE=$DIAG/rdbms/$lsid/$ORACLE_SID
DIAG_BASE=`echo $DUMPNAME | sed -e ‘s|\/trace||g’`
version=11
else
DIAG_BASE=`echo $DUMPNAME | sed -e ‘s|\/bdump||g’`
version=10
fi
Next, we need to look at the background_dump_dest, this value is easier to use to get the location of the database trace and log files:
DUMPNAME=`$WORKDIR/get_dump.sh`
So now our script looks like this:
WORKDIR=”$1″
export WORKDIR
ORAENV_ASK=’NO’
for ORACLE_SID in `ps -ef | grep smon|grep -v grep|awk ‘{print $8}’| awk -F “_” ‘{print$3}’`
do
. oraenv
sqlplus / as sysdba <<EOF
select instance_name from v$instance;
exit;
EOF
AUDIT_DEST=`$WORKDIR/get_audit.sh`
DIAG_BASE=`$WORKDIR/get_diag_base.sh`
DUMPNAME=`$WORKDIR/get_dump.sh`
if [[ -d "$DIAG" ]]; then
DIAG_BASE=$DIAG/rdbms/$lsid/$ORACLE_SID
DIAG_BASE=`echo $DUMPNAME | sed -e ‘s|\/trace||g’`
version=11
else
DIAG_BASE=`echo $DUMPNAME | sed -e ‘s|\/bdump||g’`
version=10
fi
done
Next, make the diag_base the working directory.
From there, we use the find command to find and delete the files.
Note that at this point you are deleting files. I highly recommend you run the segment below initially without the ‘rm –rf’ section to ensure that you do not delete files you want to keep:
find $DIAG_BASE -name \cdmp* -mtime +$daysback | xargs -i sh -c “echo deleting {}; rm -rf {}”
Now, if you followed this carefully you have everything you need to write a script that will clean up oracle log and trace files. Below is a segment of the script that will remove the .trc files:
#!/bin/sh
#set -x
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
export PATH
if [[ -n "$1" ]]; then
if [ $1 -ne 0 -o $1 -eq 0 2>/dev/null ]
then
if [[ $1 -lt 0 ]]; then
echo invalid input
exit 1
else
days=$1
echo days=$days
fi
fi
else
echo days=7
days=7
echo days=$days
fi
SERVER=`hostname -s`
FDATE=`date +%d_%m_%y`
for ORACLE_SID in `ps -ef | grep smon|grep -v grep|awk ‘{print $8}’| awk -F “_” ‘{print$3}’`
do
# uncomment 2 lines below if RAC environment and individual sids are not in oratab
# SID=`echo $ORACLE_SID | sed -e ‘s/1//g’`
# ORACLE_HOME=`cat /etc/oratab|grep ^$SID:| head -n 1 | cut -f2 -d’:’`
ORAENV_ASK=NO
export ORAENV_ASK
export ORACLE_SID
echo $ORACLE_SID
. /usr/local/bin/oraenv
echo SID=$ORACLE_SID
AUDIT_DEST=`$ORACLE_HOME/bin/sqlplus -s “/ as sysdba” <<!
set feedback off heading off verify off
select value from v\\$parameter where name=’audit_file_dest’;
!`
DIAGX=`$ORACLE_HOME/bin/sqlplus -s “/ as sysdba” <<!
set feedback off heading off verify off
select value from v\\$parameter where name=’diagnostic_dest’;
!`
DIAG=`echo $DIAGX | sed -e ‘s| *||g’`
DUMPNAME=`$ORACLE_HOME/bin/sqlplus -s “/ as sysdba” <<!
set feedback off heading off verify off
select value from v\\$parameter where name=’background_dump_dest’;
!`
export DIAG_BASE
finaud=`echo $AUDIT_DEST | sed -e ‘s|\?|’”$ORACLE_HOME”‘|’`
# used to clean up Oracle 9 ‘?’ in patch.
if [[ -d "$DIAG" ]]; then
# echo base=$DIAG
DIAG_BASE=$DIAG/rdbms/$lsid/$ORACLE_SID
DIAG_BASE=`echo $DUMPNAME | sed -e ‘s|\/trace||g’`
version=11
else
DIAG_BASE=`echo $DUMPNAME | sed -e ‘s|\/bdump||g’`
version=10
fi
echo base=$DIAG_BASE
echo audit=$finaud
/usr/bin/find $finaud -name \*.aud -mtime $days
/usr/bin/find $finaud -name *.aud -mtime $days | xargs -i ksh -c “echo deleting {}; rm {}”
cd $DIAG_BASE
cd ..
export DIAG_BASE
if [[ -d "$DIAG_BASE" ]]; then
# echo ‘diag_base=$DIAG_BASE’
cd $DIAG_BASE
/usr/bin/find $DIAG_BASE -name \*.trc -mtime $days
/usr/bin/find $DIAG_BASE -name \*.trc -mtime $days | xargs -i ksh -c “echo deleting {}; rm {}”
fi
done
Some people will gzip the trace and log files prior to deleting or clearing them.
