Below is an example script (check_stby.sql) which I’ve used to compare the most
recently applied archive log file at a standby database to the most recently archived log
at the primary database.
(I know, DataGuard can do this for me with much less effort..)

I am using sql*plus bind variables because I needed to connect to 2 different databases
and compare their results.
The standby database is in mounted mode, so this precludes the idea of trying to get this information
across a database link.

The sql script is then called by a shell script. (check_stby.sh) which gets called by
a host-user-defined-metric in Enterprise Manager’s Grid Control system.

If the returned value exceeds the metric’s threshold, an alert is triggered and I receive an
email warning me to check the status of the standby database.

check_stby.sql

set echo off
set feedback off
set termout off
VARIABLE prim_seq NUMBER
variable stby_seq number
variable diff number

connect sys/xxxx@primarydb.world.com as sysdba
begin
select max(sequence#)into :prim_seq from v$archived_log;
end;
/

connect sys/xxxx@standbydb.world.com as sysdba
begin
select max(sequence#) into :stby_seq from v$log_history;
end;
/

begin
:diff := :prim_seq - :stby_seq   ;
end;
/
exit :diff;

check_stby.sh

#!/bin/ksh

export ORACLE_HOME=/home/oracle/product/10.2.0

$ORACLE_HOME/bin/sqlplus -s /nolog @check_stby.sql

echo "em_result="$?"\n"

Recommended reading: