//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.