Standby abuse
- From: "Dominic Delmolino" <ddelmoli@xxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 7 Nov 2008 15:00:30 -0500
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
Other related posts:
