Automatic Standby Recreation for Data Guard
- by pablo.boixeda(at)oracle.com
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 hostWe are currently using it on our 11gR2 Data Guard tests.Any improvements will be welcome!
Normal
0
21
false
false
false
ES
X-NONE
X-NONE
MicrosoftInternetExplorer4
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
#!/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 ;
Normal
0
21
false
false
false
ES
X-NONE
X-NONE
MicrosoftInternetExplorer4
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
V_RET=0
fi
Hope it helps.
