Oracle blog: Undocumented 11g new features of standby database

Monday, September 06, 2010

Undocumented 11g new features of standby database

Apart from the documented new features of being able to use BCT (block change tracking) on the physical standby, ability to use rman duplicate command over the sqlnet to build physical standby from the primary database, here are some of the undocumented features (or change in behavior) introduced as of 11.2.0.1:

1) Ability to defer log boundary checkpoints ("_defer_log_boundary_ckpt")

2) Ability to define number of logs checkpoint can get behind ("_defer_log_count")

3) Media recovery process now reads maximum of 1024 pending asynch block reads

4) DBWR only do the writes for incremental checkpoint unless free buffer is requested

5) v$recovery_progress view is updated even when only few redo blocks applied by the MRP

6) Removed v$standby_apply_snapshot view.

7) More smoothened incremental recovery checkpoint writes

The best feature among the above is the ability to defer checkpoints happen at the log boundary.

Parameters "_defer_log_boundary_ckpt" set to "TRUE" by default and "_defer_log_count" set to 2 by default will automatically get in to play only when the physical standby is actively applying the logs instead of waiting for redo or logs to arrive in managed recovery mode.

During my tests, changing "_defer_log_count" to different values didn't work, Oracle seems to use value 2 only (more testing is needed).

If standby ever falls behind or catching up, it doesn't have to wait for the mandatory checkpoint at the log boundary to finish before applying the next log, which is a big deal if the underlying storage system can only support limited iops.

Media recovery process on the standby will read the data blocks in parallel, Oracle used "db file parallel read" wait event for this until 9i, starting 10g they changed it to "recovery read" wait event.

In 10g, MRP process can keep issuing asynch data block read requests in batches until the overall outstanding block reads for a given MRP process reach 4096. This has caused a havoc in our 10g standbys, as this huge burst of I/O caused underlying SAN to suffer even worse (finally causing corruptions). They have done it to improve the performance of redo apply.

Oracle silently changed it to read 1024 outstanding asynch block reads in 11g.

In order to even out the write activity, dbwr seems to be doing incremental recovery checkpoint writes only, unless dbwr is asked to free up buffers, (you can verify it by setting "_disable_incremental_recovery_ckpt" to true ). By default dbwr is writing once the checkpoint batch size reach 500 as defined by the parameter "_incremental_recovery_ckpt_min_batch".

These are great improvements to the performance of physical standby apply process, but it'd be even better if we can control how many logs checkpoint can defer by so that we can do one big mandatory checkpoint for every few logs and have a big enough sga to control the physical reads, this will improve the performance of standby apply process and reduce the overall I/O.

Only downside to this is, if standby crashes, it'd have to apply all the logs since the last checkpoint. But physical standby for RAC databases will greatly benefit from it especially when you have many nodes.

I've opened enhancement request 9943911 for the above request.

Overall, these are great improvements Oracle has introduced in 11g.

Labels: _defer_log_boundary_ckpt, _defer_log_count, 11g, active data guard, standby database