Since we have launched Dbvisit Replicate in 2011, we have been getting questions on exactly what the difference is between the two products and where you would use one over the other. These questions are very understandable. From a very high level overview, both products enable database replication. But they do it in a very different way, and although there is some overlap between the products, the end result and their application are quite different.
Key distinctions between physical and logical replication:
- Physical replication is a binary copy of the primary or source database. Changes are applied at the lowest level available within the DBMS, ensuring that the target or standby database is an exact replica of the primary database, including all internal database indexes, pointers and tables.
- A logical replicated database is an independent database that is kept in sync by a replication mechanism that applies updates at the logical level (e.g. via SQL statements). This means that while the data within a logical target database may be the same as that in the primary or source database, the internal database-level structures will be different. This may have implications for some applications, and for the usage of the logical replicated database in the event of a failure. This is important as the database must be viewed not only as a repository of application data, but also a container with its own management and administrative data. For example, if a password is changed in the source database it is not updated in the target, then when it comes to failover the system will not work because of an old password. It also means that, although internal linkages that support referential integrity may be in place in the standby database, these may be physically different than at the primary site, and as a result, may have an impact on the application (e.g. different automatically created foreign key values).
- Physical replication is all or nothing. Either 100% of the database is replicated or nothing at all.
- With logical replication it is possible to only replicate a subset of the database in the database (100% replication is also possible).
- Physical replication is analogous with using a tool such as rsync to synchronize Word document, with rsync replicating the file at the binary file level.
- A logical standby database is analogous with manually updating a Word document by scanning for changes in the source file and copying them to the right location within the standby file.
As can seen from the above differences, the physical replication method using a standby database is best suited for disaster recovery failover (DR) because the database is guaranteed to be an exact replica of the source or primary database.
The logical replication is best suited for data migration, real time reporting, high availability, BI, workflow etc, type business applications.
- Disaster Recovery (DR) Solution.
- Dbvisit Standby performs Physical Database Replication – The Secondary is exactly the same as the Primary, both data and structure.
- Performs DR functions such as Graceful Switch over, Failover, Create Standby database, Read only standby databases etc.
- Will replicate the physical structure of the database.
- Will replicate ALL data in the database including triggers, sequences, views, all data types.
- Replicates selected Oracle database environments for the purpose of Data Migration, Real-time Reporting, Data Replication, ETL extract solution.
- Dbvisit Replicate performs Logical Database Replication – the target can be a subset of data.
- The structure can be different.
- The versions of Oracle can be different.
- The operating system (OS) can be different.
- The target database can be non Oracle (SQL Server and MySQL).
- Conflict resolution will need to be setup to handle possible conflicts at the target database.
- Will not replicate the physical structure of the database.
Both products can be used together on the same source database to perform both physical replication for DR (with a standby database) and logical replication (for real time reporting, ETL extraction etc).
For a PDF document of the above comparison diagram, please see Dbvisit Standby versus Dbvisit Replicate.