Hot backup UNIX script

A massive script that performs a hot backup of an Oracle database with no interaction.

This script performs a hot backup of an Oracle database with no interaction. The user of the script must have write...

privileges to the backup drives or devices. The script writes to a disk but can be modified to write to a tape device. Here goes:

. $HOME/.profile 
# 
#Fill out the below with your own names and paths 
# 
ORACLE_SID=
 
   
export ORACLE_SID 
DBNAME="
  
   " 
JOBNAME="
   
    " 
JOBNAME_SHORT="
    
     " 
DBBACKUP="$HOME/Scripts/backup/datafile_`date '+%y%m%d'.log" 
CTLFILES="$HOME/Scripts/backup/control_`date '+%y%m%d'.log" 
ARCHLOGS="$HOME/Scripts/backup/archlog_`date '+%y%m%d'.log" 
ALERTLOGS="$HOME/Scripts/backup/alertlog_`date '+%y%m%d'.log" 
SPECIALOG="$HOME/Scripts/backup/special_`date '+%y%m%d'.log" 
BACKUPDIR="/usr14/backup_
     
      " 
BACKUPDIROLD="/usr13/oldbackup_
      
       " 
ARCDIR="/usr14/arch_
       
        " 
ARCOLD="/usr13/oldarch_
        
         " 

CKSUM="/bin/cksum" 
CMP="/bin/cmp" 

CKSUM_SIZE_ERR="${JOBNAME_SHORT}: fatal error in cksum size comparison." 
CKSUM_VALUE_ERR="${JOBNAME_SHORT}: fatal error in cksum value comparison." 
CKSUM_VALUE_WAR="${JOBNAME_SHORT}: warning in cksum value comparison." 
CMP_ERR="${JOBNAME_SHORT}: fatal error in cmp." 
ARCERR="${JOBNAME_SHORT}: fatal error in archive log copy." 
DIFFERR="${JOBNAME_SHORT}: Archive log deletion skipped." 
THISNODE=`uname -n` 

# 
#connect to get a listing of files to backup 
# 
svrmgrl >/dev/null<
         
           as sysdba 

set termout off 
set linesize 132 
spool $DBBACKUP; 
select file_name, tablespace_name from sys.dba_data_files 
order by tablespace_name, file_name; 
spool off; 



spool $CTLFILES; 
select name from v$controlfile; 
spool off; 

spool $ARCHLOGS; 
select value from v$parameter where name = 'log_archive_dest'; 
spool off 

spool $ALERTLOGS; 
select value from v$parameter where name='background_dump_dest'; 
spool off; 
exit; 
EOF 

# 
#begin backup 
# 
echo "${JOBNAME_SHORT}: starting backup using $DBBACKUP..." 

# 
#move the most current backup to the old directory and compress the file 
# 
if [ `ls ${BACKUPDIR} | wc -l` != 0 ] 
then 
   for FILE in ${BACKUPDIR}/* 
   do     
     mv $FILE $BACKUPDIROLD 
   done 
fi 

# 
#delete the old archive logs from the old ARCH directory 
# 
if [ `ls ${ARCOLD} | wc -l` != 0 ] 
then 
   for FILE in ${ARCOLD}/* 
   do     
     rm $FILE 
   done 
fi 

# 
#begin reading dynamic parameter file 
# 
SED="sed -e '/selected.$/d' -e '/^---.*--$/d' -e '/^FILE_NAME/d' $DBBACKUP" 

eval $SED | while read FILE TABLESPACE 
do 

svrmgrl <
          
            as sysdba 
alter tablespace $TABLESPACE begin backup; 
exit 
EOF 

  # 
  #copy a database file 
  # 
  echo "${JOBNAME_SHORT}: cp $FILE $BACKUPDIR" 
  DATAFILE=`basename $FILE` 

  cp $FILE $BACKUPDIR 
  STATUS=$? 
  if [ "$STATUS" != 0 ] 
  then 
     echo "${JOBNAME_SHORT}: error during file copy $FILE." 
  fi 

  echo "${JOBNAME_SHORT}: $CKSUM $FILE $BACKUPDIR/$DATAFILE" 
  $CKSUM $FILE $BACKUPDIR/$DATAFILE 
  CKSUM_OUT=`$CKSUM $FILE $BACKUPDIR/$DATAFILE` 
  echo $CKSUM_OUT | read VALUE1 SIZE1 NAME1 VALUE2 SIZE2 NAME2 
  if [ "$VALUE1" != "$VALUE2" ] 
  then 
    echo "$CKSUM_VALUE_WAR" 
  fi 
  if [ "$SIZE1" != "$SIZE2" ] 
  then 
    echo "$CKSUM_SIZE_ERR" 
  fi 

# 
#use system password 
# 
svrmgrl <
           
             as sysdba 
alter tablespace $TABLESPACE end backup; 
exit 
EOF 

done 

# 
#use system password 
# 
svrmgrl <
            
              as sysdba 
select * from v$backup; 
exit 
EOF 

# 
#backup control files 
# 
echo "${JOBNAME_SHORT}: backup controlfile to 
   ${BACKUPDIR}/${DBNAME}_control01.ctl." 

svrmgrl <
             
               as sysdba 
alter database backup controlfile to '${BACKUPDIR}/${DBNAME}_control01.bkp'; 
exit 
EOF 

# 
#backup archive logs 
# 
#make sure to backup the archive logs only and not the online redo logs 
# 
#force a logswitch 
echo "${JOBNAME_SHORT}: switch logfile." 
svrmgrl <
              
                as sysdba 
alter system switch logfile; 
exit 
EOF 

# 
#sleep for a while to make sure the logfile is copied 
# 
sleep 60 

# 
#copy archive logs 
# 
ARC=`sed -e 's/[  ]*$//' -e '/selected.$/d' -e '/^---.*--$/d' -e'/^VALUE/d' $ARCHLOGS` 

if [ -f $ARCDIR/* ] 
then 
  echo " " 
#  echo "${JOBNAME_SHORT}: Delete previous backup archive logs..." 
  echo "${JOBNAME_SHORT}: Move previous backup archive logs..." 
  for I in $ARCDIR/* 
  do 
    ls -l $I 
    ARCNAME=`basename $I` 
# 
#ADDED 10/11/2000 
#move existing copied Archive to archive old directory 
# 
    mv $ARCDIR/$ARCNAME $ARCOLD 
    STATUS="$?" 
    if [ "$STATUS" != 0 ] 
    then 
      echo "${JOBNAME_SHORT}: error moving to old archive log: $ARCDIR/$ARCNAME" 
    fi 
#    rm $ARCDIR/$ARCNAME 
#    STATUS="$?" 
#    if [ "$STATUS" != 0 ] 
#    then 
#      echo "${JOBNAME_SHORT}: error deleting old archive log: $ARCDIR/$ARCNAME" 
#    fi 
  done 
else 
  echo " " 
  echo "${JOBNAME_SHORT}: No old archive logs to delete."  
fi 

if [ -f $ARC/* ] 
then 
  echo " " 
  echo "${JOBNAME_SHORT}: Copying archive logs..." 
  for I in $ARC/* 
  do 
    ls -l $I 
    ARCNAME=`basename $I` 
    echo "${JOBNAME_SHORT}: cp $ARC/$ARCNAME $ARCDIR" 
    cp $ARC/$ARCNAME $ARCDIR 
    STATUS="$?" 
    if [ "$STATUS" != 0 ] 
    then 
      echo "$CMP_ERR" 
      echo "${JOBNAME_SHORT}: Archive log deletion skipped." 
    else 
      echo "${JOBNAME_SHORT}: $CKSUM $ARC/$ARCNAME $ARCDIR/$ARCNAME" 
      $CKSUM $ARC/$ARCNAME $ARCDIR/$ARCNAME 
      CKSUM_OUT=`$CKSUM $ARC/$ARCNAME $ARCDIR/$ARCNAME` 
      echo $CKSUM_OUT | read VALUE1 SIZE1 NAME1 VALUE2 SIZE2 NAME2 
      if [ "$VALUE1" != "$VALUE2" -o "$SIZE1" != "$SIZE2" ] 
      then 
        echo "$DIFFERR" 
        echo "${JOBNAME_SHORT}: Archive log deletion skipped." 
      else 
        rm $ARC/$ARCNAME 
        if [ $? != 0 ] 
        then 
          echo "${JOBNAME_SHORT}: Archive log deletion failed." 
        fi 
      fi 
    fi 
  done 
else 
  echo "${JOBNAME_SHORT}: Found no archives to copy." 
fi 

echo "${JOBNAME_SHORT}: Backup complete." 

              
             
            
           
          
         
        
       
      
     
    
   
  
 

Reader Feedback

Guillermo R. writes: This is a excellent script. However, when I was trying this script, I got a problems with spools -- it doesn't work. Example:

DBBACKUP="$HOME/Scripts/backup/datafile_`date '+%y%m%d'.log" 
SVRMGRL> SPOOL $DBBACKUP

I received an error message like this: "Cannot create spool file"

Ashish R. writes: Good tip -- superb!

For More Information

  • What do you think about this tip? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in November 2001

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close