Hot backup UNIX script

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

    Requires Free Membership to View

    Login 

 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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top