For Oracle DBA interviews and real production environments, understanding Physical Standby vs Logical Standby vs Snapshot Standby is critical because interviewers often ask not only the definitions but also why companies choose one over another.
Oracle Data Guard Standby Types – Complete Comparison
| Feature | Physical Standby | Logical Standby | Snapshot Standby |
|---|---|---|---|
| Database Structure | Exact block-by-block copy | Logical copy using SQL | Physical standby temporarily converted to RW |
| Redo Apply Method | Redo Apply (MRP) | SQL Apply (LSP) | Redo received but not applied |
| Read Only | Yes (Active Data Guard) | Yes | No |
| Read Write | No (unless activated) | Yes | Yes |
| DR Ready | Yes | Partial | No |
| Switchover Support | Yes | Yes | No |
| Failover Support | Yes | Limited | No |
| Performance Impact | Lowest | Higher | Moderate |
| Most Common in Production | Yes | Rare | Rare |
1. Physical Standby Database
How It Works Internally
The primary database generates redo logs.
Primary Database
|
v
Archive Logs / Redo Transport
|
v
Physical Standby
|
v
MRP (Managed Recovery Process)
|
v
Apply Redo Blocks
The standby receives redo and applies changes at the block level.
Oracle does not execute SQL.
It simply recreates the exact same database blocks as the primary.
Example
Primary:
INSERT INTO EMP VALUES (101,'SCOTT');
COMMIT;
Redo generated:
Block #123 modified
Standby:
Apply Block #123
No SQL execution occurs.
Read/Write Capability
Standard Physical Standby
Read = Yes
Write = No
Active Data Guard
Read = Yes
Write = No
Real-Time Queries = Yes
Advantages
✔ Exact copy of primary
✔ Fast redo apply
✔ Supports switchover/failover
✔ Simplest architecture
✔ Best DR solution
✔ Minimal administration
Limitations
✖ Cannot modify data
✖ Requires Active Data Guard license for real-time reporting
Real Production Usage
Most companies use:
Primary RAC
|
|
Physical Standby
Typical industries:
Banking
Insurance
Healthcare
Manufacturing
E-Commerce
This is the industry standard DR architecture.
2. Logical Standby Database
How It Works Internally
Instead of applying redo blocks, Oracle converts redo into SQL statements.
Primary
|
Redo Logs
|
LogMiner
|
SQL Apply
|
Logical Standby
Oracle uses:
LogMiner
to extract SQL from redo logs.
Example
Primary:
INSERT INTO EMP VALUES (101,'SCOTT');
COMMIT;
Redo arrives.
Logical standby converts it to:
INSERT INTO EMP VALUES (101,'SCOTT');
Then executes the SQL.
Internal Flow
Redo
|
LogMiner
|
Generate SQL
|
Execute SQL
|
Update Standby
Read/Write Capability
Read = Yes
Write = Yes
Users can create additional:
Tables
Indexes
Materialized Views
Reporting Objects
without affecting replication.
Advantages
✔ Open read/write
✔ Useful for reporting
✔ Can create local objects
✔ Supports heterogeneous workloads
Limitations
✖ More CPU intensive
✖ SQL Apply slower than Redo Apply
✖ Not all datatypes supported
✖ More administration
✖ Not widely used today
Real Production Usage
Usually for:
Reporting databases
Data warehouse environments
Separate reporting schemas
Example:
Primary OLTP
|
Logical Standby
|
BI Reports
3. Snapshot Standby Database
How It Works Internally
Snapshot standby begins as a physical standby.
Physical Standby
|
Convert
|
Snapshot Standby
When converted:
Redo continues arriving
Redo is NOT applied
Oracle stores incoming redo for later use.
Internal Architecture
Primary
|
Redo Transport
|
Snapshot Standby
|
Redo Stored
(Not Applied)
Users can modify data freely.
Example
Developer creates:
DELETE FROM EMP;
COMMIT;
Allowed.
Later:
Convert back to Physical Standby
Oracle discards local changes and applies accumulated redo.
Read/Write Capability
Read = Yes
Write = Yes
Fully read/write.
Advantages
✔ Testing environment
✔ Application upgrade testing
✔ Patch validation
✔ Production-like testing
Limitations
✖ Not DR ready
✖ Cannot failover
✖ Redo apply paused
✖ Local changes lost after conversion
Real Production Usage
Common for:
Application Upgrade Testing
Production
|
Snapshot Standby
|
Upgrade Testing
Patch Testing
Patch Validation
Before Production Rollout
Which Standby Type Do Companies Prefer?
Enterprise Reality
If you visit 100 companies:
| Standby Type | Usage Percentage |
|---|---|
| Physical Standby | ~90% |
| Snapshot Standby | ~8% |
| Logical Standby | ~2% |
Why Physical Standby Wins
Because organizations need:
✔ Disaster Recovery
✔ Fast Failover
✔ Data Protection
✔ Simplicity
✔ Low Administration
Physical standby provides all of these.
Licensing Considerations
Physical Standby
Included with Data Guard.
Active Data Guard
Requires additional license.
Provides:
✔ Read-only reporting
✔ Real-time query
✔ Automatic block repair
Logical Standby
Included with Data Guard.
Snapshot Standby
Included with Data Guard.
Interview Answer (2-Minute Version)
Physical Standby is an exact block-level copy of the primary database. Redo is applied using Managed Recovery Process (MRP). It is mainly used for High Availability and Disaster Recovery and is the most widely used standby type in production.
Logical Standby converts redo into SQL statements using LogMiner and applies them through SQL Apply. It supports read-write access and is mainly used for reporting environments, though it is less common due to complexity and datatype limitations.
Snapshot Standby is a temporary read-write version of a physical standby. Redo continues to be received but is not applied until the database is converted back to physical standby. It is commonly used for testing, patch validation, and upgrade rehearsals.
In real enterprise environments, Physical Standby is the preferred and most commonly deployed Data Guard configuration because it provides the best balance of data protection, performance, and operational simplicity.
