Monitoring your Standby with V$RECOVERY_PROGRESS

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
from V$recovery_progress
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.