Schedule monitor archive gap script for Dataguard physical standby sync

Monitor the gap between data guard physical Standby with email alert

Create a database link between the primary and standby link:

--- Create a database line on primary server linking with DR Server
CREATE DATABASE LINK PRIM_DR connect to user identified by password using 'PRIM_DR';

Configure UTL_MAIL if not configured for mail alert: https://smarttechways.com/2012/11/21/steps-to-configure-the-utl-mail-in-oracle-database/

Create a procedure that uses the database link to check the GAP between primary and standby and send a mail alert:

Example: If the gap is more than 5 sequences then mail is fired

CREATE OR REPLACE PROCEDURE DR_SYNC_TEST AS
PRDSEQ NUMBER;
STDSEQ NUMBER;
BEGIN
SELECT MAX(SEQUENCE#) INTO PRDSEQ FROM V$ARCHIVED_LOG;
SELECT MAX(SEQUENCE#) INTO STDSEQ FROM V$ARCHIVED_LOG@ICDR WHERE APPLIED='YES';
IF STDSEQ <(PRDSEQ-5) THEN
begin UTL_MAIL.send(sender => 'dba_support@gmail.com',recipients => 'dba_support@gmail.com',subject => 'DR SYNC TEST b/w standby and primary databases has been Failed' ,message =>  'Standby database is not in sync with the Primary database');
end;
END IF;
END;
/

Schedule the procedure on a daily basis to check the sync between the primary and standby data guard

begin
dbms_scheduler.create_job (
job_name => 'Dr_test_job',
job_type => 'PLSQL_BLOCK',
job_action => 'DR_SYNC_TEST',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; BYHOUR=9;',
end_date => NULL,
enabled => TRUE,
comments => 'Job to exec the DR_SYNC_TEST procedure');
end;
/