Remove OLD trace files

It is not so rare to experience lots of trace files in user or background dump deestinations. For instance Oracle 11.1.0.6 creates trace file almost for every session and after a while directory where trace files are located is loaded with lots of *.trc and *.trm files. As Oracle 11.1.0.6 puts all system and user traces in same directory it is difficult to search for custom 10046 or 10053 traces.

Generation of all this trace files could be reduced probably disabling/enabling some parameters and I'am planning to examine documentation about this problem, but meanwhile I wrote a simple script to clean old trace files. This script runs every day with cron and deletes all *.trc and *.trm (trace map) files older then 7 days.

I used XARGS command to avoid error message "Argument list too long" when there are too many files to delete.

$ ls -l|wc -l
14029
$ rm *.trc *.trm
-bash: /bin/rm: Argument list too long

Script:

#!/bin/sh

###########################################################
## DELETE TRACE FILES OLDER THEN 7 DAYS ###################
###########################################################

# Set Oracle environment
export ORACLE_BASE=/oracle
export ORACLE_SID=test11
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch:/sbin:/usr/local/bin

# Get the location of background and user dump destinations
sqlplus /nolog<< EOF > /tmp/xy.temp
connect system/oracle@test11  # CHANGE LOGIN PARAMETERS HERE
column xxxx format a10
column value format a80
set lines 132
SELECT 'xxxx' ,value FROM  v\$parameter WHERE  name = 'background_dump_dest'
/
SELECT 'yyyy' ,value FROM  v\$parameter WHERE  name = 'user_dump_dest'
/
exit
EOF

ERR=`less "/tmp/xy.temp"|egrep -c "ORA-|ERROR|no listener"`;
if [ $ERR != 0 ]
then
exit 1;
fi;

less /tmp/xy.temp | awk '$1 ~ /xxxx/ {print $2}' > /tmp/bkg_dump_dest.temp
read BCKDMP_DIR < /tmp/bkg_dump_dest.temp

less /tmp/xy.temp | awk '$1 ~ /xxxx/ {print $2}' > /tmp/usr_dump_dest.temp
read USRDMP_DIR < /tmp/usr_dump_dest.temp

# Delete all trace files older then 7 days
find $BCKDMP_DIR  \( -name \*.trc -o -name \*.trm \) -type f -mtime +7 -print|xargs rm -f
find $USRDMP_DIR  \( -name \*.trc -o -name \*.trm \) -type f -mtime +7 -print|xargs rm -f

# Clean temporary files
rm -f /tmp/bkg_dump_dest.temp
rm -f /tmp/usr_dump_dest.temp
rm -f /tmp/xy.temp

To enable daily execution of the script I have to add cron entry

0 8 * * * /home/oracle/scripts/clean_old_trcs.sh

(My script runs every day at 8 AM) You must change Oracle environment and login parameters to get script working.

UPDATE 13.05.2010
Roberto wrote better solution for script. Check it out below.
Thanks Roberto!

sqlplus -s system/manager <<'EOF'
set linesize 1000
set trimspool on
set heading off
spool find+7_rm.sh
select 'find '
|| (select value from v$parameter where name = 'background_dump_dest')
|| ' '
|| (select value from v$parameter where name = 'user_dump_dest')
|| ' \( -name \*.trc -o -name \*.trm \) -type f -mtime +7 -exec rm -f {} \;'
from dual;
exit
EOF

sh find+7.sh