#!/bin/ksh
#
# hot_bck_snapshot.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 using
# the snapshot facility of the filer. This script puts all tablespaces into
# backup mode and copies the control file, a trace of the control file, and
# the parameter file to a specified directory that will be included in the
# snapshot. Then the script initiates a snapshot with the filer and then
# takes the tablespaces out of backup mode. 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. Review the "Configuration settings" section of this script to customize
#       as needed for your operating system and environment.
#  3. 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.
#  4. Review the snapshot create, delete, and rename commands used in this
#       script to verify that they are appropriate for your filer.
#  5. Verify that all datafiles belonging to the database are located in
#       directories that will be part of the filer's snapshot.
#
#
# Usage: hot_bck_snapshot.sh -i instance -b backup_dirrectory -f filer_name
#                            -v snapshot_volume [-r snapshot_retention]
#                            [-l log_directory] [-o oracle_login]
#                            [-n notify_list] [-s]
#        instance is the ORACLE_SID of the database to back up. 
#        backup_directory is the full path of a directory that will be included
#            in the snapshot, where the control file and parameter file can be
#            placed by this script in order to get included in the filer
#            snapshot.
#        filer_name is the name of the filer where the snapshot is to be
#            created.
#        snapshot_volume is the name of the volume to snapshot on the filer
#        snapshot_retention is how many snapshots to retain. (The default is 5.)
#            This script will delete the oldest snapshot and rename all of the
#            others so that sid.0 is always the newest snapshot, sid.1 is the
#            previous one, sid.2 the one before that, and so forth.
#        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.
#        -s may optionally be specified to run in silent mode.
#
# Version 12-08-2006
#

#
# Configuration settings.
#

MAIL_PROGRAM="mailx"    # On some systems you may need to set this to "mail".
REMOTE_SHELL_CMD="rsh"  # Command to use to execute statements on filer.

export MAIL_PROGRAM

#
# 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=""
FILER=""
SNAP_VOLUME=""
RETAINED_SNAPS="5"
LOG_DIR=""
ORACLE_LOGIN="/"
NOTIFY=""
DISPLAY="/dev/tty"

USAGE="Usage: `basename $0` -i instance -b backup_dirrectory -f filer_name\n                           -v snapshot_volume [-r snapshot_retention]\n                           [-l log_directory] [-o oracle_login]\n                           [-n notify_list] [-s]"

while getopts :i:b:f:v:r:l:o:n:s opt
do
  case "$opt" in
    "i") SID="$OPTARG" ;;
    "f") FILER="$OPTARG" ;;
    "v") SNAP_VOLUME="$OPTARG" ;;
    "r") RETAINED_SNAPS="$OPTARG"
         ((DUMMY=RETAINED_SNAPS+1))
         if [ "$DUMMY" -gt 0 -a "$DUMMY" -lt 101 ]
         then
           DUMMY="dummy"
         else
           echo "Specify a number of snapshots between 0 and 99 for the -r parameter" 1>&2
           exit 1
         fi ;;
    "o") ORACLE_LOGIN="$OPTARG" ;;
    "n") NOTIFY="$OPTARG" ;;
    "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 ;;
    "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" -o -z "$FILER" -o -z "$SNAP_VOLUME" ]
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"
CONTROLFILE="$BCK_DIR/control.bak"
CONTROLFILE_TRACE="$BCK_DIR/control.trace"
UNIQUE_STRING="`date +%m-%d-%Y:%H:%M:%S-`$$"
WARNINGS="N"

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 snapshot-based hot backup of $SID database to $FILER" | tee "$OUTFILE" > $DISPLAY
echo "===============================================================================" | tee -a "$OUTFILE" > $DISPLAY

#
# Delete oldest snapshot from filer and rename snapshots being retained.
#

echo "`date +%H:%M:%S`  Deleting snapshot ${SID}.${RETAINED_SNAPS} from filer"\
  | tee -a "$OUTFILE" > $DISPLAY
echo "          $REMOTE_SHELL_CMD $FILER snap delete $SNAP_VOLUME ${SID}.${RETAINED_SNAPS}" | tee -a "$OUTFILE" > $DISPLAY
$REMOTE_SHELL_CMD $FILER snap delete $SNAP_VOLUME ${SID}.${RETAINED_SNAPS} > $TMPOUT 2>&1
RETCODE="$?"
cat $TMPOUT > $DISPLAY
cat $TMPOUT >> "$OUTFILE"
rm -f $TMPOUT
if [ "$RETCODE" != "0" ]
then
  WARNINGS="Y"
fi

((PREV_SNAP=RETAINED_SNAPS-1))
while [ "$PREV_SNAP" -ge 0 ]
do
  echo "`date +%H:%M:%S`  Renaming snapshot ${SID}.${PREV_SNAP} on filer" \
    | tee -a "$OUTFILE" > $DISPLAY
  echo "          $REMOTE_SHELL_CMD $FILER snap rename $SNAP_VOLUME ${SID}.${PREV_SNAP} ${SID}.${RETAINED_SNAPS}" | tee -a "$OUTFILE" > $DISPLAY
  $REMOTE_SHELL_CMD $FILER snap rename $SNAP_VOLUME ${SID}.${PREV_SNAP} ${SID}.${RETAINED_SNAPS} > $TMPOUT 2>&1
  RETCODE="$?"
  cat $TMPOUT > $DISPLAY
  cat $TMPOUT >> "$OUTFILE"
  rm -f $TMPOUT
  if [ "$RETCODE" != "0" ]
  then
    WARNINGS="Y"
  fi
  ((RETAINED_SNAPS=RETAINED_SNAPS-1))
  ((PREV_SNAP=RETAINED_SNAPS-1))
done

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

#
# Put all tablespaces into backup mode.
#

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

cat <<EOF >> $TMPSQLFILE1
WHENEVER SQLERROR EXIT 2
WHENEVER OSERROR EXIT 3
SET FEEDBACK OFF 
SET SERVEROUTPUT ON SIZE 32767

DECLARE
  CURSOR c1 IS
    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);
  e_already_in_backup_mode EXCEPTION;
  PRAGMA exception_init (e_already_in_backup_mode, -1146);
BEGIN
  FOR r IN c1 LOOP
    dbms_output.put (TO_CHAR (SYSDATE, 'HH24:MI:SS') || '  ' ||
                     'Putting tablespace ' || r.tablespace_name ||
                     ' into backup mode');
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLESPACE "' || r.tablespace_name ||
                        '" BEGIN BACKUP';
      dbms_output.put_line (' ');
    EXCEPTION
      WHEN e_already_in_backup_mode THEN
        dbms_output.put_line (' (already in backup mode)');
    END;
  END LOOP;
END;
/

EXIT 7
EOF

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

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 putting tablespaces into backup mode." ;;
esac

#
# Create a backup copy of the control file.
#

echo "`date +%H:%M:%S`  Backing up control file to $CONTROLFILE" \
  | tee -a "$OUTFILE" > $DISPLAY

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

cat <<EOF >> $TMPSQLFILE1
WHENEVER SQLERROR EXIT 2
WHENEVER OSERROR EXIT 3
SET FEEDBACK OFF

ALTER DATABASE BACKUP CONTROLFILE TO '$CONTROLFILE' REUSE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

ALTER SESSION SET /* $UNIQUE_STRING */ sql_trace = TRUE;

EXIT 7
EOF

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

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 backing up control file." ;;
esac

#
# Create a backup copy of the control file trace.
#

TRACE_FILE="`grep -l $UNIQUE_STRING $UDUMP/* 2> /dev/null | head -1 2> /dev/null`"
if [ -f "$TRACE_FILE" ]
then
  LAST_LINE=`grep -n $UNIQUE_STRING "$TRACE_FILE" | head -1 | cut -f 1 -d :`
  ((LAST_LINE=LAST_LINE-3))
  if [ "$LAST_LINE" -gt "20" ]
  then
    echo "`date +%H:%M:%S`  Backing up control file trace to $CONTROLFILE_TRACE" | tee -a "$OUTFILE" > $DISPLAY

    (head -$LAST_LINE "$TRACE_FILE" > "$CONTROLFILE_TRACE") > $TMPOUT 2>&1
    RETCODE="$?"
    cat $TMPOUT > $DISPLAY
    cat $TMPOUT >> $OUTFILE
    rm -f $TMPOUT
    if [ "$RETCODE" != "0" ]
    then
      WARNINGS="Y"
    fi
  fi
fi

#
# Create a backup copy of the parameter file.
#

if [ -f "$PARAMFILE" ]
then
  echo "`date +%H:%M:%S`  Backing up parameter file to $BCK_DIR/`basename $PARAMFILE`" | tee -a "$OUTFILE" > $DISPLAY

  cp "$PARAMFILE" "$BCK_DIR" > $TMPOUT 2>&1
  RETCODE="$?"
  cat $TMPOUT > $DISPLAY
  cat $TMPOUT >> $OUTFILE
  rm -f $TMPOUT
  if [ "$RETCODE" != "0" ]
  then
    WARNINGS="Y"
  fi
fi

#
# Create a new snapshot on the filer containing all of the database files in
# backup mode, along with the control file, control file trace, and parameter
# file.
#

echo "`date +%H:%M:%S`  Creating snapshot ${SID}.0 on filer"\
  | tee -a "$OUTFILE" > $DISPLAY
echo "          $REMOTE_SHELL_CMD $FILER snap create $SNAP_VOLUME ${SID}.0" | tee -a "$OUTFILE" > $DISPLAY
$REMOTE_SHELL_CMD $FILER snap create $SNAP_VOLUME ${SID}.0 > $TMPOUT 2>&1
RETCODE="$?"
cat $TMPOUT > $DISPLAY
cat $TMPOUT >> "$OUTFILE"
rm -f $TMPOUT
if [ "$RETCODE" != "0" ]
then
  quit_hot_bck "Error while creating snapshot on filer."
fi


#
# Take all tablespaces out of backup mode.
#

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

cat <<EOF >> $TMPSQLFILE1
WHENEVER SQLERROR EXIT 2
WHENEVER OSERROR EXIT 3
SET FEEDBACK OFF 
SET SERVEROUTPUT ON SIZE 32767

DECLARE
  CURSOR c1 IS
    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);
BEGIN
  FOR r IN c1 LOOP
    dbms_output.put_line (TO_CHAR (SYSDATE, 'HH24:MI:SS') || '  ' ||
                          'Taking tablespace ' || r.tablespace_name ||
                          ' out of backup mode');
    EXECUTE IMMEDIATE 'ALTER TABLESPACE "' || r.tablespace_name ||
                      '" END BACKUP';
  END LOOP;
END;
/

EXIT 7
EOF

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

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 taking tablespaces out of backup mode." ;;
esac

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

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." ;;
  "1") quit_hot_bck "Unable to connect to $ORACLE_SID database." ;;
  *)   WARNINGS="Y" ;;
esac

#
# Clean up.
#

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

if [ "$WARNINGS" = "N" ]
then
  exit 0
else
  echo " " | tee -a "$OUTFILE" > $DISPLAY
  echo "*** Note that the backup completed with warnings *** " | tee -a "$OUTFILE" > $DISPLAY
  if [ ! -z "$NOTIFY" ]
  then
    $MAIL_PROGRAM -s"Hot backup of $ORACLE_SID database completed with warnings" "$NOTIFY" < "$OUTFILE"
  fi
  exit 1
fi