Logical Standby in Oracle Data Guard Overview

 

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

1. User commits transaction

2. Redo generated

3. Redo shipped to standby

On Logical Standby

4. Redo received

5. LogMiner converts redo → SQL

6. 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.