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;
/