#!/bin/ksh
#
# hot_bck.sh
# ==========
#
# This script is provided by Database Specialists, Inc. 
# (http://www.dbspecialists.com) for individual use and not for sale. 
# Database Specialists, Inc. does not warrant the script in any way 
# and will not be responsible for any loss arising out of its use.
#
# Your feedback is welcome! Please send your comments about this script
# to scriptfeedback@dbspecialists.com
#
# This script takes a hot backup of the specified Oracle database and
# writes it to a backup directory for optional staging to tape. All online
# data files (except those belonging to read-only tablespaces) are captured,
# as are the control file and parameter files. A log of the backup activity
# is written to a log directory. The log indicates all tablespaces backed
# up, execution times, and the sequence number of the first and last
# archived redo log necessary to recover the database from this hot backup
# set. In silent mode, no output will be written to the display. (This is
# useful for invoking from cron.)
#
# Before you can use this script, you must do the following:
#  1. Make sure the database to be backed up is currently open and running
#       in ARCHIVELOG mode. The instance must be running on the local server.
#  2. Make sure hot_bck_1ts.sh and hot_bck_1f.sh are located in the same
#       directory as this script.
#  3. Your current working directory must be the same as the directory
#       where this script is located.
#  4. Review the "Configuration settings" section of this script to customize
#       as needed for your operating system and environment.
#  5. Note that if your database uses an instance parameter file (init.ora)
#       then this file will only get backed up if it has the standard name
#       ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora. If your database uses a
#       server parameter file (spfile.ora) then this file will get backed up
#       regardless of name.
#
# This script has been used successfully on Solaris 2.6 and 8 and 
# Redhat Linux ES 3 against Oracle 8.0, 8i, 9i, and 10g databases. 
#
# Usage: hot_bck.sh -i instance -b backup_dirrectory [-p previous_directory]
#                   [-l log_directory] [-o oracle_login] [-n notify_list]
#                   [-c] [-s]
#        instance is the ORACLE_SID of the database to back up. 
#        backup_directory is the full path where the backup should be written.
#        previous_directory is the full path where the previous backup should
#            be preserved. (If not provided then the previous backup will be 
#            overwritten.)
#        log_directory is where the log file should be written. (If not
#            provided then the log file will be written to backup_ditrectory.)
#        oracle_login is the username/password for a user on the database. (If
#            not provided then Oracle's operating system authentication will
#            be attempted.)
#        notify_list is a list of comma-separated email addresses where email
#            should be sent if the backup fails for any reason.
#        -c may optionally be specified to compress the backup files.
#        -s may optionally be specified to run in silent mode.
#
# Version 06-16-2005
#

#
# Configuration settings.
#

MAIL_PROGRAM="mailx"    # On some systems you may need to set this to "mail".

MKNOD_PROGRAM="mknod"   # If mknod is not on your path, change this to include
                        # a full path like /bin/mknod or /etc/mknod.

COMPRESS_PROGRAM="gzip" # Change this to compress or zip if you don't have gzip.

COMPRESS_SUFFIX=".gz"   # Change this to .zip for zip or .Z for compress.

export MAIL_PROGRAM MKNOD_PROGRAM COMPRESS_PROGRAM COMPRESS_SUFFIX

#
# The quit_hot_bck function sends an email message and quits.
#

function quit_hot_bck
{
  echo "$1" | tee -a "$OUTFILE" > $DISPLAY
  if [ ! -z "$NOTIFY" ]
  then
    $MAIL_PROGRAM -s"Hot backup of $ORACLE_SID database failed" "$NOTIFY" \
                  < "$OUTFILE"
  fi
  exit 1
}

#
# Parse and validate the command line arguments.
#

SID=""
BCK_DIR=""
PREV_DIR=""
LOG_DIR=""
ORACLE_LOGIN="/"
NOTIFY=""
DISPLAY="/dev/tty"
COMPRESS="NO"

USAGE="Usage: `basename $0` -i instance -b backup_directory [-p previous_directory]\n                  [-l log_directory] [-o oracle_login] [-n notify_list]\n                  [-c] [-s]"

while getopts :i:b:p:l:o:n:cs opt
do
  case "$opt" in
    "i") SID="$OPTARG" ;;
    "o") ORACLE_LOGIN="$OPTARG" ;;
    "n") NOTIFY="$OPTARG" ;;
    "c") COMPRESS="YES" ;;
    "s") DISPLAY="/dev/null" ;;
    "b") BCK_DIR="$OPTARG"
         if [ ! -d "$BCK_DIR" -o ! -r "$BCK_DIR" -o ! -w "$BCK_DIR" ]
         then
           echo "Backup directory must be a directory readable and writable by you" 1>&2
           exit 1
         fi ;;
    "p") PREV_DIR="$OPTARG"
         if [ ! -d "$PREV_DIR" -o ! -w "$PREV_DIR" ]
         then
           echo "Previous directory must be a directory writable by you" 1>&2
           exit 1
         fi ;;
    "l") LOG_DIR="$OPTARG"
         if [ ! -d "$LOG_DIR" -o ! -w "$LOG_DIR" ]
         then
           echo "Backup directory must be a directory writable by you" 1>&2
           exit 1
         fi ;;
    ":"|"?") echo "$USAGE" 1>&2 ; exit 1 ;;
  esac
done

let i=$#+1
if [ "$i" != "$OPTIND" ]
then
  echo "$USAGE" 1>&2
  exit 1
fi

if [ -z "$SID" -o -z "$BCK_DIR" ]
then
  echo "$USAGE" 1>&2
  exit 1
fi

if [ -z "$LOG_DIR" ]
then
  LOG_DIR="$BCK_DIR"
fi

#
# Set up the environment.
#

OUTFILE="$LOG_DIR/hotbck_${SID}_`date +%m%d%y`.log"
TMPOUT="/tmp/hotbck$$"
TMPSQLFILE1="/tmp/bck1_$$.sql"
TMPSPOOLFILE1="/tmp/bck1_$$.lst"
TMPSPOOLFILE2="/tmp/bck2_$$.lst"
TMPSPOOLFILE3="/tmp/bck3_$$.lst"
UNIQUE_STRING="`date +%m-%d-%Y:%H:%M:%S-`$$"

rm -f "$OUTFILE" $TMPOUT $TMPSQLFILE1 \
      $TMPSPOOLFILE1 $TMPSPOOLFILE2 $TMPSPOOLFILE3

ORACLE_SID="$SID"
ORAENV_ASK="NO"
PATH="$PATH:/usr/local/bin:/usr/sbin"
export ORACLE_SID ORAENV_ASK PATH
. /usr/local/bin/oraenv

#
# Write a heading into the log file.
#

echo "`date +%H:%M:%S`  Beginning hot backup of $SID database" | \
  tee "$OUTFILE" > $DISPLAY
echo "          to $BCK_DIR" | tee -a "$OUTFILE" > $DISPLAY
echo "==================================================================" | \
  tee -a "$OUTFILE" > $DISPLAY

if [ ! -z "$PREV_DIR" ]
then
  echo "`date +%H:%M:%S`  Preserving previous backup to $PREV_DIR" | \
    tee -a "$OUTFILE" > $DISPLAY
  for FILE in "$BCK_DIR"/*
  do
    if [ -f "$FILE" -a -r "$FILE" -a "$FILE" != "$OUTFILE" ]
    then
      mv -f "$FILE" "$PREV_DIR" > $TMPOUT 2>&1
      RETCODE="$?"
      cat $TMPOUT > $DISPLAY
      cat $TMPOUT >> "$OUTFILE"
      rm -f $TMPOUT
      if [ "$RETCODE" != "0" ]
      then
        quit_hot_bck "Error while preserving previous backup."
      fi
    fi
  done
fi

#
# Get the current redo log sequence number and the user_dump_dest parameter
# setting. Also try to deduce the name of the parameter file.
#

echo "$ORACLE_LOGIN" > $TMPSQLFILE1
chmod 700 $TMPSQLFILE1

cat <<EOF >> $TMPSQLFILE1
WHENEVER SQLERROR EXIT 2
WHENEVER OSERROR EXIT 3
SET FEEDBACK  OFF 
SET HEADING   OFF 
SET TERMOUT   OFF
SET TRIMSPOOL ON
SET PAGESIZE  0

SPOOL $TMPSPOOLFILE1

SELECT TO_CHAR (sequence#)
FROM   v\$log
WHERE  status = 'CURRENT';

SPOOL OFF

SPOOL $TMPSPOOLFILE2

SELECT value
FROM   v\$parameter
WHERE  name = 'user_dump_dest';

SPOOL OFF

SPOOL $TMPSPOOLFILE3

SELECT DECODE (value, '?/dbs/spfile@.ora',
                        '$ORACLE_HOME/dbs/spfile${ORACLE_SID}.ora',
                      value)
FROM   v\$parameter
WHERE  name = 'spfile'
AND    value IS NOT NULL
UNION
SELECT '$ORACLE_HOME/dbs/init${ORACLE_SID}.ora'
FROM   SYS.dual
WHERE  NOT EXISTS
       (
       SELECT 1
       FROM   v\$parameter
       WHERE  name = 'spfile'
       AND    value IS NOT NULL
       );

SPOOL OFF
EXIT 7
EOF

sqlplus -s @$TMPSQLFILE1 < /dev/null > $TMPOUT 2>&1
RETCODE="$?"
cat $TMPOUT > $DISPLAY
cat $TMPOUT >> $OUTFILE
rm -f $TMPOUT

REDO_LOG_FIRST="`cat $TMPSPOOLFILE1 2>&1`"
UDUMP="`cat $TMPSPOOLFILE2 2>&1`"
PARAMFILE="`cat $TMPSPOOLFILE3 2>&1`"

rm -f $TMPSQLFILE1 $TMPSPOOLFILE1 $TMPSPOOLFILE2 $TMPSPOOLFILE3

case "$RETCODE" in
  "7") ;;
  "0") quit_hot_bck "Unable to connect to $ORACLE_SID database." ;;
  "1") quit_hot_bck "Unable to connect to $ORACLE_SID database." ;;
  *)   quit_hot_bck "SQL*Plus exited with status $RETCODE when checking redo log sequence." ;;
esac

#
# Get a list of all tablespaces to be backed up.
#

echo "$ORACLE_LOGIN" > $TMPSQLFILE1
chmod 700 $TMPSQLFILE1

cat <<EOF >> $TMPSQLFILE1
WHENEVER SQLERROR EXIT 1
WHENEVER OSERROR EXIT 2
SET FEEDBACK OFF 
SET HEADING  OFF 
SET TERMOUT  OFF
SET PAGESIZE 0

SPOOL $TMPSPOOLFILE1

SELECT   A.tablespace_name
FROM     SYS.dba_tablespaces A, SYS.dba_data_files B
WHERE    A.status = 'ONLINE'
AND      B.tablespace_name = A.tablespace_name
GROUP BY A.tablespace_name
ORDER BY DECODE (A.tablespace_name, 'SYSTEM', 1, 2), SUM (B.bytes);

SPOOL OFF
EXIT 7
EOF

sqlplus -s @$TMPSQLFILE1 < /dev/null > $TMPOUT 2>&1
RETCODE="$?"
cat $TMPOUT > $DISPLAY
cat $TMPOUT >> $OUTFILE
rm -f $TMPOUT

TS=""
exec < $TMPSPOOLFILE1
while read ONE_TS
do
  TS="$TS $ONE_TS"
done

rm -f $TMPSQLFILE1 $TMPSPOOLFILE1

case "$RETCODE" in
  "7") ;;
  "0") quit_hot_bck "Unable to connect to $ORACLE_SID database." ;;
  *)   quit_hot_bck "SQL*Plus exited with status $RETCODE when getting list of tablespaces to back up." ;;
esac

#
# Invoke hot_bck_1ts.sh repeatedly to backup each tablespace on at a time.
#

export UNIQUE_STRING ORACLE_LOGIN COMPRESS UDUMP PARAMFILE

for ONE_TS in $TS
do
  ./hot_bck_1ts.sh -t $ONE_TS -b "$BCK_DIR" > $TMPOUT 2>&1 
  RETCODE="$?"
  cat $TMPOUT > $DISPLAY
  cat $TMPOUT >> "$OUTFILE"
  rm -f $TMPOUT
  if [ "$RETCODE" != "0" ]
  then
    echo "==================================================================" \
    | tee -a "$OUTFILE" > $DISPLAY
    quit_hot_bck "`date +%H:%M:%S`  Complete backup of $SID  ** FAILED **"
  fi
done

#
# Note the current log sequence number, cause a log switch, and wait for
# all online redo logs to be archived.
#

echo "$ORACLE_LOGIN" > $TMPSQLFILE1
chmod 700 $TMPSQLFILE1

cat <<EOF >> $TMPSQLFILE1
WHENEVER SQLERROR EXIT 1
WHENEVER OSERROR EXIT 2
SET FEEDBACK OFF 
SET HEADING  OFF 
SET TERMOUT  OFF
SET PAGESIZE 0

SPOOL $TMPSPOOLFILE1

SELECT TO_CHAR (sequence#)
FROM   v\$log
WHERE  status = 'CURRENT';

SPOOL OFF

ALTER SYSTEM ARCHIVE LOG CURRENT;
EXIT 7
EOF

sqlplus -s @$TMPSQLFILE1 < /dev/null > $TMPOUT 2>&1
RETCODE="$?"
cat $TMPOUT > $DISPLAY
cat $TMPOUT >> "$OUTFILE"
rm -f $TMPOUT

REDO_LOG_LAST="`cat $TMPSPOOLFILE1 2>&1`"

rm -f $TMPSQLFILE1 $TMPSPOOLFILE1

case "$RETCODE" in
  "7") ;;
  "0") quit_hot_bck "Unable to connect to $ORACLE_SID database." ;;
  *)   quit_hot_bck "SQL*Plus exited with status $RETCODE when forcing log switch." ;;
esac

#
# Clean up.
#

echo "==================================================================" | \
  tee -a "$OUTFILE" > $DISPLAY
echo "`date +%H:%M:%S`  Hot backup of $SID completed." | \
  tee -a "$OUTFILE" > $DISPLAY
echo "            Following tablespaces were backed up:\n             $TS" | \
  tee -a "$OUTFILE" > $DISPLAY
echo "            Archived logs required to use this hot backup: $REDO_LOG_FIRST - $REDO_LOG_LAST" | \
  tee -a "$OUTFILE" > $DISPLAY

exit 0