Determining Dataguard Standby Apply Rate

It can be very useful to see how your standby database is performing, whether your infrastructure has the capacity to keep your standby in synch and how fast it can actually apply the data. In particular if you have decided to save on hardware and licensing costs at your business continuity site, then you may have a lower performing server for your standby than you have at your primary location. If this is the case you really want to see what kind of apply rate you are managing, and how this compares to the amount of redo your primary is generating.

Paul Moen from Pythian wrote about a script he uses to keep a historical record of the apply rate. However if you want something more immediate you can maybe use a view called V$STANDBY_APPLY_SNAPSHOT. There does not appear to be much information out there on this view, and it was only recently that I stumbled across it. This is not documented in the Oracle documentation  in either the 10g or 11g docs. There is also nothing about this view on metalink as a search turned up no hits. The view gets a brief mention in a talk by Dataguard product manager, Larry Carpenter.

Here is a description of the view:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

SQL> desc v$standby_apply_snapshot

 Name                              Null?    Type

 --------------------------------- -------- -----------------------------------------------

 SNAPSHOT_TIME                               DATE

 SESSION_ID                                  NUMBER

 THREAD#                                     NUMBER

 RESET_TIMESTAMP                             NUMBER

 SEQUENCE#                                   NUMBER

 BLOCK#                                      NUMBER

 APPLIED_SCN                                 NUMBER

 APPLIED_TIME                                DATE

 NEWEST_RESET_TIMESTAMP                      NUMBER

 NEWEST_ARCHIVED_SEQ#                        NUMBER

 NEWEST_TIME                                 DATE

 NEWEST_USED                                 NUMBER

 NEWEST_SRL_SEQ#                             NUMBER

 BLOCKSIZE                                   NUMBER

 APPLY_RATE                                  NUMBER

This is what I see on my system:

sys@STANDBY> select snapshot_time, thread#, sequence#, applied_scn, applied_time, apply_rate
from V$standby_apply_snapshot;

SNAPSHOT_TIME     THREAD# SEQUENCE# APPLIED_SCN APPLIED_TIME  APPLY_RATE
----------------- ------- --------- ----------- ------------- ----------
27-02-08 15:45:08     1      8684    2549146933 27-02-08 15:44:48  47234
27-02-08 15:45:08     2      7145    2549146847 27-02-08 15:44:49  47234

The advantage of this view is that it’s updated pretty much continually, and you can see from the applied_time how far behind you are in applying. So is my apply rate 47MB per second? I do hope so. Though in Larry’s talk he states the apply rate is in KB/sec which is a little bit fantastic for the numbers I am seeing!

About these ads
Leave a comment

14 Comments

  1. Really good stuff. Thanks!

    Reply
  2. jarneil

     /  February 27, 2008

    Well, It just seems like this V$ view has been completely missed from the Oracle documentation.

    Reply
  3. Hi Jason,

    Nice find. It pays to read or skim the presentations from Oracle representatives.

    I tried on a local standby 10.2 and the apply rate was around 736. Looking at the historical rate it is averaging 333 KB/sec.

    The carpenter presentation was good as well.

    Cheers
    Paul

    Reply
  4. jarneil

     /  February 28, 2008

    Hi Paul,

    Thanks for reading!

    I really liked your script, quite useful to have a historical record, but this view could be good for your current rate.

    jason.

    Reply
  5. Dear Jason,

    we are currently dealing with Oracle Support regarding bug “7507011 – V$DATAGUARD_STATS may be slow or may return inconsistent/misleading results”. It seems that the view v$dataguard_stats is using the view V$standby_apply_snapshot and that it has some defects. Oracle has corrected them but only in 11gR2 there will be no backport.

    It seems that in versions < 11gR2 there is no guaranteed way to determine the apply / transport lags.

    Regards,
    Martin

    Reply
  6. jarneil

     /  October 27, 2009

    Hi Martin,

    Yep, definitely some quite infuriating behaviour with V$dataguard_stats, and it can take ages to query. Just read the note: “may be slow especially in RAC” Yep, I’m mostly running RAC, and concur with this.

    11gR2 is the release to go for if at all possible. You’ve probably heard of the V$STANDBY_EVENT_HISTOGRAM as well

    jason.

    Reply
  7. In our problem situation, the view showed huge apply/transport lags (>50000s) when in fact the database was doing realtime apply on the same log sequence.

    Reply
  8. rahul reddy

     /  January 7, 2010

    select * from v$version;

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
    PL/SQL Release 10.2.0.4.0 – Production
    CORE 10.2.0.4.0 Production
    TNS for Linux: Version 10.2.0.4.0 – Production
    NLSRTL Version 10.2.0.4.0 – Production

    SQL> select SNAPSHOT_TIME,THREAD#,SEQUENCE#,APPLIED_SCN,APPLIED_TIME,APPLY_RATE from v$standby_apply_snapshot;

    no rows selected

    no rows is showing for query in the stand by database

    Reply
  9. rahul reddy

     /  January 7, 2010

    my stand by and primay or in sync

    select process, client_process, sequence#, status from v$managed_standby;

    PROCESS CLIENT_P SEQUENCE# STATUS
    ——— ——– ———- ————
    ARCH ARCH 0 CONNECTED
    ARCH ARCH 0 CONNECTED
    MRP0 N/A 3540 WAIT_FOR_LOG

    SQL> select * from v$archive_gap;

    no rows selected

    Reply
  10. jarneil

     /  January 8, 2010

    Hello Rahul,

    can you see what recovery mode you are in:

    select recovery_mode from V$ARCHIVE_DEST_STATUS;

    jason.

    Reply
  11. rahul reddy

     /  January 8, 2010

    select recovery_mode from V$ARCHIVE_DEST_STATUS;

    RECOVERY_MODE
    ———————–
    MANAGED REAL TIME APPLY

    Reply
  1. Log Buffer #86: a Carnival of the Vanities for DBAs
  2. Monitoring your Standby with V$RECOVERY_PROGRESS « jarneil

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 56 other followers

%d bloggers like this: