Monitoring archive gap in standby

AskDBA.org Forum » DBA - Wiki

Monitoring archive gap in standby

(1 post)

Average Rating For This Topic:

Rate This Topic Yourself:

Started 11 months ago by Amit Bansal

Tags:

Amit Bansal

Oracle DBA
Joined: Jun '08
Posts: 44

offline

Reputation:57

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

AskDBA.org Forum

Subscribe to aSkDba.org Forum Posts

AskDBA.org Forum » DBA - Wiki

Monitoring archive gap in standby

Reply