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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
