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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Hot Backup scripts UNIX

Re: Hot Backup scripts UNIX

From: Suzy Vordos <lvordos_at_qwest.com>
Date: Fri, 30 Aug 2002 08:13:30 -0800
Message-ID: <F001.004C420D.20020830081330@fatcity.com> 






Here's a ksh script that dumps backup files to a remote server.  It also
accounts for multiple datafiles per tablespace, which I didn't see coded
in the Velpuri scripts.



Suzy





#!/bin/ksh

#





# Program: hot_backup.sh    

# Created By: lvordos

# Syntax: hot_backup.sh ORACLE_SID BACKUP_SRVR

#

# Executes hot backup for ${ORACLE_SID} and sends all backup data 

# to ${BACKUP_SRVR}.

#

# This script connects as BACKUP_ADM and requires the following system 

# privileges: create session, select_catalog_role, alter database, 

# alter tablespace, alter system

#

# Events of this script are logged to: 

# ${HOME}/logs/hot_backup_${ORACLE_SID}.log

#

#






####################

# LOAD ENVIRONMENT #


####################




#-- set base env --#



. ${HOME}/bin/setenv.sh



#-- set oracle env --#



ORACLE_SID="${1}" ; export ORACLE_SID


ORACLE_HOME="`cat ${ORATAB} |grep ${ORACLE_SID} | awk -F: '/^[^#]/' | \


   cut -d ":" -f2`" ; export ORACLE_HOME
unset SQLPATH 



#-- set custom env --#


NOTIFY_SUBJ="FAILURE: ${HOSTNAME}:${ORACLE_SID} ${0##*/}" ; export
NOTIFY_SUBJ
BACKUP_SRVR="${2}" ; export BACKUP_SRVR
RMTBAK_DIR1="/backup01/orcldb/${ORACLE_SID}" ; export RMTBAK_DIR1
LOCBAK_DIR1="/dbbak01/bakdata/${ORACLE_SID}" ; export LOCBAK_DIR1
ORACFG_DIR="${ORACLE_ADMIN}/${ORACLE_SID}/pfile" ; export ORACFG_DIR


SSH="/usr/local/bin/ssh" ; export SSH


SCP="/usr/local/bin/scp" ; export SCP




#####################

# PROGRAM FUNCTIONS #


#####################




#----------------------------------------# 

# generate output listing of tablespaces #

# and datafiles for backup               #

#----------------------------------------# 

do_tablespace_lst() {


function="do_tablespace_lst"; print "\nSTART ${function} at `date
+%H:%M:%S`\n"



${ORACLE_HOME}/bin/sqlplus -s /nolog <<-[]
 connect ${BACKUP_ADM}@${ORACLE_SID}



 whenever sqlerror exit sql.sqlcode;


 set feedback off echo on pagesize 0 trimspool on 



 host echo "\n++ Executing checkpoint "


 alter system checkpoint ;



 host echo "\n++ Archiving current logs " 
 alter system archive log current ;



 host echo "++ Generating tablespace and datafile list for backup" 
 spool ${LOGDIR}/${ORACLE_SID}_datafiles.txt



 select tablespace_name ||':'|| file_name 
 from dba_data_files order by tablespace_name, file_name ;



 spool off


 host echo "\n++ Backup tablespace list complete"
[]



if [[ $? != 0 ]] ; then


    print "\nFATAL: ${function} Failure generating tablespace list\n"
    NOTIFY_MSG=`cat ${LOGFILE}`


    enotify


    exit -1


else


    if [[ ! -s ${LOGDIR}/${ORACLE_SID}_datafiles.txt ]] ; then

        print "\nFATAL: ${function} Tablespace list does not exist \n"
        NOTIFY_MSG=`cat ${LOGFILE}`
        enotify
        exit -1




    fi


fi



print "END ${function} at `date +%H:%M:%S` Status $? \n"
}




#----------------------------------------# 

# roll previous backups on backup server #

#----------------------------------------# 

do_roll_backups() {


function="do_roll_backups"; print "\nSTART ${function} at `date
+%H:%M:%S`"



# remove oldest backup



if ${SSH} ${BACKUP_SRVR} rm -r ${RMTBAK_DIR1}/03 ; then


   print "\n++ Rolling previous backups on ${BACKUP_SRVR}\n"
   # rename previous & current previous backups

   ${SSH} ${BACKUP_SRVR} mv ${RMTBAK_DIR1}/02 ${RMTBAK_DIR1}/03
   ${SSH} ${BACKUP_SRVR} mv ${RMTBAK_DIR1}/01 ${RMTBAK_DIR1}/02
   ${SSH} ${BACKUP_SRVR} cp -pr ${RMTBAK_DIR1}/00 ${RMTBAK_DIR1}/01 


else


   print "\nFATAL: ${function} Could not roll backups on
${BACKUP_SRVR}\n "


   NOTIFY_MSG="`cat ${LOGFILE}`"


   enotify 


   exit -1


fi




if ${SSH} ${BACKUP_SRVR} ls -d ${RMTBAK_DIR1}/01 ; then


   print "++ Backup directory exists on ${BACKUP_SRVR}\n"
else


   print "\nFATAL: ${function} Backup directory ${RMTBAK_DIR1}/01 not
found on


${BACKUP_SRVR}\n "


   NOTIFY_MSG="`cat ${LOGFILE}`"


   enotify 


   exit -1


fi



print "END ${function} at `date +%H:%M:%S` Status $? \n"
}




#------------------------------------------------# 

# put tablespaces in backup mode - if that fails #

# for any tablespace call do_end and exit script #

#------------------------------------------------# 

do_begin_backup() {


function="do_begin_backup"; print "\nSTART ${function} at `date
+%H:%M:%S`\n"



 ${ORACLE_HOME}/bin/sqlplus -s /nolog <<-[]
 connect ${BACKUP_ADM}@${ORACLE_SID}



 whenever sqlerror exit sql.sqlcode;


 set feedback off echo on 



 variable tscnt number ;



 host echo "\n++ Altering tablespace ${T} to backup mode"
 alter tablespace ${T} begin backup ;



 set termout off


 execute select count(1) into :tscnt from v\$backup b, dba_data_files d
where d.file_id =


b.file# and d.tablespace_name = '${T}' and b.status != 'ACTIVE';



 execute if :tscnt > 0 then raise invalid_number ; end if ;



 host echo "++ Tablespace ${T} in backup mode"
[]



if [[ $? != 0 ]] ; then


    print "\nFATAL: ${function} Tablespace ${T} not in backup mode\n"
    NOTIFY_MSG="`cat ${LOGFILE}`"


    enotify


    exit -1


fi



# backup all datafiles for tablespace



F="`grep "${T}" ${LOGDIR}/${ORACLE_SID}_datafiles.txt | cut -f2 -d":"`"



for D in ${F}


   do

      RDIR="`print ${D%/*} | cut -f2 -d"/"`"
      DFILE="${D##*/}"

      print "++ Backing up datafile ${DFILE} \n"

      if ${SCP} -p ${D}
${BACKUP_SRVR}:${RMTBAK_DIR1}/01/${RDIR}/${DFILE} ; then
         print "++ Backup complete for datafile ${DFILE}\n" 
      else
         print "\nWARNING: ${function} Backup failed for datafile
${D}\n"
         NOTIFY_MSG="`cat ${LOGFILE}`"
         enotify 
      fi




   done



print "END ${function} at `date +%H:%M:%S` Status $? \n"
}




#--------------------------------# 

#                                # 

#--------------------------------# 



do_end_backup() {


function="do_end_backup"; print "\nSTART ${function} at `date
+%H:%M:%S`\n"



 ${ORACLE_HOME}/bin/sqlplus -s /nolog <<-[]
 connect ${BACKUP_ADM}@${ORACLE_SID}



 whenever sqlerror exit sql.sqlcode;


 set feedback off echo on 



 variable tscnt number ;



 host echo "\n++ Altering tablespace ${T} from backup mode"
 alter tablespace ${T} end backup ;



 set termout off


 execute select count(1) into :tscnt from v\$backup b, dba_data_files d
where d.file_id =


b.file# and d.tablespace_name = '${T}' and b.status = 'ACTIVE';



 execute if :tscnt > 0 then raise invalid_number ; end if ;



 host echo "++ Tablespace ${T} returned to normal state"
[]



if [[ $? != 0 ]] ; then


    print "\nWARNING: ${function} Tablespace ${T} left in backup mode
\n"


    NOTIFY_MSG="`cat ${LOGFILE}`"


    enotify  


    NOTIFY_MSG="WARNING: ${function} Tablespace ${T} left in backup
mode"


    pnotify


fi



print "END ${function} at `date +%H:%M:%S` Status $? \n"
}



#---------------------------------------------#

# Create backup controlfile in binary & trace #

# format then ${SCP} to backup server            # 

#---------------------------------------------#

do_controlfile() {


function="do_controlfile"; print "\nSTART ${function} at `date
+%H:%M:%S`\n"



${ORACLE_HOME}/bin/sqlplus -s /nolog <<-[]
 connect ${BACKUP_ADM}@${ORACLE_SID}



 whenever sqlerror exit sql.sqlcode;


 set feedback off echo on
 


 host echo "\n++ Creating Backup Controlfile (binary) " 
 alter database backup controlfile 


    to '${LOCBAK_DIR1}/controlfile_${DATE_DOWK}.ctl' REUSE ;



 host echo "++ Creating Backup Controlfile (trace) " 
 alter database backup controlfile to trace ;
[]



if [[ $? != 0 ]] ; then


    NOTIFY_MSG="\nWARNING: ${function} Could not create backup
controlfile"


    print NOTIFY_MSG


    enotify


else


    ## locate text controlfile and copy to the backup location
    grep -l "CONTROLFILE" ${ORACLE_ADMIN}/${ORACLE_SID}/udump/*.trc | \
    xargs -I {} mv $1{} ${LOCBAK_DIR1}/controlfile_${DATE_DOWK}.sql



    ## copy binary & trace controfiles to backup server
    print "++ Backing up controlfiles to ${BACKUP_SRVR} \n"



    for C in `ls ${LOCBAK_DIR1}/controlfile_${DATE_DOWK}.*`

       do
          ${SCP} -p ${C}
${BACKUP_SRVR}:${RMTBAK_DIR1}/01/dbbak01/${C##*/}
          if [[ $? != 0 ]] ; then 
              NOTIFY_MSG="\nWARNING: ${function} Backup failed for ${C}"
              print NOTIFY_MSG
              enotify
          fi
       done




fi



print "END ${function} at `date +%H:%M:%S` Status $? \n"
}



#---------------------# 

# backup archive logs #

#---------------------# 



do_archlogs() {


function="do_archlogs"; print "\nSTART ${function} at `date +%H:%M:%S`
\n"



 ${ORACLE_HOME}/bin/sqlplus -s /nolog <<-[]
 connect ${BACKUP_ADM}@${ORACLE_SID}



 whenever sqlerror exit sql.sqlcode;


 set feedback off echo off 



 host echo "\n++ Executing checkpoint " 
 alter system checkpoint ;



 host echo "++ Switching logfile " 


 alter system switch logfile ;


[]



if [[ $? != 0 ]] ; then


    print "\nWARNING: ${function} Checkpoint or logfile switch failed
\n"


    NOTIFY_MSG="`cat ${LOGFILE}`"


    enotify


    exit -1


fi



# Copy current archive logs to backup server even if switch logfile

fails


ARCH_DIR1="`grep ^log_archive_dest_1 ${ORACFG_DIR}/init${ORACLE_SID}.ora
| cut -f3 -d"="


| sed 's/ MANDATORY\"//'`" ; export ARCH_DIR1



if [[ -d ${ARCH_DIR1} ]] ; then


    print "++ Backing up archive logs \n"



    for A in `find ${ARCH_DIR1} -name "arch*" -mtime 0 -print`

       do
          ${SCP} -p ${A}




${BACKUP_SRVR}:${RMTBAK_DIR1}/01/dbvol02/${A##*/}


          if [[ ${?} != 0 ]] ; then
              print "\nWARNING: ${function} Backup failed for ${A}"
              NOTIFY_MSG="`cat ${LOGFILE}`"
              enotify
          fi
       done
else 
     NOTIFY_MSG="\nWARNING: ${function} Destination ${ARCH_DIR1} not
found\n "
     print ${NOTIFY_MSG}
     enotify




fi



print "END ${function} at `date +%H:%M:%S` Status $? \n"
}



#---------------------# 

# backup config files #

#---------------------# 



do_config() {


function="do_config"; print "\nSTART ${function} at `date +%H:%M:%S`"



if [[ -d ${ORACFG_DIR} && -d ${TNS_ADMIN} ]] ; then


    print "\n++ Backing up database instance & tns config files\n"
    for C in `ls ${ORACFG_DIR}/*${ORACLE_SID}.ora ${TNS_ADMIN}/*.ora`

       do
         if ${SCP} -p ${C}
${BACKUP_SRVR}:${RMTBAK_DIR1}/01/dbbak01/${C##*/}; then
            print "\n++ Backup complete for ${C}"
         else
            NOTIFY_MSG="\nWARNING: ${function} Backup failed for ${C}"
            print ${NOTIFY_MSG}
            enotify
          fi
        done
else 
     NOTIFY_MSG="\nWARNING: ${function} ${ORACFG_DIR} does not exist \n
"
     print ${NOTIFY_MSG}
     enotify




fi



print "\nEND ${function} at `date +%H:%M:%S` Status $? \n"
}




#-------------------------# 

# remove old archive logs #

#-------------------------# 



rm_archlogs() {


function="rm_archlogs"; print "\nSTART ${function} at `date +%H:%M:%S`"



# locate all archive log destinations



ARCH_DIRS="`grep ^log_archive_dest ${ORACFG_DIR}/init${ORACLE_SID}.ora |
cut -f3 -d"=" |


sed 's/ MANDATORY\"//'`" ; export ARCH_DIRS



# Remove archive logs older than N-days



for A in ${ARCH_DIRS}


    do

       if [[ -d ${A} ]] ; then
           print "\n++ Removing old archive logs from ${A}  "
           find ${A} -name "arch*" -mtime +13 -exec rm {} \;
       else
           NOTIFY_MSG="\nWARNING: ${function} Destination ${A} not
found\n"
           print ${NOTIFY_MSG}
           enotify
       fi




    done



print "\nEND ${function} at `date +%H:%M:%S` Status $? \n"
}



################

# MAIN ROUTINE #


################




#------ Make sure args have been supplied ------#

if [ ${#*} != 2 ] ; then


   print "\nERROR: Insufficient/excessive parameters specified."
   print "USAGE: ${0##*/} oracle_sid backup_host \n"
   exit -1


fi



#---- Have args, proceed ----#



log clear hot_backup_${1}.log


log start hot_backup_${1}.log


print "\n** START ${0##*/} for ${1}: `date`\n **"
   


# generate backup list



do_tablespace_lst



# roll previous backups



do_roll_backups



# start backups for each tablespace & datafiles

cat ${LOGDIR}/${ORACLE_SID}_datafiles.txt | cut -f1 -d':' |sort -u |
while read T


do


   do_begin_backup 


   do_end_backup


done



# create backup controlfiles



do_controlfile



# backup archive logs



do_archlogs



# backup config files



do_config



# cleanup old archive logs



rm_archlogs




#---- Clean up and exit ----#



print "\n** END ${0##*/} for ${1}: `date` **"
cleanup


log stop


exit 0





Eric Richmond wrote:

> 
> I am looking for a hot backup script that has been successfully used on
> UNIX(Solaris).  Wouldn't mind using the Velpuri scripts, but they seem a bit
> complicated and we have had some issues trying to actually implement them.
> Would really appreciate seeing what other people are using.  Also would be
> nice to see how they are called using cron.
> 
> Thanks.
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Eric Richmon
>   INET: cemail2_at_sprintmail.com
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: lvordos_at_qwest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Fri Aug 30 2002 - 11:13:30 CDT












Original text of this message













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