Hi,

Unfortunately sometimes a Standby Instance needs to be recreated. This can happen for many reasons such as lost archive logs, standby data files, failover, among others.

This is why we wanted to have one script to recreate standby instances in an easy way.
This script recreates the standby considering some prereqs:

-Database Version should be at least 11gR1
-Dummy instance started on the standby node (Seeking to improve this so it won't be needed)
-Broker configuration hasn't been removed
-In our case we have two TNSNAMES files, one for the Standby creation (using SID) and the other one for production using service names (including broker service name)
-Some environment variables set up by the environment db script (like ORACLE_HOME, PATH...)
-The directory tree should not have been modified in the stanby host

We are currently using it on our 11gR2 Data Guard tests.

Any improvements will be welcome!

#!/bin/ksh

###    NOMBRE / VERSION
###       recrea_dg.sh   v.1.00
###
###    DESCRIPCION
###       reacreacion de la Standby
###
###    DEVUELVE
###       0 Creacion de STANDBY correcta
###       1 Fallo
###
###    NOTAS
###       Este shell script NO DEBE MODIFICARSE.
###       Todas las variables y constantes necesarias se toman del entorno.
###
###    MODIFICADO POR:    FECHA:        COMENTARIOS:
###    ---------------    ----------    -------------------------------------
###      Oracle           15/02/2011    Creacion.
###


###
### Cargar entorno
###



V_ADMIN_DIR=`dirname $0`
. ${V_ADMIN_DIR}/entorno_bd.sh 1>>/dev/null
if [ $? -ne 0 ]
then
  echo "Error Loading the environment."
  exit 1
fi

V_RET=0

V_DATE=`/bin/date`
V_DATE_F=`/bin/date +%Y%m%d_%H%M%S`
V_LOGFILE=${V_TRAZAS}/recrea_dg_${V_DATE_F}.log
exec 4>&1
tee ${V_FICH_LOG} >&4 |&
exec 1>&p 2>&1

###
### Variables para Recrear el Data Guard
###

V_DB_BR=`echo ${V_DB_NAME}|tr '[:lower:]' '[:upper:]'`
if [ "${ORACLE_SID}" = "${V_DB_NAME}01" ]
then
        V_LOCAL_BR=${V_DB_BR}'01'
        V_REMOTE_BR=${V_DB_BR}'02'
else
        V_LOCAL_BR=${V_DB_BR}'02'
        V_REMOTE_BR=${V_DB_BR}'01'
fi

echo " Getting local instance ROLE ${ORACLE_SID} ..."
sqlplus -s /nolog 1>>/dev/null 2>&1 <<-!
whenever sqlerror exit 1
connect / as sysdba
variable salida number
declare
  v_database_role v\$database.database_role%type;
begin
  select database_role into v_database_role from v\$database;
  :salida := case v_database_role
       when 'PRIMARY' then 2
       when 'PHYSICAL STANDBY' then 3
       else 4
     end;
end;
/
exit :salida
!

case $? in
1) echo " ERROR: Cannot get instance ROLE ." | tee -a ${V_LOGFILE}   2>&1
   V_RET=1 ;;
2) echo " Local Instance with PRIMARY role." | tee -a ${V_LOGFILE}   2>&1
   V_DB_ROLE_LCL=PRIMARY ;;
3) echo " Local Instance with PHYSICAL STANDBY role." | tee -a ${V_LOGFILE}   2>&1
   V_DB_ROLE_LCL=STANDBY ;;
*) echo " ERROR: UNKNOWN ROLE." | tee -a ${V_LOGFILE}   2>&1
   V_RET=1 ;;
esac

if [ "${V_DB_ROLE_LCL}" = "PRIMARY" ]
then
        echo "####################################################################" | tee -a ${V_LOGFILE}   2>&1
        echo "${V_DATE} - Reacreating  STANDBY Instance." | tee -a ${V_LOGFILE}   2>&1
        echo "" | tee -a ${V_LOGFILE}   2>&1
        echo "DATAFILES, CONTROL FILES, REDO LOGS and ARCHIVE LOGS in standby instance ${V_REMOTE_BR} will be removed" | tee -a ${V_LOGFILE}   2>&1
        echo "" | tee -a ${V_LOGFILE}   2>&1
        V_PRIMARY=${V_LOCAL_BR}
        V_STANDBY=${V_REMOTE_BR}
fi

if [ "${V_DB_ROLE_LCL}" = "STANDBY" ]
then
        echo "####################################################################" | tee -a ${V_LOGFILE}   2>&1
        echo "${V_DATE} - Reacreating  STANDBY Instance." | tee -a ${V_LOGFILE}   2>&1
        echo "" | tee -a ${V_LOGFILE}   2>&1
        echo "DATAFILES, CONTROL FILES, REDO LOGS and ARCHIVE LOGS in standby instance ${V_LOCAL_BR} will be removed" | tee -a ${V_LOGFILE}   2>&1
        echo "" | tee -a ${V_LOGFILE}   2>&1
        V_PRIMARY=${V_REMOTE_BR}
        V_STANDBY=${V_LOCAL_BR}
fi

# Cargamos las variables de los hosts

# Cargamos las variables de los hosts

PRY_HOST=`sqlplus  /nolog << EOF | grep KEEP | sed 's/KEEP//;s/[   ]//g'
connect sys/${V_DB_PWD}@${V_PRIMARY} as sysdba
select 'KEEP',host_name from v\\$instance;
EOF`


SBY_HOST=`sqlplus  /nolog << EOF | grep KEEP | sed 's/KEEP//;s/[   ]//g'
connect sys/${V_DB_PWD}@${V_STANDBY} as sysdba
select 'KEEP',host_name from v\\$instance;
EOF`

echo "el HOST primary es: ${PRY_HOST}" | tee -a ${V_LOGFILE}   2>&1
echo "el HOST standby es: ${SBY_HOST}" | tee -a ${V_LOGFILE}   2>&1
echo "" | tee -a ${V_LOGFILE}   2>&1
##
## Paramos la instancia STANDBY
##
V_DATE=`/bin/date`
echo "${V_DATE} - Shutting down Standby instance" | tee -a ${V_LOGFILE}   2>&1
echo "" | tee -a ${V_LOGFILE}   2>&1
echo "********************************************************************************" | tee -a ${V_LOGFILE}   2>&1


##
## Paramos la instancia STANDBY
##

SBY_STATUS=`sqlplus  /nolog << EOF | grep KEEP | sed 's/KEEP//;s/[   ]//g'
connect sys/${V_DB_PWD}@${V_STANDBY} as sysdba
select 'KEEP',status from v\\$instance;
EOF`

if [ ${SBY_STATUS} = 'STARTED' ] || [ ${SBY_STATUS} = 'MOUNTED' ] || [ ${SBY_STATUS} = 'OPEN' ]
then
        echo "${V_DATE} - Standby instance shutdown in progress..." | tee -a ${V_LOGFILE}   2>&1
        echo "" | tee -a ${V_LOGFILE}   2>&1
        echo "********************************************************************************" | tee -a ${V_LOGFILE}   2>&1

        sqlplus -s /nolog 1>>/dev/null 2>&1 <<-!
        whenever sqlerror exit 1
        connect sys/${V_DB_PWD}@${V_STANDBY} as sysdba
        shutdown abort
        !
fi

V_DATE=`/bin/date`
echo ""
echo "${V_DATE} - Standby instance stopped" | tee -a ${V_LOGFILE}   2>&1
echo "" | tee -a ${V_LOGFILE}   2>&1
echo "********************************************************************************" | tee -a ${V_LOGFILE}   2>&1

##
## Eliminamos los ficheros de la base de datos
##

V_SBY_SID=`echo ${V_STANDBY}|tr '[:upper:]' '[:lower:]'`
V_PRY_SID=`echo ${V_PRIMARY}|tr '[:upper:]' '[:lower:]'`


ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/data/*.dbf
ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/arch/*.arc
ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/ctl/*.ctl
ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/redo/*.ctl
ssh ${SBY_HOST} rm /opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/redo/*.rdo



##
## Startup nomount stby instance
##

V_DATE=`/bin/date`
echo "" | tee -a ${V_LOGFILE}   2>&1
echo "${V_DATE} - Starting  DUMMY Standby Instance " | tee -a ${V_LOGFILE}   2>&1
echo "" | tee -a ${V_LOGFILE}   2>&1
echo "********************************************************************************" | tee -a ${V_LOGFILE}   2>&1


ssh ${SBY_HOST} touch /home/oracle/init_dg.ora
ssh ${SBY_HOST} 'echo "DB_NAME='${V_DB_NAME}'">>/home/oracle/init_dg.ora'
ssh ${SBY_HOST} touch /home/oracle/start_dummy.sh
ssh ${SBY_HOST} 'echo "ORACLE_HOME=/opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2 ">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "export ORACLE_HOME">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "PATH=\$ORACLE_HOME/bin:\$PATH">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "export PATH">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "ORACLE_SID='${V_SBY_SID}'">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "export ORACLE_SID">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "sqlplus -s /nolog <<-!" >>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "      whenever sqlerror exit 1 ">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "      connect / as sysdba ">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "      startup nomount pfile='\''/home/oracle/init_dg.ora'\''">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'echo "! ">>/home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'chmod 744 /home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'sh /home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'rm /home/oracle/start_dummy.sh'
ssh ${SBY_HOST} 'rm /home/oracle/init_dg.ora'

##
## TNSNAMES change, specific for RMAN duplicate
##

V_DATE=`/bin/date`
echo "" | tee -a ${V_LOGFILE}   2>&1
echo "${V_DATE} - Setting up TNSNAMES in PRIMARY host " | tee -a ${V_LOGFILE}   2>&1
echo "" | tee -a ${V_LOGFILE}   2>&1
echo "********************************************************************************" | tee -a ${V_LOGFILE}   2>&1


ssh ${PRY_HOST} 'cp /opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2/network/admin/tnsnames.ora.inst  /opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2/network/admin/tnsnames.ora'

V_DATE=`/bin/date`
echo "" | tee -a ${V_LOGFILE}   2>&1
echo "${V_DATE} - Starting STANDBY creation with RMAN.. " | tee -a ${V_LOGFILE}   2>&1
echo "" | tee -a ${V_LOGFILE}   2>&1
echo "********************************************************************************" | tee -a ${V_LOGFILE}   2>&1


rman<<-! >>${V_LOGFILE}
connect target sys/${V_DB_PWD}@${V_PRIMARY}
connect auxiliary sys/${V_DB_PWD}@${V_STANDBY}
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
dorecover
spfile
parameter_value_convert '${V_PRY_SID}','${V_SBY_SID}'
set control_files='/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/ctl/control01.ctl','/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/redo/control02.ctl'
set db_file_name_convert='/opt/oracle/db/db${V_DB_NAME}/${V_PRY_SID}/','/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/'
set log_file_name_convert='/opt/oracle/db/db${V_DB_NAME}/${V_PRY_SID}/','/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/'
set 'db_unique_name'='${V_SBY_SID}'
set log_archive_config='DG_CONFIG=(${V_PRIMARY},${V_STANDBY})'
set fal_client='${V_STANDBY}'
set fal_server='${V_PRIMARY}'
set log_archive_dest_1='LOCATION=/opt/oracle/db/db${V_DB_NAME}/${V_SBY_SID}/arch DB_UNIQUE_NAME=${V_SBY_SID} MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
set log_archive_dest_2='SERVICE="${V_PRIMARY}"','SYNC AFFIRM DB_UNIQUE_NAME=${V_PRY_SID} DELAY=0 MAX_FAILURE=0 REOPEN=300 REGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
nofilenamecheck
;
}
!

V_DATE=`/bin/date`
if [ $? -ne 0 ]
then
        echo ""
        echo "${V_DATE} - Error creating STANDBY instance"
        echo ""
        echo "********************************************************************************"
else
        echo ""
        echo "${V_DATE} - STANDBY instance created SUCCESSFULLY "
        echo ""
        echo "********************************************************************************"
fi


sqlplus -s /nolog 1>>/dev/null 2>&1 <<-!
        whenever sqlerror exit 1
        connect sys/${V_DB_PWD}@${V_STANDBY} as sysdba
        alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=${SBY_HOST})(PORT=1544))' scope=both;
        alter system set service_names='${V_DB_NAME}.eu.roca.net,${V_SBY_SID}.eu.roca.net,${V_SBY_SID}_DGMGRL.eu.roca.net' scope=both;
        alter database recover managed standby database using current logfile disconnect from session;
        alter system set dg_broker_start=true scope=both;
!

##
## TNSNAMES change, back to Production Mode
##

V_DATE=`/bin/date`
echo " " | tee -a ${V_LOGFILE}   2>&1
echo "${V_DATE} - Restoring TNSNAMES in PRIMARY "  | tee -a ${V_LOGFILE}   2>&1
echo ""  | tee -a ${V_LOGFILE}   2>&1
echo "********************************************************************************"  | tee -a ${V_LOGFILE}   2>&1


ssh ${PRY_HOST} 'cp /opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2/network/admin/tnsnames.ora.prod  /opt/oracle/db/db'${V_DB_NAME}'/soft/db11.2.0.2/network/admin/tnsnames.ora'


echo ""  | tee -a ${V_LOGFILE}   2>&1
echo "${V_DATE} -  Waiting for media recovery before check the DATA GUARD Broker"  | tee -a ${V_LOGFILE}   2>&1
echo ""  | tee -a ${V_LOGFILE}   2>&1
echo "********************************************************************************"  | tee -a ${V_LOGFILE}   2>&1


sleep 200
dgmgrl <<-! | grep SUCCESS 1>/dev/null 2>&1
    connect ${V_DB_USR}/${V_DB_PWD}@${V_STANDBY}
    show configuration verbose;
!
if [ $? -ne 0 ] ; then
        echo "       ERROR: El status del Broker no es SUCCESS" | tee -a ${V_LOGFILE}   2>&1 ;
        V_RET=1
else
         echo "      DATA GUARD OK " | tee -a ${V_LOGFILE}   2>&1 ;

        V_RET=0

fi

Hope it helps.