Best Oracle Data Guard Standby Database Configuration?

on October 1, 2013

Oracle Data Guard is a key component within Oracle MAA that provides an efficient disaster recovery solution. It enables the creation, maintenance, and monitoring of transactional consistent copies of the production database at an alternate location. The production database in an Oracle Data Guard configuration is commonly referred to as the primary database, while the transactional consistent copies are referred to as standby databases. With Oracle 11g Release 2, a single Oracle Data Guard configuration can contain up to 30 standby databases in 3 standby database configurations: physical, snapshot, and logical. All three types of standby databases can be Oracle RAC or single-instance, regardless of the configuration of the primary database. The overall layout of your Oracle Data Guard configuration is dependent on what business requirements must be satisfied by the configuration.


Beginning with 11g, you can implement a cross-platform Linux-to-Windows (and vice versa) Oracle Data Guard configuration. More information on cross-platform Oracle Data Guard configurations can be found in MOS Note: 413484.1.

Physical standby databases

A physical standby database is synchronized with the primary database by using media recovery to apply redo that was generated on the primary database. The use of media recovery ensures that a physical standby database is a block-for-block identical copy of the primary database. For this fact, a physical standby database is the preferred choice for disaster recovery. In the event of a failure, you can rest assured that your data will be intact and consistent with data that existed on the primary.

A physical standby database in Oracle Database 11g Release 2 provides the following key benefits:

  • Offers fast and efficient failover to a block-for-block copy of the primary database.
  • Applies no restrictions on data types.
  • Lets you perform role reversal of the primary and standby databases (switchover) for planned maintenance.
  • Lets you offload backups from the primary database.
  • Allows for minimal downtime upgrades and/or patching with the use of the Transient Logical Standby Database feature.
  • Allows use of the Oracle Active Data Guard option, which lets you open the physical standby database in read-only to perform reporting while redo data is being applied. In addition to the Physical Standby database being open for read write operations Oracle Active Data Guard also provides the following features/benefits:
    • If block corruption occurs on the primary database (resulting in a ORA-1578), automatic block recovery will be performed in an Oracle Active Data Guard configuration without the end user realizing that corruption was even encountered.
  • Allows for the use of block change tracking on the standby database to facilitate fast incremental backups (not possible without Oracle Active Data Guard).
  • Integrates the Oracle Active Data Guard option with the support of up to 30 standby databases, allowing for the creation of reader farms. A reader farm is a set of physical standby databases running the Oracle Active Data Guard option from a single primary database. These Oracle Active Data Guard–enabled physical standby databases are used to support and/or offload read-only workloads from the primary database.

Snapshot standby databases

Oracle Database 11g Release 1 introduced a new type of standby database called a snapshot standby, a physical standby that has been temporarily opened in read-write mode. Flashback Database technology is used to facilitate this functionality. When a physical standby is converted into a snapshot standby, an implicit guaranteed restore point is created for the standby database. After a physical standby has been converted to a snapshot standby, the database still receives all of the redo data, but instead of immediately applying the redo, it is archived into the archivelogs at the standby site. When the snapshot standby is converted back to a physical standby, all changes made while the database was in read-write mode will be discarded and the archived redo data will then be applied from the restore point forward, making the physical standby database transactionally consistent with the primary database. Switchover and/or failover operations are not permitted on a snapshot standby until the database is converted back to a physical standby.

Snapshot Standby databases are often used in the following situations:

  • For troubleshooting application-related issues that are dependent on production data sets
  • With application load testing tools, such as Real Application Testing, to ensure proper application functionality and performance prior to application upgrades take place on the production system

Logical standby databases

A logical standby database is kept in sync with the primary by transforming redo data received from the primary into logical SQL statements and then executing those SQL statements against the standby database. Because we are applying SQL statements instead of performing media recovery, it is possible for the logical standby to contain the same logical information but at the same time have a different physical structure. A logical standby is open for user access while applying changes, making it an ideal solution for a reporting database while maintaining its disaster recovery attributes.

Following are a few of the primary benefits for a logical standby:

  • Allow near zero downtime for database upgrades
  • Ability to offload reporting from the primary database
  • Ability to create additional objects to better support reporting operations

Your choice of the type of standby database to implement is a decision that will be heavily influenced by the same business requirements that justified the need for the standby database in the first place. Assume a scenario of adatabase having already died a fiery death. It is likely, in this case, that the DBA will want to ensure that downtime and data loss will never occur again. The requirements laid forth by the business might then be to ensure that the database is available for user access (Recovery Time Objective, or RTO) within minutes of such a disaster, and that the database is in the exact same structure that it was in prior to the fire. Given the fact that a physical standby database is a block-for-block copy of the primary database, the decision to implement a physical standby database is easy.

Now assume that a DBA has been dealing with complaints from the sales team that the application performance is resulting in a significant lack of productivity. The primary reason for the application slow-downs is the fact that the bean-counters in accounting are constantly running resource-intensive reports in the middle of the day to meet their own deadlines. The DBA is tired of these complaints and wants to see some return on investment (ROI) from the hardware and software that was purchased to facilitate the standby database. With Oracle Database 11g Release 2, three options can make the bean-counters and the sales team happy: logical standby, snapshot standby, or a physical standby using the Oracle Active Data Guard option.

Obviously, with all of these options, the decision is a bit complex. We know that a snapshot standby will not be “up-to-date” when it is opened in read-write mode, so increasing our RTO beyond the acceptable limitation narrows us now down to two options: logical standby or a physical standby using the Oracle Active Data Guard option. We do know that with a logical standby database, we can create additional database objects to enhance the reporting functionality, but these changes will make the structure of the database slightly different, thus invalidating the business requirement of our disaster recovery solution being identical in structure. By using the business objectives laid out by management, we used our powers of deduction to make our choice of using a physical standby with the Oracle Active Data Guard option. Oracle Active Data Guard will provide the ultimate HA solution by allowing real-time reporting while still maintaining a transactionally consistent block-for-block copy of our primary database.

In the real world, a single standby database of any specific type may not meet the needs of your business. As mentioned, Oracle Database 11g Release 2 supports up to 30 standby databases of any type in a given Oracle Data Guard configuration type, providing the ability to use all of the above standby types from a single primary server and allowing for the implementation of a solution tailored to meet the high availability and scalability needs of just about any application.

Related Posts

Leave a Reply