Re: Standby abuse

How well does Logical DG deal with tables that don't have primary keys or unique indexes? How many 3rd party apps are certified to run on a database that used to be a Logical Stdby? Is even Oracle Apps so certified? HA and DR is about more than just a technical data replication solution.

In addition I don't see why the 'advantages' listed below under "efficient use of resources" are actually advantages of logical stdby over physical either.

On Fri, Nov 7, 2008 at 8:00 PM, Dominic Delmolino <ddelmoli_at_cox.net> 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
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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