Re: Backup Script

Howard J. Rogers wrote:
>
> I have here a hot backup shell script which a student of one of my backup
> and recovery courses knocked up during the brief interludes when I wasn't
> waffling on about one thing or another.
>
> I can follow the logic, and I *think* it seems OK.
>
> Being a mere humble Microsoft boffin, however, I wouldn't know one end of a
> truss from a top or a nice, so I can't be sure (and I can't currently test
> it on any Solaris box to which I normally have access).
>
> So I throw it out here to see whether there are Unix bods that can rip it to
> shreds and make it better. Or Unix bods who can say, "Yup, it's not bad".
> Comments, please, in any case. My apologies for the length (I'm sure a
> Windows batch file would have been smaller!):
>
> #!/bin/ksh
>
> VERSION=1.0
> # 15-July-2002
>
> #######################################################################
> # Oracle Backup Utility #
> # This script is designed to demonstrate the use of a #
> # unix shell script to facilitate the backup of an Oracle database. #
> # Please make a special note that stand-alone, this script is not all #
> # that is required to back up a database. #
> # It is intended for DEMONSTRATION purposes only. #
> # Use at your own risk!!! #
> #######################################################################
>
> #############################
> # How does this script work #
> #############################
>
> # Firstly the script must be executed in an environment that will work with
> # sql*plus. ie. ORACLE_HOME and ORACLE_SID must be set.
>
> # Secondly, edit the script and set the following variables (under the
> # User Variables heading below) to something like...
> # BACKUPDIR=$HOME/dbbackup
> # SQLUSER=system
> # SQLPASS=manager
>
> # The script begins by logging into the database and checking which
> datafiles
> # exist in the database. The information about each datafile is held in
> memory
> # as array variables.
>
> # Then, one datafile at a time, it performs the following logic....
> # Skip if the datafile is TEMPORARY
> # Skip if the datafile is a ROLLBACK segment
> # Set the datafile's tablespace into hot backup mode.
> # Check it is in hot backup mode (using v$backup).
> # Issue a unix level cp (copy).
> # Set the datafile's tablespace out of hot backup mode.
> # Check it is out of hot backup mode (using v$backup).
> # If requested, switch log files.
>
> # Once each and every datafile has been dealt with, the script then backs up
> # the control file...
> # To trace
> # As a file to the backup directory.
>
> # Finally, if requested, issue a checkpoint to force SCN synchronisation.
>
> ##################
> # User Variables #
> ##################
>
> BACKUPDIR=$HOME/dbbackup # Destination directory for backup
> # Comment this line out to prompt at runtime
>
> # Username and password used to connect to the database #
> # If these are incorrect sqlplus statments will hang #
> SQLUSER=system # Set username
> SQLPASS=manager # Set password
> BUPCONTROLFILE=control.bup # Destination of backup control file
> LOGSWITCH=t # Uncomment to make the default yes
> #CHECKPOINT=t # Uncomment to make the default yes
>
> ####################
> # System Variables #
> ####################
>
> THISSCRIPT=`basename $0` # Hold name of this script
> TMPSQLFILE=/tmp/$THISSCRIPT.$$.sql # Name of temp sql file
> SQLPLUS="sqlplus -S" # sqlplus command
>
> PROMPTING=t # Uncomment this to see messages
> # and prompt for user interaction
> # Comment out to run under cron
>
> ####################
> # Define Functions #
> ####################
>
> CHECKENV() {
>
> # Called to confirm environment variables, directories etc exist
>
> if [ -z "$ORACLE_HOME" ] # No home directory
> then
> echo "\$ORACLE_HOME not defined"
> ABORT
> fi
>
> if [ -z "$ORACLE_SID" ] # No SID
> then
> echo "\$ORACLE_SID not defined"
> ABORT
> fi
>
> if [ -z "$BACKUPDIR" ] # Don't have backup directory distination
> then
> GETBACKUPDIR # Prompt user for details
> fi
>
> if [ ! -d $BACKUPDIR ]
> then
> echo "Creating backup directory $BACKKUPDIR"
> mkdir $BACKUPDIR
> if [ $? -ne 0 ]
> then
> echo "Problem creating backup directory $BACKUPDIR"
> echo
> exit 1
> fi
> chmod 777 $BACKUPDIR
> fi
>
> if [ ! -d $BACKUPDIR ]
> then
> echo "Can't find backup directory $BACKUPDIR"
> fi
>
> if [ ! -w $BACKUPDIR -o ! -x $BACKUPDIR ]
> then
> echo "Can't write to backup directory $BACKUPDIR"
> fi
>
> if [ -n "$PROMPTING" ]
> then
>
> if [ -n "$LOGSWITCH" ]
> then
> LOGSWITCHDEF=y
> else
> LOGSWITCHDEF=n
> fi
>
> GETOK "Switch log files after each datafile \
> backup? [$LOGSWITCHDEF]: " "$LOGSWITCHDEF"
> if [ $? -eq 0 ]
> then
> LOGSWITCH=t
> else
> unset LOGSWITCH
> fi
>
> if [ -n "$CHECKPOINT" ]
> then
> CHECKPOINT=y
> else
> CHECKPOINT=n
> fi
>
> GETOK "Perform checkpoint after backup? [$CHECKPOINT]: " "$CHECKPOINT"
> if [ $? -eq 0 ]
> then
> CHECKPOINT=t
> else
> unset CHECKPOINT
> fi
>
> echo
>
> fi
>
> } # CHECKENV
>
> GETOK() {
>
> # Common routine to prompt the user with argument 1 with a default of
> # argument 2.
> # Return status of 1 is NO
> # Return status of 2 is YES
>
> GETOKPROMPT="$1"
> GETOKDEFAULT=$2
>
> unset GETOKINPUT
> while [ -z "$GETOKINPUT" ]
> do
> printf "$1"
> read GETOKINPUT
>
> [ -z "$GETOKINPUT" -a -n "$GETOKDEFAULT" ] && GETOKINPUT=$GETOKDEFAULT
>
> if [ "$GETOKINPUT" = "y" -o "$GETOKINPUT" = "Y" ]
> then
> return 0
> elif [ "$GETOKINPUT" = "n" -o "$GETOKINPUT" = "N" ]
> then
> return 1
> else
> unset GETOKINPUT
> fi
>
> done
>
> } # GETOK
>
> ABORT() {
>
> echo "Aborting..."
> echo
> exit 1
>
> } # ABORT
>
> GETROLLBACKFILES() {
>
> # Statment used to extract tablespaces in database #
>
> EXTRACTROLLBACKFILESSQL="set head off
> set feedback off
> SELECT FILE_ID
> FROM dba_rollback_segs
> WHERE tablespace_name != 'SYSTEM'
> GROUP BY FILE_ID;
> exit"
>
> echo "$EXTRACTROLLBACKFILESSQL" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "extracting rollback files"
>
> rm -f $TMPSQLFILE # Clean up temp file
>
> LINE=`echo "$DATAHOLD" | head -1` # Get first line of data
> DATAHOLD=`echo "$DATAHOLD" | tail +2` # Remove first line of data
> typeset -Z3 COUNT=1 # Prepare a counter
>
> while [ -n "$DATAHOLD" -o -n "$LINE" ] # We have data to analyse
> do
> set -- $LINE # Break up the line into variables
> if [ -n "$LINE" ]
> then
> COUNT=$1
> eval ROLLBACKFILE$COUNT=true # Hold status of ROLLBACK file
> fi
>
> LINE=`echo "$DATAHOLD" | head -1`
> DATAHOLD=`echo "$DATAHOLD" | tail +2`
> done
>
> unset COUNT
>
> } # GETROLLBACKFILES
>
> GETDATAFILES() {
>
> GETROLLBACKFILES
>
> # Statment used to extract tablespaces in database #
>
> EXTRACTDATAFILESSQL="set head off
> set feedback off
> SELECT d.file_id || ' ' || d.file_name || ' ' || d.tablespace_name
> || ' ' || t.contents || ' ' || v.status || ' ' || t.status
> FROM sys.dba_data_files d, v\$datafile v, dba_tablespaces t
> WHERE d.file_id = v.file#
> AND d.tablespace_name = t.tablespace_name;
> exit"
>
> if [ -n "$PROMPTING" ]
> then
> echo "Extracting list of tablespaces and datafiles to backup"
> fi
>
> echo "$EXTRACTDATAFILESSQL" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "extracting datafiles"
>
> rm -f $TMPSQLFILE # Clean up temp file
>
> LINE=`echo "$DATAHOLD" | head -1` # Get first line of data
> DATAHOLD=`echo "$DATAHOLD" | tail +2` # Remove first line of data
> typeset -Z3 COUNT=1 # Prepare a counter
>
> while [ -n "$DATAHOLD" -o -n "$LINE" ] # We have data to analyse
> do
> set -- $LINE # Break up the line into variables
> if [ -n "$LINE" ]
> then
> eval DATAFILENO$COUNT=$1 # Hold datafile number
> eval DATAFILE$COUNT=$2 # Hold datafile name
> eval TABLESPACE$COUNT=$3 # Hold tablespace name
> eval TABLECONTENTS$COUNT=$4 # Hold tablespace content type
> eval DATAFILESTATUS$COUNT=$5 # Hold datafile status
> # SYSTEM, ONLINE, OFFLINE
> if [ "$6" = "READ" -a "$7" = "ONLY" ] # READ ONLY tablespace
> then
> eval TABLEREADONLY$COUNT=true
> fi
>
> COUNT=`expr $COUNT + 1`
> fi
>
> LINE=`echo "$DATAHOLD" | head -1`
> DATAHOLD=`echo "$DATAHOLD" | tail +2`
> done
>
> COUNT=`expr $COUNT - 1`
> DATAFILECOUNT=$COUNT
> unset COUNT
>
> if [ -n "$PROMPTING" ]
> then
> echo "Found $DATAFILECOUNT datafiles"
> echo
> fi
>
> }
>
> BADSQLPLUS() {
>
> echo "Problem executing $SQLPLUS"
> if [ -n "$1" ]
> then
> echo "Whilst $1"
> fi
>
> ABORT
>
> }
>
> BACKUPCONTROLFILE() {
>
> BUPCONTROLFILE=$BACKUPDIR/$BUPCONTROLFILE
>
> # Statment used to backup control file to trace #
> BUPCONTROLFILETRACESQL='ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
> exit'
>
> echo "$BUPCONTROLFILETRACESQL" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> if [ -n "$PROMPTING" ]
> then
> echo "Backing up control file to trace"
> fi
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "backing up controle file to trace"
>
> rm -f $TMPSQLFILE # Clean up temp file
>
> BUPCONTROLFILETRACESQL="ALTER DATABASE BACKUP CONTROLFILE TO
> '$BUPCONTROLFILE' REUSE;
> exit"
>
> echo "$BUPCONTROLFILETRACESQL" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> if [ -n "$PROMPTING" ]
> then
> echo "Backing up control file to $BUPCONTROLFILE"
> echo
> fi
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "backing up control file to $BUPCONTROLFILE"
>
> echo "$DATAHOLD" | grep -q "Database altered."
> if [ $? -ne 0 ]
> then
> echo "Problem backing control file to $BUPCONTROLFILE"
> echo
> fi
>
> rm -f $TMPSQLFILE # Clean up temp file
>
> }
>
> DISPLAYHEADER() {
>
> [ -z "$PROMPTING" ] && return 0
>
> clear
> echo
> echo "Oracle backup utility"
> echo "Version $VERSION"
> echo
>
> }
>
> CHECKTMPSQLFILE() {
> if [ ! -r $TMPSQLFILE ]
> then
> echo "Problem creating temp sql file $TMPSQLFILE"
> ABORT
> fi
>
> }
>
> TURNONBACKUPMODE() {
>
> if [ -n "$PROMPTING" ]
> then
> echo "Setting tablespace $TABLESPACE into hot backup mode"
> fi
>
> echo "ALTER TABLESPACE $TABLESPACE BEGIN BACKUP;\nexit" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "setting tablespace $TABLESPACE into hot backup"
>
> echo "set head off\nselect status from v\$backup where \
> file# = '$DATAFILENO';\nexit" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "checking tablespace $TABLESPACE is in hot
> backup"
>
> DATAHOLD=`echo "$DATAHOLD" | tail -1`
> if [ "$DATAHOLD" != "ACTIVE" ]
> then
> echo "Problem setting tablespace $TABLESPACE into hot backup mode"
> ABORT
> fi
>
> } # TURNONBACKUPMODE
>
> TURNOFFBACKUPMODE() {
>
> if [ -n "$PROMPTING" ]
> then
> echo "Setting tablespace $TABLESPACE off hot backup mode"
> fi
>
> echo "alter tablespace $TABLESPACE end backup;\nexit" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "setting tablespace $TABLESPACE off hot backup"
>
> echo "set head off\nselect status from v\$backup where file# =
> '$DATAFILENO';\nexit" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "checking tablespace $TABLESPACE is out of hot
> backup"
>
> DATAHOLD=`echo "$DATAHOLD" | tail -1`
> if [ "$DATAHOLD" != "NOT ACTIVE" ]
> then
> echo "Problem setting tablespace $TABLESPACE into hot backup mode"
> ABORT
> fi
>
> } # TURNOFFBACKUPMODE
>
> BACKUPDATAFILES() {
>
> typeset -Z3 COUNT=1
>
> while [ $COUNT -le $DATAFILECOUNT ]
> do
>
> eval DATAFILENO=\$DATAFILENO$COUNT
> eval DATAFILE=\$DATAFILE$COUNT
> eval TABLESPACE=\$TABLESPACE$COUNT
> eval TABLESTATUS=\$TABLESTATUS$COUNT
> eval TABLEREADONLY=\$TABLEREADONLY$COUNT
> eval TABLECONTENTS=\$TABLECONTENTS$COUNT
> eval ROLLBACKFILE=\$ROLLBACKFILE$COUNT
>
> if [ "$TABLECONTENTS" = "TEMPORARY" ]
> then
> if [ -n "$PROMPTING" ]
> then
> echo "Datafile $DATAFILE is TEMPORARY - skipping backup"
> echo
> fi
> elif [ -n "$ROLLBACKFILE" ]
> then
> if [ -n "$PROMPTING" ]
> then
> echo "Datafile $DATAFILE is ROLLBACK - skipping backup"
> echo
> fi
> else
> BACKUPDATAFILE
> if [ -n "$LOGSWITCH" -a $? -eq 0 ]
> then
> SWITCHLOGFILE
> fi
> fi
>
> COUNT=`expr $COUNT + 1`
> done
>
> }
>
> ISSUECHECKPOINT() {
>
> # Called to issue a checkpoint
>
> # Statment used to switch log file #
> CHECKPOINTSQL="set head off
> ALTER SYSTEM CHECKPOINT;
> exit"
>
> echo "$CHECKPOINTSQL" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> if [ -n "$PROMPTING" ]
> then
> echo "Issuing Checkpoint"
> echo
> fi
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "issuing checkpoint"
>
> rm -f $TMPSQLFILE # Clean up temp file
>
> echo "$DATAHOLD" | grep -q "System altered."
> if [ $? -ne 0 ]
> then
> echo "Problem issuing checkpoint"
> ABORT
> fi
>
> } # ISSUECHECKPOINT
>
> SWITCHLOGFILE() {
>
> # Called to perform a log file switch
>
> # Statment used to switch log file #
> SWITCHLOGFILESQL="set head off
> ALTER SYSTEM SWITCH LOGFILE;
> exit"
>
> echo "$SWITCHLOGFILESQL" > $TMPSQLFILE
> CHECKTMPSQLFILE
>
> if [ -n "$PROMPTING" ]
> then
> echo "Switching log files"
> echo
> fi
>
> DATAHOLD=`$SQLPLUS $SQLUSER/$SQLPASS @$TMPSQLFILE`
> [ $? -ne 0 ] && BADSQLPLUS "switching log files"
>
> rm -f $TMPSQLFILE # Clean up temp file
>
> echo "$DATAHOLD" | grep -q "System altered."
> if [ $? -ne 0 ]
> then
> echo "$DATAHOLD"
> echo "Problem switching log files"
> ABORT
> fi
>
> } # SWITCHLOGFILE
>
> BACKUPDATAFILE() {
>
> DESTFILE=`basename $DATAFILE`
>
> if [ -f $BACKUPDIR/$DESTFILE ]
> then
> if [ -n "$PROMPTING" ]
> then
> echo "$BACKUPDIR/$DESTFILE already exists"
> GETOK "OK to overwrite? [y]: " "y"
> if [ $? -eq 0 ] # User said YES
> then
> rm -f $BACKUPDIR/$DESTFILE # Delete old backed up file
> if [ $? -ne 0 ]
> then
> echo "Problem deleting file"
> ABORT
> fi
> echo "Old file deleted"
> else
> echo "Old file not deleted - skipping backup"
> echo
> return 1
> fi
> else # No user interaction
> rm -f $BACKUPDIR/$DESTFILE # Just delete the file without asking
> fi
> fi
>
> if [ -z "$TABLEREADONLY" ]
> then
> TURNONBACKUPMODE
> else
> if [ -n "$PROMPTING" ]
> then
> echo "Datafile $DATAFILE from $TABLESPACE is READ ONLY -"
> echo "Not setting backup mode on"
> fi
> fi
>
> [ -n "$PROMPTING" ] && echo "Backing up datafile $DATAFILE to $BACKUPDIR"
> #%%%
> cp -p $DATAFILE $BACKUPDIR
> true
> if [ $? -ne 0 ]
> then
> echo "Problem backing up file"
> ABORT
> fi
>
> if [ -z "$TABLEREADONLY" ]
> then
> TURNOFFBACKUPMODE
> fi
>
> [ -n "$PROMPTING" ] && echo
> rm -f $TMPSQLFILE
>
> return 0
>
> } # BACKUPDATAFILE
>
> GETBACKUPDIR() {
>
> if [ -z "$PROMPTING" ]
> then
> echo "Backup directory not specified"
> ABORT
> fi
>
> while [ -z "$BACKUPDIR" ]
> do
>
> printf "Enter backup directory: "
> read BACKUPDIR
>
> if [ -z "$BACKUPDIR" ]
> then
> ABORT
> fi
>
> printf "Backup directory $BACKUPDIR correct? [y]: "
> read INPUT
> [ -z "$INPUT" ] && INPUT=y
> if [ "$INPUT" != "y" ]
> then
> unset BACKUPDIR
> fi
> done
>
> }
>
> ##########################
> # Real Stuff Starts Here #
> ##########################
>
> DISPLAYHEADER
> CHECKENV
> GETDATAFILES
> BACKUPDATAFILES
> BACKUPCONTROLFILE
> [ -n "$CHECKPOINT" ] && ISSUECHECKPOINT
>
> if [ -n "$PROMPTING" ]
> then
> echo "Database backup completed successfully"
> echo
> fi
>
> exit 0
>
> Regards,
> HJR
I don't like the use of userid's and passwords in there...Also, a lot of the creation of temporary sql files is not needed. For example (taken from a backup script on one of our databases), little subroutines using 'print' can be used to both make passwords not needed and avoid

AlterTablespace()
{
  print "
    connect / as sysdba
    alter tablespace $1 $2 backup;
    exit" | sqlplus /nolog
}

which can be called with:

AlterTablespace SYSTEM begin
(copy files)
AlterTablespace SYSTEM end

The crux of the backup routine can then look something rather intuitive like:

for tspace in `awk '{print $1}' $FILELIST | uniq` do
  AlterTablespace $tspace BEGIN
  for fname in `grep "$tspace " $FILELIST | awk '{print $2}'`   do
    CopyFile $fname
  done
  AlterTablespace $tspace END
done

where $FILELIST is a file containing an ordered list of "Tablespace Filename" from dba_data_files

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."