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

Real-time query on standby server requirements and musts 

--Part 2

In the part1, I described some solutions to ensuring Oracle standby database is synched with primary for critical reporting services. I showing that STANDBY_MAX_DATA_DELAY parameter how helps to control GAP, and review 'alter session sync with primary' affection to force a synchronization between standby and primary database.

One of another techniques is creating a specific service using tuning options:

Define your read service on standby with:

srvctl add service -d <DB_UNIQUE_NAME> -s ro_service \

  -role PHYSICAL_STANDBY \

  -preferred <standby_instance> \

  -pdb <your_pdb> \

  -commit_outcome TRUE \

  -failovertype SELECT \

  -replay_init_time 1800 \

  -rlbgoal SERVICE_TIME \

  -clbgoal SHORT \

  -maxlag 5

 

Explanation

• -role PHYSICAL_STANDBY → ensures this service runs only on the standby (Active Data Guard).
• -rlbgoal SERVICE_TIME → enables runtime load balancing based on actual response times.
• -clbgoal SHORT → optimizes for short-lived connections, such as web requests.
• -maxlag 5 → enforces a maximum 5-second apply lag; if the standby falls behind, the service is automatically taken offline.
  This parameter behaves similarly to STANDBY_MAX_DATA_DELAY=5, but at the service level instead of the instance level.
• -commit_outcome TRUE and -failovertype TRANSACTION → enable Application Continuity for reliable transaction replay.
• -replay_init_time 1800 → sets 30-minute window for session replay initialization.

 

 

 

 

 

Parameter	Meaning
failovertype TRANSACTION	During a failover (or replay), in-flight transactions are fully replayed. Ensures transactional integrity. Common for read-write / primary services.
failovertype SELECT	During a failover, only SELECT statements are replayed. No transactional replay. Common for read-only / standby services.

 

-clbgoal Overview

The -clbgoal parameter in srvctl add service controls how Oracle distributes client connections at connect time across available instances.

• CLB = Connect-Time Load Balancing
• Affects which instance a new client session is sent to when multiple instances are available for a service.
• Typical values: SHORT or LONG

 

🔹 Value Definitions

Value	Meaning	When to Use
SHORT	Distribute connections to the instance with the fewest active sessions or shortest estimated workload. Optimized for short-lived connections (OLTP queries, quick selects, small jobs).	Web apps, dashboards, and services with many small queries.
LONG	Distribute connections considering long-running queries and current service time. Optimized for heavy, long-duration queries to avoid overloading an instance.	BI / reporting queries, large analytics queries on ADG standby.

 

🔹 How They Behave on Standby

a) TRANSACTION

• Guarantees full transactional continuity if a session fails over from one node to another or from standby to primary.
• All in-flight statements (including DML) are replayed.
• Introduces slightly more overhead on session initialization and replay logic.
• Mostly used for OLTP / primary read-write workloads with Application Continuity.

b) SELECT

• Only read queries (SELECTs) are replayed if a failover happens.
• No DML replay (no transactions).
• Minimal overhead, optimized for read-only reporting.
• Perfect for ADG standby read services where clients do not issue DML.

-commit_outcome : Enable Transaction Guard; when set to TRUE, the commit outcome for a transaction is accessible after the transaction's session fails due to a recoverable outage.

-replay_init_time:

For Application Continuity, this parameter specifies the difference between the time, in seconds, of original processing of the first operation of a request and the time that the replay is ready to start after a successful reconnect. Application Continuity will not replay after the specified amount of time has passed. This parameter is intended to avoid the unintentional processing of a transaction when a system is recovered after a long period. The default is 5 minutes (300). The maximum value is 24 hours (86400). If the -failover_type parameter is not set to TRANSACTION, then you cannot use this parameter.

 

-rlbgoal {NONE | SMART_CONN | SERVICE_TIME | THROUGHPUT}         

Runtime Load Balancing Goal (for the Load Balancing Advisory).

Set this parameter to SMART_CONN to enable Smart Connection Rebalance.

Set this parameter to SERVICE_TIME to balance connections by response time.

Set this parameter to THROUGHPUT to balance connections by throughput.

 

-maxlag 5 here works similar to STANDBY_MAX_DATA_DELAY, controlling read service availability based on lag.

-maxlag(maximum_lag_time ):

The default value is ANY. You must also specify the -global option.

Maximum replication lag time in seconds for a global service.

Must be a non-negative integer.

 

What is Session Draining

Session draining is a feature in Oracle that allows existing client sessions to complete gracefully before a service is stopped, relocated, or taken offline.

• Purpose: Avoid killing active user sessions or long-running queries when:
• Shutting down a service
• Moving a service to another node
• Performing maintenance on an instance

 

• How it works:

1.                   Oracle marks the service or instance as unavailable for new connections.

2.                   Existing sessions continue running until:

• They finish naturally, or
• A timeout expires (-drain_timeout), after which sessions are forcibly disconnected.

This ensures zero disruption for users while allowing controlled maintenance.

 

 

When is Session Draining Used?

• RAC (Real Application Clusters):
• Moving a service between nodes (srvctl relocate service)
• Stopping a node or instance (srvctl stop instance)
• Active Data Guard:
• Switching standby services or taking them offline
• General maintenance:
• Applying patches, restarting nodes, or scaling resources

 

3️⃣ Key SRVCTL Options for Session Draining

Option	Description	Default / Notes
-drain_timeout <seconds>	Maximum time Oracle will wait for existing sessions to complete before forcefully terminating them.	Default = 300 seconds (5 min). Can be adjusted based on workload.
-drain	Tells Oracle to drain sessions instead of immediately stopping the service. Often used with srvctl stop service or relocate service.	Must be combined with -drain_timeout.
-graceful	Similar purpose; ensures in-flight sessions complete naturally (sometimes used in older SRVCTL versions).	Less commonly used in 12c+; -drain is preferred.
-force	Immediately kills sessions without waiting. Overrides session draining.	Use only if you need immediate shutdown.

 

Example Scenarios

a) Draining sessions before stopping a service

srvctl stop service -d ORCL -s app_ro_svc -drain -drain_timeout 600

• Marks app_ro_svc as unavailable for new connections.
• Existing sessions wait up to 600 seconds to complete.
• After timeout, any remaining sessions are disconnected.

 

 

b) Relocating a service with session draining

srvctl relocate service -d ORCL -s app_rw_svc -oldinst orcl1 -newinst orcl2 -drain -drain_timeout 300

• Stops routing new connections to orcl1.
• Existing sessions finish up to 5 minutes.
• After 300 seconds, remaining sessions are moved or terminated on orcl1.

 

c) Forcing a service stop (skip draining)

srvctl stop service -d ORCL -s app_ro_svc -force

• Immediately terminates all sessions.
• Useful for emergency shutdowns, but disrupts clients.

 

Best Practices

1. Use session draining for long-running queries or critical workloads to avoid breaking users.
2. Set -drain_timeout based on expected query duration:
• OLTP: 30–60 seconds usually enough
• Analytics / reporting: 300–900 seconds depending on query length
3. Combine with service relocation for zero-downtime maintenance.
4. Monitor sessions via:

SELECT sid, serial#, username, status FROM v$session WHERE service_name='APP_RO_SVC';

5. For Active Data Guard reporting services, session draining is especially useful when you need to:
• Take standby service offline
• Apply patches or upgrade without disrupting analytics users

 

 Summary

• Session draining = letting active sessions finish gracefully before stopping or relocating a service.
• Key options:
• -drain → enable session draining
• -drain_timeout <seconds> → maximum wait for sessions to finish
• -force → bypass draining, kill sessions immediately
• Use draining for long-running queries, maintenance, or zero-downtime service moves.
• Particularly important for ADG standby services and long-running reporting workloads.

 

 

In the last part (Part3) I continue another’s techniques.

 

 

 

*************** Alireza Kamrani ****************