# Script: dbbackup.shl
# Purpose: Perform nightly backup processing, including exporting PROD,
#          creating table/index descriptions, creating a PROD textual
#          control file, generating zip (compression) and unzip scripts
#          and the list of all zipped files to be backed up for all of
#          the databases, zipping those database files, backing up the
#          results, removing old archivelogs, and generating statistics
#          for PROD.
# Author: Stephen Rea <srea@maristream.org>
#         Maristream, Inc.
# Updates:
# 2/10/00 - Run zip commands in background in parallel in sets of 10 (to not
#           exceed maximum number of processes) (should cut zip time by 75%).
# 2/22/00 - Skip import step (table/index descriptions) if export step failed.
# 4/14/00 - Shut down and start up the daemon system command listener. 
# 3/9/01  - Skip export step.
# 5/21/01 - Export to gzip file using pipe.  Add the zipped export file to
#           the end of the tape backup file list.
# 5/29/01 - Move the export step to after the datafile zip step to make sure
#           the datafiles get backed up if run out of disk space on export.
#           Also, delete the export file (for now) after tape backup so that
#           the other backups don't run out of space.
# 2/19/02 - E-mail Overview and Index Stats instead of printing them.
# 7/25/02 - Also include zipped archive logs.
# 11/30/04 - Changed export to use sys instead of system to get around FGAC
#            problem (FAQ 11766) while exporting GUBOBJS.
#
PATH=/usr/bin:/usr/lbin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:.
export PATH
#
# Set default ORACLE_SID to PROD.
#
export ORACLE_SID=PROD
ORAENV_ASK=NO
. /usr/lbin/oraenv
# #! /bin/ksh
#
# Empty today's zip directories (<sid>.gzip#, where # is 1 or 2, whichever
# has the older backup).
#
# export bkupnum=`date +%w`
if [ /u05/oradata/PROD.gzip1/unzipem.shl -nt /u05/oradata/PROD.gzip2/unzipem.shl ]
then export bkupnum=2
else export bkupnum=1
fi
find /u05/oradata/PROD.gzip${bkupnum} -type f -exec rm {} \;
find /u05/oradata/PPRD.gzip${bkupnum} -type f -exec rm {} \;
find /u05/oradata/TRNG.gzip${bkupnum} -type f -exec rm {} \;
find /u05/oradata/SEED.gzip${bkupnum} -type f -exec rm {} \;
#
if [ -a /u05/oradata/prod1.dmp.gz ]
then rm -f /u05/oradata/prod1.dmp.gz
fi
if [ -a /u05/oradata/prod2.dmp.gz ]
then rm -f /u05/oradata/prod2.dmp.gz
fi
#
# Generate overview statistics for PROD.  Then, export PROD to prod#.dmp.
# Also, create a table and index description file, which can be used to
# recreate the tables and/or indexes, if needed, along with the textual
# control file for PROD.  These steps are done only if PROD is currently
# up and running.  (These are not part of the tape backup itself.)
#
ps -ef | grep ora_smon_PROD | grep -v grep >/dummy
if [ $? -eq 0 ]; then
#
echo `date +"%D %T"`: Generating PROD overview statistics in \
/u05/oradata/prod${bkupnum}.overview.lst ...
echo systempassword | sqlplus -s system @dbb_overview.sql
cp -p overview.lst /u05/oradata/prod${bkupnum}.overview.lst
# qprt /u05/oradata/prod${bkupnum}.overview.lst
mail -s "Nightly Backup PROD Overview" myname@myuniversity.edu < /u05/oradata/prod${bkupnum}.overview.lst
#
# echo `date +"%D %T"`: Zipping /u05/oradata/prod${bkupnum}.dmp in background ... 
rm dbb_multizip*.lst
# Start at 100 to allow printing of 900 multizip listings in name sorted order.
export mz=100
# gzip -Nvf /u05/oradata/prod${bkupnum}.dmp >dbb_multizip$mz.lst 2>&1 &
#
echo `date +"%D %T"`: Creating PROD textual control file in \
/u00/oradata/PROD/udump ...
echo systempassword | sqlplus -s system @dbb_ctlfile.sql
fi
#
# Create the list of PROD, PPRD, TRNG, and SEED datafiles and other files
# that need to be backed up (actually, their zipped versions), along with
# the zip and unzip scripts to compress those files before backing them
# up and to uncompress them if they need to be restored.
#
echo `date +"%D %T"`: Creating list of files to be backed up ...
sh dbb_gen_all.shl
#
# Create the individual unzip scripts for each of the zipped databases.
#
cat unzipem.shl | grep PROD >/u05/oradata/PROD.gzip${bkupnum}/unzipem.shl
cat unzipem.shl | grep PPRD >/u05/oradata/PPRD.gzip${bkupnum}/unzipem.shl
cat unzipem.shl | grep TRNG >/u05/oradata/TRNG.gzip${bkupnum}/unzipem.shl
cat unzipem.shl | grep SEED >/u05/oradata/SEED.gzip${bkupnum}/unzipem.shl
#
# Shut down the database for a cold backup, zip (compress) the datafiles,
# archive logs, and other database files, and back up the resulting list.
#
echo `date +"%D %T"`: Shutting down databases for backup ...
# echo systempassword | sqlplus -s system @dbb_daemon_stop.sql
# sleep 5
dbshut
sleep 15
echo `date +"%D %T"`: Zipping database files in background sets of 10 ...
echo wait >>zipem.shl
sh zipem.shl
wait
cat dbb_multizip*.lst
rm dbb_multizip*.lst
echo `date +"%D %T"`: Restarting databases ...
sleep 15
dbstart
# sleep 5
# sh dbb_daemon_start.shl
echo `date +"%D %T"`: User Oracle not allowed to restart jobsub
# echo `date +"%D %T"`: Restarting jobsub ...
# sleep 15
# su - jobsub "-c sh start_jobsub.shl"
# sleep 15
#
echo `date +"%D %T"`: Exporting PROD to /u05/oradata/prod${bkupnum}.dmp.gz ...
mknod /tmp/exp_pipe p
gzip -cNf </tmp/exp_pipe >/u05/oradata/prod${bkupnum}.dmp.gz &
echo syspassword | exp "'sys as sysdba'" file=/tmp/exp_pipe full=y compress=n \
  log=/u05/oradata/prod${bkupnum}.dmp.log >/dummy 2>/dummy
rm -f /tmp/exp_pipe
tail -1 /u05/oradata/prod${bkupnum}.dmp.log
if [ -a /u05/oradata/prod${bkupnum}.dmp.gz ]
then echo /u05/oradata/prod${bkupnum}.dmp.gz >>backemup.dat
fi
#
# if [ "`tail -1 /u05/oradata/prod${bkupnum}.dmp.log`" = \
# "Export terminated successfully without warnings." ]; then
# echo `date +"%D %T"`: Creating PROD /u05/oradata/prod${bkupnum}.idx \
# Table/Index Descriptions ...
# echo systempassword | imp system file=/u05/oradata/prod${bkupnum}.dmp \
#   indexfile=/u05/oradata/prod${bkupnum}.idx full=y \
#   log=/u05/oradata/prod${bkupnum}.idx.log >/dummy 2>/dummy
# tail -1 /u05/oradata/prod${bkupnum}.idx.log
# else echo `date +"%D %T"`: WARNING: COULD NOT CREATE PROD \
# /u05/oradata/prod${bkupnum}.idx Table/Index Descriptions
# fi
#
echo `date +"%D %T"`: Backing up zipped database files ...
cat backemup.dat | cpio -ovC64 >/dev/rmt0
#
# if [ -a /u05/oradata/prod${bkupnum}.dmp.gz ]
# then rm /u05/oradata/prod${bkupnum}.dmp.gz
# fi
#
# Remove the old archivelogs (archiving not on for the non-PROD db's).
#
echo `date +"%D %T"`: Removing archive logs older than 5 days ...
find /orcl/oradata/PROD/archivelogs -name '*.arc' -mtime +5 -exec rm {} \;
find /orcl/oradata/PROD/archivelogs -name '*.arc.gz' -mtime +5 -exec rm {} \;
find /u00/oradata/PROD/udump -name '*.trc' -mtime +5 -exec rm {} \;
#
# Generate the index row deletion statistics for PROD (which takes
# a couple of hours to run).
#
echo `date +"%D %T"`: Generating Index Row Deletion Statistics ...
echo systempassword | sqlplus -s system @dbb_index_stats.sql
cp -p index_stats.lst /u05/oradata/prod${bkupnum}.index_stats.lst
# qprt /u05/oradata/prod${bkupnum}.index_stats.lst
mail -s "Nightly Backup PROD Index Stats" myname@myuniversity.edu < /u05/oradata/prod${bkupnum}.index_stats.lst
#
echo `date +"%D %T"`: Nightly backup processing completed.