Hot Backup Script

#!/bin/ksh
############################################################################
####
#
# Hot Backup Script
# Sachchida Ojha
############################################################################
###
#
#
#DBA=sojha@dbceo.com; export DBA
ORACLE_HOME=/export/home/oracle/OraHome1; export ORACLE_HOME

#ARCH_DEST=/u05/arch/hsphtpr; export ARCH_DEST
ORACLE_SID=arsystem; export ORACLE_SID

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
LOG_DIR=$HOME/scripts/log;export LOG_DIR

LOG_FILE=$LOG_DIR/do_hot_back_$ORACLE_SID.log ; export LOG_FILE
ALERT_LOG=$ORACLE_HOME/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log;
export ALERT_LOG
UDUMP=$ORACLE_HOME/admin/$ORACLE_SID/udump ;export UDUMP

BACKUP_BASE=/u01/oracle_backup/hotbackup/$ORACLE_SID; export
BACKUP_BASE

#
# Check if DB shutdown normally don't check the rest
#
ENUM=`tail -35 $ALERT_LOG | egrep -c 'CLOSE'`
if [ $ENUM -ne 0 ]; then
echo "`uname -n`:$ORACLE_SID instance was down." > $LOG_FILE
exit
fi

#
# Creating backup directory and purge directory
#
A=`date | awk '{print $2}'`; export A
B=`date | awk '{print $3}'`; export B
C=`date | awk '{print $6}'`; export C

BACKUP_DIR=$BACKUP_BASE/$A'_'$B'_'$C; export BACKUP_DIR

echo "Starting hot backup of $ORACLE_SID at" $(date) > $LOG_FILE
echo " " >> $LOGFILE
echo "Creating backup directory $BACKUP_DIR...\c" >> $LOG_FILE
echo " " >> $LOGFILE
mkdir $BACKUP_DIR || exit 1
echo "Done" >> $LOG_FILE
df -k $BACKUP_BASE >> $LOG_FILE

# Creating list of tablespaces
TSP=$HOME/scripts/tmp/hot_tsp.lis
export TSP

$ORACLE_HOME/bin/sqlplus -s internal < $TSP
set head off pages 0
set lines 150
set echo off verify off feedback off
select name from sys.ts$ where online$ !=3 and name != 'TEMP'
order
by ts# desc;
exit;
EOF

# Declaring variables for log file and shell script names to be used in
the
following loop

cat $TSP | while read LINE
do
TSPNAME=$LINE; export TSPNAME
COPY_FILE_NAME=$HOME/scripts/tmp/$TSPNAME.ksh; export
COPY_FILE_NAME
BEGIN_TBS_LOG=$HOME/scripts/tmp/$TSPNAME.begin; export
BEGIN_TBS_LOG
END_TBS_LOG=$HOME/scripts/tmp/$TSPNAME.end; export END_TBS_LOG

# Altering the tablespace to Begin backup mode

$ORACLE_HOME/bin/sqlplus -s internal < $BEGIN_TBS_LOG
set head off pages 0
set lines 150
alter tablespace $TSPNAME begin backup;
exit;
EOF

# Check for error in the begin backup execution and email error

NUM=`egrep -ci 'ORA-|error' $BEGIN_TBS_LOG`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID begin backup at "`date ' %m/%d/%y,%T'`
>>
$LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $TSPNAME failed begin
backup."
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
exit 1
fi
rm -f $BEGIN_TBS_LOG

#Generate shell scripts to compress and copy different tablespaces and
run
them

$ORACLE_HOME/bin/sqlplus -s internal < $COPY_FILE_NAME
set head off pages 0
set lines 150
set echo off verify off feedback off

select '#!/bin/ksh' from dual;
select 'cp ' || file_name || ' $BACKUP_DIR' ||
substr(file_name,
instr(file_name,'/',-1)) from dba_data_files
where tablespace_name='$TSPNAME';
select 'wait;' from dual;
exit;
EOF
# Run the compress and copy and email if any errors

chmod 744 $COPY_FILE_NAME
if $COPY_FILE_NAME
then
echo "Finished copy and compress Datafiles of" $TSPNAME " at"
$(date) >> $LOG_FILE
else
echo "Copy and Compress Failed for tablespace " $TSPNAME " at"
$(date) >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $COPY_FILE_NAME failed to
copy."
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
fi
rm $COPY_FILE_NAME

# Put the tablespace back in end backup mode

$ORACLE_HOME/bin/sqlplus -s internal < $END_TBS_LOG
alter tablespace $TSPNAME end backup;
exit;
EOF

# Check the end backup log for any errors and email if any

NUM=`egrep -ci 'ORA-|error' $END_TBS_LOG`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID end backup at "`date ' %m/%d/%y,%T'` >>
$LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID $TSPNAME failed to end
backup"
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE
exit 1
fi
rm $END_TBS_LOG
done

echo "Backing up the latest 7 archive files generated today" >>
$LOG_FILE
#
#
$ORACLE_HOME/bin/sqlplus -s internal <alter system archive log current;
exit;
EOF

#cd $ARCH_DEST || exit 1
#ls -ltr *.arc | tail -7 | awk '{print $9}' >
$HOME/scripts/tmp/ARCHLIST
#cat $HOME/scripts/tmp/ARCHLIST | while read EACHLINE
#do
#/usr/local/bin/gzip -cv < $ARCH_DEST/$EACHLINE >
$BACKUP_DIR/$EACHLINE.gz &
#done


echo "Taking a binary backup of the latest controlfile " >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus -s internal < alter database backup controlfile to
'$BACKUP_DIR/ctrl_file_binary_copy';
alter database backup controlfile to trace;
exit;
EOF

cd $ORACLE_HOME/admin/$ORACLE_SID/udump
echo " " >> $LOGFILE
echo " Taking an ascii copy of control file " >> $LOGFILE
cp `ls -ltr $ORACLE_HOME/admin/$ORACLE_SID/udump/*.trc|tail -1| awk
'{print
$9}'` $BACKUP_DIR

#
#
echo " Backup Done" >> $LOG_FILE
df -k $BACKUP_BASE >> $LOG_FILE

if [ -e $BACKUP_DIR/ctrl_file_binary_copy ]
then
cd $BACKUP_BASE
echo " " >> $LOGFILE
echo "Seems like today's backup completed Okay" >> $LOGFILE
echo " " >> $LOGFILE
echo "Following older backup directories will be purged" >>
$LOG_FILE
echo " " >> $LOGFILE
/usr/bin/find . -mtime 1 -type d >> $LOG_FILE
/usr/bin/find . -mtime 1 -type d -exec /usr/bin/rm -rf {} \;

sqlplus internal << EOF > /tmp/verbackup.lis
set pages 1000 lines 400 feedback off echo off verify off trimspool
off;
column recover heading "Requires|Recovery?" format a10;
column time heading "Date Of|Last Backup" format a15;
column Name format a40;
column Mode format a20;

select substr(tablespace_name,1,20) "Tablespace
Name",substr(name,1,45)
"Name",
a.status "Status", decode(fuzzy,'YES','BACKUP','NORMAL') "Mode"
,
recover, time
from v\$datafile_header a, v\$backup b
where a.file#=b.file# order by tablespace_name,name;
exit
EOF
cat /tmp/verbackup.lis >> $LOG_FILE
else
echo "$BACKUP_DIR does not exist" >> $LOGFILE
fi
NUM=`egrep -ci 'ORA-|error' $LOG_FILE`
if [ $NUM -gt 0 ]
then
echo "Failed $ORACLE_SID online Hot backup at "`date
' %m/%d/%y,%T'`
>> $LOG_FILE
echo "" >> $LOG_FILE
SUBJ="ERROR: `uname -n`:$ORACLE_SID `basename $0` failed"
echo $SUBJ | mailx -s "$SUBJ" $DBA >> $LOG_FILE

else

echo "Completed $ORACLE_SID Hot backup at "`date
' %m/%d/%y,%T'` >>
$LOG_FILE
SUBJ="SUCCESS: `uname -n`:$ORACLE_SID `basename $0` completed."
cat $LOG_FILE | mailx -s "$SUBJ" $DBA >> $LOG_FILE
fi
exit