Oracle Database 11g shoots the moon
Oracle's enormous 11g release rumbles with an impressive array of performance and management aids, elegant application testing, standbys that earn their keep, and the promise of lower storage requirementsFollow @infoworld
For example, say you need to make a change to a major stored procedure on your production database. The problem is that, without testing it against your production workload, you have no idea how much, if at all, the change will improve your system performance. Combining Snapshot Standby with Database Replay (see below), you can test limitless scenarios. All you have to do is record your production workload on the primary database, put your standby database into read/write mode, and implement the code changes on the standby. Then you can replay your workload on the standby and compare the performance counters from the replay with those from your initial capture.
When you put your standby into Snapshot Standby mode, it stops applying logs from the primary. (The logs are still sent across, they're just not applied.) When you've finished testing, you can put the standby back in read mode. The standby will then automatically discard all of your changes, return to the state it was in before you tested the new code, and apply the logs that are waiting to be applied. Your standby is never physically out of sync with the primary, only logically.
There are many other scenarios where a Snapshot Standby can come in handy, from troubleshooting production issues to index tuning to disk placement and partitioning. You can use Snapshot Standby to test backups and index reorgs while under heavy user loads as well. The possibilities are practically endless.
There's another practical purpose. One of the biggest problems for DBAs is keeping analysts, developers, and others out of the production system. They all have legitimate reasons for reading data out of the system, but for performance and compliance reasons, you'll typically want to limit their access as much as possible. Typically you'd put together another server for their use and keep it as in sync with the production database as you can, generally through backup/restore or replication. With Snapshot Standby, you can do it all in one system.
Active Data Guard
Like Snapshot Standby, Active Data Guard lets you have your standby and use it to. A new option introduced in Database 11g, Active Data Guard allows you to throw a standby instance into read mode to support real-time queries, solving one of the biggest issues businesses have with their OLTP databases: namely, how to separate OLTP and read activity while providing near-real-time reporting.
Active Data Guard not only allows you to make the standby readable, but you can do so with minimal configuration changes on your client. You can point reporting applications at the standby directly, or you can create what is essentially a read service on the primary server that will act as a broker that routes workload based on the type of activity. When the application connects to one service, it writes to the local OLTP database, and when it connects to the other service, the queries get routed to the standby to fulfill the reporting requests. This is powerful functionality that makes it easy for shops to report from their OLTP data.
The downside is that you can't make any permanent changes to the standby, so you're stuck with the indexing and other configurations of the OLTP database. As anyone who has ever put a reporting database together knows, OLTP index requirements are rarely the same as reporting index requirements.
Plus, configuring Active Data Guard, at least on Windows, is no picnic. I spent weeks in e-mail, on the phone, and in online conference with Oracle reps trying to get Active Data Guard up and running. The directions given online are incomplete, and there's no guidance on troubleshooting at all. Had I been alone trying to set this up, it never would have happened.