In real-time apply mode, the LGWR from the primary writes to the standby redo log at the target site.
Comparing archive log sequence numbers is not the correct way to check if the dataguard is operating correctly.
I have written the below script to check if the real time apply is working properly.
This works on the dataguard physical standby database.
It checks two columns (value and Datum_time) in V$dataguard_stats.
#!/bin/bash
################################################################################
## File Name : /adg_lag_check.sh #
## Description : Job to check gap in real time apply for DG #
## Author : Harris #
## Call Syntax : No parameters, writes local log file #
## Last Modified: 28/Mar/2013 By Harris #
################################################################################
export ORACLE_HOME=/u01/app/mydb01/oracle/product/11.2.0.3/db
export ORACLE_SID=mydb01
export PATH=$ORACLE_HOME/bin:$PATH
DATE=`date '+%m%d%Y%H%M%S'`
DBA_GROUP='harris@company.com'
sqlplus -s / as sysdba << EOF > adg_lag.log
set head off
set numf 99999999999
set feedback off
set echo off
set serveroutput on
select CASE WHEN
((extract(second from to_dsinterval(value)) + extract(minute from to_dsinterval(value)) * 60
+ extract(hour from to_dsinterval(value)) *60*60 + extract(day from to_dsinterval(value)) *60*60*24)
> 900) THEN 'Critical Data Guard lag more than 15mins'
WHEN value is null THEN ' Critical Data Broken status'
WHEN (((sysdate - to_date(DATUM_TIME,'MM/DD/YYYY HH24:MI:SS'))*24*60) > 15) THEN 'Critical Data Guard Network broken'
ELSE 'Data Guard OK'
END
from v\$dataguard_stats where name='apply lag';
EOF
if grep -c 'Critical' adg_lag.log; then
cat adg_lag.log | mailx -s "DataGuard Problem" $DBA_GROUP
fi;
