Re: Applying logs to standby in 11g

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 25 Oct 2012 11:49:57 -0700 (PDT)
Message-ID: <1351190997.54718.YahooMailClassic_at_web184803.mail.gq1.yahoo.com>

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_at_gmail.com> wrote:

From: Michael Dinh <mdinh235_at_gmail.com> Subject: Re: Applying logs to standby in 11g To: yong321_at_yahoo.com
Cc: oracle-l_at_freelists.org
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_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-l

Received on Thu Oct 25 2012 - 20:49:57 CEST