RE: Applying logs to standby in 11g

From: Yong Huang <yong321_at_yahoo.com>
Date: Wed, 24 Oct 2012 14:42:52 -0700 (PDT)
Message-ID: <1351114972.50016.YahooMailClassic_at_web184801.mail.gq1.yahoo.com>

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

Received on Wed Oct 24 2012 - 23:42:52 CEST