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