Standby abuse

From: Dominic Delmolino <>
Date: Fri, 7 Nov 2008 15:00:30 -0500
Message-ID: <>

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.


   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

Received on Fri Nov 07 2008 - 14:00:30 CST

Original text of this message