[Database] Automatic Standby Recreation for Data Guard

不幸にして待機インスタンスを再作成しなければならないことがあります。これは多くの場合アーカイブログや待機データファイルをなくしたり、フェールオーバー、その他の理由で起こります。
これが、簡単に待機インスタンスを再作成するスクリプトを持っていたかった理由です。このスクリプトはいくつかの前提条件を考慮して待機系を再作成します。
  • Databaseのバージョンは少なくとも11gR1であること
  • ダミーインスタンスが待機ノードで開始していること（改良すれば不要になるかもしれません）
  • ブローカの構成は削除されていないこと
  • 2個のTNSNAMESファイルがあること。一つは待機系作成のため（SIDを使用）、もう一つはサービス名を使う本番系（ブローカサービス名を含む）
  • 環境変数が環境DBスクリプトに定義されていること（ORACLE_HOME、PATHなど）
  • 待機系のディレクトリツリーが修正されていないこと
現在このスクリプトを11gR2 Data Guardのテスト環境で使用しています。
改善点があればどしどしご意見お寄せください、お待ちしています。

#!/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}.domain.com,${V_SBY_SID}.domain.com,${V_SBY_SID}_DGMGRL.domain.com' 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



原文はこちら。
http://blogs.oracle.com/bcndatabase/2011/02/automatic_standby_recreation_for_data_guard.html
-
カテゴリ

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)