The main difference between logical and physical standby database in Oracle is that in logical standby database, the schema structure is not exactly the same as the primary database while, in physical standby database, the schema structure is same as the primary database.
Generally, Oracle Data Guard is an extension of Oracle RDBMS. It allows maintaining a standby database in addition to the primary database. Therefore, the standby database works as an alternative database. During a failure, the data from the main database is transferred into the standby database. Overall, Oracle Data Guard supports two standby databases, which are physical standby and logical standby.
Key Areas Covered
1. What is Logical Standby Database in Oracle
-Definition, Functionality
2. What is Physical Standby Database in Oracle
–Definition, Functionality
3. Difference Between Logical and Physical Standby Database in Oracle
-Comparison of Key Differences
Key Terms
Logical Standby, Oracle Data Guard, Physical Standby, RDBMS
What is Logical Standby Database in Oracle
The logical standby database does not have the exact schema as the primary database. The database uses LogMiner techniques to convert the archived redo logs into native DML statements such as insert, delete and update. As the final step, these DML statements are applied to the standby database. Moreover, it is possible to have extra materialized views and indexes in a logical standby database to improve performance.
Generally, a logical standby database provides multiple advantages. It is possible to use reporting, summations and queries at the same time. Moreover, it helps to reduce the workload of the primary database. Finally, it allows using hardware resources more efficiently.
What is Physical Standby Database in Oracle
The physical standby database has the same set of content as the primary database. In other words, the relative physical locations are different, but the data is the same as the primary database. Generally, a physical standby database can operate in managed recovery mode or read-only mode. It is not possible to operate in both modes simultaneously unless for licensed Oracle 11.1 or higher etc. Moreover, physical standby use redo apply technology.
Generally, a physical standby database provides multiple advantages. Mainly, it provides an identical physical copy of the primary database and helps to minimize the workload of the primary database. It also provides higher data protection. Furthermore, there is a disaster recovery mechanism so that the data is available at any required time. Moreover, the physical standby database helps to increase the performance.
Difference Between Logical and Physical Standby Database in Oracle
Definition
The logical standby database is a database that does not have the schema structure exactly similar to the source database. In contrast, a physical standby database is a database that replicates the exact contents of its primary database across the Oracle Net Network layer.
Schema
In logical standby database, the schema is not the same as the primary database while in the physical standby database, the schema is as same as the primary database.
Functionality
Moreover, a logical standby database transforms the redo generated at the primary database into data, SQL and reapplies those SQL transactions on the logical standby. On the other hand, a physical standby database directly applies the archived redo logs to the standby database.
Conclusion
In brief, Oracle Data Guard supports two standby databases, which are physical and logical standby databases. The main difference between logical and physical standby database in Oracle is that in logical standby database, the schema structure is not exactly the same as the primary database while in physical standby database, the schema structure is same as the primary database.
References:
1.“Oracle Data Guard.” Wikipedia, Wikimedia Foundation, 21 Jan. 2018, Available here.
Image Courtesy:
1.”2797375″ via Pixabay
Leave a Reply