Tip of the Week
Tip for Week of December 28, 2003

Back Up the Control File Image

This tip comes from Faina Goldenberg, Oracle DBA, at Open Distributed Solutions, Inc., in Jamison, PA.

#!/bin/ksh

#
# Backup Control File as Current Control File Image ( with Extra
Switches of Logs ) per Currently Running Instance # by F.Goldenberg,
Oracle DBA, Open Distributed Solutions, Inc, USA # # Usage: For more
than 1 database per server ( If No parameters, ALL Databases Affected )
#   Examples:
#               $0              # All databases will be "touched" on the server
#               $0 a123         # Just for a123 ( only ONE )
#               $0 a123 PROD    # Just for a123 PROD ( Instance is Case sensitive )
#

export ARCH_DAY=`date +%C%y%m%d.%H%M`

# Backup Destination
export ARCH_DEST=/arch/export/arch/$ORACLE_SID/ARCH
###################################################

Backup_ControlFile()
{
echo "\n\tBackup Started for $ORACLE_SID\ton `date`"
sqlplus -s /nolog<<!
connect / as SYSDBA
-- "Extra" Redo Log Switch
alter system switch logfile;
-- If Archiving is ON, backup all Online Redo Log Files
alter system archive log all;

-- Image Backup of Controlfile
alter database backup controlfile to
'${ARCH_DEST}/bckp.${ORACLE_SID}.${ARCH_DAY}.ctrl';
exit
!
}

#
# For more than 1 database per server ( If No parameters, ALL Databases
Affected ) # if [ $# = 0 ];  then echo "\n\tBackup ControlFile will be
for all databases on `hostname`";
      ##################################################################
        ################################
        #Check For Oratab prior to check
        { ORATAB=/etc/opt/oracle/oratab && [[ -r $ORATAB ]] ; } ||
        { ORATAB=/var/opt/oracle/oratab && [[ -r $ORATAB ]] ; } ||
        { ORATAB=/etc/oratab            && [[ -r $ORATAB ]] ; } ||
        exit 100
 cat $ORATAB |grep -v '#'|grep -i ':Y'|while read LINE; do
############################  # Setup Oracle Environment
############################  export ORACLE_SID=`echo $LINE|awk -F:
'{print $1}'`  echo "$ORACLE_SID"|. oraenv  Backup_ControlFile  done
else echo "\n\tBackup ControlFile will be for $# instance(s) [$*]";
      ##################################################################
 for i in $*
 do
 ############################
 # Setup Oracle Environment
 ############################
 export ORACLE_SID=$i
 echo "$ORACLE_SID"|. oraenv
 Backup_ControlFile
 done
fi