Monitoring Standby Database


If we use real-time apply changes are applied to the standby database as soon as they are written to the standby redo logs, which should keep standby database as up to date as possibly without going to Maximum Availability or Maximum Protection.

However, if our primary database fails, how can we check what is the last change transmitted from the primary, or that all changes transmitted were actually applied before we activate standby?
Of course we can trust that Oracle will apply all the transmitted redo, when standby is activated, but without knowing what was the last transmitted change we have no way of verifying it. It is also possible that the fail over decision depends on how far behind the primary the standby was at the time of failure or how much data can potentially be lost.

One way of finding the information we need is by checking v$standby_log and v$recovery_progress views.
v$standby_log displays information about standby redo logs. Columns LAST_CHANGE# and LAST_TIME can be used to find last changes transmitted from primary.

SQL> select sequence#,ARCHIVED,APPLIED from v$archived_log order by sequence#;

Trimmed O/P

SEQUENCE# ARCHIVED APPLIED
———- ———— ————————————
84422 YES YES
84423 YES YES
84424 YES YES
84425 YES YES
84426 YES YES
84427 YES YES
84428 YES YES
84429 YES YES
84430 YES YES
84431 YES YES
84432 YES IN-MEMORY

1

1

1

v$recovery_progress can be used to monitoring database recovery operations, and gives us access to information like: Last Applied Redo, Active Apply Rate, Average Apply Rate, Apply Time per Log and a few more. The item we are interested in is “Last Applied Redo” and the value of the “TIMESTAMP” column. The value should be very close to or match the value of LAST_TIME column from v$standby_log view.

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.

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

Advertisements

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