Re: Applying logs to standby in 11g
From: Michael Dinh <mdinh235_at_gmail.com>
Date: Thu, 25 Oct 2012 06:24:46 -0700
Message-ID: <CAENwkM4NSfnxrZZJm+YvJTJO7CjADQ76-7=t52CiomD85Yw4Yg_at_mail.gmail.com>
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.
SELECT
dest_id,db_unique_name,status,database_mode,recovery_mode,archived_seq# archived ,applied_seq# applied,
(CASE WHEN archived_seq# - applied_seq# > 10
FROM
(SELECT
dest_id,db_unique_name,database_mode,recovery_mode,applied_seq#,gap_status,
*
*
-Michael.
> 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
Date: Thu, 25 Oct 2012 06:24:46 -0700
Message-ID: <CAENwkM4NSfnxrZZJm+YvJTJO7CjADQ76-7=t52CiomD85Yw4Yg_at_mail.gmail.com>
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_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 - 15:24:46 CEST