Difference between Physical and Logical Standby

The Data Guard Configuration consists of one Production database and upto nine Standby databases. The production database can be a single instance or RAC database similarly Standby database can be single instance or RAC database. It is also possible to have single instance standby database for a RAC production database.

The standby database is a transactionally consistent copy of the production database. It is created initially from the backup of production database. Once created, the data guard automatically synchronizes the standby.

There are two types of standby database, they are

1. Physical Standby
2. Logical Standby

Physical standby uses the recovery technique to sychronize the standby database where as logical standby uses the sql apply method to synchronize both the database.
The following table provides you with the difference between physical and logical standby databases,

S.No	Physical Standby	Logical Standby
1	Identical to the Primary database including the physical organization inthe disk	Same logical information but physical organization and the structure ofdata are different.
2	DG uses Redo Apply technology, which applies redo data using standardrecovery techniques.	DG uses SQL Apply, which first transforms the redo data into SQLstatements and then executes the statement.
3	Can be used for Backups	Can be opened for reporting.
4	All data types are supported	Not all data types are supported for eg. LONG, NCLOB, LONG RAW, BFILE, XML types are not supported.
5	Can open in ‘Read only’ but cannot apply logs. From 11g open in‘read write’.	Can open in normal mode and simultaneously apply the logs.
6	No additional objects can be created.	Additional indexes, materialized views can be created

Usually organizations use Logical Standby databases mainly for reporting purposes and not for failover/switchover operations. For failover and switchover they use physical standby database. The reason is maintaining logical standby is almost a full time job, need extensive tuning of log apply services, and over hundreds of patches, the logical is usually 3 to 5 hours behind the live database, thus making it impossible for failover/switchover.

Thanks