Sunday, November 27, 2022
#!/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 youremail@yourcompany.com with your Email address:
EMAIL="youremail@yourcompany.com"
# 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(a.sequence#) from v\$archived_log a, v\$database d where a.resetlogs_change# = d.resetlogs_change#;
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(a.sequence#) from v\$archived_log a, v\$database d where a.resetlogs_change# = d.resetlogs_change# and a.applied in ('YES','IN-MEMORY');
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 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
select a.thread#, max(a.sequence#) "CURRENT_SEQUENCE" from v\$archived_log a, v\$database d where a.resetlogs_change# = d.resetlogs_change# group by a.thread# order by 1;
PROMPT
PROMPT Last Applied Log Sequence# on the Standby DB:
PROMPT ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
conn ${ID}/"${CRD}"@${DRDBNAME}
set linesize 1000 pages 100
select a.THREAD#, max(a.sequence#) MAX_APPLIED_SEQUENCE from v\$archived_log a, v\$database d where a.resetlogs_change# = d.resetlogs_change# and a.applied in ('YES','IN-MEMORY') group by a.THREAD# order by 1;
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: mahmmoudadel@hotmail.com
# 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".