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;


Oracle Database 10g Enterprise Edition Release - 64bi

PL/SQL Release - Production

CORE      Production

TNS for Linux: Version - Production

NLSRTL Version - 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_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;

----------------- ------- --------- ----------- ------------- ----------
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!


14 thoughts on “Determining Dataguard Standby Apply Rate

  1. 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.


  2. 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.


  3. 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.


  4. 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


  5. select * from v$version;

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


    no rows selected

    no rows is showing for query in the stand by database

  6. my stand by and primay or in sync

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

    ——— ——– ———- ————
    MRP0 N/A 3540 WAIT_FOR_LOG

    SQL> select * from v$archive_gap;

    no rows selected

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s