Logical standby vs Physical standby

Standby database was introduced in oracle8i for high availability solution. Oracle9i enhanced the standby database and introduced Data Guard. Generally there are two type of standby database. One is physical standby and another one is logical standby. This article is written as per the version Oracle10gR2.

1. What is physical standby database?
2. What is logical standby database?
3. What is the difference between logical standby and physical?
4. At what circumstances, physical standby is useful?
5. At what circumstances, logical standby is useful?

1. 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.

2. 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.

3. What is the difference between logical standby and physical standby?

Physical standby database is always in managed recovery mode. We can only open the physical standby database in read only mode. But archive redo logs can not be applied while physical standby database is in read only mode.

Logical standby database is always in open mode. The SQL statements which is generated from primary database is applied on logical standby while the database is in open mode. We can have additional materialized views, indexes and tables added for faster performance in logical standby.

Physical standby schema matches exactly the source database. Logical standby database does not have to match the schema structure of the source database.

3. At what circumstances, physical standby is useful?

We can use Physical standby when we need to go for High availability solutions. Physical standby is the most commonly used for disaster recovery or failure.

5. At what circumstances, logical standby is useful?

We can use logical standby if we want to off-load some of the reporting overhead in primary database. The logical standby database is an excellent choice to do queries, summations, and reporting activities, thereby off-loading the primary database from those tasks and saving valuable CPU and I/O cycles.

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

Please click this link if you need to read more about standby DB.