Take a fast hot backup of your database using your filer’s snapshot facility

Providing You With Handy Scripts
Most of our senior consultants have been working with Oracle technology for over ten years. As you can imagine, they’ve developed quite a few tools and scripts that they carry with them from project to project. Below, you’ll find a sampling of our handy database administration scripts that are yours for the taking. Feel free to download and use them.

Take a fast hot backup of your database using your filer’s snapshot facility

This script puts all tablespaces of the database into backup mode and calls upon your filer’s snapshot facility to take a snapshot of the database. This allows you to take an “instantaneous” hot backup—you can then copy the snapshot to tape for archival purposes if desired. This script makes sure the snapshot backup will include a copy of the server parameter file, the control file, and a trace listing of the control file. A detailed log report indicates exactly what operations were performed and when, and which archived redo logs would be required to recover the database from the backup set.

#!/bin/ksh
#
# hot_bck_snapshot.sh
# ===================
#
# This script is provided  for individual use and
# not for sale. We do not warrant the script in any way
# and will not be responsible for any loss arising out of its use.
#
# 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

Note: * These scripts are provided by Database Specialists for individual use and not for sale. We do not warrant the scripts in any way and will not be responsible for any loss arising out of their use. By downloading or using these scripts you are agreeing to these terms and conditions.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s