Re: Standby abuse

Thanks for the link, Carel-Jan! Looks like a lot of pre-10gR2 experiences -- most of them not positive :-)

On Sat, Nov 8, 2008 at 3:46 PM, Carel-Jan Engel <careljan_at_dbalert.eu> wrote:

> http://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 <http://www.mailscanner.info/>*, and is
> believed to be clean.
>
>
>

-- 
Dominic Delmolino

--
http://www.freelists.org/webpage/oracle-l