Below script is helpful in monitoring lag in standby database and send mail to DBAs in case the lag is increasing. For the script to work, make sure dataguard broker is enabled between primary and standby database.

 

SEE DGBROKER ARTICLE:

 How to setup dgbroker in oracle 12c:

Useful dgmgrl commands:

 

SCRIPT PREPARATION:

PRIMARY DB UNIQUE_NAME – > PRIMDB
STANDBY DB UNIQUE_NAME -> STYDB

cat /home/oracle/dgmgrl_standby_lag.sh

#!/bin/bash
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=primdb
export PATH=$ORACLE_HOME/bin:$PATH
echo -e “show database stydb”|${ORACLE_HOME}/bin/dgmgrl sys/orcl1234 > DB_DG_DATABASE.log
cat /home/oracle/DB_DG_DATABASE.log  | grep “Apply Lag”  > FILTERED_DB_DG_DATABASE.log
time_value=`cut -d ” ” -f 14 FILTERED_DB_DG_DATABASE.log`
time_param=`cut -d ” ” -f 15 FILTERED_DB_DG_DATABASE.log`

if [[ “$time_param” == “minutes” && “$time_value” -ge 1 ]]
then
mailx -s “DREAIDB LAG is in minutes ”  suppor@dbaclass.com<DB_DG_DATABASE.log
else
if [[ “$time_param” == “seconds” && “$time_value” -ge 30 ]]
then
mailx -s “DREAIDB LAG is in seconds ”   support@dbaclass.com<DB_DG_DATABASE.log
else
if  [[ “$time_param” == “hour(s)” && “$time_value” -ge 1 ]]
then
mailx -s “DREAIDB LAG is in hours ”   support@dbaclass.com <DB_DG_DATABASE.log
fi
fi
fi

 

##########################

Now configure the the script in crontab

00,10,20,30,40,50 * * * * /home/oracle/dgmgrl_standby_lag.sh > /tmp/dg_lag.log