#!/usr/bin/ksh 
#~~~~~~~88888888888888888888888888888888888~/.profile

#***********************************************************************
#*                                                                     *
#*             Copyright (c) Ultra Electronics Limited 2006            *
#*                         All rights reserved                         *
#*                                                                     *
#***********************************************************************
#* 
#* Filename:           rman_db_backup.ksh
#* Originating Author: Gary Roberts
#* Design Reference:   UAS/FALCP/SY/2478
#*
#***********************************************************************
#*
#* Description:
#* This script is called to perform an RMAN backup of the database
#*   identified by the environment variable ${ORACLE_SID}
#* This script will always perform an archivelog backup irrespective
#*   of the backup type selected 
#* It must be run as an o/s user who is a member of the UNIX group
#*   (typically dba) that can connect to the database with the SYSDBA
#*   privilege using o/s authentication (i.e sqlplus '/ as sysdba')
#*
#* Usage:
#*     rman_db_backup.ksh -b <FULL|ARCHIVELOG> -d <backup directory>
#*        [-l <logfile name>] [-c <recovery catalog usr/pwd@tnsname>]     
#*         
#* Options:
#*     -b This argument is mandatory. It describes the backup type and
#*        can either be FULL or ARCHIVELOG. If the backup type FULL is     
#*        chosen then the database, controlfile and archived logs will 
#*        be backed up. If ARCHIVELOG is chosen then just the archived
#*        logs and the current controlfile will be backed up.
#*     -d This argument is mandatory. It is used to inform RMAN of the
#*        directory into which backups will be taken.
#*     -l This argument is optional. It is used to pass in the path to
#*        a file to which the logs will be written. If the file does not
#*        exist then it will be created. If this argument is not 
#*        supplied, this script will generate its own logfile name,
#*        which will include a combination of the current date and time.
#*     -c This argument is optional. It is used to specify the 
#*        connection details for a recovery catalog (if one exists). The
#*        string should be in the form <username>/<password>@<tnsname>,
#*        listing the username and password of the recovery catalog user
#*        and the tnsname of the database in which the recovery catalog
#*        exists.
#*         
#*
#***********************************************************************
#* Program Identification Array
#*
#* "%Z%%M% VERSION %I% %E% %U%"
#***********************************************************************
#* Modification History
#*
#* Rel Date/Time      Proj/Id   CA Ref/Details Of Change
#* --- -------------- --------  --------------------------------------
#* 1.1 xx.xx.06/xx:xx xxxxx/xx  CA/xxxx xxxxxxxx
#**********************************************************************/

#~~~~~~~~~~~~~DEFAULT VARIABLES~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#  The following variables are set to default in this script, and these
#  values should not be directly changed. If different values are
#  required, then an environment variable with the same name as the 
#  variable can be set. Environment variables will take priority over
#  the default values in this script. 
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

###############################################
# Set number of days to keep STANDBY archived   
# redo logs on disk before deleteing them
#
# Default duration is 5 days
#
###############################################

STDBY_ARCH_LOG_RETENTION=${STDBY_ARCH_LOG_RETENTION:-5}


###############################################
# Set number of days to keep backup log files
# in /tmp
# Default duration is 7 days
#
###############################################

TMP_LOG_FILE_RETENTION=${TMP_LOG_FILE_RETENTION:-10}

##~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


##~~~~~~~~~~~~~~~~~~~SYSTEM VARIABLES~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Detailed below are the format masks used to name the RMAN backup
# These masks display key data to aid during a restore.
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#----------------------------------------------
#
# Mask     Impact
# ====================================== 
#  %I      Stores DBID 
#  %Y      Stores Year backup taken
#  %M      Stores Month backup taken
#  %D      Stores day backup taken
#  %U      Stores Unique Identifier for each bkuppiece
#
#-----------------------------------------------

export       DB_BKUP_MASK=db_full_%I_%Y-%M-%D_%U
export ARCH_LOG_BKUP_MASK=arch_log_%I_%Y-%M-%D_%U
export CTL_FILE_BKUP_MASK=ctl_file_%I_%Y-%M-%D_%U

##~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



#############################################################
#
#                        FUNCTIONS
#
#############################################################

f_usage()
{
  echo                                                >>${LOGFILE}
  echo "DB_BKUP: USAGE: $0 [-b <full/full_nocat/archivelog>] "\
          "[-d <PRIMARY/PHYSICAL_STANDBY>] "\
          "[-c <user>/<pwd>@<recovery_cat_tnsname>] "\
          "[-l <rman log name>]" >>${LOGFILE}

  return 0
}


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# tidy up copies of rman bkup logs held in /tmp 
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
f_tidy_up()
{
  #Put a copy of the RMAN log file into /tmp
  cp -p ${LOGFILE} /tmp/

  # Delete all rman log files in /tmp older than
  # ${TMP_LOG_FILE_RETENTION} days
  find /tmp/rman_*.log -mtime +${TMP_LOG_FILE_RETENTION}\
                          -exec rm -f {} \;

  find ${BACKUP_DIR}/rman_*.log -mtime +${TMP_LOG_FILE_RETENTION}\
                          -exec rm -f {} \;  

  return $?
}


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Output header for logfile
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
f_log_header()
{
    echo "-----------------------------------" >>${LOGFILE}
    echo "----RMAN Log follows---------------" >>${LOGFILE}
    echo "-----------------------------------" >>${LOGFILE}
}



#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Add information that will aid in db restore and recovery
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
f_add_database_state()
{
    echo                                       >>${LOGFILE}
    echo "-----------------------------------" >>${LOGFILE}
    echo "Database State Info"                 >>${LOGFILE}
    echo "-----------------------------------" >>${LOGFILE}

    sqlplus -s '/as sysdba' <<!!!
    set pages 1000
    set feedback off
    set lines 80

    alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
    whenever sqlerror exit failure;
    whenever oserror exit failure;

    spool ${LOGFILE} append

    Prompt Database State info Post Backup

    select name from v\$controlfile;
    select * from v\$instance;
    select * from v\$database;
    select * from v\$log;
    select to_char(sysdate, 'dd-MON-yyyy hh24:mi:ss') "SysDate" from dual;
    select checkpoint_change#, current_scn from v\$database;

    archive log list;
    spool off
!!!
}

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Find out if the database role is PRIMARY or PHYSICAL_STANDBY
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
f_db_role()
{
TEMPFILE="/tmp/database_role$$.lst" 
   # Write the database role to a file.
      sqlplus -s '/ as sysdba' << EOF > /dev/null
      set head off
      set feedback off
      set pagesize 0
      spool ${TEMPFILE}
        SELECT database_role FROM v\$database;
      spool off
EOF

 # Read the database_role from the file
  DB_ROLE=`cat ${TEMPFILE} | sed "s/ //g"`

  # Delete the file
  rm -f ${TEMPFILE}

  if [[ ${DB_ROLE} != "PRIMARY" && ${DB_ROLE} != "PHYSICAL_STANDBY" ]];
  then
    logger "DB_BKUP: rman_db_backup.ksh: ERROR: Unable to determine "\
           "database type. Backup aborted".
    exit 1
  fi
}

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Perform RMAN backup dependent upon backup mode selected
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
f_rman_db_bkup()
{
  #Build list of cmds to execute in RMAN depending upon bkup
  #mode chosen

  #Every backup will backup the archivelogs
  #full and full_nocat will also backup the db first
  BKUP_CMD="backup archivelog all format = '${BACKUP_DIR}/${ARCH_LOG_BKUP_MASK}';\
            backup current controlfile format = '${BACKUP_DIR}/${CTL_FILE_BKUP_MASK}';\
            delete noprompt obsolete;"

  if [[ ${BACKUP_TYPE} = "full" ]];
   then

    BKUP_CMD="connect catalog ${RECOVERY_CATALOG};\
              backup database format = '${BACKUP_DIR}/${DB_BKUP_MASK}';"${BKUP_CMD}
 
  elif [[ ${BACKUP_TYPE} = "full_nocat" ]];
   then

    BKUP_CMD="backup database format = '${BACKUP_DIR}/${DB_BKUP_MASK}';"${BKUP_CMD}
  elif [[ ${BACKUP_TYPE} = "archivelog" ]];
   then
    #Archivelog backups are the standard and will always be performed for any
    #backup type
    #As such do nothing else as BKUP_CMD is set for arch log bkups above
    echo

  else
     #Unknown backup cmd so do nothing but report error
     echo "Unknown backup cmd ${BACKUP_TYPE}" >>${LOGFILE}
     return 1;
  fi

  export BKUP_CMD
  ##~~~~~~~~~~~~~~~~~~##
  ##Perform RMAN backup
  ##~~~~~~~~~~~~~~~~~~##
  rman target /  <<EOF >>${LOGFILE}
${BKUP_CMD}
EOF
  retcode=$?
echo "return value ${retcode}"

  #Return RMAN retcode
  return $retcode 
}

#############################################################
#                     END FUNCTIONS
#############################################################

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Assign command line options to variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

BACKUP_TYPE="full"

while getopts d:l:b:c: options
do
  case $options in
    b) BACKUP_TYPE=`echo $OPTARG | tr [A-Z] [a-z]` ;;
    c) RECOVERY_CATALOG=$OPTARG ;;
    d) BACKUP_DIR=$OPTARG ;;
    l) LOGFILE=$OPTARG ;;
  [?]) f_usage
       exit 2 ;;
  esac
done

if [[ $? -ne 0 ]];
then
   logger "DB_BKUP: rman_db_backup.ksh: ERROR: Unable to interpret "\
          "command line options"
fi

#######################################################################
#
#                 Pre-Processing Checks 
#
#######################################################################

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Check variables have been assigned. If not then display Usage. 
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

if [[ $BACKUP_DIR = "" ]];
then
    f_usage
    exit 3
fi

if [[ ${BACKUP_TYPE} != "full" && ${BACKUP_TYPE} != "archivelog" ]];
then
    f_usage
    exit 4
fi

## START LOGS
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Set date variable used to identify the time at
# which each backup has been taken
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DT=`date +'%D:%H%M'|sed s!/!_!g`


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Check if location passed in to store backups
# is a directory and we can read/write to it
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if [[ ! -d ${BACKUP_DIR} ]];
 then
   echo
   echo ERROR: ${BACKUP_DIR} is not a directory
   exit 5
fi

if [[ ! -w ${BACKUP_DIR} ]];
 then
   echo
   echo ERROR: ${BACKUP_DIR} cannot be written to
   exit 6
fi

if [[ ! -r ${BACKUP_DIR} ]];
 then
   echo
   echo ERROR: ${BACKUP_DIR} cannot be read
   exit 7
fi


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# If the name of the rman_log has not been specified using the
# command line -l option,  generate a log file name now. 
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if [[ ${LOGFILE} = "" ]]
then
LOGFILE=${BACKUP_DIR}/rman_${DT}_${ORACLE_SID}.log
fi

#Dont clean up logs on error as we want to see how far RMAN got
#before the trap was invoked
trap "echo Unexpected Error Occurred - Terminating >>${LOGFILE}; exit" INT QUIT HUP TERM


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Set return code from rman_db_backup.ksh 
# Assunme failure (indicated by retcode=1)
# RMAN process will set this to 0 if it is
# successful
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
typeset -i retcode
export retcode=1


echo
echo "All information and errors will be written to ${LOGFILE}"
echo


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Determine if ORACLE_SID is set
# If not raise error
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if [[ ${ORACLE_SID} = "" ]];
then
   echo ERROR: ORACLE_SID not SET >> ${LOGFILE}
   exit 8
fi



#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Check /tmp where log will go is a directory 
# and we can read/write to it
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if [[ ! -d /tmp ]];
 then
   echo
   echo ERROR: /tmp is not a directory
   exit 9
fi

if [[ ! -w /tmp ]];
 then
   echo
   echo ERROR: /tmp cannot be written to
   exit 10
fi

if [[ ! -r /tmp ]];
 then
   echo
   echo ERROR: /tmp cannot be read
   exit 11
fi




#############################################################
#############################################################
#
#                  MAIN PROCESSING
#
#############################################################
#############################################################

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# If no recovery catalog has been specified and
# backup type is "fuil", then change the type to
# full_nocat.
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

if [[ ${RECOVERY_CATALOG} = "" ]];
then
    if [[ ${BACKUP_TYPE} = "full" ]];
    then 
        BACKUP_TYPE="full_nocat"

        #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        # Master Standby will use recovery
        # catalogs by default, so warn user if
        # one hasn't been supplied.
        #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        if [[ ${APPN_MASTER_SBY} = "Y" ]];
        then
            logger "DB_BKUP: rman_db_backup.ksh: WARNING - "\
                   "You have not specified a recovery "\
                   "catalog. If you do not use a recovery "\
                   "catalog then this warning can safely be ignored. "\
                   "Otherwise you should resync the catalog "\
                   "at the earliest opportunity."
        fi
    fi
fi

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Check to see if db we are running on is 
# a PHYSICAL_STANDBY a normal PRIMARY db 
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
f_db_role
if [[ $? -ne 0 ]];
then
   logger "DB_BKUP: rman_db_backup.ksh: ERROR: Unable to determine"\
          " database role."
fi


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Check to see if we are connecting to a Standby
# Oracle database.
#
# This can be determined by ${DB_ROLE} containing the 
# text "PHYSICAL STANDBY". Note that the command line 
# text is converted to upper case first.
#
# If we are simply delete the standby redo logs
# older than ${STDBY_ARCH_LOG_RETENTION}
# and exit as there is nothing to backup
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

if [[ ${DB_ROLE} = "PHYSICAL_STANDBY" ]];
 then
TEMPFILE="/tmp/del_stdby_arch_log$$.lst"
   sqlplus -s '/as sysdba' <<EOF | /bin/ksh
   set head off
   set pages 10000
   set lines 80 
   set feedback off
   spool $TEMPFILE
   select 'rm -f '|| name
     from v\$archived_log 
    where next_time<=sysdate - ${STDBY_ARCH_LOG_RETENTION} 
    and applied='YES' and substr(name,1,1)='/';
   spool off
   exit;
EOF
   chmod +x ${TEMPFILE}
   /bin/ksh ${TEMPFILE}
   DEL_STDBY_ARCH_LOG_RETCODE=$?
   rm -f ${TEMPFILE}
   exit ${DEL_STDBY_ARCH_LOG_RETCODE}


elif [[ $DB_ROLE = "PRIMARY" ]];
 then

  #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  # Depending on backup type requested execute 
  # appropriate RMAN backup
  #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  ##################################################
  #
  # In this case perform a full database backup
  # with a recovery catalog
  #
  #################################################

  case ${BACKUP_TYPE} in
   full)
   #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   # Perform FULL db backup with recovery catalog
   #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    echo >>${LOGFILE}
    echo Full backup with Recovery Catalog chosen >>${LOGFILE}
    echo >>${LOGFILE}

    #Update output log flagging start of RMAN data
    f_log_header

    #Perform RMAN backup
    f_rman_db_bkup 

    retcode=$?

    echo Rman return code was $retcode >>${LOGFILE}

    f_add_database_state

    f_tidy_up
    if [[ $? -ne 0 ]];
     then
     echo f_tidy_up function in ${BACKUP_TYPE} case statement failed  >>${LOGFILE}
    fi
 
    ;;

  archivelog)
  #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  # Perform archivelog backup
  #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    echo >>${LOGFILE}
    echo Archive log backup chosen >>${LOGFILE}
    echo >>${LOGFILE}

    #Update output log flagging start of RMAN data
    f_log_header
 
    #Perform RMAN backup
    f_rman_db_bkup 

    retcode=$?

    echo Rman return code was $retcode >>${LOGFILE}

    f_add_database_state

    f_tidy_up
    if [[ $? -ne 0 ]];
     then
     echo f_tidy_up function in ${BACKUP_TYPE} case statement failed  >>${LOGFILE}
    fi

    ;;

  full_nocat)
  #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  # Perform FULL db backup without recovery catalog
  #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    echo >>${LOGFILE}
    echo Full backup chosen - not connected to Recovery Catalog >>${LOGFILE}
    echo >>${LOGFILE}

    #Update output log flagging start of RMAN data
    f_log_header

    #Perform RMAN backup
    f_rman_db_bkup 

    retcode=$?

    echo Rman return code was $retcode >>${LOGFILE}

    f_add_database_state
    f_tidy_up
    if [[ $? -ne 0 ]];
     then
     echo f_tidy_up function in ${BACKUP_TYPE} case statement failed  >>${LOGFILE}
    fi

   ;;

  *)
  #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  # Unable to determine backup type
  #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    echo >>${LOGFILE}
    echo Problem with script parameters  >>${LOGFILE}
    echo >>${LOGFILE}

    echo Input parameters were >>${LOGFILE}
    echo $0 $1 $2  $3 >>${LOGFILE}
    f_tidy_up
    if [[ $? -ne 0 ]];
     then
     echo f_tidy_up function in default case statement failed  >>${LOGFILE}
    fi

    retcode=12
    ;;
    
  esac

  exit $retcode

else
  #If database type is not recognised
  echo                                    >>${LOGFILE}
  echo Error with Database Type passed in >>${LOGFILE}
  echo Database type is: ${DB_ROLE} >>${LOGFILE}
  f_usage                                   >>${LOGFILE}
  exit 13
 
fi

