RE: Applying logs to standby in 11g
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 24 Oct 2012 14:42:52 -0700 (PDT)
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
Other related posts:
