In Oracle Data Guard, a Logical Standby Database is a standby database that:
-
Receives redo data from the Primary database
-
Converts redo into SQL statements
-
Replays those SQL statements on the standby
-
Can be OPEN READ WRITE while applying changes
This is different from Physical Standby.
🔹 1️⃣ What is Logical Standby?
A Logical Standby:
-
Uses SQL Apply
-
Redo is transformed into SQL statements
-
Allows users to:
-
Query data
-
Create indexes
-
Create additional tables
-
Run reports
-
-
Can be open in READ WRITE mode
It must have the same logical structure as primary (tables, schemas), but physical storage can differ.
🔹 2️⃣ How It Works (Step-by-Step)
On Primary Database
-
User commits transaction
-
Redo generated
-
Redo shipped to standby
On Logical Standby
-
Redo received
-
LogMiner converts redo → SQL
-
SQL Apply executes SQL statements
🔹 3️⃣ Architecture Diagram
Here is a simple visual diagram for understanding:
PRIMARY DATABASE
----------------
| User DML |
| INSERT/UPD |
----------------
|
| Redo Transport
v
=========================
| Logical Standby |
|------------------------|
| Redo Received |
| LogMiner |
| SQL Apply |
| Open READ WRITE |
=========================
🔹 4️⃣ Logical vs Physical Standby
| Feature | Physical Standby | Logical Standby |
|---|---|---|
| Apply Method | Redo Apply | SQL Apply |
| Database Open Mode | Read Only | Read Write |
| Block Level Copy | Yes | No |
| Can create new indexes? | No | Yes |
| Good for reporting? | Limited | Excellent |
🔹 5️⃣ Important Background Processes
Logical standby uses:
-
LSP (Logical Standby Process)
-
LogMiner
-
SQL Apply Engine
Unlike physical standby which uses MRP (Managed Recovery Process).
🔹 6️⃣ Advantages
✅ Reporting without impacting primary
✅ Can create extra indexes
✅ Can skip certain tables
✅ Good for data warehouse reporting
🔹 7️⃣ Limitations
❌ Some data types not supported
❌ Not all DDL supported
❌ Slightly slower apply than physical
❌ More complex
🔹 8️⃣ When to Use Logical Standby?
Use when:
-
You need reporting with write capability
-
You want different indexes
-
You need to replicate subset of objects
🔹 9️⃣ Simple Real DBA Example
Primary:
INSERT INTO employees VALUES (101,'John');
Redo shipped.
Logical standby converts redo into:
INSERT INTO employees VALUES (101,'John');
SQL Apply executes it.
