I have blogged previously about various ways you can monitor the progress of your physical standby, and I have now come across another way of determining how well your standby is doing at applying the redo logs that are coming it’s way.
The view V$RECOVERY_PROGRESS also gives you an interesting window into the performance of your standby. The official documentation on this is a bit confusing. Basically when you select from this view you get 9 rows of information on each of the various recovery operations you have started. So if you have only ever started managed recovery once since you started the instance you will only see 9 rows returned.
With a physical standby each time you start a managed recovery there will be this series of 9 rows entered into the view. Each row has a type, but while recovering the standby it is always Media Recovery.
SQL> select start_time, item, units, sofar
order by 1, 2
START_TIME ITEM UNITS SOFAR ----------------- -------------------------------- ---------- ---------- 04-06-08 08:45:54 Active Apply Rate KB/sec 3368 04-06-08 08:45:54 Active Time Seconds 6184 04-06-08 08:45:54 Apply Time per Log Seconds 1 04-06-08 08:45:54 Average Apply Rate KB/sec 35 04-06-08 08:45:54 Checkpoint Time per Log Seconds 1 04-06-08 08:45:54 Elapsed Time Seconds 3553772 04-06-08 08:45:54 Last Applied Redo SCN+Time 1.8447E+19 04-06-08 08:45:54 Log Files Files 2146 04-06-08 08:45:54 Redo Applied Megabytes 123788
Helpfully, each item has the unit that the associated measurement comes in.
The average apply rate includes time waiting for the redo to arrive. You can see the active time we have spent applying redo log information is a small proportion of the total elapsed time since we started managed recovery.
The active apply rate therefore gives a better indication of how fast you can actually apply redo on your standby, if you think you are generating redo at a faster rate than this number, then you may well be falling behind on your standby.
As indicated in the documentation this V$RECOVERY_PROGRESS view is actually just a subset of the V$SESSION_LONGOPS view, and while all the information is available there too, the V$RECOVERY_PROGRESS view summarises the relevant data for your media recovery progress in a standby quite nicely.