Physical vs logical standby database in oracle 

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

What is physical standby database?

Physical standby database is physically identical to the primary database. It is block by block copy of the primary images. The archived redo log files are shipped to standby database and applied the archived redo log files on the standby database. So standby database should be always in recovery mode. This is like, DBA is sitting in remote location and recovering the primary database in different server by applying the archived redo log files.

What is logical standby database?

Logical standby database is logically identical to the primary database. Oracle use the logminer technology to transforms standard archived redo logs(by default) or redo logs(if real-time apply enabled) into SQL statements and applies them to the logical stand by database. A logical standby database can remain open and the same time its tables are updated from the primary database, and those tables are simultaneously available for read access. Oracle9i introduced logical standby database.

Physical standby is different from logical standby:

• Physical standby schema matches exactly the source database.

• Archived redo logs and FTP'ed directly to the standby database which is always running in "recover" mode.  Upon arrival, the archived redo logs are applied directly to the standby database.

Logical standby is different from physical standby:

• Logical standby database does not have to match the schema structure of the source database.

 • Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete).  This DML is transported and applied to the standby database.

 • Logical standby tables can be open for SQL queries (read only), and all other standby tables can be open for updates.

 • Logical standby database can have additional materialized views and indexes added for faster performance.

Configuring Physical standbys offers these benefits:

• An identical physical copy of the primary database

• Disaster recovery and high availability

• High Data protection

• Reduction in primary database workload

• Performance Faster

Configuring Logical standbys offer:

• Simultaneous use for reporting, summations and queries

• Efficient use of standby hardware resources

• Reduction in primary database workload

• Some limitations on the use of certain datatypes

A comparison

S.No	Physical Standby	Logical Standby
1	Identical to the Primary database including the physical organization in the disk	Same logical information but physical organization and the structure of data are different.
2	DG uses Redo Apply technology, which applies redo data using standard recovery techniques.	DG uses SQL Apply, which first transforms the redo data into SQL statements 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. To see what other datatypes which may not be support in your logical standby database, you can query the view DBA_LOGSTDBY_UNSUPPORTED.
5	Can open in ‘Read only’ but cannot apply logs.	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.

Note:-

Oracle recommends not to apply by DML operations on logical standby tables maintained by SQL Apply. This will introduce deviations between the primary and standby databases that will make it impossible for the logical standby database to be maintained.

As per Oracle10gR2, Some of the SQL statments will not be shipped from primary database to logical standby database. Some sample SQL statements are......

1. CREATE or ALTER or DROP MATERIALIZED VIEW

2. CREATE or ALTER or DROP MATERIALIZED VIEW LOG

3. ALTER SESSION

4. CREATE PFILE

5. CREATE or DROP DATABASE LINK