KSH version of Backup Scripts
Date: 23 Mar 1994 12:32:52 -0800
Message-ID: <2mq91k$lch_at_bach.seattleu.edu>
the following is a ksh backup script for backing up ORACLE data spaces. This includes control files, data files, and redo logs. It always backs up to disk. I am still looking for a satisfactory method of backing up directly to tape.
Please use this script and e-mail any problems or enhancements.
Thanks
Larry S. Dare
ldare_at_wdni.com
---------------------------backup.sh----------------------------------
#!/bin/ksh
#
# O R A C L E B A C K U P S C R I P T
# Description: This script is intended to backup all ORACLE database files
# for a given instance of a database.
# Requirements: To successful run this script, you should be logged into the
# oracle account. This script backs up the database to disk,
# so there should be enough disk space in the
# $BACKUP_DIST directory
# Input Parameters:
# First Parameter: is a valid ORACLE_SID pointing to a database
# instance
# Second Parmeter: is whether or not to backup ON or OFF line
#
# Example: $ backup.sh PROD ON
# This command will backup the PROD database online.
#
# DISCLAIMER: The author doesn't warrant this script at all. None. As with
# all database utilities, you should test it to your own
# satisfaction. Please e-mail the author any problems or
# enhancements.
#
#
# I D E N T I F I C A T I O N
# file =backup.sh
# date =3/23/94
# version=1.0
# author= Larry Dare, Weyerhaeuser, ldare_at_wdni.com, 206-924-4319
#
#
#------------------------------------------------------------
# initialization
#------------------------------------------------------------
initialization () {
let IM=0
DEBUG=OFF
#
#
# INITIALIZATION PARAMETERS
# DATA_FILES
# Description: This variable is set to the top level directory
# of where the ORACLE data files reside. This variable
# along with the ORACLE_sid variable are the path
DATA_FILES="/u0*/ORACLE"
# BACKUP_DIST
# Description: This variable is set to the disk directory which
# all database files are backed up.
BACKUP_DIST_BASE_NAME="/u04/temp_ora_backup"
if [[ -d "$BACKUP_DIST_BASE_NAME" ]]
then # backup directory exists
# create a new directory for this days backup
BACKUP_DIST=$BACKUP_DIST_BASE_NAME/dbback$(date +%m%d%y)
if [[ ! -d "$BACKUP_DIST" ]]
then # directory doesn't exist create it
mkdir $BACKUP_DIST
fi
else
info_message "Backup Directory doesn't exist" ERROR
fi
# BACKUP_LOG
# Description: This variable is set to the file which contains all
# messages during the backup procedure
BACKUP_LOG="./backup$(date +%m%d%y).log"
# ORATAB
# Description: This variable is set to the oratab file that is an
# oracle file that contains entries for all valid
# databases on the machine
ORATAB="./oratab"
# TAPE_CMD
# Description: This variable is the tape command to move the disk
# of the backup file to tape.
TAPE_CMD="tar -cv "
# TAPE_DEV
# Description: This variable is the destination device for the
# TAPE_CMD
TAPE_DEV="/dev/rmt0"
# COPY
# Description: This variable is the copy command used to copy
# database files to the BACKUP_DEST If any command
# flags are needed they can be added within this command
COPY="/bin/cp "
# CRLF
# Description: This variable is used to interput the output from
# the SQL commands. CAUTION: to work properly this
# variable should be a <cr> and the next line a ^M
# (control M)
CRLF="
"
} # end initialization
#
#-------------------------------------------------------------------
# info_message
# Description: This function outputs a string to the
# BACKUP_LOG. When calling this function make
# sure to pass the entire string enclosed in quotes.
# calling this routine without any arguments results
# in a blank line being sent with the time stamp
#-------------------------------------------------------------------
info_message () {
TIMESTAMP=$(date)
case $# in
1)
MESSAGE=$1
echo $TIMESTAMP $MESSAGE >> $BACKUP_LOG
;;
2)
MESSAGE=$1
case $2 in
"DEBUG")
let IM=IM+1
if [[ "$DEBUG" = "ON" ]]
then
echo DEBUG$IM: $MESSAGE
fi
;;
"ERROR")
echo ERROR: $TIMESTAMP $MESSAGE | tee -a $BACKUP_LOG
;;
esac
;;
3)
MESSAGE=$1
case $3 in
"DEBUG")
let IM=IM+1
if [[ "$DEBUG" = "ON" ]]
then
echo DEBUG$IM: $MESSAGE
fi
;;
"ERROR")
echo ERROR: $MESSAGE | tee -a $BACKUP_LOG
;;
esac
;;
*)
MESSAGE=$1
echo ERROR calling info_message
echo $TIMESTAMP $MESSAGE >> $BACKUP_LOG
;;
esac
}
#
#--------------------------------------------------------------------
# oracle_shutdown
#--------------------------------------------------------------------
oracle_shutdown () {
info_message "Shut down $DB_NAME"
ORACLE_SID=$DB_NAME
sqldba >> $BACKUP_LOG 2>&1 <<quitsql!
connect internal
shutdown immediate
quitsql!
} #end oracle_shutdown
#
#--------------------------------------------------------------------
# oracle_startup
#--------------------------------------------------------------------
oracle_startup () {
info_message "Oracle Startup up $DB_NAME"
ORACLE_SID=$DB_NAME
sqldba >> $BACKUP_LOG 2>&1 <<quitsql!
connect internal
startup
quitsql!
} #end oracle_startup
#
#----------------------------------------------------------------------
# backup_datafiles
#----------------------------------------------------------------------
backup_datafiles () {
info_message "Datafiles Backup start args = $?"
while (( $# != 0 ))
do
info_message "Datafiles $1 being backed up "
case $1 in
DBF)
DBF_FILES=""
for i in $DATA_FILES/$ORACLE_sid/*.dbf
do
DBF_FILES="$DBF_FILES $i "
cp $i ${BACKUP_DIST}
done
;;
CTL)
CTL_FILES=""
for i in $DATA_FILES/$ORACLE_sid/*.ctl
do
CTL_FILES="$CTL_FILES $i "
cp $i ${BACKUP_DIST}
done
;;
LOG)
info_message "Backing up Log files"
LOG_FILES=""
for i in $DATA_FILES/$ORACLE_sid/*.log \
$DATA_FILES/$ORACLE_sid/*/*.log
do
LOG_FILES="$LOG_FILES $i "
cp $i ${BACKUP_DIST}
done
;;
esac
shift
done
info_message "Datafiles Backup End " } #end backup_datafiles
#
#--------------------------------------------------------------------
# backup_OFFLINE
#--------------------------------------------------------------------
# backup OFFLINE databases
backup_OFFLINE (){
# check to see if database is on line
# this determines whether or not to bring the database back on-line
if [[ "$(ps -ef | grep ora_smon_$DB_NAME | grep -v grep)" != "" ]]
then # database is up
DB_UP=YES
oracle_shutdown
else
DB_UP=NO
fi
backup_datafiles DBF CTL LOG
if [[ "$DB_UP" = "YES" ]]
then
oracle_startup
fi
} #end backup_OFFLINE
exec_sql () {
if (($# != 1))
then
info_message "Usage exec_sql sql string in quotes " ERROR
exit -1
fi
SQL_STRING=$1
info_message "EXEC_SQL=$1" DEBUG
SQLCONNECT=" connect internal;"
sqldba lmode=y << !quitsql
$SQLCONNECT
$SQL_STRING
exit;
!quitsql
} # end exec_sql
#alter_tablespace
# this function is passed two args
# Tablespace name = arg 1
# action = arg 2
alter_tablespace () {
action=$2
filename=$1
SQL_STRING="alter tablespace $filename $action BACKUP;"
exec_sql " $SQL_STRING" >> $BACKUP_LOG
} # end alter_tablespace
#
#-------------------------------------------------------------------
# backup_tablespace_file
#-------------------------------------------------------------------
backup_tablespace_file () {
FILENAME=${1%% *}
cp $FILENAME $BACKUP_DIST
} #end backup_tablespace_file
backup_tablespaces () {
# DB_NAME is a global
# define the tablespaces that makeup the instance
info_message " Beginning Backup of Tablespaces " DEBUG SQL_STRING=" select 'VALID:' || a.tablespace_name || ':' || b.file_name \
from \
sys.dba_tablespaces a, sys.dba_data_files b \
where \
a.tablespace_name = b.tablespace_name; "
let num=1
IFS=$CRLF
info_message "pre exec = $SQL_STRING " DEBUG
for i in $(exec_sql " ${SQL_STRING}")
do
output[$num]=$i
let num=num+1
done
# TRIM the sql return array
# These variables are set for using SQLDBA
# TRIM the sql return array
# These variables are set for using SQLDBA
let maxnum=num
let num=1
let i=1
while [[ ${output[$num]:=""} != "" ]]
do
if [[ "$(echo ${output[$num]} | cut -d: -f1)" = "VALID" ]]
then
tablespace_array[$i]=${output[$num]}
let i=i+1
fi
let num=num+1
done
# retrieved all the tablespace names and their associated files
let maxnum=i
let i=1
while [[ ${tablespace_array[$i]:=""} != "" ]]
do
# Alter the tablespace
# remember to pass either the tablespace name or the associated
# file name
TABLESPACE=$(echo ${tablespace_array[$i]}|cut -d: -f2)
TBFILENAME=$(echo ${tablespace_array[$i]}|cut -d: -f3)
info_message " Backing up TB $TABLESPACE File $TBFILENAME "
alter_tablespace $TABLESPACE BEGIN
backup_tablespace_file $TBFILENAME
alter_tablespace $TABLESPACE END
let i=i+1
done
} # end backup_tablespaces
#
#--------------------------------------------------------------------
# backup_controlfile
#--------------------------------------------------------------------
backup_controlfile () {
info_message " Backing up Control File " controlfile_backup=$BACKUP_DIST/controlfile_backup.ctl SQL_STRING="alter database backup controlfile to \
'${controlfile_backup}' reuse;"
exec_sql " $SQL_STRING " >> $BACKUP_LOG
} #end backup_controlfile
#--------------------------------------------------------------------
# backup_ONLINE
#--------------------------------------------------------------------
backup_ONLINE () {
info_message " Beginning ONLINE Backup Procedure "
backup_tablespaces
backup_controlfile
backup_datafiles "LOG"
} #end backup_ONLINE
#
#
# M A I N P R O G R A M
initialization
# check to see that the proper number of arguments are passed
# then check to see if they are valid
# check to see if script being run as oracle
info_message "Checking User validation"
if [[ "oracle" != "$(whoami)" ]]
then
ERR_STR="Backup must be executed from the oracle user account"
info_message " $ERR_STR " ERROR
return 1
fi
if (( $# != 2 ))
then
ERR_STR="Usage - $0 ORACLE_SID ON/OFF......"
info_message " $ERR_STR " ERROR
return 1
fi
DB_NAME=$1
typeset -u BACKUP_METHOD=$2
info_message "Checking Database validation" if (( $(awk -F: 'substr($1,1,1) != " " && substr($1,1,1) != "#" \
{print $1}' $ORATAB | grep $DB_NAME > /dev/null;echo $?) != 0 )) then
ERR_STR="Invalid ORACLE_SID $DB_NAME not found in oratab "
info_message " $ERR_STR " ERROR
return 1
fi
info_message "Database Valid."
#
# set the ORACLE_SID
ORACLE_SID=$DB_NAME
typeset -l ORACLE_sid=$ORACLE_SID
#
# Perform BACKUP_METHOD defaults to INVALID
info_message "Checking validation of backup method = $BACKUP_METHOD" DEBUG case $BACKUP_METHOD in
"ON")
info_message "Checking ARCHIVE Option"
if [[ "$(ps -ef | grep ora_arch_$ORACLE_SID | grep -v grep )" \
= "" ]]
then
ERR_STR="BACKUP_METHOD ON is invalid. \
Database is either shutdown or is not being archived."
info_message " $ERR_STR " ERROR
return 1
fi
backup_ONLINE
;;
"OFF")
info_message "Starting OFF line backups " DEBUG
backup_OFFLINE
;;
*)
ERR_STR="BACKUP_METHOD is invalid; ON/OFF "
info_message " $ERR_STR " ERROR
return 1
;;
esac
# Notify DBA backups complete
# echo "Backup complete $(date) " | mail oracle -s"Backup Log Report"
Received on Wed Mar 23 1994 - 21:32:52 CET