#

#! /bin/ksh

# Usage: hotorabk {database}

# Notes:

# Input:

# ORACLE_SID

# Variables:

# LOGDIR directory where log and temp files go.

# NAPTIME number of seconds to wait after log switch

# before backing up archived redo logs.

# TARDIR directory where tarfiles go.

# TARFLAGS flags to pass to tar. Note that this value is

# set three (3) times in this script, so if you

# change it, be sure to change it everywhere.

# Environment variables:

# If you have a script to set up the ORACLE environment, you can

# call it from this script; search for ORACLE_SETUP to see where.

# Alternatively, the script itself can set the environment vars.

#-------------------------------------------------------------------------------

# Check for usage.

#-------------------------------------------------------------------------------

if [ "$#" -ne 2 ]

then

echo "hotorabk"

echo "Usage: hotorabk {database} {mountpoint}"

echo "where {database} is the connect string to the database you"

echo "want to backup,"

echo "and {mountpoint} the mount point name of the disk you want"

echo "to backup the database to."

echo "Exiting."

exit 1

#-------------------------------------------------------------------------------

# call your ORACLE_SETUP script here, or set the environment variables directly.

# Set your site/db specific information here:

#-------------------------------------------------------------------------------

ORACLE_SID="$1" ; export ORACLE_SID

MOUNTPOINT="$2" ; export MOUNTPOINT

SID="$1" ; export SID

ORAENV_ASK=NO ; export ORAENV_ASK

# comment out below two lines or run oracle env file

ORACLE_HOME=/export/home/oracle/product/8.1.6;export ORACLE_HOME

PATH=${PATH}:${ORACLE_HOME}:/bin;export PATH

#. ~oracle/SH_ORASETUP

HOST=`/bin/uname -n` ; export HOST

PATH=/usr/bin:.:/usr/sbin:/usr/ccs/bin:$ORACLE_HOME/bin ; export PATH

LOGDIR=/${MOUNTPOINT}/${HOST}/oradata/${SID}/logbook

DATE=`date +%y%m%d`

TARDIR=/${MOUNTPOINT}/${HOST}/oradata/${SID}/tarfiles

NAPTIME=20

TARFLAGS=cvf

DESTDIR=/${MOUNTPOINT}/${HOST}/oradata/${SID}

MAIL=/usr/ucb/mail

MAILDIST="oradba@questam.com"

#-------------------------------------------------------------------------------

# The following environment variables are internal to this script. There should

# be no need to change these.

#-------------------------------------------------------------------------------

CURRENT_TS=NULL

ARCINFOFILE="${LOGDIR}/log_file_dest.log"

LOGFILE="${LOGDIR}/h_$1_${DATE}.log"

DBFLIST="${LOGDIR}/hotflist.${DATE}"

CONTROLFILE="${TARDIR}/control_$1_${DATE}.ctl"

#-------------------------------------------------------------------------------

# Put Startup info into logfile:

#-------------------------------------------------------------------------------

MSG="

\n------------------------------------------------------------------------

\nHot Oracle Backup of $1

\nBackup begun at `date`

\nORACLE_HOME = $ORACLE_HOME

\nORACLE_SID = $ORACLE_SID

\n------------------------------------------------------------------------

echo $MSG > $LOGFILE

#-------------------------------------------------------------------------------

# Generate a list of database files, by tablespace.

#-------------------------------------------------------------------------------

sqlplus -s > $DBFLIST << EOF

set feedback off

set heading off

set linesize 180

set pagesize 0

column tablespace_name format a30

column file_name format a90

column short_file_name format a40

select tablespace_name,

file_name,

substr(file_name, instr(file_name, '/', -1) + 1) short_file_name

from dba_data_files

order by tablespace_name, file_name

exit

EOF

echo "Following is list of tablespaces and files to be backup up" >> $LOGFILE

cat $DBFLIST >> $LOGFILE

#-------------------------------------------------------------------------------

# Make sure that worked out ok. (There had better by a SYSTEM tablespace.)

#-------------------------------------------------------------------------------

SYSCOUNT="`grep -c SYSTEM $DBFLIST`"

if [ $SYSCOUNT -lt 1 ]

then

echo "fatal error during backup file creation." >> $LOGFILE

echo "Backup aborting." >> $LOGFILE

exit

#-------------------------------------------------------------------------------

# Find out how many files we have to backup.

#-------------------------------------------------------------------------------

FILECOUNT="`wc -l $DBFLIST | awk '{print $1}'`"

#-------------------------------------------------------------------------------

# Read the file list; mark tablespace begin/end backup and tar the files.

#-------------------------------------------------------------------------------

cat $DBFLIST | while read TABLESPACE FILENAME FNAME

FILECOUNT=`expr $FILECOUNT - 1`

if [ $TABLESPACE != $CURRENT_TS ]

then

if [ $CURRENT_TS != "NULL" ]

then

echo "Marking $CURRENT_TS end backup" >> $LOGFILE

svrmgrl << EOF

connect internal

alter tablespace $CURRENT_TS end backup;

EOF

echo "Marking $TABLESPACE begin backup" >> $LOGFILE

svrmgrl << EOF

connect internal

alter tablespace $TABLESPACE begin backup;

EOF

CURRENT_TS=$TABLESPACE

echo "tar $TARFLAGS ${TARDIR}/${FNAME}_${DATE}.tar $FILENAME" >>$LOGFILE

tar $TARFLAGS ${TARDIR}/${FNAME}_${DATE}.tar $FILENAME

if [ $FILECOUNT = 0 ]

then

echo "Marking $CURRENT_TS end backup" >> $LOGFILE

svrmgrl << EOF

connect internal

alter tablespace $CURRENT_TS end backup;

EOF

done

#-------------------------------------------------------------------------------

# Now backup the control file, after deleting any lingering copy

#-------------------------------------------------------------------------------

if [ -f $CONTROLFILE ]

then

chmod 666 $CONTROLFILE

rm $CONTROLFILE

echo "Backing up control file." >> $LOGFILE

svrmgrl << EOF

connect internal

alter database backup controlfile to '$CONTROLFILE' ;

exit

EOF

echo "tar $TARFLAGS ${CONTROLFILE}_${DATE}.tar $CONTROLFILE" >> $LOGFILE

tar $TARFLAGS ${CONTROLFILE}_${DATE}.tar $CONTROLFILE

#-------------------------------------------------------------------------------

# Now find the archived redo log files, force a log switch and backup the

# archived redo log files.

#-------------------------------------------------------------------------------

sqlplus -s > $ARCINFOFILE << EOF

set head off

set pages 0

set feedback off

select value

from v\$parameter

where name = 'log_archive_dest'

exit

EOF

svrmgrl << EOF

connect internal

alter system switch logfile;

exit

EOF

# ----------------------------------------------------------------------

# Controlfile backup from trace file

# ----------------------------------------------------------------------

sqlplus -s <<EOF

alter database backup controlfile to trace

set heading off

set feedback off

spool ${DESTDIR}/ctrlfile/trace${SID}.lst

select lower(a.name)||'_ora_'||p.spid||'.trc'

from v\$database a, v\$session s, v\$process p

where s.paddr = p.addr

and s.username = (select username from v\$session

where audsid = userenv('SESSIONID'))

spool off

EOF

# copying the trace file to the hotorabk area

echo "-----------------------------------------------------------------" >> $LOGFILE

echo "Control file backup" >> $LOGFILE

TRACEFILE=`grep -i _ora_ ${DESTDIR}/ctrlfile/trace${SID}.lst`

cp ${HOME}/admin/${SID}/udump/${TRACEFILE} ${DESTDIR}/ctrlfile/${TRACEFILE}

# Check whether the trace file is copied to the destination

if [ -f ${DESTDIR}/ctrlfile/${TRACEFILE} ] ; then

echo "cp ${HOME}/admin/${SID}/udump/${TRACEFILE} ${DESTDIR}/ctrlfile/${TRACEFILE}" >>

$LOGFILE

echo "rm ${HOME}/admin/${SID}/udump/${TRACEFILE}" >> $LOGFILE

rm ${HOME}/admin/${SID}/udump/${TRACEFILE}

else

echo "Trace file not copied not copied to destination" >> $LOGFILE

#--------------------------------------------------------------------------------

# Backing up the init<db>.ora and config<db>.ora files

#--------------------------------------------------------------------------------

cp ${HOME}/admin/${SID}/pfile/init${SID}.ora ${DESTDIR}/pfile/init${SID}.ora

cp ${HOME}/admin/${SID}/pfile/config${SID}.ora ${DESTDIR}/pfile/config${SID}.ora

# check for init${SID}.ora file

if [ -f ${DESTDIR}/pfile/init${SID}.ora ] ; then

echo "-----------------------------------------------------------------" >> $LOGFILE

echo "Parameter files backup " >> $LOGFILE

echo "cp ${HOME}/admin/${SID}/pfile/config${SID}.ora ${DESTDIR}/pfile/config${SID}.ora"

>> $LOGFILE

echo "cp ${HOME}/admin/${SID}/pfile/init${SID}.ora ${DESTDIR}/pfile/init${SID}.ora"

>>$LOGFILE

else

echo "config${SID}.ora and init${SID}.ora not copied to destination " >>$LOGFILE

#-------------------------------------------------------------------------------

# Clean up some files that are no longer needed

#-------------------------------------------------------------------------------

echo "Cleaning up temporary files." >> $LOGFILE

echo "rm $CONTROLFILE" >> $LOGFILE

rm $CONTROLFILE

echo "rm $DBFLIST" >> $LOGFILE

rm $DBFLIST

echo "rm $ARCINFOFILE" >> $LOGFILE

rm $ARCINFOFILE

#-------------------------------------------------------------------------------

# That's it. Write wrap up info to logfile and exit the shell.

#-------------------------------------------------------------------------------

MSG="

\n------------------------------------------------------------------------

\nHot Oracle Backup of $1

\nBackup finished at `date`

\n------------------------------------------------------------------------

echo $MSG >> $LOGFILE

$MAIL -s "$ORACLE_SID Hot Backup Report" $MAILDIST < $LOGFILE

exit