Opening a Physical Standby Database

1. Real-time Query

What is Real-time Query?

Real-time Query allows you to run read-only queries on a physical standby database while redo apply is running in the background.

This means your standby can serve reporting and analytics workloads in real-time, offloading the primary database and maximizing resource usage.

🏷️ This feature is available only with Oracle Active Data Guard (licensed).

Why Use Real-time Query?

Requirements Before You Start

Steps to Enable Real-time Query

Here’s how you can enable real-time query on a physical standby:

Step-by-step Guide

-- 1. Cancel managed recovery (stop redo apply)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- 2. Open standby database in read-only mode
ALTER DATABASE OPEN;

-- 3. Restart redo apply in background (real-time apply)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

💡 Now your standby is open for read-only access and applying redo at the same time — this is called “Real-time Query mode”.

How to Check if It’s Working

SELECT open_mode FROM V$DATABASE;

Expected Result:

Monitor Apply Lag (How behind is the standby?)

SELECT name, value, datum_time, time_computed 
FROM V$DATAGUARD_STATS 
WHERE name = 'apply lag';

This tells you how much delay exists between the primary and standby.

Control Query Freshness with STANDBY_MAX_DATA_DELAY

By default, Oracle lets you query even if the standby is slightly behind. But you can control how “fresh” the data must be.

🔧 Example:

ALTER SESSION SET STANDBY_MAX_DATA_DELAY = 3;

This tells Oracle: “Let me run the query only if the data is at most 3 seconds old.”

⚠️ If not satisfied, Oracle throws: ORA-03172: standby data not consistent

Want to Wait Until Standby Catches Up?

You can force the session to wait until standby is caught up:

ALTER SESSION SYNC WITH PRIMARY;

This is useful if you’re okay to wait a bit and want guaranteed consistency.

You can also automate this with a trigger, like so:

CREATE TRIGGER adg_logon_sync_trigger
AFTER LOGON ON SCHEMA
BEGIN
  IF SYS_CONTEXT('USERENV','DATABASE_ROLE') = 'PHYSICAL STANDBY' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SYNC WITH PRIMARY';
  END IF;
END;

What if Data Block is Corrupted?

Real-time query supports automatic block media recovery. If a block is corrupted, Oracle fetches a good copy from the primary automatically.

Manual Recovery (if needed):

RECOVER BLOCK FOR FILE 5 BLOCK 1234;

Real-time Query: Important Notes

✅ Summary

2. Using SQL and PL/SQL on Active Data Guard Standbys

What’s New in 19c?

Starting with Oracle 19c, Active Data Guard now allows SQL and PL/SQL operations on physical standby databases — including DML and top-level PL/SQL blocks.

This means you can now:

• Perform occasional inserts, updates, and deletes
• Run PL/SQL procedures/functions
• Automatically recompile invalid PL/SQL objects

⚠️ But remember — these operations are redirected to the primary, not executed on the standby itself.

🔍 Why Allow DML & PL/SQL on a Standby?

DML Operations on Active Data Guard Standby

How Does It Work?

• DML (e.g. INSERT, UPDATE, DELETE) is automatically forwarded to the primary database
• The standby waits for the change to be applied
• Read consistency is maintained (you can see your own uncommitted changes)

This is useful for interactive dashboards, minor edits, or mixed workloads.

Setup: Enable DML Redirection

Example

-- On the standby
ALTER SESSION ENABLE ADG_REDIRECT_DML;

-- Run a DML
INSERT INTO employees VALUES (201, 'John', 'Doe', 5000);

✅ This DML is sent to primary, executed there, and the result is shipped back.

Until committed on the primary, only this session sees the change. Other sessions on standby will see it after commit.

DML: Best Practices & Warnings

PL/SQL Execution on Standby

You can now run top-level PL/SQL blocks (procedures/functions) on Active Data Guard standby.

These too are automatically redirected to the primary.

💡 “Top-level” means: directly run blocks — not anonymous PL/SQL inside SQL tools.

Enabling PL/SQL Redirection

ALTER SESSION ENABLE ADG_REDIRECT_PLSQL;

🔹 Only works at the session level
🔹 Only for top-level calls (not dynamic SQL or bind variables)

PL/SQL Example

-- Assume this procedure exists on both primary & standby
CREATE OR REPLACE PROCEDURE update_sal (
  emp_id IN NUMBER,
  sal IN NUMBER
) AS
BEGIN
  UPDATE employees SET salary = sal WHERE employee_id = emp_id;
END;

Use it on the standby:

ALTER SESSION ENABLE ADG_REDIRECT_DML;  -- Required if PL/SQL uses DML
ALTER SESSION ENABLE ADG_REDIRECT_PLSQL;

EXEC update_sal(105, 6000);

✅ Oracle will redirect the call to the primary, execute it, and return the result.

Automatic Recompilation of Invalid PL/SQL Objects

If a PL/SQL object becomes invalid (e.g., because of a table change), Oracle can recompile it automatically on the primary — even from the standby.

How It Works

• The PL/SQL is invalid
• You attempt to use it on standby
• Oracle redirects the recompilation to the primary
• When done, it ships back the valid object

Requirement

-- Must be enabled to allow this:
ALTER SYSTEM SET ADG_REDIRECT_DML = TRUE;

Recompilation Example

Suppose the update_sal procedure becomes invalid due to a table change:

-- Table altered on primary:
ALTER TABLE employees ADD COLUMN department_id NUMBER;

-- On standby
ALTER SESSION ENABLE ADG_REDIRECT_DML;
EXEC update_sal(105, 6000);  -- Automatically recompiles on primary, then executes

Oracle detects the invalid PL/SQL, redirects the DDL, recompiles, and then executes your call.

✅ Summary Table

3. Using Temporary Tables on Active Data Guard Instances

What Are Temporary Tables?

Temporary tables are special tables used to hold session-specific or transaction-specific data. Data in these tables is:

• Private to your session
• Automatically deleted after a session or transaction ends
• Not written to disk permanently
• Perfect for reporting, intermediate calculations, or scratch work

🔄 Types of Temporary Tables

✅ Can We Use These on Active Data Guard (ADG)?

Yes! Starting with Oracle 12c and enhanced in 19c+, you can create and use:

Global Temporary Tables (GTT) on ADG

DML on GTT: How It Works

Even though ADG is read-only, Oracle allows DML on GTTs because it uses a feature called Temporary Undo.

📌 Key Concept: Temporary Undo

✅ This reduces redo traffic and improves performance for both primary and standby.

Example: DML on Global Temporary Table

-- On the ADG standby
CREATE GLOBAL TEMPORARY TABLE temp_sales (
   id NUMBER, 
   amount NUMBER
) ON COMMIT PRESERVE ROWS;

-- Insert data (allowed!)
INSERT INTO temp_sales VALUES (1, 1000);

✅ Works perfectly! Data is session-specific and no redo is generated.

Benefits of Temporary Undo on ADG

Restrictions on GTT in ADG

DDL on GTTs: Create/Drop

You can issue CREATE and DROP GTT statements from the ADG standby.

These are redirected to the primary, executed there, and reflected back.

🔁 Managed recovery must be in real-time apply mode and standby must be in sync.

Example: Create GTT on ADG

-- On standby, create table
CREATE GLOBAL TEMPORARY TABLE temp_orders (
   order_id NUMBER, 
   region VARCHAR2(20)
) ON COMMIT PRESERVE ROWS;

✅ Oracle transparently redirects this DDL to the primary, executes it, and reflects the change to the standby.

🔒 Note on DDL Redirection

Private Temporary Tables (PTT) on ADG

What Makes PTTs Special?

Example: Create PTT on ADG

-- On ADG standby
CREATE PRIVATE TEMPORARY TABLE ora$ptt_sales (
   product_id NUMBER,
   amount NUMBER
);

✅ No redo, no disk writes — safe and fast on ADG.

❗Table names must start with ORA$PTT_ or ORA$ by default

Quick Feature Summary Table

📌Summary: Best Practices

4. IM Column Store in an Active Data Guard Environment

What Is the IM Column Store?

The In-Memory Column Store (IM column store) is a feature that stores table data in a compressed columnar format in memory, enabling super-fast analytical queries and reporting by scanning only the relevant columns.

Starting with Oracle 12c Release 2 (12.2.0.1), Oracle supports the IM column store on Active Data Guard (ADG) standby databases.

Why Use IM Column Store on ADG?

Using the IM column store on a standby database has several key benefits:

Configuration Steps

Example Configuration (Simplified)

-- On standby database
ALTER SYSTEM SET INMEMORY_SIZE = 2G SCOPE=SPFILE;
ALTER SYSTEM SET ENABLE_IMC_WITH_MIRA = TRUE SCOPE=BOTH;

-- Restart the standby instance to activate IM column store

Practical Benefits in an ADG Setup

Limitations to Be Aware Of

🔍 Summary Table

✅ Best Practices

5. In-Memory External Tables in an Active Data Guard Environment

What Are In-Memory External Tables?

In-Memory External Tables combine the speed of Oracle In-Memory (IM) column store with the flexibility of external tables (tables that reference data outside the database—e.g., flat files on disk). They allow high-speed analytics on external data as if it’s part of the database, using in-memory columnar format.

With Active Data Guard, this feature is now available on standby databases as well—great for offloading analytics from primary!

Benefits in ADG Environment

Key Behavior & Configuration Notes

How It Works (High-Level Flow)

✅ Example Syntax (Simplified)

CREATE TABLE ext_sales (
  sale_id    NUMBER,
  sale_date  DATE,
  amount     NUMBER
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      sale_id, sale_date DATE "YYYY-MM-DD", amount
    )
  )
  LOCATION ('sales_data.csv')
)
INMEMORY;

This will load sales_data.csv into IM column store, and the same table can be queried on the standby database with excellent performance.

Important Limitations

Summary Table

Best Practices

6. Using Sequences in Oracle Active Data Guard

Oracle Active Data Guard supports global sequences (created with default CACHE and NOORDER) and session sequences. These sequences can be safely accessed on standby databases with proper caching and setup. This allows read-mostly standby databases to perform limited inserts or operations requiring unique IDs.

1️⃣ Global Sequences on Standby

How They Work

• When a standby database accesses a global sequence for the first time, it requests a range of sequence numbers from the primary.
• The primary database allocates a non-overlapping range based on the CACHE size, and updates the dictionary.
• Standby consumes the assigned range. Once exhausted, it requests a new one.
• Ensures unique values across all standbys and the primary.

💡 Supported only for sequences with CACHE and NOORDER.

Best Practices

• ❗ Always specify CACHE with a high enough value to minimize frequent round-trips to the primary.
• ❗ ORDER or NOCACHE sequences are not supported on standby.
• ✅ Define LOG_ARCHIVE_DEST_n on terminal standby pointing back to the primary (needed for redo transport for metadata changes).

Example: Multi-Standby Environment

Step 1: On Primary

CREATE GLOBAL TEMPORARY TABLE gtt (a INT);

CREATE SEQUENCE g CACHE 10;

Step 2: On Standby 1

INSERT INTO gtt VALUES (g.NEXTVAL);
-- 1

INSERT INTO gtt VALUES (g.NEXTVAL);
-- 2

SELECT * FROM gtt;
-- A = 1, 2 (standby 1 gets sequence 1–10)

Step 3: On Primary

SELECT g.NEXTVAL FROM dual;
-- 11
SELECT g.NEXTVAL FROM dual;
-- 12 (primary gets 11–20)

Step 4: On Standby 2

INSERT INTO gtt VALUES (g.NEXTVAL);
-- 21
INSERT INTO gtt VALUES (g.NEXTVAL);
-- 22

SELECT * FROM gtt;
-- A = 21, 22 (standby 2 gets 21–30)

2️⃣ Session Sequences on Standby

What Are Session Sequences?

• Special type of sequence: values are unique only within a session, not across sessions.
• Not persistent across sessions.
• Created on primary but can be accessed from read-only or read/write databases (including standby).
• Useful with global temporary tables having session-level visibility.
• Ignores CACHE/NOCACHE and ORDER/NOORDER.

Creating & Altering Session Sequences

✅ Create a session sequence:

CREATE SEQUENCE s SESSION;

Convert global to session:

ALTER SEQUENCE s SESSION;

Convert session to global:

ALTER SEQUENCE s GLOBAL;

Example: Using Session Sequences

On Primary

CREATE GLOBAL TEMPORARY TABLE gtt (a INT);

CREATE SEQUENCE s SESSION;

On Standby – Session 1

INSERT INTO gtt VALUES (s.NEXTVAL);
-- 1
INSERT INTO gtt VALUES (s.NEXTVAL);
-- 2

SELECT * FROM gtt;
-- A = 1, 2

On Standby – Session 2

INSERT INTO gtt VALUES (s.NEXTVAL);
-- 1
INSERT INTO gtt VALUES (s.NEXTVAL);
-- 2

SELECT * FROM gtt;
-- A = 1, 2 (values are local to this session)

Result: Each session gets its own private range starting from 1.

Notes & Restrictions

Summary

• Use global sequences with CACHE (not NOCACHE) for consistent values across all databases.
• Use session sequences for lightweight, session-specific ID generation with temp tables.
• Be aware of round-trips from standby to primary when cache is exhausted.
• Monitor cache size and redo transport when using sequences on standby.

7. Using the Result Cache on Physical Standby Databases

In an Active Data Guard environment, query result caching can be enabled on physical standby databases to improve performance for repeated queries. This reduces computation time for queries that repeatedly access the same data, without impacting the performance of the standby.

Default Behavior

• By default, query results are not cached on a physical standby database.
• Enabling result caching must be done explicitly for each table involved in recurring queries.

How It Works

• Oracle allows the result cache to store query results for enabled tables on physical standby databases.
• A query result will be cached only if all dependent tables used in the query are enabled for result cache using the clause: sqlCopyEditRESULT_CACHE (STANDBY ENABLE)

🔸 Views are not cached, even if their underlying tables are enabled.

📌 When Is Result Cache Used?

A query will utilize the standby result cache only if:

• It’s run on a physical standby in Active Data Guard mode.
• All the tables involved in the query are enabled for result cache.
• The query is repeatable (i.e., returns the same result between redo apply intervals).

Enabling Result Cache on Standby Tables

1. Modify Existing Table

To enable result cache for an existing table:

ALTER TABLE employee RESULT_CACHE (STANDBY ENABLE);

2. During Table Creation

To enable result cache while creating the table:

CREATE TABLE employee (
  emp_id NUMBER,
  ename VARCHAR2(50),
  sal   NUMBER
) RESULT_CACHE (STANDBY ENABLE);

You may also see this written as:

RESULT_CACHE (STABLE ENABLE);

STABLE tells Oracle the data does not change frequently and is safe to cache.

💡 Important Notes

🧠 Best Practices

• ✅ Enable result cache for tables used in frequent queries on the standby.
• ❗ Ensure all tables in multi-table queries are enabled for standby cache.
• ❌ Don’t rely on result cache for views or volatile data.
• 🔄 Consider query patterns and use hints (e.g., /*+ RESULT_CACHE */) if needed.
• 📊 Monitor cache performance if using it extensively across multiple PDBs.

✅ Summary

• Result caching on standby can significantly boost performance for repetitive read queries.
• You must explicitly enable this feature on each table using the STANDBY ENABLE clause.
• Only works for tables, not views, and requires Active Data Guard.
• Works per-PDB in container databases.