To check the LAG between Primary & Standby database

#!/bin/bash
# #################################################
# This script MUST run from the Primary DB server.
# It checks the LAG between Primary & Standby database
# To be run by ORACLE user
# ###############################################

# #####################################
# Variables MUST be edited by the user: [Otherwise the script will not work]
# #####################################

# Here you replace youremail@yourcompany.com with your Email address:
EMAIL_RECEIVER=”youremail@yourcompany.com”
export EMAIL_RECEIVER

# Replace ${ORACLE_SID} with the Primary DB instance SID:
ORACLE_SID=${ORACLE_SID}
export ORACLE_SID

# Replace STANDBY_TNS_ENTRY with the Standby Instance TNS entry you configured in the primary site tnsnames.ora file:
DRDBNAME=STANDBY_TNS_ENTRY
export DRDBNAME

# Replace ${ORACLE_HOME} with the ORACLE_HOME path on the primary server:
ORACLE_HOME=${ORACLE_HOME}
export ORACLE_HOME

# Log Directory Location:
LOG_DIR=’/tmp’
export LOG_DIR

# Here you replace SYSPASS with user SYS password on the standby DB:
CRD=’SYSPASS’
export CRD

# Replace “5” with the number of LAGGED ARCHIVELOGS if reached an Email alert will be sent to the receiver:
LAGTHRESHOLD=5
export LAGTHRESHOLD

# #############################################
# Other variables will be picked automatically:
# #############################################

SCRIPT_NAME=”check_standby_lag.sh”
export SCRIPT_NAME

SRV_NAME=`uname -n`
export SRV_NAME

LNXVER=`cat /etc/redhat-release | grep -o ‘[0-9]’|head -1`
export LNXVER

MAIL_LIST=”-r ${SRV_NAME} ${EMAIL_RECEIVER}”
export MAIL_LIST

# #########################################
# Script part to execute On the Primary:
# #########################################
# Check the current Redolog sequence number:
PRDBNAME_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
select name from v\$database;
exit;
EOF
)

PRDBNAME=`echo ${PRDBNAME_RAW} | awk ‘{print $NF}’`

PRSEQ_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
select max(sequence#) from v\$archived_log;
exit;
EOF
)

PRSEQ=`echo ${PRSEQ_RAW} | awk ‘{print $NF}’`
export PRSEQ

# #########################################
# Script part to execute On the STANDBY:
# #########################################

# Get the last applied Archive Sequence number from the Standby DB:

DRSEQ_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
conn SYS/”${CRD}”@${DRDBNAME} AS SYSDBA
select max(sequence#) from v\$archived_log where applied=’YES’;
exit;
EOF
)

DRSEQ=`echo ${DRSEQ_RAW} | awk ‘{print $NF}’`
export DRSEQ

# Compare Both PRSEQ & DRSEQ to detect the lag:
# ############################################
LAG=$((${PRSEQ}-${DRSEQ}))
export LAG

if [ ${LAG} -ge ${LAGTHRESHOLD} ]
then
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
set linesize 1000 pages 100
spool ${LOG_DIR}/DR_LAST_APPLIED_SEQ.log
PROMPT Current Log Sequence on the Primary DB:
PROMPT ———————————————————

archive log list

PROMPT
PROMPT Last Applied Log Sequence# on the Standby DB:
PROMPT —————————————————————–

conn SYS/”${CRD}”@${DRDBNAME} AS SYSDBA
set linesize 1000 pages 100
select THREAD#,max(SEQUENCE#) from V\$ARCHIVED_LOG where APPLIED=’YES’ group by THREAD#;
exit;
EOF
# Send Email with LAG details:
mail -s “ALARM: DR DB [${DRDBNAME}] is LAGGING ${LAG} sequences behind Primary DB [${PRDBNAME}] on Server [${SRV_NAME}]” ${MAIL_LIST} < ${LOG_DIR}/DR_LAST_APPLIED_SEQ.log
fi

echo
echo Primary DB Sequence is: ${PRSEQ}
echo Standby DB Sequence is: ${DRSEQ}
echo Number of Lagged Archives Between is: ${LAG}
echo

# #############
# END OF SCRIPT
# #############

Reinstating a Failed Oracle Rac Primary Database using Dataguard Broker

In the previous article, we have performed the Oracle RAC to RAC physical standby database failover steps in 11gR2. Now, let us continue with the reinstate process of the failed primary RAC database.

Below is the environment details:-Note:-

As per the current status, the primary site ORCL_PRODRAC1 is failed and physical standby site ORCL_PRODRAC2 already converted as primary by failover process. So, now ORCL_PRODRAC2 is primary and ORCL_PRODRAC1 is failed primary which should be reinstated.

Steps involved in reinstate of failed primary RAC database:-

A) Check Database Configuration
B) Performing Reinstate
C) Verify the Reinstate of new physical standby database

A) Check Database Configuration:-

Configuration check in Cluster:-

Failed Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC2
DGMGRL> show configuration verbose;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC2 - Primary database
ORCL_PRODRAC1 - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

B) Performing Reinstate:-

During the reinstate using dataguard broker, the failed primary site will be flashbacked and converted as physical standby database, then the media recovery process will be started automatically started. These can be view in the alert log of the database which is to be reinstated.

DGMGRL> reinstate database 'ORCL_PRODRAC1';

Reinstating database "ORCL_PRODRAC1", please wait...
Operation requires shutdown of instance "ORCL1" on database "ORCL_PRODRAC1"
Shutting down instance "ORCL1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL_PRODRAC1" ...
Operation requires shutdown of instance "ORCL1" on database "ORCL_PRODRAC1"
Shutting down instance "ORCL1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL_PRODRAC1" ...
Reinstatement of database "ORCL_PRODRAC1" succeeded

Attached the output of alert log of instance ORCL1 of database  ORCL_PRODRAC1.

C) Verify the Reinstate of new physical standby database:-

Configuration check in Cluster:-

Failed Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Verify in DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration verbose;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC2 - Primary database
ORCL_PRODRAC1 - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

From the above we can notice that the failed primary is now converted as physical standby database. If needed we can switchback it to primary. Refer here for the switchover process.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba