Oracle Clean up. Delete old Oracle trace files. Delete old dmp files.

=========================
General
=========================

Oracle creates trc files that are not being cleaned up from server.
Here is an example of delete unwanted files, using crontab task.

=========================
Evidences
=========================

crontab

1 6 * * * /software/oracle/oracle/scripts/scripts/delete_trace_files.sh

/software/oracle/oracle/scripts/delete_trace_files.sh

#!/bin/bash

ORACLE_SID=igt

#===============
#Listener Logs
#===============
SERVER_NAME=`hostname`
LISTENER_NAME=lsnr_igt
LISTENER_HOME=/software/oracle/diag/tnslsnr
LISTENER_PATH=${LISTENER_HOME}/${SERVER_NAME}/${LISTENER_NAME}/trace

mv ${LISTENER_PATH}/${LISTENER_NAME}.log_9  ${LISTENER_PATH}/${LISTENER_NAME}.log_10
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_8  ${LISTENER_PATH}/${LISTENER_NAME}.log_9
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_7  ${LISTENER_PATH}/${LISTENER_NAME}.log_8
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_6  ${LISTENER_PATH}/${LISTENER_NAME}.log_7
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_5  ${LISTENER_PATH}/${LISTENER_NAME}.log_6
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_4  ${LISTENER_PATH}/${LISTENER_NAME}.log_5
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_3  ${LISTENER_PATH}/${LISTENER_NAME}.log_4
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_2  ${LISTENER_PATH}/${LISTENER_NAME}.log_3
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_1  ${LISTENER_PATH}/${LISTENER_NAME}.log_2
mv ${LISTENER_PATH}/${LISTENER_NAME}.log  ${LISTENER_PATH}/${LISTENER_NAME}.log_1

DAYS_TO_KEEP=14

LISTENER_PATH=${LISTENER_HOME}/${SERVER_NAME}/${LISTENER_NAME}/alert/

find ${LISTENER_PATH} -type f -name "log*.xml" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

#=======================
#delete old trace files
#=======================
LOG_DIR=/software/oracle/oracle/scripts/logs
if [[ ! -d $LOG_DIR ]]; then
  mkdir $LOG_DIR
fi

LOG_FILE=${LOG_DIR}/delete_trace.log
touch $LOG_FILE

ORA_INST=$ORACLE_SID
DAYS_TO_KEEP=10
RUN_DATE=`date +"%Y%m%d_%H%M%S"`
ORA_TRACE_HOME=/software/oracle/diag/rdbms
TRACE_PATH=${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/trace


echo "========================================" >> $LOG_FILE
echo "Start Deleting Files at $RUN_DATE       " >> $LOG_FILE
echo "========================================" >> $LOG_FILE


echo Starting Delete trc files older than $DAYS_TO_KEEP days: >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trc" -mtime +${DAYS_TO_KEEP} -exec rm {} \;
echo " " >> $LOG_FILE

echo Starting Delete trm files older than $DAYS_TO_KEEP days: >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trm" -mtime +${DAYS_TO_KEEP} -exec rm {} \;
echo " " >> $LOG_FILE


echo Starting Delete files bigger than 1Gb: >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trc" -size +1G -exec rm {} \;
echo " " >> $LOG_FILE

echo "Starting Delete files with text kcbzib: dump suspect buffer:"  >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trc" | xargs grep -l "kcbzib: dump suspect buffer"  >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trc" | xargs grep -l "kcbzib: dump suspect buffer" | xargs rm -f
echo " " >> $LOG_FILE

echo "Starting Delete files with text kkpamMapRow under incident folder:" >>  $LOG_FILE
find ${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/incident -type f -name "*.trc" | xargs grep -l "kkpamMapRow" >>  $LOG_FILE
find ${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/incident -type f -name "*.trc" | xargs grep -l "kkpamMapRow" | xargs rm -f
echo " " >> $LOG_FILE

echo "Starting Delete cdump folders under trace folder:" >>  $LOG_FILE
find ${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/trace -type d -name "cdmp*" >>  $LOG_FILE 
find ${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/trace -type d -name "cdmp*" | xargs rm -rf
echo " " >> $LOG_FILE

echo "========================================" >> $LOG_FILE
echo "Delete and Archive Files                " >> $LOG_FILE
echo "========================================" >> $LOG_FILE

SEARCH_STING="DUMP REDO"

TRACE_FILES_LIST="$(find ${TRACE_PATH} -type f -name "*.trc" | xargs grep -l "${SEARCH_STING}") " 

for f in $TRACE_FILES_LIST
do
  echo "handling trace file $f" >> $LOG_FILE
  NEW_TRACE_NAME=/software/oracle/oracle/scripts/`basename $f`
  head -1000 $f > ${NEW_TRACE_NAME}
  tail -1000 $f >> ${NEW_TRACE_NAME}
  rm -f $f
done


echo " " >> $LOG_FILE
RUN_DATE=`date +"%Y%m%d_%H%M%S"`

echo "========================================" >> $LOG_FILE
echo "Finished Deleting Files at $RUN_DATE    " >> $LOG_FILE
echo "========================================" >> $LOG_FILE

exit 0

as shdaemon

1 6 * * * /home/shdaemon/scripts/move_old_log_files.sh

/home/shdaemon/scripts/move_old_log_files.sh

#!/bin/bash

#=============================

#move logs to old_logs folder

#=============================

FOR_BACKUP_HOME=/backup/ora_exp/for_backup

LOG_HOME=/backup/ora_exp/for_backup/old_log

for f in ${FOR_BACKUP_HOME}/export_igt*log 

do

  mv $f ${LOG_HOME}/

done

#=====================

#delete old dmp files

#=====================

BACKUP_HOME=/backup/ora_exp

DAYS_TO_KEEP=4

find ${BACKUP_HOME} -type f -name "export*.dmp" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

exit 0

=======================

delete_old_exp_files.sh

=======================

1 6 * * * /home/shdaemon/scripts/delete_old_exp_files.sh

/home/shdaemon/scripts/delete_old_exp_files.sh

#!/bin/bash

BACKUP_HOME=/backup/ora_exp

FOR_BACKUP_HOME=/backup/ora_exp/for_backup

LOG_HOME=/backup/ora_exp/for_backup/old_log

DAYS_TO_KEEP=4

#=============================

#move logs to old_logs folder

#=============================

for f in ${FOR_BACKUP_HOME}/export_igt*log do

  mv -f ${FOR_BACKUP_HOME}/export_igt*log ${LOG_HOME}/

done

exit 0

#=====================

#delete old dmp files

#=====================

find ${BACKUP_HOME} -type f -name "export*.dmp" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

exit 0

=====================

move_old_log_files.sh

=====================

1 6 * * * /home/shdaemon/scripts/move_old_log_files.sh

/home/shdaemon/scripts/move_old_log_files.sh

#!/bin/bash

BACKUP_HOME=/backup/ora_exp

FOR_BACKUP_HOME=/backup/ora_exp/for_backup

LOG_HOME=/backup/ora_exp/for_backup/old_log

DAYS_TO_KEEP=4

====================

delete_old_exp_files.sh

====================

1 6 * * * /home/shdaemon/scripts/delete_old_exp_files.sh

/home/shdaemon/scripts/delete_old_exp_files.sh

#!/bin/bash

BACKUP_HOME=/backup/ora_exp

FOR_BACKUP_HOME=/backup/ora_exp/for_backup

LOG_HOME=/backup/ora_exp/for_backup/old_log

DAYS_TO_KEEP=4

#================================================

#delete old dmp files

#================================================

find ${BACKUP_HOME} -type f -name "export*.dmp" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

exit 0

====================

SQL Cleanup

====================

0 23 * * * /software/oracle/oracle/scripts/sql_cleanup.sh

sql_cleanup.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

WORK_DIR=/software/oracle/oracle/scripts

LOG_FILE=${WORK_DIR}/sql_cleanup.log

RUN_DATE=`date +"%Y%m%d_%H%M%S"`

echo "Logfile : $LOG_FILE"

echo "" >> ${LOG_FILE}

echo "Starting Truncate at $RUN_DATE" >> ${LOG_FILE}

cd /software/oracle/oracle/scripts

sqlplus system/XXXXXX@igt @sql_cleanup.sql | tee $LOGFILE

echo "Finished Truncate at $RUN_DATE" >> ${LOG_FILE}

sql_cleanup.sql

TRUNCATE TABLE MY_USER.MY_TABLE;

EXIT;