Re: Standby abuse

  • From: Carel-Jan Engel <careljan@xxxxxxxxxx>
  • To: ddelmoli@xxxxxxx
  • Date: Sat, 08 Nov 2008 21:46:12 +0100

//www.freelists.org/archives/oracle-l/11-2006/msg00173.html

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.


Other related posts: