#! /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