AskDBA.org Forum Forum for Oracle DBA's

About This Topic

• Posted 2 years ago
• By Amit Bansal
• Latest reply from Amit Bansal
• This topic is not a support question
• RSS feed for this topic

Tags

• archive
• dataguard
• primary
• standby

1. 

   Amit Bansal
   

   Oracle DBA
   Joined: Jun '08
   Posts: 73

   offline

   Reputation:96

    

   
   Posted 2 years ago
   #

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

   sqlplus -s "/ as sysdba" <<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') > 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') > 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
   /
   

   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

   Cheers
   Amit
   http://askdba.org/weblog/