Re: Applying logs to standby in 11g

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: Michael Dinh <mdinh235@xxxxxxxxx>
  • Date: Thu, 25 Oct 2012 11:49:57 -0700 (PDT)

Michael,
Indeed "archive gap" should be considered different from "archive lag". My 
check standby script used to have two parts, check gap (query v$archive_gap) 
and check lag (query v$archived_log). Later I commented out the first part.

Due to the specific meaning of "gap", the "GAP" column in your script may be 
called "LAG" so it's different from GAP_STATUS. Or you think whenever 
gap_status is 'NO GAP', your gap will be 0 and vice versa? I find that 
applied_seq# column in v$archive_dest_status is smaller than max(sequence#) in 
v$archived_log where applied='YES'. The following is from my 11gR2 2-node RAC 
primary. Archived_thread# and applied_thread# happen to be the same so the 
concept of lag by simple subtraction of seq# makes sense:

SQL> select archived_thread#, archived_seq#, applied_thread#, applied_seq#, 
gap_status from v$archive_dest_status where dest_id = 2;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# GAP_STATUS
---------------- ------------- --------------- ------------ 
------------------------
               2         21118               2        21114 NO GAP <-- 
21118-21114=4

SQL> select max(sequence#) from v$archived_log where applied = 'YES' and 
thread# = 2;

MAX(SEQUENCE#)
--------------
         21117  <-- only 1 behind

The error column is null. I guess v$archive_dest_status.applied_* columns are 
more conservative.

Yong Huang

--- On Thu, 10/25/12, Michael Dinh <mdinh235@xxxxxxxxx> wrote:

From: Michael Dinh <mdinh235@xxxxxxxxx>
Subject: Re: Applying logs to standby in 11g
To: yong321@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Date: Thursday, October 25, 2012, 8:24 AM

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


Other related posts: