Physical Or Logical Replication?

Oracle physical and logical replication both offer good solutions for moving your data so the choice of which one to use depends on your business need. Physical replication is all or nothing, that is, either 100% of the database is replicated or nothing at all. On the other hand, with logical replication it is possible to only replicate a subset of the database in the database (although of course 100% replication is also possible).

Physical Replication

Physical replication

  • is a binary copy of the primary or source database
  • changes are applied at the lowest level available within the DBMS
  • ensures the target or standby database is an exact replica of the primary database
  • includes all internal database indexes, pointers and tables
  • is best suited for Disaster Recovery

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.

Logical Replication

Logical replication

  • uses SQL statements to replicate data
  • there can be just a subset of data replicated
  • real-time or near real-time
  • pulls data directly from redo or archive logs
  • is best suited for information sharing, data analysis, operational reporting, migrations

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 secondary file.