#!/bin/ksh
#---------------------------------------------------------------#
# Script generates online backup scripts and run the scripts #
# ---------------------------------------------------------- #
# Date : 2000/08/11 #
# ---- #
#---------------------------------------------------------------#
# NOTES : For implementation of script: #
# ------- #
# 1. Create a separate directory where the script resides #
# because the program generates it's own scripts for #
# the run of a backup. #
# 2. Table ORA_ONLINE_BACKUP is created and maintained by #
# this program to run the correct cycle for a specific day #
# when this program is used to break the online backup of the#
# database into more than 1 backup piece. #
# Trigger is also required to ensure only 1 row resides in #
# table. #
# #
# create table ORA_ONLINE_BACKUP #
# ( cycle number(1)); #
# #
# create or replace trigger #
# backup_trigger before insert on ORA_ONLINE_BACKUP #
# for each row #
# declare #
# num_rows number; #
# begin #
# select count(*) into num_rows from ORA_ONLINE_BACKUP; #
# if num_rows > 0 #
# then #
# raise_application_error(-20000, 'Only 1 row allowed in table');#
# end if; #
# end; #
# / #
# 3. Table ORA_ONLINE_BACKUP_FILES is created and maintained #
# for recordkeeping of when files was last backed up. #
# #
# create table ORA_ONLINE_BACKUP_FILES #
# ( file_name varchar2(513), #
# last_backup date ); #
# #
# create unique index ORA_ONLINE_BACKUP_KEY #
# on ORA_ONLINE_BACKUP_FILES ( file_name ); #
# 4. Procedure ORA_BACKUP_PROC1 for updating above table #
# during run of backup. #
# #
# create or replace procedure ORA_BACKUP_PROC1 ( file1 varchar2 ) #
# as #
# begin #
# insert into ora_online_backup_files #
# values ( file1 , sysdate ); #
# commit; #
# exception #
# when dup_val_on_index #
# then update ora_online_backup_files #
# set last_backup = sysdate #
# where file_name = file1; #
# commit; #
# end; #
#/ #
# #
# #
#---------------------------------------------------------------#
# #
# #
# #
# Changes : #
# --------- #
# #
# #
#---------------------------------------------------------------#
#
#
# Working Environment #
# -------------------
#
PROGNAME=onlinebackup.ksh
DBUSER=ops\$backup
DBPASS=backreco
WORKDIR=/usr1/app/oracle/product/8.0.5/home/desibackup
WORKFILE=workfile.in
NEWWORKFILE=newworkfile.in
CONTROLFILE=controlfile.in
UPDATEFILE=updatefile.in
BACKUPCONTROLFILE=$ORACLE_SID''backupcontrolfile.control
TRACECONTROLFILE=$ORACLE_SID''backupcontrolfile.trace
ENDSQL=end.sql
GENSQL=gen.sql
BEGINSQL=begin.sql
NETWORKER=backfile_networker
BACKUPRUN=backfile_run
BACKSUCCESS=success
BACKUPLOGFILE=$ORACLE_SID''_backup.logfile
SYSTEM_001PR=`cat /var/opt/oracle/SYSTEM_001P`
INSTALLFLAG=nono
integer DBCYCLE=0
integer counter1=1
integer counter2=0
integer NOFILES=0
integer DBLOGFILE=0
#
#
#
# -------------------------------------------------------#
# Check whether tables,triggers etc. are installed before#
# run of script. #
# #
#--------------------------------------------------------#
#
echo "--------------------------------------"
echo "STARTING INSTALL OR DATABASE UP CHECK "
echo "--------------------------------------"
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "select '1' from ORA_ONLINE_BACKUP ;" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
INSTALLFLAG=`sqlplus -s $DBUSER/$DBPASS @$WORKDIR/$GENSQL`
rm $WORKDIR/$GENSQL
if [ $INSTALLFLAG = 1 ]
then
echo " "
else
echo "------------------------------------------"
echo "INSTALLFLAG value : $INSTALLFLAG "
echo "------------------------------------------"
echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "Table ORA_ONLINE_BACKUP does not exist or database down !!" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "Table ORA_ONLINE_BACKUP does not exist or database down !!"
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit;
fi;
#
#
#------------------------------------------------------------#
# Check input parameter for amount of backup pieces #
#------------------------------------------------------------#
#
echo "--------------------------------------"
echo "CHECKING INPUT PARAMETER............. "
echo "--------------------------------------"
echo "***************************************************"
echo " Input parameter for no of files : $1"
NOFILES=$1
echo " Run Cycles generated is : $NOFILES "
echo "***************************************************"
#
if [ $NOFILES -eq 0 ]
then
echo "**************************************************"
echo " Mandatory parameter for no of cycles to generate "
echo " was not passed to program : Please restart "
echo " I.E. PROGRAM PARAMETER1 "
echo "**************************************************"
exit
fi;
if [ $NOFILES -gt 9 ]
then
echo "**************************************************"
echo " Parameter for cycles is out of range. Only use "
echo " 1 - 9. "
echo "**************************************************"
exit
fi;
#------------------------------------------------------------#
# Check for already running backup : #
#------------------------------------------------------------#
echo "--------------------------------------"
echo "CHECKING FOR ALREADY RUNNING BACKUP.. "
echo "--------------------------------------"
signalle=yep
integer counter=0
integer counter1=0
while [ $signalle = yep ]
do
counter=$counter+1
counter1=`ps -ef|grep "$PROGNAME" |grep -v grep|grep -v "sh -c" | wc -l`
if [ counter1 -gt 1 ]
then
echo "Another Backup for $ORACLE_SID is busy"
echo "Waiting 5 minutes..... "
sleep 300
else
signalle=norunning
fi;
if [ $counter -gt 48 ]
then
signalle=nono
fi;
done
if [ $signalle = nono ]
then
echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID failed " >> err.out
echo " due to waiting for more than " >> err.out
echo " 4 hours for previous job " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID failed "
echo " due to waiting for more than "
echo " 4 hours for previous job "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit
fi;
#
#
#------------------------------------------------------------#
# S T A R T O F B A C K U P : #
#------------------------------------------------------------#
#
#
echo "--------------------------------------"
echo "STARTING BACKUP...................... "
echo "--------------------------------------"
echo " ----------------------------------------------------- " >> $WORKDIR/$BACKUPLOGFILE
echo " BACKUP STARTED :" >> $WORKDIR/$BACKUPLOGFILE
date >> $WORKDIR/$BACKUPLOGFILE
#
#
#
# -------------------------------------------------------#
# Check for day's cycle in table ORA_ONLINE_BACKUP #
# #
#--------------------------------------------------------#
#
echo "--------------------------------------"
echo "GETTING CYCLE FOR BACKUP RUN......... "
echo "--------------------------------------"
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "select cycle from ORA_ONLINE_BACKUP ;" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
DBCYCLE=`sqlplus -s $DBUSER/$DBPASS @$WORKDIR/$GENSQL`
if [ $? -gt 0 ]
then
echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "Table ORA_ONLINE_BACKUP does not exist or database down !!" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "Table ORA_ONLINE_BACKUP does not exist or database down !!"
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit;
fi;
echo "-------------------"
echo "DBCYCLE : $DBCYCLE "
echo "-------------------"
#
if [ DBCYCLE -gt NOFILES ]
then
DBCYCLE=$NOFILES
echo "-------------------"
echo "NEW DBCYCLE : $DBCYCLE"
echo "-------------------"
fi;
echo " BACKUP CYCLE : $DBCYCLE" >> $WORKDIR/$BACKUPLOGFILE
#
#------------------------------------------------------------#
# Switch database logfile : #
#------------------------------------------------------------#
echo "--------------------------------------"
echo "SWITCHING DATABASE LOGFILE........... "
echo "--------------------------------------"
#
rm $WORKDIR/$GENSQL
svrmgrl << EOF
connect internal
spool $WORKDIR/$GENSQL
alter system switch logfile;
spool off;
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "ERROR : switch logfile failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "ERROR : switch logfile failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
fi;
DBLOGFILE=`cat $WORKDIR/$GENSQL | grep -i ORA-09817 | wc -l`
if [ $DBLOGFILE -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "ERROR : Alert log file system is full!!!! " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "ERROR : Alert log file system is full!!!! "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit
fi;
rm $WORKDIR/$GENSQL
#
#
#------------------------------------------------------------#
# Backup controlfiles to $WORKDIR #
#------------------------------------------------------------#
#
echo "--------------------------------------"
echo "BACKING UP CONTROLFILE TO DIRECTORY.. "
echo "--------------------------------------"
rm $WORKDIR/$BACKUPCONTROLFILE
echo "-------------------------------------------------------"
echo " Backing up controlfile : $BACKUPCONTROLFILE"
echo "-------------------------------------------------------"
#
svrmgrl << EOF
connect internal
alter database backup controlfile to '$WORKDIR/$BACKUPCONTROLFILE';
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "ERROR : Backup of controlfile failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "ERROR : Backup of controlfile failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
fi;
#
#
#
#
#
#------------------------------------------------------------#
# Backup controlfiles to TRACE #
#------------------------------------------------------------#
#
echo "--------------------------------------"
echo "BACKING UP CONTROLFILE TO TRACE...... "
echo "--------------------------------------"
rm $WORKDIR/$TRACECONTROLFILE
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "alter database backup controlfile to trace;" >> $WORKDIR/$GENSQL
echo "select rtrim(spid) from v\$process , v\$session" >> $WORKDIR/$GENSQL
echo " where paddr = addr and" >> $WORKDIR/$GENSQL
echo "sid in ( select sid from v\$session where " >> $WORKDIR/$GENSQL
echo " audsid = userenv('sessionid'));" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
UPROCESSID=`sqlplus -s system/$SYSTEM_001PR @$WORKDIR/$GENSQL`
rm $WORKDIR/$GENSQL
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "select rtrim(value) from v\$parameter where name = 'user_dump_dest';" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
USERDUMPDEST=`sqlplus -s system/$SYSTEM_001PR @$WORKDIR/$GENSQL`
rm $WORKDIR/$GENSQL
cp $USERDUMPDEST"/"$ORACLE_SID""_ora_""$UPROCESSID.trc $WORKDIR/$TRACECONTROLFILE
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "ERROR : Copy of controlfile from trace to " >> err.out
echo "ERROR : trace copy in workdir failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "ERROR : Copy of controlfile from trace to "
echo "ERROR : trace copy in workdir failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
fi;
#
#------------------------------------------------------------#
# Generate workfiles and sql's for backup run #
#------------------------------------------------------------#
#
#------------------------------------------------------------#
# List of datafiles: #
# --------------------- #
#
echo "--------------------------------------"
echo "GENERATING WORKFILES................. "
echo "--------------------------------------"
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "set wrap off " >> $WORKDIR/$GENSQL
echo "set lines 133" >> $WORKDIR/$GENSQL
echo "spool $WORKDIR/$WORKFILE" >> $WORKDIR/$GENSQL
echo "select rtrim(tablespace_name,' ')||' '||rtrim(a.file_name,' ')||' '||a.bytes||' '||'*'||substr(file_name,instr(file_name,'.',1,1)) from dba_data_files a, v\$datafile b where file_id = file# and to_char(trunc(creation_time),'yyyy-mm-dd') < to_char(trunc(sysdate),'yyyy-mm-dd') and a.file_name not in ( select file_name from ORA_ONLINE_BACKUP_FILES c where ( trunc(sysdate) - trunc(last_backup) ) > $NOFILES ) order by a.bytes desc,tablespace_name;" >> $WORKDIR/$GENSQL
echo "spool off" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
#
#
# List of new datafiles created on day of run of backup and
# datafiles that has not been backed up due to creation of
# new datafiles causing the generated backup run file to
# shuffle a lot.
#-------------------------------------------------------
#
#
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "set wrap off " >> $WORKDIR/$GENSQL
echo "set lines 133" >> $WORKDIR/$GENSQL
echo "spool $WORKDIR/$NEWWORKFILE" >> $WORKDIR/$GENSQL
echo "select rtrim(tablespace_name,' ')||' '||rtrim(file_name,' ')||' '||a.bytes||' '||'*'||substr(file_name,instr(file_name,'.',1,1)) from dba_data_files a, v\$datafile b where file_id = file# and to_char(trunc(creation_time),'yyyy-mm-dd') = to_char(trunc(sysdate),'yyyy-mm-dd') order by a.bytes desc,tablespace_name;" >> $WORKDIR/$GENSQL
echo "select rtrim(tablespace_name,' ')||' '||rtrim(a.file_name,' ')||' '||a.bytes||' '||'*'||substr(file_name,instr(file_name,'.',1,1)) from dba_data_files a, v\$datafile b where file_id = file# and a.file_name in ( select file_name from ORA_ONLINE_BACKUP_FILES c where ( trunc(sysdate) - trunc(last_backup) ) > $NOFILES ) order by a.bytes desc,tablespace_name;" >> $WORKDIR/$GENSQL
echo "spool off" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
#
#
#
#------------------------------------------------------------#
# Begin and end backup sql scripts : #
# ---------------------------------- #
#
echo "--------------------------------------"
echo "GENERATING BEGIN AND END SQL......... "
echo "--------------------------------------"
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "spool $WORKDIR/$ENDSQL" >> $WORKDIR/$GENSQL
echo "select 'alter tablespace '||tablespace_name||' end backup;' from dba_tablespaces;" >> $WORKDIR/$GENSQL
echo "spool off" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "spool $WORKDIR/$BEGINSQL" >> $WORKDIR/$GENSQL
echo "select 'alter tablespace '||tablespace_name||' begin backup;' from dba_tablespaces;" >> $WORKDIR/$GENSQL
echo "spool off" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$ENDSQL
echo "exit;" >> $WORKDIR/$BEGINSQL
#
#------------------------------------------------------------#
# Generate networker and backup run file for backup : #
# --------------------------------------------------- #
#
# Current files :
# ---------------
#
#
echo "--------------------------------------"
echo "GENERATING NETWORKER/BACKUP RUN FILE.."
echo "--------------------------------------"
integer counter1=1
integer counter2=0
rm $WORKDIR/$NETWORKER
rm $WORKDIR/$BACKUPRUN
rm $WORKDIR/$UPDATEFILE
backfilerun=""
cat $WORKDIR/$WORKFILE | while read TB DF DFSIZE DFSUFF
do
if [ $counter1 -eq $DBCYCLE ]
then backfilerun=$backfilerun" "$DF
echo "$DF" >> $WORKDIR/$UPDATEFILE
echo "<< $DF >>" >> $WORKDIR/$NETWORKER
echo " +compressasm: "$DFSUFF >> $WORKDIR/$NETWORKER
echo " " >> $WORKDIR/$NETWORKER
fi;
if [ $counter2 -eq 0 ] ;
then counter1=$counter1+1 ; fi
if [ $counter2 -eq 1 ] ;
then counter1=$counter1-1 ; fi
if [ $counter1 -eq $NOFILES+1 ] -a [ $counter2 -eq 0 ] ;
then counter1=$counter1-1
counter2=1 ; fi
if [ $counter1 -eq 0 ] -a [ $counter2 -eq 1 ] ;
then counter1=$counter1+1
counter2=0 ; fi
done
backfilerun=$backfilerun" "$WORKDIR/$BACKUPCONTROLFILE
backfilerun=$backfilerun" "$WORKDIR/$TRACECONTROLFILE
echo "<< $WORKDIR/$BACKUPCONTROLFILE >>" >> $WORKDIR/$NETWORKER
echo " +compressasm: *.control" >> $WORKDIR/$NETWORKER
echo " " >> $WORKDIR/$NETWORKER
echo "<< $WORKDIR/$TRACECONTROLFILE >>" >> $WORKDIR/$NETWORKER
echo " +compressasm: *.trace" >> $WORKDIR/$NETWORKER
echo " " >> $WORKDIR/$NETWORKER
#
#
# Newlyadded files :
# ------------------
#
#
#
cat $WORKDIR/$NEWWORKFILE | while read TB DF DFSIZE DFSUFF
do
backfilerun=$backfilerun" "$DF
echo "$DF" >> $WORKDIR/$UPDATEFILE
echo "<< $DF >>" >> $WORKDIR/$NETWORKER
echo " +compressasm: "$DFSUFF >> $WORKDIR/$NETWORKER
echo " " >> $WORKDIR/$NETWORKER
done
echo "/usr/bin/nsr/save -i -f $WORKDIR/$NETWORKER $backfilerun" > $WORKDIR/$BACKUPRUN
#
#------------------------------------------------------------#
# Run of actual backup of files to tape : #
#------------------------------------------------------------#
#
echo "--------------------------------------"
echo "STARTING ACTUAL RUN OF BACKUP........."
echo "--------------------------------------"
svrmgrl << EOF
connect internal
@$WORKDIR/$ENDSQL;
EOF
svrmgrl << EOF
connect internal
@$WORKDIR/$BEGINSQL;
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
echo " BACKUP FAILED " >> $WORKDIR/$BACKUPLOGFILE
echo " BACKUP ENDED :" >> $WORKDIR/$BACKUPLOGFILE
date >> $WORKDIR/$BACKUPLOGFILE
echo "-------------------------------------------------" >> $WORKDIR/$BACKUPLOGFILE
exit
fi;
#
echo " "
echo " Starting save to tape..........."
echo " "
echo " "
chmod 755 $WORKDIR/$BACKUPRUN
#
#
echo "--------------------------------------"
echo "SAVING TO TAPE........................"
echo "--------------------------------------"
SLEEPFLAG=on
BACKSUCCESS=nono
integer counter=0
#
while [ $BACKSUCCESS = nono ]
do
$WORKDIR/$BACKUPRUN
if [ $? -gt 0 ]
then
echo "---------------------------------"
echo "save failed : Retrying .........."
echo "---------------------------------"
else
BACKSUCCESS=success
SLEEPFLAG=off
counter=$counter+10
fi;
if [ $SLEEPFLAG = on ]
then
echo "sleeping for 15 minutes"
SLEEPFLAG=off
sleep 900
fi;
counter=$counter+1
if [ counter -eq 2 ]
then
echo " backup failed "
BACKSUCCESS=failed
fi;
done
svrmgrl << EOF
connect internal
@$WORKDIR/$ENDSQL;
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit
fi;
if [ $BACKSUCCESS = 'failed' ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
echo " BACKUP FAILED " >> $WORKDIR/$BACKUPLOGFILE
echo " BACKUP ENDED :" >> $WORKDIR/$BACKUPLOGFILE
date >> $WORKDIR/$BACKUPLOGFILE
echo "-------------------------------------------------" >> $WORKDIR/$BACKUPLOGFILE
exit
fi;
#-------------------------------------------------------------#
# Update CYCLE for next backup run : #
# ---------------------------------- #
# #
#-------------------------------------------------------------#
echo "--------------------------------------"
echo "UPDATE CYCLE FOR NEXT BACKUP RUN......"
echo "--------------------------------------"
DBCYCLE=$DBCYCLE+1
if [ DBCYCLE -gt NOFILES ]
then DBCYCLE=1
fi;
echo "--------------------------------"
echo "Resetting DBCYCLE to : $DBCYCLE "
echo "--------------------------------"
svrmgrl << EOF
connect internal
update $DBUSER.ORA_ONLINE_BACKUP set cycle = $DBCYCLE;
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Reset of cycle for next backup failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Reset of cycle for next backup failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
fi;
#
#-------------------------------------------------------------#
# Update backup date in table : ORA_ONLINE_BACKUP_FILES #
# ---------------------------------- #
# #
#-------------------------------------------------------------#
#
echo "--------------------------------------"
echo "UPDATE BACKUP DATE OF FILES IN TABLE.."
echo "--------------------------------------"
cat $WORKDIR/$UPDATEFILE | while read FIL
do
echo "exec ORA_BACKUP_PROC1('$FIL')" >> $WORKDIR/$GENSQL
done
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
#
#
echo "********************************************************"
echo " $ORACLE_SID ONLINE BACKUP COMPLETED SUCCESSFULLY "
echo "********************************************************"
echo "********************************************************" > err.out
echo " $ORACLE_SID ONLINE BACKUP COMPLETED SUCCESSFULLY " >> err.out
echo "********************************************************" >> err.out
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
echo " BACKUP COMPLETED SUCCESSFULLY " >> $WORKDIR/$BACKUPLOGFILE
echo " BACKUP ENDED :" >> $WORKDIR/$BACKUPLOGFILE
date >> $WORKDIR/$BACKUPLOGFILE
echo "-------------------------------------------------" >> $WORKDIR/$BACKUPLOGFILE
#
#
#------------------------------------------------------------#
# T H E E N D #
#------------------------------------------------------------#
#---------------------------------------------------------------#
# Script generates online backup scripts and run the scripts #
# ---------------------------------------------------------- #
# Date : 2000/08/11 #
# ---- #
#---------------------------------------------------------------#
# NOTES : For implementation of script: #
# ------- #
# 1. Create a separate directory where the script resides #
# because the program generates it's own scripts for #
# the run of a backup. #
# 2. Table ORA_ONLINE_BACKUP is created and maintained by #
# this program to run the correct cycle for a specific day #
# when this program is used to break the online backup of the#
# database into more than 1 backup piece. #
# Trigger is also required to ensure only 1 row resides in #
# table. #
# #
# create table ORA_ONLINE_BACKUP #
# ( cycle number(1)); #
# #
# create or replace trigger #
# backup_trigger before insert on ORA_ONLINE_BACKUP #
# for each row #
# declare #
# num_rows number; #
# begin #
# select count(*) into num_rows from ORA_ONLINE_BACKUP; #
# if num_rows > 0 #
# then #
# raise_application_error(-20000, 'Only 1 row allowed in table');#
# end if; #
# end; #
# / #
# 3. Table ORA_ONLINE_BACKUP_FILES is created and maintained #
# for recordkeeping of when files was last backed up. #
# #
# create table ORA_ONLINE_BACKUP_FILES #
# ( file_name varchar2(513), #
# last_backup date ); #
# #
# create unique index ORA_ONLINE_BACKUP_KEY #
# on ORA_ONLINE_BACKUP_FILES ( file_name ); #
# 4. Procedure ORA_BACKUP_PROC1 for updating above table #
# during run of backup. #
# #
# create or replace procedure ORA_BACKUP_PROC1 ( file1 varchar2 ) #
# as #
# begin #
# insert into ora_online_backup_files #
# values ( file1 , sysdate ); #
# commit; #
# exception #
# when dup_val_on_index #
# then update ora_online_backup_files #
# set last_backup = sysdate #
# where file_name = file1; #
# commit; #
# end; #
#/ #
# #
# #
#---------------------------------------------------------------#
# #
# #
# #
# Changes : #
# --------- #
# #
# #
#---------------------------------------------------------------#
#
#
# Working Environment #
# -------------------
#
PROGNAME=onlinebackup.ksh
DBUSER=ops\$backup
DBPASS=backreco
WORKDIR=/usr1/app/oracle/product/8.0.5/home/desibackup
WORKFILE=workfile.in
NEWWORKFILE=newworkfile.in
CONTROLFILE=controlfile.in
UPDATEFILE=updatefile.in
BACKUPCONTROLFILE=$ORACLE_SID''backupcontrolfile.control
TRACECONTROLFILE=$ORACLE_SID''backupcontrolfile.trace
ENDSQL=end.sql
GENSQL=gen.sql
BEGINSQL=begin.sql
NETWORKER=backfile_networker
BACKUPRUN=backfile_run
BACKSUCCESS=success
BACKUPLOGFILE=$ORACLE_SID''_backup.logfile
SYSTEM_001PR=`cat /var/opt/oracle/SYSTEM_001P`
INSTALLFLAG=nono
integer DBCYCLE=0
integer counter1=1
integer counter2=0
integer NOFILES=0
integer DBLOGFILE=0
#
#
#
# -------------------------------------------------------#
# Check whether tables,triggers etc. are installed before#
# run of script. #
# #
#--------------------------------------------------------#
#
echo "--------------------------------------"
echo "STARTING INSTALL OR DATABASE UP CHECK "
echo "--------------------------------------"
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "select '1' from ORA_ONLINE_BACKUP ;" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
INSTALLFLAG=`sqlplus -s $DBUSER/$DBPASS @$WORKDIR/$GENSQL`
rm $WORKDIR/$GENSQL
if [ $INSTALLFLAG = 1 ]
then
echo " "
else
echo "------------------------------------------"
echo "INSTALLFLAG value : $INSTALLFLAG "
echo "------------------------------------------"
echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "Table ORA_ONLINE_BACKUP does not exist or database down !!" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "Table ORA_ONLINE_BACKUP does not exist or database down !!"
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit;
fi;
#
#
#------------------------------------------------------------#
# Check input parameter for amount of backup pieces #
#------------------------------------------------------------#
#
echo "--------------------------------------"
echo "CHECKING INPUT PARAMETER............. "
echo "--------------------------------------"
echo "***************************************************"
echo " Input parameter for no of files : $1"
NOFILES=$1
echo " Run Cycles generated is : $NOFILES "
echo "***************************************************"
#
if [ $NOFILES -eq 0 ]
then
echo "**************************************************"
echo " Mandatory parameter for no of cycles to generate "
echo " was not passed to program : Please restart "
echo " I.E. PROGRAM PARAMETER1 "
echo "**************************************************"
exit
fi;
if [ $NOFILES -gt 9 ]
then
echo "**************************************************"
echo " Parameter for cycles is out of range. Only use "
echo " 1 - 9. "
echo "**************************************************"
exit
fi;
#------------------------------------------------------------#
# Check for already running backup : #
#------------------------------------------------------------#
echo "--------------------------------------"
echo "CHECKING FOR ALREADY RUNNING BACKUP.. "
echo "--------------------------------------"
signalle=yep
integer counter=0
integer counter1=0
while [ $signalle = yep ]
do
counter=$counter+1
counter1=`ps -ef|grep "$PROGNAME" |grep -v grep|grep -v "sh -c" | wc -l`
if [ counter1 -gt 1 ]
then
echo "Another Backup for $ORACLE_SID is busy"
echo "Waiting 5 minutes..... "
sleep 300
else
signalle=norunning
fi;
if [ $counter -gt 48 ]
then
signalle=nono
fi;
done
if [ $signalle = nono ]
then
echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID failed " >> err.out
echo " due to waiting for more than " >> err.out
echo " 4 hours for previous job " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID failed "
echo " due to waiting for more than "
echo " 4 hours for previous job "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit
fi;
#
#
#------------------------------------------------------------#
# S T A R T O F B A C K U P : #
#------------------------------------------------------------#
#
#
echo "--------------------------------------"
echo "STARTING BACKUP...................... "
echo "--------------------------------------"
echo " ----------------------------------------------------- " >> $WORKDIR/$BACKUPLOGFILE
echo " BACKUP STARTED :" >> $WORKDIR/$BACKUPLOGFILE
date >> $WORKDIR/$BACKUPLOGFILE
#
#
#
# -------------------------------------------------------#
# Check for day's cycle in table ORA_ONLINE_BACKUP #
# #
#--------------------------------------------------------#
#
echo "--------------------------------------"
echo "GETTING CYCLE FOR BACKUP RUN......... "
echo "--------------------------------------"
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "select cycle from ORA_ONLINE_BACKUP ;" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
DBCYCLE=`sqlplus -s $DBUSER/$DBPASS @$WORKDIR/$GENSQL`
if [ $? -gt 0 ]
then
echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "Table ORA_ONLINE_BACKUP does not exist or database down !!" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "Table ORA_ONLINE_BACKUP does not exist or database down !!"
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit;
fi;
echo "-------------------"
echo "DBCYCLE : $DBCYCLE "
echo "-------------------"
#
if [ DBCYCLE -gt NOFILES ]
then
DBCYCLE=$NOFILES
echo "-------------------"
echo "NEW DBCYCLE : $DBCYCLE"
echo "-------------------"
fi;
echo " BACKUP CYCLE : $DBCYCLE" >> $WORKDIR/$BACKUPLOGFILE
#
#------------------------------------------------------------#
# Switch database logfile : #
#------------------------------------------------------------#
echo "--------------------------------------"
echo "SWITCHING DATABASE LOGFILE........... "
echo "--------------------------------------"
#
rm $WORKDIR/$GENSQL
svrmgrl << EOF
connect internal
spool $WORKDIR/$GENSQL
alter system switch logfile;
spool off;
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "ERROR : switch logfile failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "ERROR : switch logfile failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
fi;
DBLOGFILE=`cat $WORKDIR/$GENSQL | grep -i ORA-09817 | wc -l`
if [ $DBLOGFILE -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "ERROR : Alert log file system is full!!!! " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "ERROR : Alert log file system is full!!!! "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit
fi;
rm $WORKDIR/$GENSQL
#
#
#------------------------------------------------------------#
# Backup controlfiles to $WORKDIR #
#------------------------------------------------------------#
#
echo "--------------------------------------"
echo "BACKING UP CONTROLFILE TO DIRECTORY.. "
echo "--------------------------------------"
rm $WORKDIR/$BACKUPCONTROLFILE
echo "-------------------------------------------------------"
echo " Backing up controlfile : $BACKUPCONTROLFILE"
echo "-------------------------------------------------------"
#
svrmgrl << EOF
connect internal
alter database backup controlfile to '$WORKDIR/$BACKUPCONTROLFILE';
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "ERROR : Backup of controlfile failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "ERROR : Backup of controlfile failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
fi;
#
#
#
#
#
#------------------------------------------------------------#
# Backup controlfiles to TRACE #
#------------------------------------------------------------#
#
echo "--------------------------------------"
echo "BACKING UP CONTROLFILE TO TRACE...... "
echo "--------------------------------------"
rm $WORKDIR/$TRACECONTROLFILE
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "alter database backup controlfile to trace;" >> $WORKDIR/$GENSQL
echo "select rtrim(spid) from v\$process , v\$session" >> $WORKDIR/$GENSQL
echo " where paddr = addr and" >> $WORKDIR/$GENSQL
echo "sid in ( select sid from v\$session where " >> $WORKDIR/$GENSQL
echo " audsid = userenv('sessionid'));" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
UPROCESSID=`sqlplus -s system/$SYSTEM_001PR @$WORKDIR/$GENSQL`
rm $WORKDIR/$GENSQL
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "select rtrim(value) from v\$parameter where name = 'user_dump_dest';" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
USERDUMPDEST=`sqlplus -s system/$SYSTEM_001PR @$WORKDIR/$GENSQL`
rm $WORKDIR/$GENSQL
cp $USERDUMPDEST"/"$ORACLE_SID""_ora_""$UPROCESSID.trc $WORKDIR/$TRACECONTROLFILE
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID " >> err.out
echo "ERROR : Copy of controlfile from trace to " >> err.out
echo "ERROR : trace copy in workdir failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID "
echo "ERROR : Copy of controlfile from trace to "
echo "ERROR : trace copy in workdir failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
fi;
#
#------------------------------------------------------------#
# Generate workfiles and sql's for backup run #
#------------------------------------------------------------#
#
#------------------------------------------------------------#
# List of datafiles: #
# --------------------- #
#
echo "--------------------------------------"
echo "GENERATING WORKFILES................. "
echo "--------------------------------------"
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "set wrap off " >> $WORKDIR/$GENSQL
echo "set lines 133" >> $WORKDIR/$GENSQL
echo "spool $WORKDIR/$WORKFILE" >> $WORKDIR/$GENSQL
echo "select rtrim(tablespace_name,' ')||' '||rtrim(a.file_name,' ')||' '||a.bytes||' '||'*'||substr(file_name,instr(file_name,'.',1,1)) from dba_data_files a, v\$datafile b where file_id = file# and to_char(trunc(creation_time),'yyyy-mm-dd') < to_char(trunc(sysdate),'yyyy-mm-dd') and a.file_name not in ( select file_name from ORA_ONLINE_BACKUP_FILES c where ( trunc(sysdate) - trunc(last_backup) ) > $NOFILES ) order by a.bytes desc,tablespace_name;" >> $WORKDIR/$GENSQL
echo "spool off" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
#
#
# List of new datafiles created on day of run of backup and
# datafiles that has not been backed up due to creation of
# new datafiles causing the generated backup run file to
# shuffle a lot.
#-------------------------------------------------------
#
#
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "set wrap off " >> $WORKDIR/$GENSQL
echo "set lines 133" >> $WORKDIR/$GENSQL
echo "spool $WORKDIR/$NEWWORKFILE" >> $WORKDIR/$GENSQL
echo "select rtrim(tablespace_name,' ')||' '||rtrim(file_name,' ')||' '||a.bytes||' '||'*'||substr(file_name,instr(file_name,'.',1,1)) from dba_data_files a, v\$datafile b where file_id = file# and to_char(trunc(creation_time),'yyyy-mm-dd') = to_char(trunc(sysdate),'yyyy-mm-dd') order by a.bytes desc,tablespace_name;" >> $WORKDIR/$GENSQL
echo "select rtrim(tablespace_name,' ')||' '||rtrim(a.file_name,' ')||' '||a.bytes||' '||'*'||substr(file_name,instr(file_name,'.',1,1)) from dba_data_files a, v\$datafile b where file_id = file# and a.file_name in ( select file_name from ORA_ONLINE_BACKUP_FILES c where ( trunc(sysdate) - trunc(last_backup) ) > $NOFILES ) order by a.bytes desc,tablespace_name;" >> $WORKDIR/$GENSQL
echo "spool off" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
#
#
#
#------------------------------------------------------------#
# Begin and end backup sql scripts : #
# ---------------------------------- #
#
echo "--------------------------------------"
echo "GENERATING BEGIN AND END SQL......... "
echo "--------------------------------------"
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "spool $WORKDIR/$ENDSQL" >> $WORKDIR/$GENSQL
echo "select 'alter tablespace '||tablespace_name||' end backup;' from dba_tablespaces;" >> $WORKDIR/$GENSQL
echo "spool off" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
echo "set heading off" > $WORKDIR/$GENSQL
echo "set pages 0" >> $WORKDIR/$GENSQL
echo "set feed off " >> $WORKDIR/$GENSQL
echo "set echo off" >> $WORKDIR/$GENSQL
echo "spool $WORKDIR/$BEGINSQL" >> $WORKDIR/$GENSQL
echo "select 'alter tablespace '||tablespace_name||' begin backup;' from dba_tablespaces;" >> $WORKDIR/$GENSQL
echo "spool off" >> $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
echo "exit;" >> $WORKDIR/$ENDSQL
echo "exit;" >> $WORKDIR/$BEGINSQL
#
#------------------------------------------------------------#
# Generate networker and backup run file for backup : #
# --------------------------------------------------- #
#
# Current files :
# ---------------
#
#
echo "--------------------------------------"
echo "GENERATING NETWORKER/BACKUP RUN FILE.."
echo "--------------------------------------"
integer counter1=1
integer counter2=0
rm $WORKDIR/$NETWORKER
rm $WORKDIR/$BACKUPRUN
rm $WORKDIR/$UPDATEFILE
backfilerun=""
cat $WORKDIR/$WORKFILE | while read TB DF DFSIZE DFSUFF
do
if [ $counter1 -eq $DBCYCLE ]
then backfilerun=$backfilerun" "$DF
echo "$DF" >> $WORKDIR/$UPDATEFILE
echo "<< $DF >>" >> $WORKDIR/$NETWORKER
echo " +compressasm: "$DFSUFF >> $WORKDIR/$NETWORKER
echo " " >> $WORKDIR/$NETWORKER
fi;
if [ $counter2 -eq 0 ] ;
then counter1=$counter1+1 ; fi
if [ $counter2 -eq 1 ] ;
then counter1=$counter1-1 ; fi
if [ $counter1 -eq $NOFILES+1 ] -a [ $counter2 -eq 0 ] ;
then counter1=$counter1-1
counter2=1 ; fi
if [ $counter1 -eq 0 ] -a [ $counter2 -eq 1 ] ;
then counter1=$counter1+1
counter2=0 ; fi
done
backfilerun=$backfilerun" "$WORKDIR/$BACKUPCONTROLFILE
backfilerun=$backfilerun" "$WORKDIR/$TRACECONTROLFILE
echo "<< $WORKDIR/$BACKUPCONTROLFILE >>" >> $WORKDIR/$NETWORKER
echo " +compressasm: *.control" >> $WORKDIR/$NETWORKER
echo " " >> $WORKDIR/$NETWORKER
echo "<< $WORKDIR/$TRACECONTROLFILE >>" >> $WORKDIR/$NETWORKER
echo " +compressasm: *.trace" >> $WORKDIR/$NETWORKER
echo " " >> $WORKDIR/$NETWORKER
#
#
# Newlyadded files :
# ------------------
#
#
#
cat $WORKDIR/$NEWWORKFILE | while read TB DF DFSIZE DFSUFF
do
backfilerun=$backfilerun" "$DF
echo "$DF" >> $WORKDIR/$UPDATEFILE
echo "<< $DF >>" >> $WORKDIR/$NETWORKER
echo " +compressasm: "$DFSUFF >> $WORKDIR/$NETWORKER
echo " " >> $WORKDIR/$NETWORKER
done
echo "/usr/bin/nsr/save -i -f $WORKDIR/$NETWORKER $backfilerun" > $WORKDIR/$BACKUPRUN
#
#------------------------------------------------------------#
# Run of actual backup of files to tape : #
#------------------------------------------------------------#
#
echo "--------------------------------------"
echo "STARTING ACTUAL RUN OF BACKUP........."
echo "--------------------------------------"
svrmgrl << EOF
connect internal
@$WORKDIR/$ENDSQL;
EOF
svrmgrl << EOF
connect internal
@$WORKDIR/$BEGINSQL;
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
echo " BACKUP FAILED " >> $WORKDIR/$BACKUPLOGFILE
echo " BACKUP ENDED :" >> $WORKDIR/$BACKUPLOGFILE
date >> $WORKDIR/$BACKUPLOGFILE
echo "-------------------------------------------------" >> $WORKDIR/$BACKUPLOGFILE
exit
fi;
#
echo " "
echo " Starting save to tape..........."
echo " "
echo " "
chmod 755 $WORKDIR/$BACKUPRUN
#
#
echo "--------------------------------------"
echo "SAVING TO TAPE........................"
echo "--------------------------------------"
SLEEPFLAG=on
BACKSUCCESS=nono
integer counter=0
#
while [ $BACKSUCCESS = nono ]
do
$WORKDIR/$BACKUPRUN
if [ $? -gt 0 ]
then
echo "---------------------------------"
echo "save failed : Retrying .........."
echo "---------------------------------"
else
BACKSUCCESS=success
SLEEPFLAG=off
counter=$counter+10
fi;
if [ $SLEEPFLAG = on ]
then
echo "sleeping for 15 minutes"
SLEEPFLAG=off
sleep 900
fi;
counter=$counter+1
if [ counter -eq 2 ]
then
echo " backup failed "
BACKSUCCESS=failed
fi;
done
svrmgrl << EOF
connect internal
@$WORKDIR/$ENDSQL;
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
exit
fi;
if [ $BACKSUCCESS = 'failed' ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Online backup of $ORACLE_SID failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Online backup of $ORACLE_SID failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
echo " BACKUP FAILED " >> $WORKDIR/$BACKUPLOGFILE
echo " BACKUP ENDED :" >> $WORKDIR/$BACKUPLOGFILE
date >> $WORKDIR/$BACKUPLOGFILE
echo "-------------------------------------------------" >> $WORKDIR/$BACKUPLOGFILE
exit
fi;
#-------------------------------------------------------------#
# Update CYCLE for next backup run : #
# ---------------------------------- #
# #
#-------------------------------------------------------------#
echo "--------------------------------------"
echo "UPDATE CYCLE FOR NEXT BACKUP RUN......"
echo "--------------------------------------"
DBCYCLE=$DBCYCLE+1
if [ DBCYCLE -gt NOFILES ]
then DBCYCLE=1
fi;
echo "--------------------------------"
echo "Resetting DBCYCLE to : $DBCYCLE "
echo "--------------------------------"
svrmgrl << EOF
connect internal
update $DBUSER.ORA_ONLINE_BACKUP set cycle = $DBCYCLE;
EOF
if [ $? -gt 0 ]
then echo "++++++++++++++++++++++++++++++++++++++++++++" > err.out
echo "ERROR : Reset of cycle for next backup failed " >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++" >> err.out
echo "++++++++++++++++++++++++++++++++++++++++++++"
echo "ERROR : Reset of cycle for next backup failed "
echo "++++++++++++++++++++++++++++++++++++++++++++"
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
fi;
#
#-------------------------------------------------------------#
# Update backup date in table : ORA_ONLINE_BACKUP_FILES #
# ---------------------------------- #
# #
#-------------------------------------------------------------#
#
echo "--------------------------------------"
echo "UPDATE BACKUP DATE OF FILES IN TABLE.."
echo "--------------------------------------"
cat $WORKDIR/$UPDATEFILE | while read FIL
do
echo "exec ORA_BACKUP_PROC1('$FIL')" >> $WORKDIR/$GENSQL
done
echo "exit;" >> $WORKDIR/$GENSQL
sqlplus $DBUSER/$DBPASS @$WORKDIR/$GENSQL
rm $WORKDIR/$GENSQL
#
#
echo "********************************************************"
echo " $ORACLE_SID ONLINE BACKUP COMPLETED SUCCESSFULLY "
echo "********************************************************"
echo "********************************************************" > err.out
echo " $ORACLE_SID ONLINE BACKUP COMPLETED SUCCESSFULLY " >> err.out
echo "********************************************************" >> err.out
mailx -s "`uname -n`:$ORACLE_SID: Online Backup" oradba < err.out
rm err.out
echo " BACKUP COMPLETED SUCCESSFULLY " >> $WORKDIR/$BACKUPLOGFILE
echo " BACKUP ENDED :" >> $WORKDIR/$BACKUPLOGFILE
date >> $WORKDIR/$BACKUPLOGFILE
echo "-------------------------------------------------" >> $WORKDIR/$BACKUPLOGFILE
#
#
#------------------------------------------------------------#
# T H E E N D #
#------------------------------------------------------------#
기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… | 12-27 | 2022 | ||
[채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취… | 12-11 | 1478 | ||
53 | [평일주간]100%환급6건,안드로이드,자바,C#,스프링3.2,SQL,힌트/… | 03-15 | 1233 | |
52 | [기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍 | 01-31 | 1392 | |
51 | [평일,기업100%환급]SQL기초에서 Schema Object까지 | 01-31 | 1254 | |
50 | [평일야간]HTML5, CSS3,Ajax, jQuery마스터과정 | 01-31 | 1083 | |
49 | [평일주간,평일야간,주말]Spring,MyBatis,Hibernate개발자과정 | 01-19 | 1395 | |
48 | [평일주간,평일야간,주말]안드로이드개발자과정 | 01-11 | 1225 | |
47 | [평일야간,주말주간]JAVA,Network&JSP&Spring,MyBatis,Hibernate | 01-03 | 1724 | |
46 | 기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… | 12-27 | 2022 | |
45 | [기업100%환급,평일주간]자바기초에서 JDBC, Servlet/JSP까지 | 12-19 | 1491 | |
44 | [평일야간, 주말]웹퍼블리싱 마스터(HTML5,CSS3,jQUERY,AJAX,Jav… | 12-14 | 1468 | |
43 | [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취… | 12-11 | 1478 | |
42 | [평일,기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 | 12-09 | 1182 | |
41 | [평일야간, 주말]닷넷(C#,Network,ADO.NET,ASP.NET)마스터 | 12-01 | 1396 | |
40 | [기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍(평일주간(단기)… | 12-01 | 1544 | |
39 | [평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝) | 12-01 | 1050 |
댓글 없음:
댓글 쓰기