Making the Right Choice

---

Data Guard is able to provide an efficient disaster recovery solution by maintaining transactionally consistent copies of the production database at a remote site. These copies, or standbys, can be one of two types: physical or logical. Which one you choose to include in your Data Guard configuration depends on what business needs you are trying to satisfy.

Physical Standby Databases

A physical standby database is kept in sync with the primary database by using media recovery to apply redo that was generated on the primary database. Because media recovery is used, we can be assured that a physical standby is a block-for-block identical copy of the primary database. Because of its nature, a physical standby is an excellent choice for disaster recovery. In the event of a failure, we can rest assured that our data will be intact and consistent with data that existed on the primary. A few of the primary benefits of a physical standby are

• Protection from user and logical errors

• Fast and efficient failover

• Ability to perform planned switchover for maintenance

• Ability to offload backups from the primary database

• Ability to open the standby database in read-only mode to perform reporting

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. As a logical standby is open for user access while applying changes, it is an ideal solution for a reporting database while maintaining its disaster recovery attributes. A few of the primary benefits for a logical standby are

• Ability to offload reporting from the primary database

• Ability to create additional objects to support better reporting

• Enables rolling upgrades of the primary database

Choosing which standby to implement is a very important yet straightforward process. As logical standby does not support all Oracle datatypes, the first step is to determine if your application makes use of any of the unsupported datatypes. To determine objects within your primary database that are using unsupported datatypes, run the following query:

select distinct owner, table_name from dba_logstdby_unsupported order by owner;

If your application is making use of the unsupported datatypes and you cannot transition to supported datatypes, a physical standby is your best choice.

Next you should consider if you have a need for the standby database to be open read-only or open read/write while changes are being applied. If you do have to have the standby open read-only, and your application does not make use of unsupported datatypes, you should evaluate logical standby as your option. If access to the standby while changes are being applied is not required or you have datatypes not supported by logical standby, you should implement a physical standby.

In some cases you should consider implementing both a physical and a logical standby. This type of configuration gives us the best of both worlds and provides the highest degree of data protection and use of system resources.

---