Hello Yong, Archive gap is different from archive applied (Media Recovery). I have encountered many situations where there is no archive gap but the archive apply is behind as much as 100+ logs. Been meaning to blog about this. Also, I have not been able to test standby using open read only when archive apply is so behind. With 11.2 there's column *gap_status from **v$archive_dest_status providing information on archive gap without having to connect to standby.* * * *Here is an example:* * * SELECT dest_id,db_unique_name,status,database_mode,recovery_mode,archived_seq# archived ,applied_seq# applied, (CASE WHEN archived_seq# - applied_seq# > 10 THEN 'ERR-' || TO_CHAR (archived_seq# - applied_seq#) ELSE TO_CHAR (archived_seq# - applied_seq#) END) gap, gap_status FROM (SELECT dest_id,db_unique_name,database_mode,recovery_mode,applied_seq#,gap_status, LAG (archived_seq#) OVER (ORDER BY dest_id) archived_seq#, status FROM v$archive_dest_status) WHERE dest_id = 2; DEST_ID DB_UNIQUE_N STATUS DATABASE_MODE RECOVERY_MODE ARCHIVED APPLIED GAP GAP_STATUS ------- ----------- --------- --------------- ----------------------- ---------- ---------- -------- ---------- 2 XXXXXXXXXXX VALID MOUNTED-STANDBY MANAGED REAL TIME APPLY 160591 160561 ERR-30 NO GAP * * -Michael. On Wed, Oct 24, 2012 at 2:42 PM, Yong Huang <yong321@xxxxxxxxx> wrote: > Looks like there're various ways to detect log apply lag. I used to check > v$archive_gap, but it seems unreliable. Now my script checks v$archived_log > where applied='YES'. It's not applied<>'NO' so I'm immune from the 11g > 'IN-MEMORY' feature as Joe mentioned. (Yet another method: Some shops check > for '^Media Recovery Waiting for thread' in alert.log, and will be affected > by this feature.) > > Saibabu's first SQL that runs on a read-only active data guard standby > contains > select scn_to_timestamp(current_scn) from v$database > Scn_to_timestamp probably relies on SMON's update of the sys.smon_scn_time > table. But I think SMON doesn't do that as often to catch up with > v$database.current_scn. So I have to deduct some number, e.g. 500, > sometimes more, from current_scn on my database to make that SQL work (i.e. > to avoid ORA-08181: specified number is not a valid system change number). > > Danut's SQL checks v$standby_log.last_time. A minor concern. Judging by > the fact that Oracle adds next_change# and next_time in 11gR2 as exact > duplicates of last_change# and last_time (check v$fixed_view_definition), I > wonder if the two last_* columns will eventually be removed in future > versions. In most views about redo logs, there's name next_*, not last_*. > > Yong Huang > > -----Original Message----- > I used also the next statement: > select round(24*60*(sysdate - last_time)) from V$STANDBY_LOG where > sequence# <> 0; > > the result it is in minutes. > > > Danut Bancea > Tel: 416 643 1631 > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Saibabu Devabhaktuni > Sent: October 24, 2012 2:22 PM > To: free > Subject: Re: Applying logs to standby in 11g > > Yong Huang just sent me a note that the below query doesn't work on the > dataguard when it is in the mount mode, he is right below query works > only on Active dataguard and when the standby is in read only mode. > select > abs(nvl(max(ceil( > (extract(day from replication_lag)*24*60*60)+ > (extract(hour from replication_lag)*60*60)+ > (extract(minute from replication_lag)*60)+ > (extract(second from replication_lag)) > )),0)) lag_in_seconds from > (select sysdate-scn_to_timestamp(current_scn) replication_lag from > v$database); > > You can use below query to get the lag when standby is in mount mode in > addition to relying on v$dataguard_stats: > > select max(lag_time) lag_time from > (select max(timestamp) lag_time from v$recovery_progress where type='Media > Recovery' and item='Last Applied Redo' > union all > select max(checkpoint_time) lag_time from v$datafile where file#=1); > > Parameter standby_max_data_delay and current_scn in v$database rely on the > recovery progress maintained in v$recovery_progress. > > Thanks, > Sai > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l