Forum OpenACS Q&A: Response to Oracle hotbackup scripts?
Posted by
Andrew Piskorski
on 07/14/02 01:31 AM
Well, I started out this thread asking about Oracle hot backup
scripts, so here's mine. I've been using it for several weeks now,
seems to work fine:
#!/bin/sh
#
# ora-rman-hb.sh
# $Header: /home/cvsroot/unix/usr/local/adm/bin/ora-rman-hb.sh,v 1.4 2002/06/27 22:12:43 andy Exp $
#
# by atp@piskorski.com, 2002/06/19
# originally based on a similar script by Xuequn "Robert" Xu
# <xux@arsdigita.com>, c. 2000/10/03.
# Oracle physical hot backup using RMAN. Run as user oracle from
# crontab something like so, to run every night at 0:45 am:
#
# 45 0 * * * /usr/local/adm/bin/ora-rman-hb.sh
# We assume that /etc/profile sets and exports ORACLE_SID,
# ORACLE_HOME, etc.:
. /etc/profile
BACKUP_ROOT="/web/oracle-backups"
backup_root=$BACKUP_ROOT
backup_dir="$backup_root/rman"
backup_dir_old_1="$backup_root/rman-old-1"
backup_dir_old_7="$backup_root/rman-old-7"
ARCHIVED_LOGS_DIR="$ORACLE_BASE/admin/ora8/arch"
PASSWORD_FILE="/usr/local/adm/bin/ora-system-password.sh"
MAX_SIZE_KBYTES=614400
LOG_DATE_FORMAT='+%a %Y-%m-%d %H:%M:%S %Z'
ORA_USERNAME=""
ORA_PASSWORD=""
. $PASSWORD_FILE
CMDNAME=`basename $0`
log_file=`basename $CMDNAME`.log
USAGE="Do nightly Oracle 8.1.7 physical hot backup using RMAN.
Usage: ${CMDNAME} [-h] [-t]
Where:
-h : Show this help and exit.
-t : Don't do use the 'backup database' command, use
'backup tablespace' instead.
-r : Redirect stdout and stdin to file. Set to empty string to
turn off default redirection to the log file.
Default: $backup_dir/$log_file
-d : Directory root where we will place our directories of backup files.
This must be an ABSOLUTE pathname, and the oracle unix user must be
able to write to it.
Default: $BACKUP_ROOT
So by default, the backup files will be placed in:
$backup_dir/
$backup_dir_old_1/
$backup_dir_old_7/
"
tablespace_p=0
redirect_p=0
while getopts htr:d: OPT
do
case $OPT in
h)
echo
echo "$USAGE"
echo
exit
;;
t) tablespace_p=1
;;
r) STDOUT=$OPTARG
STDERR=$OPTARG
redirect_p=1
;;
d) backup_root=$OPTARG
backup_dir="$backup_root/rman"
backup_dir_old_1="$backup_root/rman-old-1"
backup_dir_old_7="$backup_root/rman-old-7"
;;
?)
echo 1>&2
echo "ERROR: One or more bad command line options." 1>&2
echo 1>&2
echo "$USAGE" 1>&2
exit 1
;;
esac
done
shift `expr $OPTIND - 1`
# Redirect stdout and stderr to where? Settings STDOUT and STDERR to
# empty string will cause no redirection to occur at all:
if [ "$redirect_p" -ne 1 ]
then
STDOUT=$backup_dir/$log_file
STDERR=$backup_dir/$log_file
fi
create_dir() {
# Takes the path name of directory to create. Allows recursive
# creation. If the file already exists, returns an error if it is a
# normal file, does nothing if the file is a directory.
return_code=0
if [ -f $1 ]
then
return_code=1
echo "Error: A file '$1' already exists. Cannot create directory '$1'."
elif [ ! -d $1 ]
then
mkdir -p $1
return_code=$?
fi
return $return_code
}
# Delete the 2 day old backup, and move the yesterday's backup from
# the current to the 1 day old directory. On Monday, instead of
# deleting the old Saturday 12:45 am backup, move it into the week-old
# directory:
create_dir $backup_dir
create_dir $backup_dir_old_1
create_dir $backup_dir_old_7
# 0 is Sunday:
weekday_num=`date "+%w"`
if [ $weekday_num -eq 1 ]
then
/bin/rm -f $backup_dir_old_7/*
/bin/mv -f $backup_dir_old_1/* $backup_dir_old_7/
fi
/bin/rm -f $backup_dir_old_1/*
/bin/mv -f $backup_dir/* $backup_dir_old_1/
if [ "$tablespace_p" -eq 1 ]
then
# Using 'backup database' also includes the temp tablespace, which
# we don't need, and which on our machien is currently ludicrously
# large. So for now, we want to use 'backup tablespace' instead.
# For some info on fixing the temp tablespace problem, see:
# https://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=000542
# Therefore, generate the list of tablespaces we went to backup,
# excluding the temp tablespace:
# --atp@piskorski.com, 2002/06/20 09:36 EDT
tablespace_list=`$ORACLE_HOME/bin/sqlplus -S $ORA_USERNAME/$ORA_PASSWORD <<EOS 1>>$STDOUT 2>>$STDERR
set feed off linesize 500 pages 0
select tablespace_name
from dba_tablespaces
where tablespace_name not like 'TEMP%';
exit
EOS`
tablespace_list=`echo $tablespace_list | /bin/sed -e 's/ /, /g'`
backup_what="tablespace $tablespace_list include current controlfile ;"
else
# Use 'backup datbase' rman command, NOT 'backup tablespace':
backup_what="database ;"
fi
echo "$CMDNAME: We will backup: $backup_what" 1>>$STDOUT 2>>$STDERR
# Run rman, executing the rman script lines up to the "EOS" sign:
#
# We set the max size on the channel of a single backup piece file to
# MAX_SIZE_KBYTES. We pick a default value of 600 MB so that we're
# certain we could fit each file onto a 650 MB CD-R disk if for some
# reason we want to. (If 1 kb = 1023 bytes, 600 MB = 614400 kb. If 1
# kb = 1000 bytes, 600 MB = 629145.6 kb.)
#
# Note that while the Oracle docs are vague on the distinction,
# filesperset is the maximum number of Oracle data files to read into
# the backup set, NOT the max number of "backup piece" files to write
# out! Presumably, as many backup pieces will be created as
# necessary, and the max size of each backup piece is defined by the
# 'set limit channel' command.
#
# We could also allocate additional channels in order to spread the
# backup across multiple disks. Each single backup set goes to only
# one channel, an each channel can have 1 or more backup sets.
echo "$CMDNAME: rman backup STARTING: `date "$LOG_DATE_FORMAT"`" 1>>$STDOUT 2>>$STDERR
$ORACLE_HOME/bin/rman target / nocatalog <<EOS 1>>$STDOUT 2>>$STDERR
run {
allocate channel m01 type disk format '$backup_dir/%d-t%t-s%s-p%p.bak';
allocate channel m02 type disk format '$backup_dir/%d-t%t-s%s-p%p.bak';
allocate channel m03 type disk format '$backup_dir/%d-t%t-s%s-p%p.bak';
set limit channel m01 kbytes $MAX_SIZE_KBYTES;
set limit channel m02 kbytes $MAX_SIZE_KBYTES;
set limit channel m03 kbytes $MAX_SIZE_KBYTES;
backup
filesperset = 6
skip inaccessible
skip offline
$backup_what
# Archives the current redo log plus any other non-archived redo
# logs. The Oracle docs say you should do this immediately after
# backing up the database:
# http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76990/troubler.htm#446214
# --atp@piskorski.com, 2002/06/19 21:11 EDT
sql 'alter system archive log current';
# Creates a "trace file" script which when run will re-create the
# control file, but all RMAN info in the control file will be lost.
# We've already made a physical backup of th control file above, so
# also doing this here is belt-and-suspenders:
# --atp@piskorski.com, 2002/06/19 21:11 EDT
sql 'alter database backup controlfile to trace';
}
exit
EOS
echo "$CMDNAME: rman backup DONE: `date "$LOG_DATE_FORMAT"`" 1>>$STDOUT 2>>$STDERR
# Note that the 'backup database' command does NOT backup the archived
# redo logs. Since our nightly file-system backs them up directly
# from where Oracle puts them, this is ok, we don't need to do a
# 'backup archivelog' command in RMAN.
# Note that since we have put them under CVS control, here we are NOT
# backing up the init.ora file, listener.ora, and other config files
# located in:
# $ORACLE_BASE/admin/ora8/pfile/
# $ORACLE_HOME/network/admin/
# TODO: The 'backup database' command backs up the control file, but
# it seems unclear WHICH version of the control file it backs up - the
# control file as it existed BEFORE RMAN started, or the version AFTER
# the RMAN backup of the database files completes, when it will
# include recovery catalog information for the database backup just
# completed. If RMAN backs up the before version, perhaps we would
# also want to do 'backup current controlfile' or 'include current
# controlfile' after the 'backup database'. I recall seeing at least
# one example in the Oracle docs that implies that this might be a
# good thing to do, but none of the rest the examples do so, so it's
# probably not necessary.
# --atp@piskorski.com, 2002/06/19 19:56 EDT
# It would be nice to have RMAN write to a named pipe and have gzip
# read from the named pipe at the same time in the background, like we
# do in the ora-export.sh script, but I'm not even really certain
# whether that's feasible. So to keep things simple, just gzip
# separately after RMAN is done: --atp@piskorski.com, 2002/06/25 09:19 EDT
echo "$CMDNAME: gzip STARTING: `date "$LOG_DATE_FORMAT"`" 1>>$STDOUT 2>>$STDERR
/bin/gzip $backup_dir/*.bak 1>>$STDOUT 2>>$STDERR
echo "$CMDNAME: gzip DONE: `date "$LOG_DATE_FORMAT"`" 1>>$STDOUT 2>>$STDERR
# Remove archived redo logs more than 12 days old:
#
# Since on Monday we move the previous Saturday backup into the week
# old directory (2 days old), a minimum of ** 10 ** days of archived
# redo logs (7 days + 2 + 1 extra to account for different times
# during the day) should insure that we always have enough archived
# logs on disk to take us all the way back to the oldest full backup
# which we have on disk.
/bin/find $ARCHIVED_LOGS_DIR ( -ctime +12 ) -exec /bin/rm {} ; 1>>$STDOUT 2>>$STDERR
echo "$CMDNAME: everything DONE: `date "$LOG_DATE_FORMAT"`" 1>>$STDOUT 2>>$STDERR
