Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close