(Get this article & the current version of oraback.sh in one tar file.)
Trying to back up today's databases is like trying to catch a charging rhino with a butterfly net. Real world databases have become screaming behemoths that are very difficult to capture (back up), and standard UNIX utilities are like butterfly nets. If you try to use these utilities for the job, not only will you not capture the data -- you'll break your arm! This article is my attempt to build a better rhino trap. It uses many advanced shell scripting features, so you may find it useful even if you do not have Oracle.
One of the first decisions a database administrator must make is whether to perform "cold" or "hot" backups. A cold backup involves:
Here are the steps to perform a hot backup. (Note that a hot backup requires much more knowledge of Oracle's internal structure, so a basic definition of helpful terms is given in the sidebar "Definitions.") First, you must determine the name of each tablespace in the instance and the files that are within it. Then, for each tablespace, you:
Once all tablespaces are done:
It is much easier to perform cold backups, so why do hot backups at all? Simply stated, they increase your backup window, or the time in which you are allowed to perform backups. Because cold backups require a shutdown of the database, your users will probably allow only a very small backup window. Hot backups allow you to back up without shutting down, remaining virtually invisible to the user, and thereby significantly enlarging your window. I have even performed hot backups of production data during primetime hours. Try that with a cold backup!
If you are going to use hot backups, though, you must follow certain precautions. Failure to do so could mean you would only be able to restore up to your last cold backup -- if you made one.
During a cold backup, all data files have been synchronized. A restore simply requires copying all files into place and restarting the database. However, during a hot backup, each file is backed up at a different time; thus each has a different time stamp. You must have some method of syncing all of those files to the same point during a restore. That is what Oracle's Redo Logs, (also known as archiving, or Archive Logs) are for. Archiving continually records all database changes in what is called the On-line Redo log. Once that log is full, it is copied to an Archived Redo log. (For more information, see the sidebar "Should I Use Archiving?")
When restoring from a hot backup, you first replace the damaged database files with their respective backup copies. (You only need to replace the damaged files.) You then "play" the redo logs, effectively "redoing" all the transactions that affected those files. All files are then synchronized, and the database can be brought on line.
With all the logs in place, this restore can be done with one command. However, if just one redolog is lost, your restore could range from extremely difficult to impossible. If you do not have the set of redo logs that span the time of your backup, you will not recover at all.
This is why you must treat your redo logs as if they were gold. Some of the methods of protecting them are:
It is not uncommon, or unwise, to use all of the methods listed above to protect your redo logs.
To automate Oracle backups, you must know the location of tablespaces, raw partitions, data files, control files, redolog files, and whether archiving is running. To make things more difficult, each installation will have different answers to these questions. Oraback.sh (Listing 1) addresses all of the problems above by obtaining most of its information from Oracle. The script will do the following for each instance:
Oraback.sh supports these features:
The script assumes that all instances want hot backups, which require redologs. Oraback.sh will warn you of any instances that do not have logs. It will continue to warn you until you correct the problem or customize your setup for that instance. Customization is done with oraback.conf (Listing 2).
Now that I've explained the overall purpose of oraback.sh, here is a description of each step within the script. Oraback.sh begins by checking oraback.conf to see whether the user running the backup is Oracle, or a user listed in field 7. It then looks to see whether the word "skip" is in field 2. If so, it skips the backup once, and removes the word from oraback.conf. The script then does one of the following, depending on which arguments, if any, it received:
Oraback.sh first checks the $HOST.master line in oraback.conf to find out if: (a) there is a tape device in field 6. If so, it labels the tape, making sure the no-rewind device is being used; and (b) there is a number in field 8. If so, it will perform that many simultaneous copies of database files.
Next, oraback.sh asks Oracle questions that will determine how (or if) the SID will be backed up. It asks:
If the instance is online, logs are on, and the instance is not excluded from hot backups, the script will put each tablespace into backup mode by using the sqldba command begin backup. The script then copies that tablespace's files to the backup device. When the copying is done, it takes the tablespace out of backup mode with the sqldba command end backup.
These three steps are the core of the hot backup. While the tablespace is in backup mode, the files will not change. Any changes made to the tablespace while it is in backup mode are sent to the redo logs, and are applied once it is taken out of backup mode. This is all done automatically by Oracle.
The script supports simultaneous copying of individual data files to dramatically increase the speed of the backup. Depending on how many files there are per tablespace, there may be one or more tablespaces in backup mode at once. This will be done in order to perform the number of concurrent file copies (using dd) that are specified in field 8 of oraback.conf, if you are using this feature.
Next, the script forces a checkpoint and archive log switch, which causes the online redo log to be sent to an archived redo log. It then backs up the control files to disk using both sqldba and a manual copy. Finally, the script makes copies of the essential redo logs and compresses them. (Essential logs are those that span the time of your backup.) If the backup device is a tape, it then backs up all files that were sent to disk to the tape device.
If the instance is off-line, oraback.sh only needs to copy the datafiles to the backup device. The script then backs up the control files and redo logs the same way the hot backup does.
If the instance is online, but archiving is off, it checks oraback.conf for a line that reads:
$HOST:$SID:NOARCHIVELOG
If that line is not found, oraback.sh complains and sends you mail. If the line is found, it looks for the word "offline" on that same line. If "offline" is there, oraback.sh will shut down the instance, perform a cold backup, and then restart the instance. (This is one of the ways you can customize oraback.sh for different instances.)
If the instance is online, oraback.sh also looks for a line in oraback.conf that reads
$HOST:$SID:::nohotbackup
If that word is found, the script will skip this instance when performing a hot backup.
In summary, oraback.sh can back up from one to all of the instances in oratab. To back up all instances, run it with no arguments. To back up one or more, run it with those SID's as arguments. To schedule a backup for the time in oraback.conf, run it with the at argument.
Check the following values in the site-specific section at the top of
the script. They must be changed to fit your site.
a. BINDIR must be set to where you install oraback.sh.
b. ORATAB must be set to the name and location of Oracle's oratab
file. (It must be located in a directory that Oracle can write to.)
c. ORACONF must be set to the name and location of your oraback.conf
file.
You should also review and confirm all other preference variables in the site-specific section, including ORADIR, TMP, LOG, and PATH.
You must also verify that:
a. You know which instances are using archiving
b. There is a valid archive_dest in each instances's config.ora
(not the default /?/dbs/arch)
c. Oracle can create directories in the filesystem that you are
using for backups
Oraback.conf is the main configuration file for oraback.sh. If you want to use the at argument, you must have the following line in that file:
$HOST.master::Sun:1200:1730:::5 :/dmp::Mail -s Error_oracle:
These and other fields are described below:
There are four predefined functions, unused by default, which are listed in the site-specific section of oraback.sh. They are listed below, with their respective usage times:
Preback: Runs before entire backup starts
Preshut: Runs before shutdown of each instance
Poststart: Runs after startup of each instance
Postback: Runs after entire backup finishes
If you wanted to restart orasrv, for instance, after an instance is restarted (with a script called rc.orasrv.sh,) you would change the Poststart function to read:
Poststart(){
rc.orasrv.sh
}
If you have an instance that will not run archiving or hot backups, you need a line in oraback.conf that starts with:
$HOST:$SID::::
For testing, select a small instance and run the following command as Oracle:
$ /usr/local/bin/oraback.sh <instance>
Be aware that if the word "offline," is in oraback.conf, or if the day that you specified for cold backups is today, the instance will be shut down!
If this test functions properly, you can put an entry in Oracle's cron. Call this command:
/usr/local/bin/oraback.sh at
at around 1200. It will check the oraback.conf and schedule an at job that will perform the backup.
Never accept that a new backup strategy works until you have fully tested it under all circumstances! Make sure you test all of the following until you are comfortable:
Once your restore test is successful, you will have caught and tamed the rhino! This script represents more than three years of work, and its development is a continual process. I hope you find it useful.
In one word, yes! Having logs available during a recovery is a tremendous help. There are several things that you can do with logs that simply are not possible without them:
There are two common arguments for not using archiving. The first is that it would decrease performance. The performance loss is relatively minor, unless you have some parameters set incorrectly. It is important to know that redo logs are always being created in every Oracle instance. Enabling archiving simply copies the closed redo log to another directory for recovery purposes. Before you make any decision about archiving, you should benchmark your instance with and without archiving. (A recent benchmark I performed showed a 2.5 percent performance loss during continuous loads.)
The second argument is that archiving is superfluous. Some databases are read-only and are updated only occasionally. Users might argue that archiving is not needed. They often tell you to backup the database manually when it changes, after a load or import. This defeats one of the main purposes of automating: cron never forgets! If the database does not need archiving because there is little activity, then enabling it won't hurt. The CPU usage is negligible, and archiving will allow you to do hot backups and use all the features listed above.
There is one type of database that I think would be significantly hampered by archiving: the test database. I still believe that logs should be on whenever possible, but when a DBA is testing a new database, he/she may be loading and unloading tables all day long. This generates a huge amount of archived redo logs that have no value. It was for this type of database that I added the ability to do a nightly cold backup of an instance that does not have archiving.
In summary, every production and development database should use archiving,
unless continuous imports make it unrealistic.
PLEASE NOTE! This script is always changing, trying to keep up with the latest versions of Oracle and requests from users. To make sure you are running the most current version, contact me. I am in the process of adding some new features to it, so the version that you see here may not be fully functional! Once again, contact me!
#!/bin/sh
#
# Rcs_ID" = '$Id: oraback.sh,v 1.3 1997/06/20 15:26:59 Pcurtis Exp Pcurtis $'
#
# @(#)oraback.sh - (Hot backups for Oracle databases)
# Copyright (C) 1996 Curtis Preston - curtis@pencom.com
#
# This program is free software; you can redistribute it and/or modify it
# under the terms of the GNU General Public License as published by the Free
# Software Foundation; either version 2 of the License, or (at your option)
# any later version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
# details.
#
# For a copy of the license, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139
#
# Many thanks to Sam Binder, for bringing me gracefully into SQL land...
#
#######################################################
##Site-specific section (change as appopriate)
COMPRESS=/usr/local/bin/gzip
BINDIR=/usr/local/bin #Where $0 is located
ORACLE=oracle
ORATAB=/var/opt/oracle/oratab #Oracle's configfile
ORADIR=`grep -v '^#' $ORATAB|cut -d: -f2|tail -1`
ORACONF=$ORACLE_HOME/oraback.conf
TMP=/var/tmp
PATH=$PATH:/usr/bin:/usr/sbin:/sbin:/usr/5bin:/bin:$ORACLE_HOME/bin
ORIG_PATH=$PATH
Preshut() { #Run prior to shutting down instance
echo
}
Poststart() { #Run after starting up instance
echo
}
Preback() { #Run prior to backup
echo
}
Postback() { #Run after entire backup finishes
echo
}
export BINDIR ORATAB ORADIR ORACONF TMP PATH ORIG_PATH
##End site-specific configuration section
#######################################################
Usage() {
echo "Usage: $0 [at|\$ORACLE_SID(s)]"
echo "(Must be run as one of the following users:"
echo "${USERS}."
exit 1
}
Log_error() { #Send errors to various places
if [ "$1" -gt 0 ] ; then
TIME="`date '+%b %e %T'` `uname -n`"
MSG=" `basename $0` $SID: WARNING:"
ERR=`echo $2|sed "s/^/$TIME $MSG /"`
echo "$ERR" |$TLOG
echo "$ERR" >>$TMP/$X.Mail
[ "$1" = 99 ] && touch $TMP/$X.Fatal
fi
}
Error_chk() { #Check sqldba output files for errors
ERROR=`egrep -i 'dba\-|ora\-|error' $1`
[ -n "$ERROR" ] && Log_error 1 "$ERROR"
[ ! -s $1 ] && Log_error 99 "Bus error on sqdldba"
}
Backup_offline() { #backup database offline
echo " Backing up $ORACLE_SID OFFLINE." |$TLOG
Backup_instance cold
}
Backup_instance() { #All tablespaces in an instance
cut -d: -f1 $CWD/$TBLSPACE_LIST | sort -u |\
while read TBS ; do
if [ -n "$SIMULT" ] ; then #If doing simultaneous
Ps_check #backups, then do
export TBS #them in the
Backup_one_tablespace $1 & #background
else
export TBS
Backup_one_tablespace $1
fi
done #done with all tablespaces
while [ -n "`ls $TMP/$X.$SID.*.* 2>/dev/null`" ] ; do
sleep 10 #Wait for all TBS's to finish
done
}
Backup_one_tablespace() { #All files in one tbs
touch $TMP/$X.$SID.${TBS}all.allfiles
if [ $1 = "hot" ] ; then #If hot b/up, then put in
$SQLDBA<<EOF_BOT >$TMP/$X.$TBS.tablebu 2>&1 #b/up mode
connect internal;
alter tablespace $TBS begin backup;
exit;
EOF_BOT
Error_chk $TMP/$X.$TBS.tablebu
fi
grep "^$TBS:" $CWD/$TBLSPACE_LIST | cut -d: -f2 | \
while read FILE ; do
if [ -n "$SIMULT" ] ; then #More simult. copying
export FILE
Ps_check
Copy_one_database_file &
else
CT=`cat $TMP/$X.CT`
echo `expr $CT + 1` > $TMP/$X.CT
export FILE CT
Copy_one_database_file
fi
done #Done with all files in TBS
while [ -n "`ls $TMP/$X.*.$TBS.* 2>/dev/null`" ] ; do
sleep 10 #Wait for all files to copy
done
if [ $1 = "hot" ] ; then #If hot, take out of b/u mode
$SQLDBA << EOF_TBS > $TMP/$X.$TBS.tablebu 2>&1
connect internal;
alter tablespace $TBS end backup;
exit;
EOF_TBS
Error_chk $TMP/$X.$TBS.tablebu
fi
rm $TMP/$X.$SID.${TBS}all.allfiles
}
Copy_one_database_file() {
DBF_BASENAME=`basename $FILE`
touch $TMP/$X.$SID.$TBS.$DBF_BASENAME
[ -n "$TAPE" ] && DEVICE=$TAPE || DEVICE=$CWD/$DBF_BASENAME
NEW=`find $FILE -newer $CWD/$DBF_BASENAME.Z -print 2>/dev/null`
#If datafile newer than b/u,or if no b/u file,copy it
if [ $? -gt 0 -o "$NEW" = "$FILE" ] ; then
echo " ${CT}- Backing up $FILE." |$TLOG
if [ "$COMP" = Y ] ; then
dd if=$FILE bs=64k 2>/dev/null| $COMPRESS -c >$DEVICE.Z
else
dd if=$FILE of=$DEVICE bs=64k 2>/dev/null
fi
Log_error $? "Error Copying $FILE to $DEVICE"
else
echo " NOT Backing up $FILE. (NO CHANGE)" |$TLOG
fi
rm $TMP/$X.$SID.$TBS.$DBF_BASENAME
}
Switch_logfile_n_backup_controlfile() {
echo " Switch logfile and backup controlfile"|$TLOG
#This section needs to be changed to use the 'alter system logfile current'
#syntax, while providing backwards compatibility to this old way for v.6
$SQLDBA << EOF_CONTROL >$TMP/$X.control 2>&1
connect internal;
alter system checkpoint;
alter system switch logfile;
alter database backup controlfile to '$CWD/cntrlfile';
exit;
EOF_CONTROL
Error_chk $TMP/$X.control
$COMPRESS -f $CWD/cntrlfile >/dev/null 2>&1
Log_error $? "Error compressing $CWD/cntrlfile"
#If it is a valid archive log dir, and there are logs in it
if [ -d $ARCH_LOG_DIR -a -f ${LOGDEST}* ] ; then
NEWER=`find $ARCH_LOG_DIR -type f -name "$ARCH_LOG_FILE*" -newer $OLD -print`
NEWER=`echo $NEWER`
NEWEST=`ls -t $NEWER|head -1`
OLD_SIZE=`du -s $NEWEST|awk '{print $1}'` ; SLEEP=Y
#This section sleeps if last archive log file is still being copied
#(sometimes this takes a few minutes)
#This is a 'hacky' way to do it, but was the best way to do in v.6
while test $SLEEP = Y ; do
sleep 60
NEW_SIZE=`du -s $NEWEST|awk '{print $1}'`
[ $NEW_SIZE -eq $OLD_SIZE ] && SLEEP=N || OLD_SIZE=$NEW_SIZE
done
else
[ ! -d $ARCH_LOG_DIR ] && Log_error 1 "Log dest. (${ARCH_LOG_DIR}) not found."
[ ! -f ${LOGDEST}* ] && Log_error 1 "NO archive logs found in $ARCH_LOG_DIR."
fi
}
Copy_control_and_essential_redolog_files() {
if [ -f $CWD/$CONTROLFILE_LIST ] ; then
echo " Manually copying one controlfile." |$TLOG
CONTROLFILE=`grep -v '^$' $CWD/$CONTROLFILE_LIST`
CONTROLFILE_BASENAME=`basename $CONTROLFILE`
$COMPRESS -c $CONTROLFILE > $CWD/$CONTROLFILE_BASENAME.Z
Log_error $? "Error copying $FILE to $CWD"
else
Log_error 1 "$CWD/$CONTROLFILE_LIST: File not found"
fi
if [ -f $CWD/$CONTROLFILE_LIST $CWD/$LOGFILE_LIST ] ; then
if [ -z "$NOLOG" ] ; then
#If Instance is up, remove old logs, otherwise leave them
if [ "$DB_UP" = 2 ] ; then
REMOVE=`ls $CWD/BacKuP.CoPy.${ARCH_LOG_FILE}* 2>/dev/null|grep -v $BOLD`
REMOVE=`echo $REMOVE`
if [ -n "$REMOVE" ] ; then
echo " Removing old archive logs: $REMOVE" |$TLOG
rm -f $REMOVE
else
echo " NO old archive logs found." |$TLOG
fi
fi
echo " Copying redo logs created since $0 started:" |$TLOG
NEWER=`find $ARCH_LOG_DIR -type f -name "$ARCH_LOG_FILE*" -newer $OLD -print`
NEWER=`echo $NEWER`
if [ -n "$NEWER" ] ; then
echo " New archive logs found: $NEWER" |$TLOG
#Files are stable, copy them
for ARCHIVED_LOG_FILE in $NEWER ; do
ARCHIVED_LOG_FILE_BASENAME=`basename $ARCHIVED_LOG_FILE`
BACKUP_COPY_NAME="BacKuP.CoPy.$ARCHIVED_LOG_FILE_BASENAME"
cp -p $ARCHIVED_LOG_FILE $CWD/$BACKUP_COPY_NAME
[ $? -ne 0 ] && Log_error 1 "Error copying $ARCHIVED_LOG_FILE to $CWD"
done
$COMPRESS -f $CWD/BacKuP.CoPy.${ARCH_LOG_FILE}*
[ $? -ne 0 ] && Log_error 1 "Error compressing $CWD/${ARCH_LOG_FILE}\*"
else
echo " NO archive logs made since this backup started." |$TLOG
fi
else
echo " No logs: $SID set to NOARCHIVELOG." |$TLOG
fi
else
Log_error 1 "$CWD/$LOGFILE_LIST: File not found"
fi
if [ -n "$TAPE" ] ; then #Send disk b/u's to tape
CT=`cat $TMP/$X.CT`
echo `expr $CT + 1` > $TMP/$X.CT
echo " ${CT}- Tar file of $CWD." |$TLOG
cd $CWD
tar cvf $TAPE ./*
mt -t $TAPE offl
fi
}
Copy_online_redologs() {
echo " Copying online redo logs." |$TLOG
cat $CWD/$REDOLOGS_LIST | \
while read REDOLOG
do
LOGBASE=`basename $REDOLOG`
$COMPRESS -c $REDOLOG >$CWD/$LOGBASE.Z
[ $? -ne 0 ] && Log_error 1 "Error compressing $REDOLOG to $CWD/LOGBASE.Z"
done
}
Shutdown_instance() {
echo " (Shutting down $ORACLE_SID `date` )" |$TLOG
Preshut #Pre-Shutdown function
$SQLDBA<<EOF_SID >$TMP/$SID.shut.out 2>&1
connect internal;
shutdown immediate;
host sleep 2
startup restrict;
host sleep 2
shutdown;
exit;
EOF_SID
Error_chk $TMP/$SID.shut.out
}
Startup_instance() {
echo " (Starting up $ORACLE_SID `date` )" |$TLOG
$SQLDBA<<EOF_STARTUP >$TMP/$SID.start.out 2>&1
connect internal;
startup;
exit;
EOF_STARTUP
Error_chk $TMP/$SID.start.out
Poststart #Post-startup function
}
Ps_check() {
while [ `$PS|grep "$WHO.*dd" | grep -v grep | wc -l \
|sed 's/ *//'` -ge $SIMULT ] ; do
sleep 10 #Sleep while more than $SIMULT processes
done
}
#Setup log and global variables
LOG=$TMP/oraback.log
touch $LOG
chown $ORACLE $LOG
TLOG="tee -a $LOG"
LOGFILE_LIST=logfile.list
CONTROLFILE_LIST=controlfile.list
REDOLOGS_LIST=redologs.list
X=$$
TBLSPACE_LIST=tablespace.list
export LOGFILE_LIST CONTROLFILE_LIST TBLSPACE_LIST REDOLOGS_LIST
ps -ef >/dev/null && PS='ps -ef' || PS='ps -aux'
HOST=`uname -n|cut -d. -f1`
SKIP=`grep "^$HOST.master:" $ORACONF | cut -d: -f2`
COLD=`grep "^$HOST.master:" $ORACONF | cut -d: -f3`
[ "$COLD" -gt 0 ] 2>/dev/null && DAY=`date +%d` || DAY=`date +%a`
[ "$COLD" = '*' ] && COLD=$DAY
if [ "$DAY" = "$COLD" ] ; then
TYPE=COLD ; NUM=4
else
TYPE=HOT ; NUM=5
fi
TIME=`grep "^$HOST.master:" $ORACONF | cut -d: -f$NUM`
TAPE=`grep "^$HOST.master:" $ORACONF | cut -d: -f6`
USERS=`grep "^$HOST.master:" $ORACONF | cut -d: -f7`
[ -z "$USERS" ] && USERS=$ORACLE
SIMULT=`grep "^$HOST.master:" $ORACONF | cut -d: -f8`
BACKUPDIR=`grep "^$HOST.master:" $ORACONF |cut -d: -f9`
if [ -n "$TAPE" -a -n "$SIMULT" ] ; then
echo "Simultaneous backups not supported for" |$TLOG
echo "tape devices. (Will do serially.) " |$TLOG
SIMULT='' ; export SIMULT
fi
COMP=`grep "^$HOST.master:" $ORACONF | cut -d: -f10`
MAIL_DBA=`grep "^$HOST.master:" $ORACONF |cut -d: -f11`
WHO=`id | cut -d'(' -f2 | cut -d')' -f1`
if [ -z "$BACKUPDIR" -o -z "$MAIL_DBA" ] ; then
echo "Field 9 or 12 in $ORACONF is empty!" |$TLOG
echo "(9=BACKUPDIR, 12=complete Mail command)" |$TLOG
exit 1
fi
#Build list of SID's
if [ $# -ne 0 -a "$1" != "at" ] ; then
GREP=`echo $* | sed 's/ /:|^/g' | sed 's/$/:/'`
SID_LIST=`egrep "^$GREP" $ORATAB`
ARG=backup
else
SID_LIST=`grep -v '^\#' $ORATAB`
[ $# -eq 0 ] && ARG=backup || ARG=at
fi
if [ -z "`echo $WHO | egrep \"$USERS\"`" ] ; then
echo "WRONG LOGIN!\n"
Usage
fi
if [ ! -s $ORATAB ]; then
Log_error 1 "NO $ORATAB - ABORTING BACKUP"
exit 1
fi
touch $ORACONF ${ORACONF}.2
Log_error $? "Unable to create $ORACONF or $ORACONF.2 (Needed 4 SKIP feature.)"
#Skip b/u if find "skip" in ORACONF
if [ "$SKIP" = "skip" ]; then
sed "s/$HOST.*:skip:/$HOST.master::/" $ORACONF >${ORACONF}.2
mv -f ${ORACONF}.2 $ORACONF
echo "SKIPPING $0 ON $HOST TONIGHT ONLY DUE" |$TLOG
echo "TO THE WORD \"skip\" IN $ORACONF!!" |$TLOG
exit 0
fi
case ${ARG} in
at) #Check $ORACONF for backup and schedule it
if [ -z "$TIME" ]; then
Log_error 1 "No backup time found in $ORACONF"
exit 1
fi
#Apparently, HP-UX doesn't like the -s flag for 'at'
[ `uname -s` = "HP-UX" ] && _S='' || _S='-s'
at $_S $TIME<<EOF_AT
#Start an at job w/Bourne sh
$BINDIR/oraback.sh > $TMP/oraback.out 2>&1
EOF_AT
;;
backup)
#Actually run backup
echo "in backup"
echo "\n====================================" |$TLOG
echo "Began Oracle backup: `date '+%X %D'`" |$TLOG
echo "1" > $TMP/$X.CT
Preback #Pre-backup function
if [ -n "$TAPE" ]; then #If using tape, label
LABEL="$* $X.`date +%D`"
mt -t $TAPE rew
if [ $? -eq 0 ]; then
sleep 2
echo $LABEL > $TAPE
else
Log_error 1 "$TAPE failed to rewind!"
exit 1
fi
if [ "`dd if=$TAPE count=5 2>/dev/null`" = "$LABEL" ]
then
Log_error 1 "$TAPE NOT the non-rewind device"
exit 1
else
mt -t $TAPE rew
mt -t $TAPE fsf 1
echo "Label the tape in $TAPE: \"$LABEL\"." |$TLOG
fi
fi
#For each SID we are backing up
for LINE in $SID_LIST
do
ORACLE_SID=`echo $LINE | cut -d: -f1`
SID=$ORACLE_SID
CWD=$BACKUPDIR/$SID
ORACLE_HOME=`echo $LINE | cut -d: -f2`
[ -f $ORACLE_HOME/bin/sqldba ] && SQLDBA=sqldba || SQLDBA=svrmgrl
export CWD ORACLE_SID SID ORACLE_HOME SQLDBA
mkdir $CWD 2>/dev/null
chmod 775 $CWD ; chgrp dba $CWD
PATH=$ORIG_PATH:$ORACLE_HOME/bin ; export PATH
DBNUM=`$PS|egrep -c "ora_...._$SID"`
if [ $DBNUM -ge 4 ] ; then #Database is UP
DB_UP=2
$SQLDBA <<EOF_XTBS >$TMP/$X.tbs #Make list of tbls
connect internal;
select tablespace_name, file_name \
from sys.dba_data_files;
exit;
EOF_XTBS
nawk 'NF == 2 && $2 ~ "/" {print $1":"$2}' $TMP/$X.tbs > $CWD/$TBLSPACE_LIST
Error_chk $TMP/$X.tbs
$SQLDBA <<EOF_LOGFILE > $TMP/$X.rdl
connect internal;
select * from v\$logfile ;
exit;
EOF_LOGFILE
nawk 'NF == 2 && $2 ~ "/" {print $2}' $TMP/$X.rdl > $CWD/$REDOLOGS_LIST
Error_chk $TMP/$X.rdl
$SQLDBA <<EOF_ARCHIVE >$TMP/$X.lf #List archiving info
connect internal;
archive log list;
exit;
EOF_ARCHIVE
awk '/^Ar/ {print $3}' $TMP/$X.lf > $CWD/logfile.list
Error_chk $TMP/$X.lf
LOGMODE=`grep 'og mode.* ARCHIVELO' $TMP/$X.lf`
[ -n "$LOGMODE" ] && LOGS_UP=20 || LOGS_UP=10
#
#Yet another section that needs to be coded for v.6
# Original version of the "get control files" (which works for v.6)
#
# $SLQDBA<<EOF_SHOW >$TMP/$X.cf #Get name of controlfile
#connect internal;
#show parameters;
#exit;
#EOF_SHOW
# grep '^control_' $TMP/$X.cf | awk '{print $3}' \
# |sed 's/,//' >$CWD/$CONTROLFILE_LIST
# Error_chk $TMP/$X.cf
$SQLDBA <<EOF >$TMP/$X.cf #Get name of controlfile
connect internal;
select name from v_\$controlfile;
exit;
EOF
grep "^/" $TMP/$X.cf > $CWD/$CONTROLFILE_LIST
Error_chk $TMP/$X.cf
else #else Database is DOWN
DB_UP=1 ; LOGS_UP=10
fi
LOGDEST=`grep -v '^$' $CWD/$LOGFILE_LIST`
ARCH_LOG_FILE=`basename $LOGDEST`
ARCH_LOG_DIR=`echo $LOGDEST | sed 's-/[^/]*$--'`
OLD=`ls -t $ARCH_LOG_DIR/${ARCH_LOG_FILE}* | head -3 | tail -1`
BOLD=`basename $OLD`
NOLOG=`grep "^$HOST:$SID:NOARCHIVELOG" $ORACONF`
NOHOTBACKUP=`grep -i "$HOST:$SID:.*nohotbackup" $ORACONF`
[ -n "$NOHOTBACKUP" ] && HOTBACKUP=100 || HOTBACKUP=200
DB_STAT=`expr $HOTBACKUP + $LOGS_UP + $DB_UP`
[ -f $TMP/$X.Fatal ] && DB_STAT=221
if [ "$TYPE" = COLD ] ; then
#IF instance is not already down, and its cold backup
#time, then shut her down, else set a variable to not
#start it when we are done
if [ $DB_STAT -ne 111 o $DB_STAT -ne 211 ] ; then
Switch_logfile_n_backup_controlfile #Function
Shutdown_instance
DB_UP=1 ; LOGS_UP=10
DB_STAT=`expr $HOTBACKUP + $LOGS_UP + $DB_UP`
[ -f $TMP/$X.Fatal ] && DB_STAT=221
else
DONT_START=DONT
fi
fi
echo " -----------------------------" |$TLOG
echo " Backup of $SID Started `date +%T`" |$TLOG
case $DB_STAT in
222) #DB=up, LOGS=on, Backup gets green light
Backup_instance hot #Function
Switch_logfile_n_backup_controlfile #Function
Copy_online_redologs
Copy_control_and_essential_redolog_files ;;
211|111) #Database is completely down
if [ $DB_STAT = 211 -a "$TYPE" = "HOT" ] ; then
Log_error 1 "!!$SID IS DOWN During a Hot Backup!!"
fi
Backup_offline
Copy_online_redologs
Copy_control_and_essential_redolog_files ;;
212) #Database is up, but is set to NOARCHIVELOG
if [ -n "$NOLOG" ] ; then #Logs should be off
if [ -n "`echo "$NOLOG"|grep ':offline'`" ] ; then
Shutdown_instance #Configured for offline
Backup_offline #backup, so do it
Copy_online_redologs
Copy_control_and_essential_redolog_files
Startup_instance
Switch_logfile_n_backup_controlfile
Error_chk $TMP/$X.control
else #else just skip instance
echo " $SID skipped! (NOARCHIVELOG)" |$TLOG
fi
else #This SID has no logs and is not customized
Log_error 1 "BACKUP FAILED (NOARCHIVELOG)!"
fi ;;
112|122) #Word 'nohotbackup' is in ORACONF
echo " Skipping $SID. Found NOHOTBACKUP" |$TLOG
echo " variable in $ORATAB." |$TLOG ;;
221) #Fatal error in early sqldba command
Log_error 1 "No backup performed"
rm $TMP/$X.Fatal ;;
esac
#If instance was not already down before the /bu started,
#then start it up
if [ "$DONT_START" != DONT ] ; then
[ "$TYPE" = COLD ] && Startup_instance
fi
done #done with all $SIDS
Postback #Run Post-backup script if there is one
echo "Ended Oracle backup: `date '+%X %D'`" |$TLOG
echo "====================================" |$TLOG
#If there were errors, mail them to the dba
[ -s $TMP/$X.Mail ] && $MAIL_DBA < $TMP/$X.Mail
rm -f $TMP/$X.* ;; #Remove temporary logs
esac
#End of oraback.sh
#(Back-slashes used for display only. Real config #lines must be continuous) #Host.master:skip:Sun:2330:1730:/dev/rmt/1cn \ # :oracle|curtis:3:/backup:Y:/usr/ucb/Mail -s Backup_Error # oracle@Host,sa@HisPC #Host:instance:NOARCHIVELOG:offline:nohotbackup: saturn.master::Sat:2330:1830:::5:/back:Y:/usr/ucb/Mail -s Backup_Error oracle@saturn,sa@HisPC saturn:OraSatDbA:NOARCHIVELOG:offline:: saturn:OraSatDbB:::nohotbackup: #'.Master' line says that all db's on saturn will get a #cold b/u on Sat at 2330, a hot b/u all other nights at #1830, doing 5 simultaneous copies, backing up to /back #with compression, and will send mail to id's oracle&sa #OraSatDbA has no redologs-will do cold b/u every night #OraSatDbB will be skipped on hot b/u nights, and will #only be backed up by the cold b/u on Saturday apollo.master::*:2330::/dev/rmt/1cn:root::/dump:Y:/usr/ucb/Mail -s \ Backup_Error root@apollo #This host will do a cold b/u every night, and will #use the tape /dev/rmt/1cn. Only root is allowed to #run oraback.sh. (Simult. backups do not work to tape) # End of File