Script (UNIX) to automate your Hot Backups

#! /bin/ksh
# The script can be tailored to meet your Environment
# Plug in appropriate values between the "<>" to meet your environment
# The following values needs to be replaced
# <ORACLE_SID>
# <ORACLE_HOME>
# <COLD_BACKUP_DIRECTORY>
# <email_address>
# <username>
# <password>

trap  2 3

# Set up environmental variables - Oracle specific
#
export ORACLE_SID=<ORACLE_SID>
export ORACLE_HOME=<ORACLE_HOME>
export PATH=$PATH:/$ORACLE_HOME/bin

# Set up variables to hold directory information
#
HOT=<HOT_BACKUP_DIRECTORY>
LOG=<BACKGROUND_DUMP_DEST>/alert_${ORACLE_SID}.log

# Make sure that the Lock File does not exists this confirms that
# the previous cron job has finished its activity of cold backup
#
if [ -f ${HOT}/hotbackup.lock ]; then
  mail <email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
    Previous Hot Backup was not completely Successful !
  EOF
  exit 1
else
  touch ${HOT}/hotbackup.lock
fi

# Make sure that the Database Is up and running
# If not inform the concerned person
#
database_up=`ps -ef | grep pmon_${ORACLE_SID} | grep -v grep | wc -l`
if [ $database_up -eq 0 ]
then
  mail <email_address> <<-EOF
    ERROR - COLD BACKUP For "${ORACLE_SID}" Has Failed
    Database Should be up to take a trace of the data files - Please Investigate!
  EOF
  exit 1
fi

# Make sure that the Database Is running in archive log mode
# If not inform the concerned person
#
sqlplus /nolog <<-EOF
  connect / as sysdba
  spool ${HOT}/archive_mode.lst
  archive log list
  spool off
  EXIT
EOF
archive_mode=`grep -i "No Archive Mode*" ${HOT}/archive_mode.lst|grep -v grep|wc -l`
if [ $archive_mode -eq 1 ]
then
  mail <<email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
  Database Is Not Running in Archive Log Mode as Desired
  during Hot Backup's - Please put the database in Archive Log Mode and restart!
  EOF
  exit 1
fi

# Find the count of 'alter tablespace .* begin backup' and
# 'alter tablespace .* end backup' in your alert logs
# if both are equal this signifies the previous hot backup is successful
#
if [ -f $LOG ] ; then
  Bgn_Bkp=egrep -ci '^alter tablespace .* begin backup' $LOG
  End_Bkp=egrep -ci '^alter tablespace .* end backup' $LOG
  Diff_Bkp=expr $Bgn_Bkp - $End_Bkp
else
  mail <email_address> <<-EOF
    ERROR - HOT BACKUP For "${ORACLE_SID}" Has Failed
    Could not locate Alert Log - Please Investigate !
  EOF
  exit 1
fi

# Logon to sqlplus - preferably as a DBA
# the sql script, generates a files called ora_HOT_backup.sql
# The file contains all the commands to perform hot backup's
# 1. Backup control file before starting hot backup.
# 2. Put first tablespace online.
# 3. Backup the datafile associated with the tablespaces and compress them (to save space).
# 4. Bring the first tablespace online.
# 5. Repeat steps 2 through 5 till all tablespaces are backed up
#
sqlplus <<username>/<password> <<-EOF
  column tablespace_name noprint
  column seqn noprint
  set pagesize 0
  set linesize 132
  set feedback off
  set sqlprompt ""
  Whenever SQLERROR exit FAILURE
  select chr(1) tablespace_name, -9999 seqn,
  'alter system switch logfile;'
  from dual
  UNION
  select chr(1) tablespace_name, -9998 seqn,
  'alter database backup controlfile to  ||
  '${HOT}/controlfile_${ORACLE_SID}.HOT.full.before reuse;'
  from dual
  UNION
  select tablespace_name, 0,
  'alter tablespace '||tablespace_name||' begin backup;'
  from sys.dba_tablespaces
  where status = 'ONLINE'
  UNION
  select tablespace_name, file_id,
  '!compress < '||file_name||'> ${HOT}/' ||
  substr(file_name,instr(file_name,'/',-1) + 1) || '.Z'
  from sys.dba_data_files
  where status = 'AVAILABLE'
  UNION
  select tablespace_name, 9999,
  'alter tablespace '||tablespace_name||' end backup;'
  from sys.dba_tablespaces
  where status = 'ONLINE'
  UNION
  select chr(255) tablespace_name, 9998 seqn,
  'alter database backup controlfile to  ||
  '${HOT}/controlfile_${ORACLE_SID}.HOT.full.after reuse;'
  from dual
  UNION
  select chr(255) tablespace_name, 9999 seqn,
  'alter system switch logfile;'
  from dual
  ORDER
  BY 1, 2
  spool ora_HOT_backup.sql
  /
  spool off
  start ora_HOT_backup.sql
  exit
EOF

# Find the count of 'alter tablespace .* begin backup' and
# 'alter tablespace .* end backup' in your alert logs
# if both are equal this signifies the previous hot backup is successful
# Else mail the concerned person associated with the backup's.
#
Bgn_Bkp=egrep -ci '^alter tablespace .* begin backup' $LOG
End_Bkp=egrep -ci '^alter tablespace .* end backup' $LOG
if [ $Bgn_Bkp != expr $End_Bkp + $Diff_Bkp ] ; then
  mail <email_address> <<-EOF
    ERROR : HOT BACKUP For "${ORACLE_SID}" Has Failed
    Number of "Begin Backup" statenents does not equal "End Backup"
    Please Investigate Immediately !
  EOF
  exit 1
fi