Difference between Physical Standby and Logical Standby DB

Difference between Physical Standby and Logical Standby DB

Physical standby:
============

1. Physical standby schema matches exactly the source database.

2. Archived redo logs and FTP’ed directly to the standby database which is always running in “recover” mode. Upon arrival, the archived redo logs are applied directly to the standby database.

Logical standby:
===========

1. Logical standby database does not have to match the schema structure of the source database.

2. Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete). This DML is transported and applied to the standby database.

3. Logical standby tables can be open for SQL queries (read only), and all other standby tables can be open for updates.

4. Logical standby database can have additional materialized views and indexes added for faster performance.

DIFFERENCE
=========

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.

Background mechanism
=================
LNS (log-write network-server) and ARCH (archiver) processes running on the primary database select archived redo logs and send them to the standby database,[5] where the RFS (remote file server) background process within the Oracle instance performs the task of receiving archived redo-logs originating from the primary database.

Alternatively, a supplementary mechanism may transfer the archived redo logs. On the standby database a Fetch Archive Log (FAL) client monitors for gaps in the sequence of received logs. If it finds a gap, it may invoke one or more Fetch Archive Log (FAL) servers to run on the primary database to forward the missing item(s).[6]

Once the archived redo logs have arrived, other processes (such as an ARCH (Archiver process), an MRP (Managed Recovery Process), and/or an LSP (Logical Standby Process)) may set about applying the log contents to the standby database.