#!/bin/sh
#
# Script: rmanclone.shl
#
# Purpose: Perform an RMAN clone of one database SID to another database SID
#          up to an optional point-in-time or up to the current point-in-time.
#          The RMAN database backup(s) of the source SID must be available
#          on-line, as well as sufficient archive logs to recover up to the
#          desired point-in-time.
#          Usage:
#            rmanclone.shl <from SID> <to SID> <MM/DD/YY:HH/MI/SS>
#          where the "as of" date and time is optional (default is now).
#
# Notes:   The target SID's init.ora file or spfile.ora file must already exist.
#          The target SID's init.ora file, if any, is saved off, and an spfile
#          is created for the target.  Also, the connect string for the source
#          and the target is assumed to be in the form of "<server>_<sid>",
#          such as myserver_prod and myserver_test, and those source and target
#          entries must exist in the $ORACLE_HOME/network/admin/tnsnames.ora
#          file.  The source and target SID's must also be in the /etc/oratab
#          file.
#
# Examples:
#          To clone PROD to TEST as of now:
#            rmanclone.shl PROD TEST
#          To clone PROD to TEST as of midnight:
#            rmanclone.shl PROD TEST `date +"%D:00:00:00"`
#
# Author: Stephen Rea <srea@maristream.org>
#         Maristream, Inc.
#
# Updates:
# 10/02/08 - Initial Release
# 10/21/08 - Create the directories for the clone, if not already created.
#            Reduced the output of the script (sqlplus sessions silent).
# 10/22/08 - Check to make sure that ". oraenv" can change to the SID's.
#            Added a warning that the clone database will be deleted.
# 10/24/08 - Remove feedback and timing lines from environment variable SQL
#            assignments.
# 10/27/08 - Set ORAENV_ASK to YES so that ". oraenv" will prompt for the SID.
# 10/31/08 - Run this script using the Bourne shell (for the export command).
#
unset rman_from_sid rman_to_sid rman_to_date rman_from_pw rman_to_pw rman_server rman_gubinst
export rman_server=myserver
export ORAENV_ASK=YES
#
if [ $# -lt 2 -o $# -gt 3 ]; then
echo "Usage: rmanclone.shl <from SID> <to SID> <MM/DD/YY:HH/MI/SS>"
echo "       where the 'as of' date and time is optional (default is now)"
exit
fi
#
# Get the upper-case SID's, along with the current date if no date is entered.
#
export rman_from_sid=`echo $1 | tr '[a-z]' '[A-Z]'`
export rman_to_sid=`echo $2 | tr '[a-z]' '[A-Z]'`
if [ $# -eq 2 ]; then
export rman_to_date=`date +"%D:%T"`
else
export rman_to_date=$3
fi
#
# Check that the from and to SID's are different, that the SID's exist
# in /etc/oratab, that the parameter files exist, and that the connect
# strings are in the tnsnames.ora file.  Also, check the oraenv command
# to make sure it can switch to the from and to SID's.
#
#
if [ "${rman_from_sid}" = "${rman_to_sid}" ]; then
echo
echo "The from and to SID's must be different"
echo
exit
fi
#
if [ -z "`grep -i ${rman_from_sid} /etc/oratab | grep -v '^#'`" ]; then
echo
echo "Entry for ${rman_from_sid} not found in /etc/oratab"
echo
exit
fi
if [ -z "`grep -i ${rman_to_sid} /etc/oratab | grep -v '^#'`" ]; then
echo
echo "Entry for ${rman_to_sid} not found in /etc/oratab"
echo
exit
fi
#
if [ `ls ${ORACLE_HOME}/dbs | egrep "(init${rman_from_sid}.ora|spfile${rman_from_sid}.ora)" | wc -l` = 0 ]; then
echo
echo "Parameter file not found for ${rman_from_sid} in ${ORACLE_HOME}/dbs"
echo
exit
fi
if [ `ls ${ORACLE_HOME}/dbs | egrep "(init${rman_to_sid}.ora|spfile${rman_to_sid}.ora)" | wc -l` = 0 ]; then
echo
echo "Parameter file not found for ${rman_to_sid} in ${ORACLE_HOME}/dbs"
echo
exit
fi
#
if [ -z "`grep -i ${rman_server}_${rman_from_sid} ${ORACLE_HOME}/network/admin/tnsnames.ora`" ]; then
echo
echo "Entry for ${rman_server}_${rman_from_sid} not found in ${ORACLE_HOME}/network/admin/tnsnames.ora"
echo
exit
fi
if [ -z "`grep -i ${rman_server}_${rman_to_sid} ${ORACLE_HOME}/network/admin/tnsnames.ora`" ]; then
echo
echo "Entry for ${rman_server}_${rman_to_sid} not found in ${ORACLE_HOME}/network/admin/tnsnames.ora"
echo
exit
fi
#
echo ${rman_to_sid} | . oraenv >/dev/null
if [ "${ORACLE_SID}" != "${rman_to_sid}" ]; then
echo
echo "The \". oraenv\" command is unable to change the ORACLE_SID to ${rman_to_sid}"
echo
exit
fi
echo ${rman_from_sid} | . oraenv >/dev/null
if [ "${ORACLE_SID}" != "${rman_from_sid}" ]; then
echo
echo "The \". oraenv\" command is unable to change the ORACLE_SID to ${rman_from_sid}"
echo
exit
fi
#
# Get the sys passwords for the source database and the target database.
#
echo
echo "Enter sys password for ${rman_from_sid}: \c"; read rman_from_pw
echo "Enter sys password for ${rman_to_sid}, if not ${rman_from_pw}: \c"; read rman_to_pw
if [ "${rman_to_pw}" = "" ]; then
export rman_to_pw=${rman_from_pw}
fi
#
# Confirm that this is what the user wants to clone.
#
echo
echo "===> WARNING: The ${rman_to_sid} database and all of its backups will be deleted"
echo "===> by this procedure, so, make sure this is what you want to do!"
echo
echo "Clone ${rman_from_sid} (pw ${rman_from_pw}) to ${rman_to_sid} (pw ${rman_to_pw}) as of ${rman_to_date} (y/n)? \c"; read ANS
echo
if [ ! \( "$ANS" = "y" -o "$ANS" = "Y" \) ]; then
exit
fi
echo "-------------------------------------------------------------------------"
echo "Cloning ${rman_from_sid} to ${rman_to_sid} as of ${rman_to_date} ..."
echo "-------------------------------------------------------------------------"
echo
#
# Do a log file switch to make sure we have all archive logs available up to
# the current point-in-time.
#
sqlplus -S "/ as sysdba" <<EOF >/dev/null
  alter system archive log current;
EOF
#
# Get the datafile and temp file directories to create for the clone, if needed.
#
cd
sqlplus -S "/ as sysdba" <<EOF >rmanclone_do.shl
  set heading off echo off feedback off trimspool on
  select distinct 'mkdir -p ' || replace(substr(file_name,1,
    instr(file_name,'/',-1)-1),'${rman_from_sid}','${rman_to_sid}')
    from dba_data_files;
  select distinct 'mkdir -p ' || replace(substr(file_name,1,
    instr(file_name,'/',-1)-1),'${rman_from_sid}','${rman_to_sid}')
    from dba_temp_files;
  exit
EOF
#
echo ${rman_to_sid} | . oraenv >/dev/null
#
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
cp -p $ORACLE_HOME/dbs/init${rman_to_sid}.ora $ORACLE_HOME/dbs/init${rman_to_sid}.ora.save.`date +"%y%m%d%H%M%S"`
#
# Drop the target database and its backups after creating a pfile (which will
# not be deleted by the "drop database" command; whereas, the spfile will be).
# NOTE: You will get RMAN errors if the database doesn't exist - ignore them.
#
echo ">>>> NOTE: Errors from the following RMAN session can probably be ignored. <<<<"
rman target / <<EOF
  startup force nomount
  sql 'create pfile from spfile';
  alter database mount;
  sql 'alter system enable restricted session';
  drop database including backups noprompt;
  exit
EOF
#
# Start up the target database in nomount state and create the spfile.
# Also, define the db_file_name_convert and log_file_name_convert
# parameters in the spfile for this clone (requires restart to use the
# spfile and to set the spfile file_name_convert parameters, and another
# restart to use the spfile file_name_convert parameters that were set).
# NOTE: You will get an error if the spfile already exists - ignore it.
#
sqlplus -S "/ as sysdba" <<EOF >/dev/null 2>/dev/null
  startup force nomount
  create spfile from pfile;
  startup force nomount
  alter system set db_file_name_convert='${rman_from_sid}','${rman_to_sid}' scope=spfile;
  alter system set log_file_name_convert='${rman_from_sid}','${rman_to_sid}' scope=spfile;
  startup force nomount
  exit
EOF
sqlplus -S "/ as sysdba" <<EOF
  column name format a25
  column value format a50
  select name,value from v\$parameter where name like '%file_name_convert';
  exit
EOF
#
# Get the dest and archivelog directories to create for the clone, and create
# the clone's directories, if needed.
#
sqlplus -S "/ as sysdba" <<EOF >>rmanclone_do.shl
  set heading off echo off feedback off trimspool on
  select distinct 'mkdir -p ' || value
    from v\$parameter where name in ('background_dump_dest','user_dump_dest',
    'core_dump_dest','audit_file_dest','log_archive_dest')
    and value is not null;
  select distinct 'mkdir -p ' || substr(substr(value,instr(value,'LOCATION=')+9),1,
    instr(substr(value,instr(value,'LOCATION=')+9),' ')-1)
    from v\$parameter where name between 'log_archive_dest_1' and
    'log_archive_dest_10' and instr(value,'LOCATION=') > 0;
  exit
EOF
#
grep mkdir rmanclone_do.shl | sort -u
grep mkdir rmanclone_do.shl | sort -u | sh
rm rmanclone_do.shl
#
# Clone the source database as of the given date to the target database.
#
rman target sys/${rman_from_pw}@${rman_server}_${rman_from_sid} auxiliary sys/${rman_to_pw}@${rman_server}_${rman_to_sid} <<EOF
  run
  {
    set until time = "to_date('${rman_to_date}','MM/DD/YY:HH24:MI:SS')";
    duplicate target database to ${rman_to_sid};
  }
  exit
EOF
#
# Update the gubinst fields to reflect the clone and date (if there is a gubinst
# table in this database).
#
`echo \`sqlplus -S "/ as sysdba" <<EOF
  set feedback off timing off
  select count(*) from dba_tables where owner = 'GENERAL'
    and table_name = 'GUBINST';
EOF\` | sed 's/.* \(.*\)/export rman_gubinst=\1/'`
if [ $rman_gubinst -eq 1 ]; then
sqlplus -S "/ as sysdba" <<EOF
  update gubinst set (gubinst_instance_name,gubinst_name) =
    (select name || to_char(sysdate,'MMDD'),name || ' was cloned on ' ||
    to_char(sysdate,'MM/DD/YY') from v\$database);
  select gubinst_instance_name,gubinst_name from gubinst;
  exit
EOF
fi
#
# Create the password file for the target database.
#
rm -f $ORACLE_HOME/dbs/orapw${rman_to_sid}
orapwd file=$ORACLE_HOME/dbs/orapw${rman_to_sid} password=${rman_to_pw} entries=5