Sunday 6 November 2011

Difference between physical and logical standby database


A standby database can be either a physical standby database or a logical standby database:
Physical standby database
Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database by recovering the redo data received from the primary database.
Logical standby database
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database by transforming the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database. A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.

The difference between physical and logical standby is in the way the changes from the primary are applied. Both created as an exact image of the primary database. Both receive redo logs from the primary database. The difference is that a physical standby is mounted (but not open) and applies the received redo logs just as in the case of media failure recovery. A logical standby reconstructs SQL statements from the received redo logs and executes them. A logical standby is (must be) opened and can with some limitations be used for reporting or other purposes. A physical standby is mounted and generally cannot be used for any other purposes. You can however switch temporarily to read-only mode and query it but synchronization with the primary will be paused until you return to the recovery mode. In 10g Rel. 2 you can also open it read-write and then flashback to its original state

No comments:

Post a Comment