Description
This alert will monitor the delta between Oracle redo logs that are archived on the primary instance and those that are applied on the standby instance.
Note: Additional grants may be required on the monitored instances if the monitoring user cannot query from v$archived_log.
This describes how to set this up as a custom alert, but the same SQL can be used to define a custom metric (see Oracle custom metrics section).
SQL Statement:
select (
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG
WHERE DEST_ID=1
AND ARCHIVED='YES'
) - (
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG
WHERE DEST_ID=2
AND APPLIED='YES'
) from dual;
