Forum OpenACS Q&A: Response to Oracle hotbackup scripts?

Posted by Andrew Piskorski on
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:

# $Header: /home/cvsroot/unix/usr/local/adm/bin/,v 1.4 2002/06/27 22:12:43 andy Exp $
# by, 2002/06/19
# originally based on a similar script by Xuequn "Robert" Xu
# <>, 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/

# We assume that /etc/profile sets and exports ORACLE_SID,
# ORACLE_HOME, etc.:

. /etc/profile


LOG_DATE_FORMAT='+%a %Y-%m-%d %H:%M:%S %Z'


CMDNAME=`basename $0`
log_file=`basename $CMDNAME`.log

USAGE="Do nightly Oracle 8.1.7 physical hot backup using RMAN. 

Usage:  ${CMDNAME} [-h] [-t]  


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





while getopts htr:d: OPT
  case $OPT in
        echo "$USAGE"
    t)  tablespace_p=1
    d)  backup_root=$OPTARG
        echo 1>&2
        echo "ERROR:  One or more bad command line options." 1>&2
        echo 1>&2
        echo "$USAGE" 1>&2
        exit 1
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 ]

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.

  if [ -f $1 ]
    echo "Error:  A file '$1' already exists.  Cannot create directory '$1'."
  elif [ ! -d $1 ]
    mkdir -p $1

  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 ]
  /bin/rm -f $backup_dir_old_7/*
  /bin/mv -f $backup_dir_old_1/* $backup_dir_old_7/

/bin/rm -f $backup_dir_old_1/*
/bin/mv -f $backup_dir/* $backup_dir_old_1/

if [ "$tablespace_p" -eq 1 ]
  # 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:
  # Therefore, generate the list of tablespaces we went to backup,
  # excluding the temp tablespace:
  #, 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%';

  tablespace_list=`echo $tablespace_list | /bin/sed -e 's/ /, /g'`
  backup_what="tablespace  $tablespace_list  include current controlfile ;"

  # Use 'backup datbase' rman command, NOT 'backup tablespace':
  backup_what="database ;"


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;

    filesperset = 6
    skip inaccessible
    skip offline

  # 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:
  #, 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:
  #, 2002/06/19 21:11 EDT

  sql 'alter database backup controlfile to trace';

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.
#, 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 script, but I'm not even really certain
# whether that's feasible.  So to keep things simple, just gzip
# separately after RMAN is done:, 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