Re: Standby abuse

From: Carel-Jan Engel <>
Date: Sat, 08 Nov 2008 21:46:12 +0100
Message-Id: <>

Best regards,

Carel-Jan Engel

If you think education is expensive, try ignorance. (Derek Bok) ===

On Fri, 2008-11-07 at 15:00 -0500, Dominic Delmolino wrote:

> In an effort to chum the water:
> Why wouldn't DG Logical Standby always be preferable to Physical
> Standby?
> According to the 11g DataGuard Concepts guide:
> Benefits of a Physical Standby Database
> A physical standby database provides the following benefits:
> * Disaster recovery and high availability
> A physical standby database is a robust and efficient disaster
> recovery and high availability solution. Easy-to-manage
> switchover and failover capabilities allow easy role reversals
> between primary and physical standby databases, minimizing the
> downtime of the primary database for planned and unplanned
> outages.
> * Data protection
> A physical standby database can prevent data loss, even in the
> face of unforeseen disasters. A physical standby database
> supports all datatypes, and all DDL and DML operations that
> the primary database can support. It also provides a safeguard
> against data corruptions and user errors. Storage level
> physical corruptions on the primary database will not be
> propagated to a standby database. Similarly, logical
> corruptions or user errors that would otherwise cause data
> loss can be easily resolved.
> * Reduction in primary database workload
> Oracle Recovery Manager (RMAN) can use a physical standby
> database to off-load backups from a primary database, saving
> valuable CPU and I/O cycles.
> A physical standby database can also be queried while Redo
> Apply is active, which allows queries to be offloaded from the
> primary to a physical standby, further reducing the primary
> workload.
> * Performance
> The Redo Apply technology used by a physical standby database
> is the most efficient mechanism for keeping a standby database
> updated with changes being made at a primary database because
> it applies changes using low-level recovery mechanisms which
> bypass all SQL level code layers.
> Benefits of a Logical Standby Database
> A logical standby database is ideal for high availability (HA) while
> still offering data recovery (DR) benefits. Compared to a physical
> standby database, a logical standby database provides significant
> additional HA benefits:
> * Protection against additional kinds of failure
> Because logical standby analyzes the redo and reconstructs
> logical changes to the database, it can detect and protect
> against certain kinds of hardware failure on the primary that
> could potentially be replicated through block level changes.
> Oracle supports having both physical and logical standbys for
> the same primary server.
> * Efficient use of resources
> A logical standby database is open read/write while changes on
> the primary are being replicated. Consequently, a logical
> standby database can simultaneously be used to meet many other
> business requirements, for example it can run reporting
> workloads that would problematical for the primary's
> throughput. It can be used to test new software releases and
> some kinds of applications on a complete and accurate copy of
> the primary's data. It can host other applications and
> additional schemas while protecting data replicated from the
> primary against local changes. It can be used to assess the
> impact of certain kinds of physical restructuring (for
> example, changes to partitioning schemes). Because a logical
> standby identifies user transactions and replicates only those
> changes while filtering out background system changes, it can
> efficiently replicate only transactions of interest.
> * Workload distribution
> Logical standby provides a simple turnkey solution for
> creating up-to-the-minute, consistent replicas of a primary
> database that can be used for workload distribution. As the
> reporting workload increases, additional logical standbys can
> be created with transparent load distribution without
> affecting the transactional throughput of the primary server.
> * Optimized for reporting and decision support requirements
> A key benefit of logical standby is that significant auxiliary
> structures can be created to optimize the reporting workload;
> structures that could have a prohibitive impact on the
> primary's transactional response time. A logical standby can
> have its data physically reorganized into a different storage
> type with different partitioning, have many different indexes,
> have on-demand refresh materialized views created and
> maintained, and it can be used to drive the creation of data
> cubes and other OLAP data views.
> * Minimizing downtime on software upgrades
> Logical standby can be used to greatly reduce downtime
> associated with applying patchsets and new software releases.
> A logical standby can be upgraded to the new release and then
> switched over to become the active primary. This allows full
> availability while the old primary is converted to a logical
> standby and the patchset is applied.
> Based on this, I see that:
> 1. Both provide DR and HA, while Standby has the additional benefit of
> not replicating block-level corruption
> 2. Both can offload backup workload
> 3. Only Standby can be continuously used for reporting and aggregation
> constructs
> 4. Only Standby can be used to support the infamous rolling software
> upgrades
> 5. In theory Standby could handle nologging index rebuilds without
> corruption by skipping all index rebuild DDL
> How significant is Physical's performance advantage?
> --
> Dominic Delmolino
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.

Received on Sat Nov 08 2008 - 14:46:12 CST

Original text of this message