Re: Standby abuse

From: Ian MacGregor <>
Date: Fri, 07 Nov 2008 12:19:13 -0800
Message-ID: <>

There are also crtain data types which force one to use a physical standby. Logical has many advantages why not test with it before making your decision? I am assuming you have time to test, so often untrue

On 11/7/08 12:00 PM, "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?

Received on Fri Nov 07 2008 - 14:19:13 CST

Original text of this message