#!/bin/ksh
# -------------------------------------------------------------------------------
# Name: $HOME/scripts/hot_backup.ksh
# Author: Murali Sreedhar
# Usage: hot_backup.ksh SID
# Description: Performs hot backup of specified database.
# Change the environment variables according to your specific environment.
# For a step by step explanation of this script, visit the following link -
# http://www.dbaquest.com/modules.php?op=modload&name=News&file=article&sid=15
#
# Disclaimer: Use this script at your own risk. The author is not responsible
# for any loss arising out of the use of this script.
# -------------------------------------------------------------------------------
# Check for number of arguments
if (($# == 0 || $# > 1))
then
print "\nUsage: hot_backup.ksh SID\n"
exit 1
fi
######## Begin variable definition. Modify according to your system ########
# Set the environment variables, with ORACLE_SID being the first argument
# that was entered on the command line.
export ORACLE_SID=$1
. $HOME/.profile
# Change these variables based on your system. You will not need to change anything after this section.
# Make sure OS user doing the backup has permissions to write to these directories.
# The file to write a list of tablespaces and datafiles to
export FILE_LIST=$SCRIPTS/log/hot_file_list.txt
# The SQL script to write all the backup commands to
export SQL_SCRIPT=$SCRIPTS/hot_backup.sql
# The directory where you want to store your backup
export BACKUP_DIR=/u03/backup
# The archive log destination for your database
# Check init.ora parameters log_archive_dest or log_archive_dest_n
export ARCH_DIR=/u03/oracle/oradata/prod/arch
# The file name extension of your archive logs. This is case sensitive
# Check init.ora parameters log_archive_format
export ARCH_EXT=ARC
# Time stamp to create unique backup file name
export TSTAMP=`date '+%Y_%m_%d:%H:%M'`
######## End variable definition, script begins. Do not modify below this line ########
# Check if the instance is up. If not, abort backup
BGPROC_COUNT=`ps -ef | grep smon_${ORACLE_SID} | egrep -c -v grep`
if [[ $BGPROC_COUNT -ne 0 ]]
then
print "Instance is up. Proceeding..."
else
print "Instance not up. How am I supposed to get the file names? Aborting backup...\n"
exit 1
fi
# Check if database is in archivelog mode. If not, abort backup
LOG_MODE=`$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" </dev/null
set head off
set feedback off
set verify off
set pagesize 0
spool $FILE_LIST
select rtrim(tablespace_name)||':'||rtrim(file_name)||':' from dba_data_files order by tablespace_name desc;
spool off
exit
EOF
# Check if file list exists. If it doesnt, we stop here.
# Also checking for ORA- errors in the file
if [[ ! -f $FILE_LIST ]]
then
print "$FILE_LIST: Non-existent file, or not accessible\n"
exit 1
elif [ -n "`egrep 'ORA-' $FILE_LIST`" ]
then
print "Errors in $FILE_LIST. Aborting hot backup !!"
exit 2
else
print "Got tablespace and file names. Creating SQL script...\n"
fi
# All the backup commands are spooled to a SQL file which is then executed from sqlplus
echo "alter system archive log current;" > $SQL_SCRIPT
PREV_TSNAME=none
IFS=:
exec 0<$FILE_LIST
while read -r CURR_TSNAME CURR_FILEPATH EMPTY_SPACE
do
CURR_FILENAME=`basename $CURR_FILEPATH`
if [ ${PREV_TSNAME} = none ]
then
echo "alter tablespace ${CURR_TSNAME} begin backup;" >> $SQL_SCRIPT
echo "host cp ${CURR_FILEPATH} ${BACKUP_DIR}/${CURR_FILENAME}" >> $SQL_SCRIPT
PREV_TSNAME=$CURR_TSNAME
elif [ ${CURR_TSNAME} = ${PREV_TSNAME} ]
then
echo "host cp ${CURR_FILEPATH} ${BACKUP_DIR}/${CURR_FILENAME}" >> $SQL_SCRIPT
else
echo "alter tablespace ${PREV_TSNAME} end backup;" >> $SQL_SCRIPT
echo "alter tablespace ${CURR_TSNAME} begin backup;" >> $SQL_SCRIPT
echo "host cp ${CURR_FILEPATH} ${BACKUP_DIR}/${CURR_FILENAME}" >> $SQL_SCRIPT
PREV_TSNAME=$CURR_TSNAME
fi
done
echo "alter tablespace ${PREV_TSNAME} end backup;" >> $SQL_SCRIPT
echo "alter database backup controlfile to '${BACKUP_DIR}/binary_control_${TSTAMP}.bkp';" >> $SQL_SCRIPT
echo "alter database backup controlfile to trace;" >> $SQL_SCRIPT
echo "alter system archive log current;" >> $SQL_SCRIPT
echo "host chmod 400 ${ARCH_DIR}/*.${ARCH_EXT}" >> $SQL_SCRIPT
# Run our SQL script which will do the backup and move the archive logs
echo "SQL script generation complete. Starting hot backup..."
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" \@$SQL_SCRIPT
find ${ARCH_DIR} -name "*.${ARCH_EXT}" -type f -perm 400 -exec mv \{\} ${BACKUP_DIR} \;
# You can tar and compress the files, but it takes a lot of OS resources to do this
# Uncomment the following if you want your backup as a compressed tarball.
# Make sure these utilities are defined in your PATH variable
# Be careful with the remove-files option for tar. Test before using in production.
# Options for tar and compress may differ based on your OS. Example below is for Linux
tar --remove-files -cf ${BACKUP_DIR}/hot_backup_${ORACLE_SID}_${TSTAMP}.tar ${BACKUP_DIR}/*.*
compress ${BACKUP_DIR}/hot_backup_${ORACLE_SID}_${TSTAMP}.tar