Re: Applying logs to standby in 11g
Date: Wed, 24 Oct 2012 21:14:33 -0500
Message-ID: <CAN-mCbMGrTE=1aaG=s9KqMNSOA+gvbv1FWzuBYeWkDpEE6Zh4g_at_mail.gmail.com>
I use below query on the standby site to check the gap.
SELECT distinct ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence
Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# -
APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE
(THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM
V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE
(THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM
V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
On Wed, Oct 24, 2012 at 4:42 PM, Yong Huang <yong321_at_yahoo.com> 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_at_xxxxxxxxxxxxx [mailto:oracle-l-bounce_at_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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 25 2012 - 04:14:33 CEST