#

#!/bin/ksh -p

# Author: Gilbert Standen

# Credits:

# http://www.injunea.demon.co.uk/frames/frm205.htm (Richard's Demon Shell Site) if [ c1 ] || [ c2 ] (OR in an if stmt)

# Larry Bell "echo sql; quit " | sqlplus "/ as sysdba" | post-query processing and additional shell scripts

# nmeyers@javalinux.net at http://www.blu.org/pipermail/discuss/2003-December/044329.html for: tr -d '\012' also 015

# Dan McEwen use of array variables (for i in ${ts_status} )

# http://www.informatik.uni-frankfurt.de/RBI/hp-service/patches/s700_800/11.X/PHCO_29042.txt patch for ps -efx fix

# Mitch Clem, production support.

#####################################################################################################################

# and a.tablespace_name in ('SYSTEM','AHLD','APPLSYSD','APPLSYSX','BNED','XTRX')

[ "${DEBUG}" = "TRUE" ] && set -x

export xx=$7

cd /home/orapf"$xx"

. .profile

cd /home/orapf"$xx"/osbmenu

. ${ORACLE_HOME}/pfcs"$xx".env

export ORACLE_HOME=$1

export ORACLE_SID=$2

export ext1=$3

export TMPFILE=$4

export RUNNING_FILE=$5

export LOG_FILE=$6

export DBA_LOG=/home/orapf"$xx"/osbmenu/log/dba_backup_log.$ext1

export dest=/mnt/pfcs/pfcs"$xx"/HOTBACKUP/backup/

export dest_prev=/mnt/pfcs/pfcs"$xx"/HOTBACKUP/backup2/

export dest_files=/mnt/pfcs/pfcs"$xx"/HOTBACKUP/tablespace_copy_scripts/

export recovery_file=$dest_files/recovery.ksh

export special_recovery_file=$dest_files/special_recovery.$ext1.ksh

echo "set pages 0 lines 512 head off verify off feed off

select a.tablespace_name||':'||

a.file_name||':'||

substr( a.file_name, instr( a.file_name, '/', -1, 1 ) + 1, length( a.file_name ))||':'||

NVL(b.status, 'UNKNOWN STATUS')

from sys.dba_data_files a, sys.v\$backup b

where a.file_id = b.file#(+)

order by a.tablespace_name, a.file_name;

quit

echo ${ts_status} | cut -f1 -d':' | read tsp

# $MNT is the mount point where the datafiles are located. MNT is only used in the grep commands to check for

# running processes.

# If you go to multiple mount points for database files you would change the definition of MNT as follows:

# export MNT="/mnt/pfcs/LOC1/pfcs$xx|/mnt/pfcs/LOC2/pfcs$xx" (using a "|" in between each value) so that the

# egrep command will look for all of them (all the database datafile mount points)

export MNT="/mnt/pfcs/pfcs$xx"

# $allfiles is set to null at the beginning.

export allfiles=

let j=0

let n=5

# n=allowed number of parallel gzip processes occuring simultaneously.

# adjust n to the level your system can handle. If you add processors and read/write heads, you can up n higher.

let m=5

# m=allowed number of parallel tablespace copy shell scripts executing simultaneously.

# adjust m to the level your system can handle. If you add processors and read/write heads, you can up m higher.

rm ${TMPFILE} > /dev/null 2>&1

touch ${TMPFILE}

rm ${TMPFILE} > /dev/null 2>&1

echo "#!/bin/ksh -p" > ${TMPFILE}

echo "#!/bin/ksh -p" > $recovery_file

echo "#!/bin/ksh -p" > $special_recovery_file

echo "# this is a dynamically generated backup command file from Oracle" >> ${TMPFILE}

echo "# this is a dynamically generated recovery command file from Oracle" >> $recovery_file

echo "# this is a dynamically generated recovery command file from Oracle" >> $special_recovery_file

echo "cd /home/orapf$xx" >> ${TMPFILE}

echo "cd /home/orapf$xx" >> $recovery_file

echo "cd /home/orapf$xx" >> $special_recovery_file

echo '. .profile' >> ${TMPFILE}

echo '. .profile' >> $recovery_file

echo '. .profile' >> $special_recovery_file

echo "cd /home/orapf$xx/osbmenu" >> ${TMPFILE}

echo "cd /home/orapf$xx/osbmenu" >> $recovery_file

echo "cd /home/orapf$xx/osbmenu" >> $special_recovery_file

chmod +x ${TMPFILE}

chmod +x $recovery_file

chmod +x $special_recovery_file

for i in ${ts_status}

echo $i | cut -f1 -d':' | read tsname

echo $i | cut -f2 -d':' | read lfname

echo $i | cut -f3 -d':' | read sfname

echo $i | cut -f4 -d':' | read fstatus

if [ ${tsname} != $tsp ] || [ $j -eq 0 ]

then

if [ "$j" -gt 0 ]

then

echo ' ' >> ${TMPFILE}

echo "export allfiles=\"'$allfiles'\"" >> ${TMPFILE}

#echo "echo '$allfiles' >> $5" >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | grep -ic '$tsp' | read afc1" >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | egrep -c '$allfiles' | read afc2" >> ${TMPFILE}

echo 'while [ $afc1 -gt 0 ] || [ $afc2 -gt 0 ]' >> ${TMPFILE}

echo 'do' >> ${TMPFILE}

echo 'sleep 2' >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | grep -ic '$tsp' | read afc1" >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | egrep -c '$allfiles' | read afc2" >> ${TMPFILE}

#echo "ps -ef | grep 'cat' | egrep '$MNT' | grep -i '$tsp' >> $5" >> ${TMPFILE}

#echo "ps -ef | grep 'cat' | egrep '$MNT' | egrep '$allfiles' >> $5" >> ${TMPFILE}

echo 'done' >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | egrep -c '$allfiles' | read afc3" >> ${TMPFILE}

#echo 'ps -ef to check for running sh scripts just executed at line 89'

echo 'while [ $afc3 -gt 0 ]' >> ${TMPFILE}

echo 'do' >> ${TMPFILE}

echo 'sleep 2' >> ${TMPFILE}

echo "ps -ef | grep 'cat $MNT' | egrep -c '$allfiles' | read afc3" >> ${TMPFILE}

#echo 'ps -ef to check for running sh scripts just executed at line 95'

echo 'done' >> ${TMPFILE}

echo "ls -l $dest | grep 'gz' | egrep '$allfiles' >> $5" >> ${TMPFILE}

echo 'echo "alter tablespace '${tsp}' end backup;' >> ${TMPFILE}

echo 'quit' >> ${TMPFILE}

echo '" | sqlplus -s "/ as sysdba" | sed '\'/^$/d\'' | tr -d '\''\\'012\'' | tr -d '\''\\'015\'' | read tstatus2' >> ${TMPFILE}

echo 'echo $tstatus2 | sed '\'s/\\.//\'' | sed '\'s/ //\'' | read tstatus' >> ${TMPFILE}

#echo 'echo $tstatus2' >> ${TMPFILE}

#echo 'echo $tstatus' >> ${TMPFILE}

echo 'if [ $tstatus = '\'Tablespacealtered\'' ]' >> ${TMPFILE}

echo 'then' >> ${TMPFILE}

echo 'export mode='\'IMPLIED INACTIVE\''' >> ${TMPFILE}

#echo 'echo $mode' >> ${TMPFILE}

echo 'fi' >> ${TMPFILE}

echo "date '+%Y-%m-%d %X' | read stamp" >> ${TMPFILE}

echo 'echo "$stamp End backup executed for tablespace '"$tsp" zzz\"' >> '$5 >> ${TMPFILE}

echo 'echo "$stamp Tablespace '"$tsp" left in '$mode' mode.\"' >> '$5 >> ${TMPFILE}

cp ${TMPFILE} ${dest_files}${tsp}'.sh'

date '+%Y-%m-%d %X' | read stamp

echo "$stamp OS copy commands executed for tablespace $tsp zzz" >> $5

# Added to make sure that there are not more than m copy shell scripts executing at once GLS 04/02/11.

ps -ef | grep -i 'backup' | grep "pfcs$xx" | grep -c 'tablespace_copy_scripts' | read pfc1

#echo 'ps -ef to check for running sh scripts just executed at line 115'

while [ $pfc1 -gt $m ]

sleep 2

ps -ef | grep -i 'backup' | grep "pfcs$xx" | grep -c 'tablespace_copy_scripts' | read pfc1

#echo 'ps -ef to check for running sh scripts just executed at line 120'

done

${dest_files}${tsp}'.sh' &

rm ${TMPFILE} > /dev/null 2>&1

echo "#!/bin/ksh -p" > ${TMPFILE}

echo "# this is a dynamically generated command file from Oracle" >> ${TMPFILE}

echo "cd /home/orapf$xx" >> ${TMPFILE}

echo '. .profile' >> ${TMPFILE}

echo "cd /home/orapf$xx/osbmenu" >> ${TMPFILE}

chmod +x ${TMPFILE}

export tsp=${tsname}

if [ ${fstatus} = 'NOTACTIVE' ]

then

echo "alter tablespace ${tsname} begin backup;

quit

" | sqlplus -s "/ as sysdba" | sed '/^$/d' | tr -d '\012' | tr -d '\015' | read tstatus2

echo $tstatus2 | sed 's/\.//' | sed 's/ //' | read tstatus

if [ $tstatus = 'Tablespacealtered' ]

then

export mode='IMPLIED ACTIVE'

date '+%Y-%m-%d %X' | read stamp

echo "$stamp Begin backup executed for tablespace $tsname zzz" >> $5

echo "$stamp Tablespace $tsname in $mode online backup mode." >> $5

elif [ ${fstatus} = 'OFFLINE NORMAL' ]

then

echo 'Error: '${sfname}' in '${fstatus}' status' >> $5

write_special_recovery_script_v1.ksh $2 $n $3 $4 $xx $lfname $sfname $special_recovery_file $5

elif [ ${fstatus} = 'ACTIVE' ]

then

echo 'Warning: '${sfname}' in prior '${fstatus}' status: Extra archivelogs may be needed for recovery.' >> $5

elif [ ${fstatus} = 'UNKNOWN STATUS' ]

then

write_special_recovery_script_v1.ksh $2 $n $3 $4 $xx $lfname $sfname $special_recovery_file $5

else

echo 'Possible Error: '${sfname}' has status of '${fstatus} >> $5

write_special_recovery_script_v1.ksh $2 $n $3 $4 $xx $lfname $sfname $special_recovery_file $5

write_backup_script_v9.ksh $2 $n $3 $4 $xx $lfname $sfname $tstatus $5

write_recovery_script_v2.ksh $2 $n $3 $4 $xx $lfname $sfname $recovery_file $5

date '+%Y-%m-%d %X' | read stamp

echo "$stamp File $sfname added to OS copy script for tablespace $tsname zzz" >> $5

export allfiles=${sfname}

else

date '+%Y-%m-%d %X' | read stamp

write_backup_script_v9.ksh $2 $n $3 $4 $xx $lfname $sfname $tstatus $5

write_recovery_script_v2.ksh $2 $n $3 $4 $xx $lfname $sfname $recovery_file $5

echo "$stamp File $sfname added to OS copy script for tablespace $tsname zzz" >> $5

if [ $j -eq 0 ]

then

export allfiles=${sfname}

else

export allfiles=$allfiles'|'${sfname}

let j=$j+1

done

echo ' ' >> ${TMPFILE}

echo "export allfiles=\"'$allfiles'\"" >> ${TMPFILE}

#echo "echo '$allfiles' >> $5" >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | grep -ic '$tsname' | read afc1" >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | egrep -c '$allfiles' | read afc2" >> ${TMPFILE}

echo 'while [ $afc1 -gt 0 ] || [ $afc2 -gt 0 ]' >> ${TMPFILE}

echo 'do' >> ${TMPFILE}

echo 'sleep 2' >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | grep -ic '$tsname' | read afc1" >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | egrep -c '$allfiles' | read afc2" >> ${TMPFILE}

#echo "ps -ef | grep 'cat' | egrep '$MNT' | grep -i '$tsname' >> $5" >> ${TMPFILE}

#echo "ps -ef | grep 'cat' | egrep '$MNT' | egrep '$allfiles' >> $5" >> ${TMPFILE}

echo 'done' >> ${TMPFILE}

echo "ps -ef | grep 'cat' | egrep '$MNT' | egrep -c '$allfiles' | read afc3" >> ${TMPFILE}

#echo 'ps -ef to check for running sh scripts just executed at line 89'

echo 'while [ $afc3 -gt 0 ]' >> ${TMPFILE}

echo 'do' >> ${TMPFILE}

echo 'sleep 2' >> ${TMPFILE}

echo "ps -ef | grep 'cat $MNT' | egrep -c '$allfiles' | read afc3" >> ${TMPFILE}

#echo 'ps -ef to check for running sh scripts just executed at line 95'

echo 'done' >> ${TMPFILE}

echo "ls -l $dest | grep 'gz' | egrep '$allfiles' >> $5" >> ${TMPFILE}

echo 'echo "alter tablespace '${tsname}' end backup;' >> ${TMPFILE}

echo 'quit' >> ${TMPFILE}

echo '" | sqlplus -s "/ as sysdba" | sed '\'/^$/d\'' | tr -d '\''\\'012\'' | tr -d '\''\\'015\'' | read tstatus2' >> ${TMPFILE}

echo 'echo $tstatus2 | sed '\'s/\\.//\'' | sed '\'s/ //\'' | read tstatus' >> ${TMPFILE}

#echo 'echo $tstatus2' >> ${TMPFILE}

#echo 'echo $tstatus' >> ${TMPFILE}

echo 'if [ $tstatus = '\'Tablespacealtered\'' ]' >> ${TMPFILE}

echo 'then' >> ${TMPFILE}

echo 'export mode='\'IMPLIED INACTIVE\''' >> ${TMPFILE}

#echo 'echo $mode' >> ${TMPFILE}

echo 'fi' >> ${TMPFILE}

echo "date '+%Y-%m-%d %X' | read stamp" >> ${TMPFILE}

echo 'echo "$stamp End backup executed for tablespace '"$tsname" zzz\"' >> '$5 >> ${TMPFILE}

echo 'echo "$stamp Tablespace '"$tsname" left in '$mode' mode.\"' >> '$5 >> ${TMPFILE}

cp ${TMPFILE} ${dest_files}${tsname}'.sh'

date '+%Y-%m-%d %X' | read stamp

echo "$stamp OS copy commands executed for tablespace $tsname zzz" >> $5

# Added to make sure that there are not more than m copy shell scripts executing at once GLS 04/02/11.

ps -ef | grep -i 'backup' | grep "pfcs$xx" | grep -c 'tablespace_copy_scripts' | read pfc2

#echo 'ps -ef to check for running sh scripts just executed at line 194'

while [ $pfc2 -gt $m ]

sleep 2

ps -ef | grep -i 'backup' | grep "pfcs$xx" | grep -c 'tablespace_copy_scripts' | read pfc2

#echo 'ps -ef to check for running sh scripts just executed at line 199'

done

${dest_files}${tsname}'.sh' &

# First check uses ps -ef and does not look for tablespace_copy_scripts to avoid pstat failure error.

ps -ef | grep -i 'backup' | grep -c "pfcs$xx" | read pfc3

while [ $pfc3 -gt 0 ]

sleep 2

ps -ef | grep -i 'backup' | grep -c "pfcs$xx" | read pfc3

#ps -ef | grep -i 'backup' | grep "pfcs$xx" >> $5

done

# Final check should use ps -efx after OS patch is applied (see script header) to verify procs complete.

ps -ef | grep -i 'backup' | grep "pfcs$xx" | grep -c 'tablespace_copy_scripts' | read pfc4

#echo 'ps -ef to check for running sh scripts just executed at line 215'

while [ $pfc4 -gt 0 ]

sleep 2

ps -ef | grep -i 'backup' | grep "pfcs$xx" | grep -c 'tablespace_copy_scripts' | read pfc4

#echo 'ps -ef to check for running sh scripts just executed at line 220'

done

if [ -r $DBA_LOG ]

then

rm $DBA_LOG

echo ${ts_status} | cut -f1 -d':' | sed 's/$/ zzz/' | read tsn1

more $5 | grep "$tsn1" >> $DBA_LOG

for i in ${ts_status}

echo $i | cut -f1 -d':' | sed 's/$/ zzz/' | read tslogrec

#echo "$tslogrec"

if [ "$tslogrec" != "$tsn1" ]

then

#echo "$tslogrec" "$tsn1"

more $5 | grep "$tslogrec" >> $DBA_LOG

export tsn1="$tslogrec"

done

more $DBA_LOG | sed 's/ zzz//' > /home/orapf"$xx"/osbmenu/temp_log

mv /home/orapf"$xx"/osbmenu/temp_log $DBA_LOG

more $5 | sed 's/ zzz//' > /home/orapf"$xx"/osbmenu/temp_log

mv /home/orapf"$xx"/osbmenu/temp_log $5

exit