Real-time query on standby server requirements and musts - Part1

Introduction:

In many Oracle environments, the physical standby database is treated as a purely passive DR asset, something you hope to never touch until a disaster happens. But modern SLAs, cost pressures, and read scalability needs are pushing teams to run real-time queries directly on standby. 

 

This sounds simple (“just open it read-only”), but in practice it raises tough questions:

 

Ø  How do we guarantee the standby is truly in sync with primary?   

Ø  What configuration parameters are mandatory (Redo Transport, Real-Time Apply, `SYNC` vs `ASYNC`, protection modes, etc.)? 

Ø  What does the right architecture look like to balance performance, latency, and consistency? 

Ø  How do we detect and prevent hidden lags, apply lag, transport lag, network issues, that can quietly invalidate “real-time” assumptions? 

Ø  What operational controls and monitoring are required so DBAs can confidently let critical reports and near real-time queries run on standby?

 

This post walks through the requirements and must-haves for safe real-time query on a standby:

 

Ø  Core architecture patterns for primary–standby setups supporting real-time query 

Ø  Key Oracle Data Guard parameters and settings that influence sync behavior 

Ø  Recommended modes, transport and apply options to minimize lag 

Ø  Practical checks, scripts, and metrics to prove the standby is really current 

 

By the end, you should have a concrete checklist of configuration, monitoring, and architectural decisions that let you treat your standby as a trustworthy, near-real-time read platform, without compromising its primary role as your last line of defense.

 

DBAs have some techniques to prepare Physical Standby server to offloading reporting, based on reporting importance we need main configuration on design, parameters, protection mode, redo apply mechanism to guarantee a real-time query processing with Zero-Gap.

Regardless of architecture and design, I review main parameters to control a user in SESSION level and internal of Data guard to achieve an exactly zero-gap and real-time query processing. This hep DBA to decrease Primary workload and also, we can use ADG for backup purpose.

 

We have some technique to prepare Standby for reporting service:

Ø  Use ADG_REDIRECT_DML to enable or disable automatic redirection of DML operations from a standby to the primary in Oracle Active Data Guard environments.

Ø  Use Active Data Guard with preferred Protection mode (based on business requirements)

Ø  Use Real-Time Apply and NODELAY Option in Media Recovery processing

But in this topic, I will demonstrate some new Options to ensuring a near 100% real-time processing for critical and financial reporting, because none of the above options guarantee that the data guard will be in sync, especially during peak hours.

Our requirement is to receive the records from the tables exactly as they are from the primary.

To what extent can this need be fulfilled?

 

Configuring Apply Lag Tolerance in a Real-time Query Environment

 

The STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-administrative users to a physical standby database that is in real-time query mode.

This capability allows queries to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if the standby database has become unacceptably stale.

If STANDBY_MAX_DATA_DELAY is set to the default value of NONE, then queries issued to a physical standby database are executed regardless of the apply lag on that database.

If STANDBY_MAX_DATA_DELAY is set to a nonzero value, then queries issued to a physical standby database are executed only if the apply lag is less than or equal to STANDBY_MAX_DATA_DELAY. Otherwise, an ORA-3172 error is returned to alert the client that the apply lag is too large.

If STANDBY_MAX_DATA_DELAY is set to 0, a query issued to a physical standby database is guaranteed to return the exact same result as if the query were issued on the primary database, unless the standby database is lagging behind the primary database, in which case an ORA-3172 error is returned.

Use the ALTER SESSION SQL statement to set STANDBY_MAX_DATA_DELAY. For example:

SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=2

 

Forcing Redo Apply Synchronization in a Real-time Query Environment

To ensure that all redo data received from the primary database has been applied to a physical standby database, you can use a SQL ALTER SESSION statement.

Issue the following SQL statement:

SQL> ALTER SESSION SYNC WITH PRIMARY;

This statement blocks until all redo data received by the standby database at the time that this command is issued has been applied to the physical standby database. An ORA-3173 error is returned immediately, and synchronization does not occur, if the redo transport status at the standby database is not SYNCHRONIZED or if Redo Apply is not active.

To ensure that Redo Apply synchronization occurs in specific cases, use the SYS_CONTEXT('USERENV','DATABASE_ROLE') function to create a standby-only trigger (enabled on the primary but that only takes certain actions if it is running on a standby). For example, you could create the following trigger that would execute the ALTER SESSION SYNC WITH PRIMARY statement for a specific user connection at logon:

CREATE TRIGGER adg_logon_sync_trigger

 AFTER LOGON ON user.schema

  begin

    if (SYS_CONTEXT('USERENV', 'DATABASE_ROLE')  IN ('PHYSICAL STANDBY')) then

      execute immediate 'alter session sync with primary';

    end if;

  end;

----------------------------------------------------------

Ø  ALTER SESSION SYNC WITH PRIMARY;

Key Characteristics

Aspect	Behavior
Nature	Blocking
Guarantee	Standby is fully caught up
Failure	ORA-3173 if sync not possible
Scope	Session-specific

 

When ORA-3173 occurs:

Ø  Redo transport not SYNCHRONIZED

Ø  Redo Apply not running

Ø  Not using SYNC transport

Ø  Real-time Query Restrictions

 

 

Why Protection Mode Matters:

Both STANDBY_MAX_DATA_DELAY = 0 and SYNC WITH PRIMARY impose strict requirements on redo transport.

Supported Protection Modes

Protection Mode	Supported	Reason
Maximum Performance	Not guaranteed	ASYNC transport
Maximum Availability	Supported	SYNC + optional fallback
Maximum Protection	Fully supported	SYNC + no data loss

 

Technical Dependency Matrix

Feature	Requires
STANDBY_MAX_DATA_DELAY = 0	SYNC transport
ALTER SESSION SYNC WITH PRIMARY	SYNC transport
Guaranteed identical results	Real-Time Apply
No ORA-3173	Transport status = SYNCHRONIZED
MAA compliance	Maximum Availability

 

Key MAA Controls:

Layer	Control
Redo Transport	SYNC (Max Availability)
Apply	Real-Time Apply
Query Control	STANDBY_MAX_DATA_DELAY
Hard Sync	ALTER SESSION SYNC WITH PRIMARY
Access Control	Logon triggers per user
Monitoring	Apply lag alerts

 

 

 

Choosing the Right Strategy: Decision Table

Reporting Criticality	Recommended Approach
Near-real-time dashboards	STANDBY_MAX_DATA_DELAY = 2–5
Financial / regulatory reports	SYNC WITH PRIMARY
Mixed workloads	Combination via logon trigger
Read-only analytics	No delay control
Zero data divergence	STANDBY_MAX_DATA_DELAY = 0

 

Key Takeaways

1.        Active Data Guard alone does not guarantee freshness

2.        STANDBY_MAX_DATA_DELAY = policy-based lag control

3.        ALTER SESSION SYNC WITH PRIMARY = hard consistency barrier

4.        Both features require SYNC redo transport

5.        Maximum Availability mode is mandatory for critical reporting

6.        Combining these features is a canonical Oracle MAA pattern

 

If we use Physical STBY for Critical reporting(finance) that need a query freshness guarantee, do we need these settings when using Real-Time Apply on a physical standby with a higher protection mode such as Maximum Protection?

1.       Yes — even in Maximum Protection mode, you still need
STANDBY_MAX_DATA_DELAY and/or ALTER SESSION SYNC WITH PRIMARY
if the standby is used for critical reporting.

Why?

Maximum Protection guarantees no data loss on commit
It does NOT guarantee that a query on an open standby sees the latest data at query time.

These are two different problem domains.

 

2. What Maximum Protection Actually Guarantees (and What It Doesn’t)

What Maximum Protection guarantees

Ø  LGWR SYNC redo transport

Ø  Commit on primary does not complete unless redo is written to standby SRL

Ø  Zero data loss under all failure scenarios

Ø  Redo transport status = SYNCHRONIZED

This is a transaction durability guarantee, not a query guarantee.

What Maximum Protection does NOT guarantee

Ø  That redo has been applied

Ø  That standby queries see the latest committed SCN

Ø  That a reporting session waits for apply

Ø  That standby query results are identical to primary at that instant

 Redo can be:

• Shipped
• Persisted
• Not yet applied

This gap can exist even in Maximum Protection based on some situations!.

 

In Maximum Protection

Aspect	Status
Redo transport	Already SYNC
Apply lag possible	Yes
Query freshness control	Without this
Query freshness control	With this

✔ It prevents silent stale reads
✔ It converts freshness into a contract
✔ It fails fast with ORA-3172 if violated

Role of ALTER SESSION SYNC WITH PRIMARY in Maximum Protection

What it adds:

ALTER SESSION SYNC WITH PRIMARY;

This does one critical thing:

It blocks the session until all received redo has been applied.

 

Why it is still needed

Even in Maximum Protection:

• Redo arrival ≠ Redo applied
• Reporting may start before apply catches up

This command:

• Forces apply to catch up before query
• Guarantees identical results to primary at that moment

Fast Sync Mode – What It Is and What It Is Not

What Fast Sync Actually Means?

Fast Sync is (SYNC NOAFFIRM) and enables all sessions waiting for a remote RFS write to proceed as soon as the write is submitted, not when the write completes. This offers the best performance since it reduces the overall SYNC remote write wait event.

It refers to:

• LGWR SYNC + AFFIRM
• Reduced commit latency using:
• Network batching
• Standby Redo Logs (SRL)
• Optimized acknowledgment path

Fast Sync:

• Improves commit performance
• Does not change apply behavior
• Does not change query consistency

 

 

 

 

 

Fast Sync ≠ Apply Synchronization

Feature	Fast Sync
Commit latency	Improved
Redo safety	Same
Apply lag	Unchanged
Query freshness	Not guaranteed

 

So even with Fast Sync:

• Standby may still lag in apply
• Queries may still read stale data

 

Final MAA Guidance (Clear and Practical)

If your standby is used for critical reporting

Even in Maximum Protection, you SHOULD:

1. Use Real-Time Apply
2. Use STANDBY_MAX_DATA_DELAY
3. Use ALTER SESSION SYNC WITH PRIMARY for strict use cases
4. Enforce via logon triggers per reporting user
5. Monitor apply lag continuously
6. Use NODELAY Option for media Recovery

 

This list discusses restrictions related to real-time query mode.

• The apply lag control and Redo Apply synchronization mechanisms described above require that the client be connected and issuing queries to a physical standby database that is in real-time query mode.
• The following additional restrictions apply if STANDBY_MAX_DATA_DELAY is set to 0 or if the ALTER SESSION SYNC WITH PRIMARY SQL statement is used:
• The standby database must receive redo data via the SYNC transport.
• The redo transport status at the standby database must be SYNCHRONIZED and the primary database must be running in either maximum protection mode or maximum availability mode.
• Real-time apply must be enabled.
• Oracle Active Data Guard achieves high performance of real-time queries in an Oracle RAC environment through the use of cache fusion. This allows the Oracle Data Guard apply instance and queries to work out of cache and not be slowed down by disk I/O limitations.

 A consequence of this is that an unexpected failure of the apply instance leaves buffers in inconsistent states across all the open Oracle RAC instances. If the database is open on any other instances, one of the open instances performs Active Data Guard instance recovery to bring the database to a consistent state and all the open instances remain open.

 

Wait for Part2 & Part3 in next weeks

*******************************************************************