Shell Script To check the lag / synchronization status between Primary and Standby Oracle DBs http://dba-tips.blogspot.ae/2017/11/shell-script-to-check-lag-sync-status.html

check_standby_lag.sh

#!/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		
#					#   #     #
# Author:	Mahmmoud ADEL	      # # # #   ###
# Created:	29-10-2015	    #   #   # #   # 
# Modified:	21-04-2021 allowed a DBA user to be used for SQLPLUS login instead of SYSDBA
#		21-04-2021 Simplified the Variables section for the end user.
#
# #########################################################################################

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

# Here you replace [email protected] with your Email address:
EMAIL="[email protected]"

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

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

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

# Log Directory Location:
LOG_DIR='/tmp'

# Here you replace DBA_USER with a real user having DBA privlege:
ID=DBA_USER

# Here you replace ABC123 with the DBA user password on the standby DB:
CRD='ABC123'

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

export EMAIL
export ORACLE_SID
export DRDBNAME
export ORACLE_HOME
export LOG_DIR
export ID
export CRD
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}"
export MAIL_LIST

# Neutralize login.sql file:
# #########################
# Existance of login.sql file under current working directory eliminates many functions during the execution of this script:

        if [ -f ./login.sql ]
         then
mv ./login.sql   ./login.sql_NeutralizedBy${SCRIPT_NAME}
        fi


# #########################################
# 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 ${ID}/"${CRD}"@${DRDBNAME}
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 ${ID}/"${CRD}"@${DRDBNAME}
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:
echo "Sending an Email alert ..."
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 Primary and Standby is: ${LAG}
echo

# De-Neutralize login.sql file:
# ############################
# If login.sql was renamed during the execution of the script revert it back to its original name:
        if [ -f ./login.sql_NeutralizedBy${SCRIPT_NAME} ]
         then
mv ./login.sql_NeutralizedBy${SCRIPT_NAME}  ./login.sql
        fi

# #############
# END OF SCRIPT
# #############
# REPORT BUGS to: [email protected]
# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM:
# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".