Pavan DBA's Blog

The DBA Knowledge Store

script for trimming alert log to 1 day and taking backup of prev day

Posted by Pavan DBA on April 23, 2010


#!/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

One Response to “script for trimming alert log to 1 day and taking backup of prev day”

  1. Good one dude….Very much useful

    Best regards,

    Rafi.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: