http://www.forum.askdba.org/ Forum for Oracle DBA's en Sun, 12 Feb 2012 10:19:23 +0000 http://www.forum.askdba.org/topic/monitoring-archive-gap-in-standby#post-80 Tue, 25 Aug 2009 16:35:50 +0000 Amit Bansal 80@http://www.forum.askdba.org/ <p>You can use following sql to monitor the gap in standby using following sql</p> <pre> sqlplus -s "/ as sysdba" &lt;&lt;EOF set head off set feedb off select (select name from V\$DATABASE),(select max(sequence#) from v\$archived_log where dest_id=1) Current_primary_seq,( select max(sequence#) from v$\archived_log where to_date(next_time,'dd-mm-yyyy') &gt; sysdate-1 and dest_id=2 ) max_stby,(select nvl((select max(sequence#) - min(sequence#) from v\$archived_log where to_date(next_time,'dd-mm-yyyy') &gt; sysdate-1 and dest_id=2 and applied='NO'),0) from dual) "To be applied",((select max(sequence#) from v\$archived_log where dest_id=1) - (select max(sequence#) from v\$archived_log where dest_id=2)) "To be Shipped" from dual / </pre> <p>Assumptions -<br /> Dest_id=1 --&gt; Primary DB<br /> Dest_id=2 --&gt;Standby site</p> <p>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</p> <p><a href="http://forums.oracle.com/forums/thread.jspa?messageID=3708034" rel="nofollow">http://forums.oracle.com/forums/thread.jspa?messageID=3708034</a> </p>