[Amit Bansal]

You can use following sql to monitor the gap in standby using following sql

<br />
sqlplus -s "/ as sysdba" &lt;&lt;EOF<br />
set head off<br />
set feedb off<br />
select (select name from V$DATABASE),(select max(sequence#) from v$archived_log<br />
where dest_id=1) Current_primary_seq,( select max(sequence#) from v$archived_log<br />
where to_date(next_time,'dd-mm-yyyy') &gt; sysdate-1<br />
and dest_id=2 ) max_stby,(select nvl((select max(sequence#) - min(sequence#) from v$archived_log<br />
where to_date(next_time,'dd-mm-yyyy') &gt; sysdate-1 and dest_id=2 and applied='NO'),0)  from<br />
dual) "To be applied",((select max(sequence#) from v$archived_log<br />
where dest_id=1) - (select max(sequence#) from v$archived_log<br />
where dest_id=2)) "To be Shipped"<br />
from dual<br />
/<br />

Assumptions –

Dest_id=1 –> Primary DB

Dest_id=2 –>Standby site

This query reports the number of archives which need’s to be shipped along with number of archive log’s which need to be applied. If you are looking for script, you can get the logic from following thread

http://forums.oracle.com/forums/thread.jspa?messageID=3708034

[Author]

Posts