Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Backup Script

Backup Script

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Tue, 16 Jul 2002 12:52:00 +1000
Message-ID: <ah01n8$ala$1@lust.ihug.co.nz> 





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

Received on Mon Jul 15 2002 - 21:52:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US