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@xxxxxxxxxx> wrote: > //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