Sync Standby DATABASe with PROD

[dba_admin]

I have a primary database prod and standby database stby. The standby database is seldom used. It's in "Managed Recovery" mode so that it will always in sync with PROD. However, sometimes the managed recovery mode might fail to perform the sync. Sometimes, it's too late to find the problem and the gap between them is as huge as 1000 archived logs. It will be a trouble to sync them at that time. Sometimes, even impossible.

I am thinking to write a script to check the following view on both PROD and STBY:

SELECT 'max:'||max(sequence#) FROM v$log_history;

Then, place the script on the cron job so that it will check everyday and notify you whenever the gap is more than , let's say, 5 archived logs.

I am glad to share the script and also looking for better solution.

Here are my scripts:

1. SQL

--maxlog.sql
set termout off
set head off
set echo off

spool &1

SELECT 'max:'||max(sequence#) FROM v$log_history;
spool off
exit

================================================================================
2. Shell

#!/bin/ksh

export EMAIL_ADDRESS='test@test.com'

. $HOME/.oracle

{
cd /export/home/oracle/test

export ORACLE_SID=gtre

sqlplus -s internal @maxlog.sql stby_gtre.log

sqlplus -s dba_admin/gdgdfff@ttnn.test.com @maxlog.sql prmy_gtre.log

echo stby_gtre.log
cat stby_gtre.log

echo prmy_gtre.log
cat prmy_gtre.log


stby=`grep max stby_gtre.log|awk -F: '{print $2}'`
prmy=`grep max prmy_gtre.log|awk -F: '{print $2}'`

if (( $prmy > $stby ))
then
cat /export/home/oracle/test/ck_stby.log|mailx -s "============stby database out of sync============" $EMAIL_A
DDRESS
fi
}> /export/home/oracle/test/ck_stby.log

[uday]

As an alternate idea, Did you consider using "ARCHIVE LOG LIST"

[vinit]

Hi,
Correct me if I am wrong... but going by the script you will always get an alert since the sequence id on your prod db will always be > then the seq on stby.

Cheers,