Tip

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

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.