Listing 1: oraback.sh

#!/bin/sh
#
# @(#)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
#
# Acknowledgements to: Samuel_Binder@coretech-group.com
# (For assistance with sql commands and early coding.)
#
#######################################################
##Site-specific section (change as appopriate)
BINDIR=/usr/local/bin              #Where $0 is located
ORATAB=/var/opt/oracle/oratab      #Oracle's configfile
ORADIR=`grep -v '^#' $ORATAB|cut -d: -f2|tail -1`
ORACONF=/usr/local/bin/oraback.conf   ;    TMP=/var/tmp
PATH=$PATH:/usr/bin:/usr/ccs/bin      ; ORIG_PATH=$PATH
Preshut() {        #Run prior to shutting down instance
echo
}
Poststart() {           #Run after starting up instance
echo
}
Preback() {            #Run before entire backup begins
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/$TSLST | 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 >$TMP/$X.$TBS.tablebu 2>&1     #b/up mode
connect internal;
alter tablespace $TBS begin backup;
exit;
EOF
Error_chk $TMP/$X.$TBS.tablebu
fi

grep "^$TBS:" $CWD/$TSLST | 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 > $TMP/$X.$TBS.tablebu 2>&1
connect internal;
alter tablespace $TBS end backup;
exit;
EOF
Error_chk $TMP/$X.$TBS.tablebu
fi
rm $TMP/$X.$SID.${TBS}all.allfiles
}
Copy_one_database_file() {
BN=`basename $FILE`
touch $TMP/$X.$SID.$TBS.$BN
[ -n "$TAPE" ]  &&  DEVICE=$TAPE  ||  DEVICE=$CWD/$BN

NEW=`find $FILE -newer $CWD/$BN.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 2>/dev/null|compress >$DEVICE.Z
else
dd if=$FILE 2>/dev/null >$DEVICE
fi

Log_error $? "Error Copying $FILE to $DEVICE"

else
echo "   NOT Backing up $FILE. (NO CHANGE)" |$TLOG
fi
rm $TMP/$X.$SID.$TBS.$BN
}
Copy_control_and_redolog_files() {
if [ -f $CWD/$CFLST ] ; then

echo "  Manually copying controlfile." |$TLOG
CNT=`grep -v '^$' $CWD/$CFLST` ;BNAME=`basename $CNT`
compress -c $CNT > $CWD/$BNAME.Z
Log_error $? "Error copying $FILE to $CWD"

else
Log_error 1 "$CWD/$CFLST: File not found"
fi

if [ -f $CWD/$CFLST $CWD/$LFLST ] ; then
if [ -z "$NOLOG" ] ; then
[ "$DB_UP" = 2 ] && (ls $CWD/$LGF* 2>/dev/null \
| grep -v $BOLD | xargs rm -f  2>/dev/null)

if [ -d "$ARCH_DIR" -a -f "$OLD" ] ; then
echo "  Copying essential redo logs." |$TLOG
find $ARCH_DIR -type f -name "*$LGF*" \
-newer $OLD -exec cp -p {} $CWD \;
compress -f $CWD/$LGF*
else
Log_error 1 "Invalid Archive Log Dir or Filename."
fi

else
echo "  No logs:  $SID set to NOARCHIVELOG." |$TLOG
fi
else
Log_error 1 "$CWD/$LFLST: 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
}
Shutdown_instance() {
echo "  (Shutting down $ORACLE_SID `date` )" |$TLOG
Preshut                         #Pre-Shutdown function
sqldba<<EOF >$TMP/$SID.shut.out 2>&1
connect internal;
shutdown immediate;
host sleep 10
startup exclusive;
host sleep 10
shutdown;
exit;
EOF
Error_chk $TMP/$SID.shut.out
}
Startup_instance() {
echo "  (Starting up $ORACLE_SID `date` )" |$TLOG
sqldba<<EOF >$TMP/$SID.start.out 2>&1
connect internal;
startup;
exit;
EOF
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"
LFLST=logfile.list  ;  CFLST=controlfile.list  ;   X=$$
TSLST=tablespace.list    ;     export LFLST CFLST TSLST
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
#Skip b/u if find "skip" in ORACONF
if [ "$SKIP" = "skip" ] ; then
cat $ORACONF \
|sed "s/$HOST.*:skip:/$HOST.master::/">${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

at -s $TIME<<EOF          #Start an at job w/Bourne sh
$BINDIR/oraback.sh > $TMP/oraback.out 2>&1
EOF
;;

backup)                            #Actually run 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`
export CWD ORACLE_SID SID ORACLE_HOME
mkdir $CWD 2>/dev/null
chmod 775 $CWD  ;  chgrp dba $CWD
PATH=$ORIG_PATH:$ORACLE_HOME/bin  ;  export PATH

DBNUM=`$PS|grep "ora_...._$SID"|wc -l|sed 's/  *//'`
if [ $DBNUM -ge 4 ] ; then            #Database is UP
DB_UP=2

sqldba <<EOF >$TMP/$X.tbs    #Make list of tbls
connect internal;
select tablespace_name, file_name \
from sys.dba_data_files;
exit;
EOF
grep '       ' $TMP/$X.tbs | grep -v 'ACE_N.*_NAM' \
|grep -v '^  *$'|awk '{print $1":"$2}'> $CWD/$TSLST
Error_chk $TMP/$X.tbs

sqldba <<EOF >$TMP/$X.lf   #List archiving info
connect internal;
archive log list;
exit;
EOF
grep '^Ar' $TMP/$X.lf | awk '{print $3}'>$CWD/$LFLST
Error_chk $TMP/$X.lf

LOGMODE=`grep 'og mode.* ARCHIVELO' $TMP/$X.lf`
[ -n "$LOGMODE" ]  &&  LOGS_UP=20  ||  LOGS_UP=10

sqldba<<EOF >$TMP/$X.cf  #Get name of controlfile
connect internal;
show parameter control_files;
exit;
EOF
grep '^control' $TMP/$X.cf | awk '{print $3}' \
|sed 's/,//' >$CWD/$CFLST
Error_chk $TMP/$X.cf
else                           #else Database is DOWN
DB_UP=1  ;  LOGS_UP=10
fi

if [ "$TYPE" = COLD ] ; then
Shutdown_instance
DB_UP=1  ;  LOGS_UP=10
fi

DEST=`grep -v '^$' $CWD/$LFLST | sed 's/%.*//'`
LGF=`basename $DEST`
ARCH_DIR=`echo $DEST | sed 's-/[^/]*$--'`
OLD=`ls -t $ARCH_DIR/$LGF* | head -3 | tail -1`
BOLD=`basename $OLD`

NOLOG=`grep "^$HOST:$SID:NOARCHIVELOG" $ORACONF`
NOHBACK=`grep -i "$HOST:$SID:.*nohotbackup" $ORACONF`
[ -n "$NOHBACK" ]  &&  HBACK=100  ||  HBACK=200

DB_STAT=`expr $HBACK + $LOGS_UP + $DB_UP`
[ -f $TMP/$X.Fatal ]  &&  DB_STAT=221

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

echo "  Switch logfile and backup controlfile"|$TLOG

sqldba << EOF >$TMP/$X.control 2>&1
connect internal;
alter system checkpoint;
alter system switch logfile;
host sleep 10
alter database backup controlfile to '$CWD/cntrlfile';
exit;
EOF
Error_chk $TMP/$X.control

compress -f $CWD/cntrlfile >/dev/null 2>&1
Log_error $? "Error compressing $CWD/cntrlfile"

Copy_control_and_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_control_and_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_control_and_redolog_files
Startup_instance
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

[ "$TYPE" = COLD ]  &&  Startup_instance

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