#! /bin/ksh
#
# MODULE NAME:    coldbackup.ksh
# SCCS VERSION:   1.11
# LAST MODIFIED:  12/15/98 17:11:34
# WHAT INFO:      @(#) coldbackup.ksh
# SYNOPSIS:       Performs COLD BACKUP of Oracle database in tar format.
#                 The files are TAR'ed using relative path from / (root).
#                 Please make sure the following items are checked to
#                 ensure a successful backup:
#                   1) Verify that ORATAB entries are valid.
#                   2) Confirm that BACKUP_DIR is accessible by the user
#                      that runs this script.  Although this script may be
#                      run by a DBA, Oracle is the preferred user for
#                      running this script.
#                   3) Confirm that ZIP command is valid.
#
#                 To restore the DB, perform the following steps:
#                   1) cd /
#                   2) gunzip -c /ora_backup/oracle/backup/sample.tar.gz | tar xvf -
#
# USAGE:          coldbackup.ksh ORACLE_SID
#
#-----------------------------------------------------------------------------
# Name     Date      Description
# -------- --------- ---------------------------------------------------------
# Jhreddy  05/18/05  Modified
#
##############################################################################
#
# CONFIGURATION PARAMETERS: The following variables may be changed
#                 to customize the backup script.
#
BACKUP_DIR=/u06/ora_bkup
MAIL_LIST='hanmanthj@bankofthewest.com'
MODULE_NAME=coldbackup9
ORATAB=/etc/oratab
ZIP='/usr/contrib/bin/gzip'; Z=gz
########################################################################
USAGE_MSG=">> Usage: $0 ORACLE_SID"
if [[ $# != 1 ]]; then
  echo "$USAGE_MSG"
    exit 1
    fi

##################################
# Initialize Parameters
##################################
umask 002

ORACLE_SID=$1
ORACLE_HOME=`grep ^${ORACLE_SID}: $ORATAB | awk -F: '{print $2}'`
SHLIB_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:/usr/bin:/usr/ccs/bin:/usr/local/bin:/usr/sbin:/usr/contrib/bin:.
export ORACLE_SID ORACLE_HOME SHLIB_PATH PATH
unset TWO_TASK

HOSTNAME=`/usr/bin/hostname`
TIME='date +%H:%M:%S'
DATE_ID="`date +%Y\%m\%d`"
LONG_DATE_ID="`date +%Y\%m\%d'_'\%H\%M\%S`"

LOGFILE=${BACKUP_DIR}/${ORACLE_SID}_${DATE_ID}.log
OUTFILE=${BACKUP_DIR}/${ORACLE_SID}_${DATE_ID}.tar.${Z}
TMPFILE=${BACKUP_DIR}/${ORACLE_SID}.tmp
LSTFILE=${BACKUP_DIR}/${ORACLE_SID}.lst

if [[ -a $LOGFILE || -a $OUTFILE ]]; then
LOGFILE=${BACKUP_DIR}/${ORACLE_SID}_${LONG_DATE_ID}.log
OUTFILE=${BACKUP_DIR}/${ORACLE_SID}_${LONG_DATE_ID}.tar.${Z}
fi

ACTION='Init'
FAIL_MSG="$MODULE_NAME FAILED for $ORACLE_SID on $HOSTNAME"
SUCCESS_MSG="$MODULE_NAME SUCCESSFUL for $ORACLE_SID on $HOSTNAME"
LONGLINE='===================================================================='
##################################
# Function: check_error
##################################
check_error()
{
if [[ $STAT != 0 ]]
then
echo ">> Error while $ACTION" | tee -a $LOGFILE
echo ">> Please wait while termininating the process ..." | tee -a $LOGFILE
sqlplus /nolog >> $LOGFILE 2>&1 <<EOF
connect internal
shutdown abort
host sleep 10
startup
exit
EOF
/usr/bin/rm -f $OUTFILE
 echo "$LONGLINE" | tee -a $LOGFILE
 echo "`date` $FAIL_MSG" | tee -a $LOGFILE
 echo "$LONGLINE" | tee -a $LOGFILE
 mailx -s "$FAIL_MSG" $MAIL_LIST < $LOGFILE
 exit 1
  fi
  }
##################################
# Function: sigint_action
##################################
sigint_action()
{
echo ">> `$TIME` SIGINT Detected." | tee -a $LOGFILE
echo ">> Please wait while termininating the process ..." | tee -a $LOGFILE
sqlplus /nolog >> $LOGFILE 2>&1 <<EOF
connect internal
shutdown abort
host sleep 10
startup
exit
EOF
/usr/bin/rm -f $OUTFILE
echo "$LONGLINE" | tee -a $LOGFILE
echo "`date` TERMINATED $MODULE_NAME due to SIGINT" | tee -a $LOGFILE
echo "$LONGLINE" | tee -a $LOGFILE
exit 1
}
##################################
# Main
##################################
#-----------------------#
# Print Banner          #
#-----------------------#
echo "$LONGLINE" | tee $LOGFILE
echo "`date` STARTING $MODULE_NAME" | tee -a $LOGFILE
echo "$LONGLINE" | tee -a $LOGFILE
if [[ $? != 0 ]]
then
  echo "$LONGLINE"
    echo "`date` $FAIL_MSG"
      echo "$LONGLINE"
	mailx -s "$FAIL_MSG" $MAIL_LIST < $LOGFILE
	$FAIL_MSG
	Error while creating $LOGFILE.
	EOF
	  exit 1
	  fi
#-----------------------#
# Print Env Variables   #
#-----------------------#
echo "##################################" >> $LOGFILE
echo "# MODULE_NAME    = $MODULE_NAME" >> $LOGFILE
echo "# BACKUP_DIR     = $BACKUP_DIR" >> $LOGFILE
echo "# ORATAB         = $ORATAB" >> $LOGFILE
echo "# MAIL_LIST      = $MAIL_LIST" >> $LOGFILE
echo "# LOGFILE        = $LOGFILE" >> $LOGFILE
echo "# OUTFILE        = $OUTFILE" >> $LOGFILE
echo "# TMPFILE        = $TMPFILE" >> $LOGFILE
echo "# LSTFILE        = $LSTFILE" >> $LOGFILE
echo "##################################" >> $LOGFILE

#-----------------------#
# Check ORACLE_SID      #
#-----------------------#
MATCH_COUNT=`grep -c ^${ORACLE_SID}: $ORATAB`
if [[ $MATCH_COUNT != 1 ]]; then
  echo ">> Unable to match the ORACLE_SID in $ORATAB file.  Exiting ..." | tee -a $LOGFILE
    echo "$LONGLINE" | tee -a $LOGFILE
      echo "`date` $FAIL_MSG" | tee -a $LOGFILE
	echo "$LONGLINE" | tee -a $LOGFILE
	  mailx -s "$FAIL_MSG" $MAIL_LIST < $LOGFILE
	  $FAIL_MSG
	  Unable to match ORACLE_SID in $ORATAB.
	  This message is automatically generated by $MODULE_NAME.
	  EOF
	    exit 1
	    fi
#-----------------------#
# Shutdown Oracle       #
#-----------------------#
trap sigint_action SIGINT

ACTION="Shutting down $ORACLE_SID Instance"
echo "> `$TIME` $ACTION" | tee -a $LOGFILE

sqlplus /nolog > /dev/null 2>&1 <<EOF
connect internal
WHENEVER SQLERROR continue
shutdown immediate
EOF
STAT=$?
check_error

echo "< `$TIME` Finished $ACTION" | tee -a $LOGFILE

#-----------------------#
# Generate TMPFILE      #
#-----------------------#
ACTION="Generating TMPFILE"
echo "> `$TIME` $ACTION" | tee -a $LOGFILE

sleep 30

/usr/bin/rm -f $TMPFILE

sqlplus /nolog >> $LOGFILE 2>&1 <<EOF
REM
REM Startup Oracle
REM
connect internal
startup restrict
REM
REM Generate TMPFILE
REM
set echo off
set tab off
set termout off
column member format A255
spool $TMPFILE
select '.'||NAME NAME from v\$datafile union select '.'||MEMBER from v\$logfile 
union select '.'||NAME from v\$controlfile;
spool off
REM
REM Shutdown Oracle for Backup
REM
set termout on
host sleep 30
shutdown immediate
exit
EOF
STAT=`grep -c ^ORA- $LOGFILE`
check_error

echo "< `$TIME` Finished $ACTION" | tee -a $LOGFILE

#-----------------------#
# Generate LSTFILE      #
#-----------------------#
ACTION="Generating LSTFILE"
echo "> `$TIME` $ACTION" | tee -a $LOGFILE

/usr/bin/ls $TMPFILE > /dev/null 2>&1
STAT=$?
check_error

/usr/bin/rm -f $LSTFILE
egrep -v -e '^NAME|----------------|rows selected' -e '^SQL|2|union' $TMPFILE > $LSTFILE
STAT=$?
check_error

#######################################
# List additional files to backup here
########################################
echo ".${ORACLE_HOME}/dbs/*${ORACLE_SID}*" >> $LSTFILE
STAT=$?
check_error

echo "< `$TIME` Finished $ACTION" | tee -a $LOGFILE

#-----------------------#
# Perform TAR & ZIP     #
#-----------------------#
ACTION="Performing TAR"
echo "> `$TIME` $ACTION (This may take a while)" | tee -a $LOGFILE
cd /
tar chf - `/usr/bin/cat $LSTFILE` | $ZIP -c > $OUTFILE 2>> $LOGFILE
STAT=$?
check_error

echo "< `$TIME` Finished $ACTION" | tee -a $LOGFILE

#-----------------------#
# Verifying Backup      #
#-----------------------#
ACTION="Verifying Backup"
echo "> `$TIME` $ACTION" | tee -a $LOGFILE

/usr/bin/ls -o `/usr/bin/cat $LSTFILE` >> $LOGFILE 2>&1
STAT=$?
check_error

$ZIP -l $OUTFILE >> $LOGFILE 2>&1
STAT=$?
check_error

echo "< `$TIME` Finished $ACTION" | tee -a $LOGFILE

#----------------------------------#
# Cleanup Older Backup Files (older than 3 days)
#----------------------------------#
ACTION="Deleting Backup Files Older Than 3 Days"
echo "> `$TIME` $ACTION" | tee -a $LOGFILE

/usr/bin/find $BACKUP_DIR -name "${ORACLE_SID}_*.*" -mtime +3 \
-exec ls -o {} \; \
-exec rm -f {} \; >> $LOGFILE 2>&1 < /dev/null

echo "< `$TIME` Finished $ACTION" | tee -a $LOGFILE

#-----------------------#
# Start Oracle          #
#-----------------------#
ACTION="Starting $ORACLE_SID Instance"
echo "> `$TIME` $ACTION" | tee -a $LOGFILE

sqlplus /nolog >> $LOGFILE 2>&1 <<EOF
connect internal
startup
exit
EOF
#STAT=`grep -v "EXP-00091" ${LOGFILE}|egrep -c ^"ORA-|EXP-"`
STAT=`grep -c ^ORA- $LOGFILE`
check_error

echo "< `$TIME` Finished $ACTION" | tee -a $LOGFILE

#-----------------------#
# Print Banner          #
#-----------------------#
echo "$LONGLINE" | tee -a $LOGFILE
echo "`date` $SUCCESS_MSG" | tee -a $LOGFILE
echo "$LONGLINE" | tee -a $LOGFILE
mailx -s "$SUCCESS_MSG" $MAIL_LIST < $LOGFILE

##################################
# End
#################################
