Script for trimming alert log to 1 day and taking backup of prev day
#!/usr/bin/ksh
############################################################################
## Program : save_alert_log.sh #
## #
## Purpose : The alert logs on many Oracle databases can grow to a very #
## large size over time. This can often impede the maintenace#
## of the system – because the DBA will need to sometimes scan#
## through many days or months of data when researching an #
## issue. This script tries to avoid that by ensuring that #
## the log file can be “refreshed” on a daily basis, meaning #
## that only the current day’s data will be kept in the log, #
## while the previous day’s data will be saved to another file#
## in a backup area. #
## #
## This script should be run from Oracle’s crontab at midnight#
## every night, so that the database will always have a new #
## alert log file each day. An example crontab entry could be#
## 0 00 * * * /oracle/product/local/scripts/save_alert.sh 2>&1#
## #
## Date : 19 May 2006. #
## Author : Basil S. Mullings #
############################################################################
## Modified: #
## #
## #
# Modification History: #
# DATE WHO DESC #
# ——– —– —————————————————-#
# 05/29/06 Basil Add an extra variable LOG_KEEP_DAYS to hold the #
# number of days that the log files should be kept on #
## the server before being deleted. #
## #
## #
############################################################################
##Setup some needed variables.
BKUP=bkup ##The backup directory to store the logs…
ORATAB=”/etc/oratab”
LOG_KEEP_DAYS=365 ##Keep this many days of log files on the server.
TMPFILE=/var/tmp/OracleAlertLog ##Just a temp scratch work area.
SQLUSER=”/ as sysdba”
GEN_ORA_ERROR=”ORA\-[0-9][0-9]*”
PATH=”$HOME:$HOME/bin:/usr/contrib/bin:/usr/local/bin:/usr/bin:/bin:/etc:.”
export PATH
## Now, parse the oratab file for all databases on the system.
## Then use the ORACLE_SID that is found in the oratab file
## to log onto that database, and retrieve the directory where
## the alter log file is stored (.ie. retrieve the path to the
## bdump directory.
##
#for sidEntry in `cat $ORATAB | grep -v “^#”`
for sidEntry in `cat $ORATAB | awk -F: ‘{print $1}’ | grep -v “^#”`
do
## Get date and time
CURR_DATE=`date ‘+%a_%m%d%H%M’` ##Example Fri_05191256 for Friday May 19th @1256 PM.
#ORACLE_SID=`echo $sidEntry | cut -f 1 -d :`
ORACLE_SID=$sidEntry
echo “Oracle Sid is $ORACLE_SID”
export ORACLE_SID
## Set the Oracle environment for this SID.
ORAENV_ASK=NO
. /usr/local/bin/oraenv
rm -f $TMPFILE > /dev/null 2>&1
##Now, let’s log onto the DB, and try to
##retrieve the bdump directory path.
sqlplus -s /nolog << EOF > $TMPFILE
connect $SQLUSER
set heading off;
set echo off;
set feedback off;
select ‘BACKGROUND_DUMP_DEST=’ ||value
from v\$parameter
where name=’background_dump_dest’;
exit;
EOF
##Ok, we had a problem talking to the database.
if [ `grep -c $GEN_ORA_ERROR $TMPFILE` -ne 0 ]
then
echo “ERROR: Unable to find the path to the alert log for DB $ORACLE_SID”
rm -f $TMPFILE > /dev/null 2>&1
else ##Ok, we can log into the DB, now let’s go find our bdump directory.
bdump=`grep BACKGROUND_DUMP_DEST $TMPFILE | awk -F “=” ‘{print $2}’`
#echo “BDUMP is $bdump”
bkupDir=$bdump/$BKUP
##Make sure our backup directory exists.
if [ ! -d $bkupDir ]
then
mkdir $bkupDir > /dev/null 2>&1
fi
##Now, move the alert log.
#echo “now moving $bdump/alert_${ORACLE_SID}.log to $bkupDir/alert_${ORACLE_SID}.$CURR_DATE”
mv $bdump/alert_${ORACLE_SID}.log $bkupDir/alert_${ORACLE_SID}.$CURR_DATE
#Procedure to shrink the log to 365 days
##Keep only the last 365 days worth of logs…delete all logs older than 365 days.
#echo “Now shrinking the logs in dir $bkupDir …”
find $bkupDir -name “*.*” -mtime +${LOG_KEEP_DAYS} -exec rm -f {} \;
fi
done
#!/usr/bin/ksh
############################################################################
## Program : save_alert_log.sh #
## #
## Purpose : The alert logs on many Oracle databases can grow to a very #
## large size over time. This can often impede the maintenace#
## of the system – because the DBA will need to sometimes scan#
## through many days or months of data when researching an #
## issue. This script tries to avoid that by ensuring that #
## the log file can be “refreshed” on a daily basis, meaning #
## that only the current day’s data will be kept in the log, #
## while the previous day’s data will be saved to another file#
## in a backup area. #
## #
## This script should be run from Oracle’s crontab at midnight#
## every night, so that the database will always have a new #
## alert log file each day. An example crontab entry could be#
## 0 00 * * * /oracle/product/local/scripts/save_alert.sh 2>&1#
## #
## Date : 19 May 2006. #
## Author : Basil S. Mullings #
############################################################################
## Modified: #
## #
## #
# Modification History: #
# DATE WHO DESC #
# ——– —– —————————————————-#
# 05/29/06 Basil Add an extra variable LOG_KEEP_DAYS to hold the #
# number of days that the log files should be kept on #
## the server before being deleted. #
## #
## #
############################################################################
##Setup some needed variables.
BKUP=bkup ##The backup directory to store the logs…
ORATAB=”/etc/oratab”
LOG_KEEP_DAYS=365 ##Keep this many days of log files on the server.
TMPFILE=/var/tmp/OracleAlertLog ##Just a temp scratch work area.
SQLUSER=”/ as sysdba”
GEN_ORA_ERROR=”ORA\-[0-9][0-9]*”
PATH=”$HOME:$HOME/bin:/usr/contrib/bin:/usr/local/bin:/usr/bin:/bin:/etc:.”
export PATH
## Now, parse the oratab file for all databases on the system.
## Then use the ORACLE_SID that is found in the oratab file
## to log onto that database, and retrieve the directory where
## the alter log file is stored (.ie. retrieve the path to the
## bdump directory.
##
#for sidEntry in `cat $ORATAB | grep -v “^#”`
for sidEntry in `cat $ORATAB | awk -F: ‘{print $1}’ | grep -v “^#”`
do
## Get date and time
CURR_DATE=`date ‘+%a_%m%d%H%M’` ##Example Fri_05191256 for Friday May 19th @1256 PM.
#ORACLE_SID=`echo $sidEntry | cut -f 1 -d :`
ORACLE_SID=$sidEntry
echo “Oracle Sid is $ORACLE_SID”
export ORACLE_SID
## Set the Oracle environment for this SID.
ORAENV_ASK=NO
. /usr/local/bin/oraenv
rm -f $TMPFILE > /dev/null 2>&1
##Now, let’s log onto the DB, and try to
##retrieve the bdump directory path.
sqlplus -s /nolog << EOF > $TMPFILE
connect $SQLUSER
set heading off;
set echo off;
set feedback off;
select ‘BACKGROUND_DUMP_DEST=’ ||value
from v\$parameter
where name=’background_dump_dest’;
exit;
EOF
##Ok, we had a problem talking to the database.
if [ `grep -c $GEN_ORA_ERROR $TMPFILE` -ne 0 ]
then
echo “ERROR: Unable to find the path to the alert log for DB $ORACLE_SID”
rm -f $TMPFILE > /dev/null 2>&1
else ##Ok, we can log into the DB, now let’s go find our bdump directory.
bdump=`grep BACKGROUND_DUMP_DEST $TMPFILE | awk -F “=” ‘{print $2}’`
#echo “BDUMP is $bdump”
bkupDir=$bdump/$BKUP
##Make sure our backup directory exists.
if [ ! -d $bkupDir ]
then
mkdir $bkupDir > /dev/null 2>&1
fi
##Now, move the alert log.
#echo “now moving $bdump/alert_${ORACLE_SID}.log to $bkupDir/alert_${ORACLE_SID}.$CURR_DATE”
mv $bdump/alert_${ORACLE_SID}.log $bkupDir/alert_${ORACLE_SID}.$CURR_DATE
#Procedure to shrink the log to 365 days
##Keep only the last 365 days worth of logs…delete all logs older than 365 days.
#echo “Now shrinking the logs in dir $bkupDir …”
find $bkupDir -name “*.*” -mtime +${LOG_KEEP_DAYS} -exec rm -f {} \;
fi
done
