Oracle Data Guard Active Data Guard (ADG) Offloading Strategy for Reporting Workloads

Executive Summary

Active Data Guard (ADG) is Oracle’s flagship disaster-recovery and workload-offloading technology. By opening a physical standby database in READ ONLY WITH APPLY mode, ADG enables real-time query (RTQ) access to a transactionally consistent snapshot of the primary while redo is continuously applied. For enterprises running heavy reporting, BI, data-extract, and analytical workloads, ADG offloading is the most cost-effective way to reclaim primary CPU, I/O, and memory without licensing a second primary database.

This guide provides a production-ready strategy for designing, validating, and operating an ADG offloading architecture. It covers the diagnostic commands needed to assess readiness, a step-by-step runbook for implementation, and the operational guardrails that prevent the two most common failure modes: apply-lag-induced snapshot staleness and patch drift — including ORA-00600 internal errors that can surface during broker reconfiguration or redo apply when primary and standby run divergent patch levels.

ADG offloading is not a “flip a switch” feature. It requires deliberate design of redo transport, standby redo logs, TEMP/UNDO sizing, role-based services, and monitoring. Done correctly, it transforms a passive DR standby into an active asset that absorbs the majority of read traffic. Done incorrectly, it produces stale reports, ORA-00600 internal errors, and a standby that cannot keep up with apply.

1. Overview

ADG offloading is a layered architecture that depends on five components working in concert:

1. Licensed ADG option with a physical standby in READ ONLY WITH APPLY state.
2. Redo transport configured as SYNC or ASYNC with zero or bounded data loss.
3. Standby redo logs, TEMP tablespaces, and UNDO sized for reporting concurrency.
4. Role-based services (ROLE=PHYSICAL_STANDBY) with JDBC/OCI failover.
5. Continuous validation of APPLY_LAG, TRANSPORT_LAG, and standby AWR snapshots.

The implementation sequence follows a six-phase process:

Phase 1 — Assess: Inventory reporting workloads, quantify primary CPU/IO, and confirm ADG licensing and standby topology. Phase 2 — Configure: Add standby redo logs, TEMP tablespaces, and UNDO; verify READ ONLY WITH APPLY and redo transport health. Phase 3 — Validate: Confirm zero/bounded data loss, APPLY_LAG within SLA, and patch alignment (DBA_REGISTRY_SQLPATCH) across both sites. Phase 4 — Route: Create role-based services (ROLE=PHYSICAL_STANDBY) and point reporting connections at the standby service. Phase 5 — Monitor: Track apply lag, standby event histograms, TEMP/UNDO pressure, and standby AWR snapshots continuously. Phase 6 — Optimize: Tune apply processes, parallel query, and service failover; revisit sizing as reporting concurrency grows.

2. Diagnostic Checklist

Run the following checks before designing or enabling ADG offloading. Each command should be executed on the appropriate host (primary, standby, or both) and the output recorded as a baseline.

2.1 Topology & Role Verification

# On BOTH primary and standby, as the grid infrastructure owner:
dgmgrl sys/"<password>"@<connect_identifier>
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE '<primary_db_unique_name>';
DGMGRL> SHOW DATABASE '<standby_db_unique_name>';

-- On BOTH primary and standby:
SELECT database_role, open_mode, protection_mode, protection_level,
       db_unique_name, force_logging
FROM   v$database;

SELECT inst_id, instance_name, status, database_status
FROM   gv$instance;

-- Confirm the standby is in the correct ADG state:
SELECT open_mode, recovery_status
FROM   v$database
WHERE  database_role = 'PHYSICAL STANDBY';
-- Expected: OPEN_MODE = READ ONLY WITH APPLY, RECOVERY_STATUS = ACTIVE

2.2 Redo Transport & Apply Lag

-- On the STANDBY:
SELECT name, value, unit, time_computed
FROM   v$dataguard_stats
WHERE  name IN ('transport lag', 'apply lag', 'apply finish time');

-- On the PRIMARY:
SELECT dest_id, destination, status, error, gap_status,
       redo_transport_mode, net_timeout
FROM   v$archive_dest
WHERE  dest_id > 1;

-- On the STANDBY — apply throughput and bottleneck events:
SELECT event, total_waits, time_waited_micro, average_wait
FROM   v$standby_event_histogram
WHERE  total_waits > 0
ORDER  BY time_waited_micro DESC;

-- On the STANDBY — redo apply rate (MB/s):
SELECT TO_CHAR(COMPLETION_TIME, 'DD-MON-YY HH24:MI') AS applied_at,
       ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024, 2) AS mb_applied
FROM   v$archived_log
WHERE  applied = 'YES'
  AND  completion_time > SYSDATE - 1
GROUP  BY TO_CHAR(COMPLETION_TIME, 'DD-MON-YY HH24:MI')
ORDER  BY 1;

2.3 Standby Readiness (TEMP, UNDO, Datafiles)

-- On the STANDBY:
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb,
       ROUND(SUM(user_bytes)/1024/1024/1024, 2) AS used_gb
FROM   dba_temp_files
GROUP  BY tablespace_name;

-- TEMP usage by active reporting sessions:
SELECT tablespace_name, ROUND(SUM(blocks*block_size)/1024/1024, 2) AS temp_mb
FROM   v$tempseg_usage
GROUP  BY tablespace_name;

-- UNDO on standby (ADG uses local undo for read-consistent queries):
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024, 2) AS undo_gb
FROM   dba_data_files
WHERE  tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO')
GROUP  BY tablespace_name;

-- Datafile status on standby:
SELECT file#, status, ROUND(bytes/1024/1024/1024, 2) AS size_gb,
       checkpoint_time, last_change_time
FROM   v$datafile
WHERE  status NOT IN ('ONLINE', 'SYSTEM');

2.4 Service & Connection Routing

-- On the STANDBY — verify reporting services are registered:
SELECT name, network_name, creation_date, failover_type,
       failover_method, goal, clb_goal
FROM   dba_services
WHERE  name LIKE '%REPORT%' OR name LIKE '%ADG%';

-- On the STANDBY — active sessions using the reporting service:
SELECT inst_id, service_name, COUNT(*) AS session_count,
       ROUND(SUM(cpu_time)/1000000, 2) AS cpu_sec
FROM   gv$active_session_history
WHERE  sample_time > SYSDATE - 1/24
GROUP  BY inst_id, service_name
ORDER  BY 3 DESC;

2.5 Patch & Security Alignment

-- On BOTH primary and standby — compare output side-by-side:
SELECT patch_id, patch_type, action, status,
       TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI') AS applied_at
FROM   dba_registry_sqlpatch
ORDER  BY action_time;

-- On BOTH — check Data Guard broker / redo transport component status:
SELECT comp_id, comp_name, version, status
FROM   dba_registry
WHERE  comp_id IN ('OAS', 'CATALOG', 'APS');

-- On BOTH — confirm the latest Quarterly CPU/PSU patch is applied:
SELECT patch_id, description, action, status
FROM   dba_registry_sqlpatch
WHERE  action = 'APPLY'
ORDER  BY action_time DESC
FETCH FIRST 5 ROWS ONLY;

2.6 Workload Measurement (Baseline)

-- On the PRIMARY — quantify the read workload to be offloaded:
SELECT s.service_name,
       ROUND(SUM(s.cpu_time)/1000000, 2) AS cpu_sec,
       ROUND(SUM(s.physical_read_bytes)/1024/1024/1024, 2) AS read_gb,
       COUNT(DISTINCT s.sample_time) AS samples
FROM   gv$active_session_history s
WHERE  s.session_type = 'FOREGROUND'
  AND  s.sample_time > SYSDATE - 7
GROUP  BY s.service_name
ORDER  BY 2 DESC;

-- On the PRIMARY — top SQL by physical reads (candidates for offloading):
SELECT sql_id, ROUND(SUM(physical_read_bytes)/1024/1024/1024, 2) AS read_gb,
       COUNT(*) AS executions
FROM   gv$sql
WHERE  command_type IN (3, 47)  -- SELECT and PL/SQL
GROUP  BY sql_id
ORDER  BY 2 DESC
FETCH FIRST 20 ROWS ONLY;

3. Step-by-Step Resolution Runbook

Step 0: Safety Checks

Before making any change, establish a safety baseline. ADG offloading touches redo transport, standby state, and connection routing — all of which are failover-critical.

# 0.1 — Verify primary is healthy and force logging is enabled:
sqlplus / as sysdba
SELECT force_logging, supplemental_log_data_pk, supplemental_log_data_all
FROM   v$database;
# Expected: FORCE_LOGGING = YES (mandatory for Data Guard)

# 0.2 — Verify a recent backup exists (RMAN):
rman target /
RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER 'SYSDATE-2';

# 0.3 — Verify the standby is currently in a consistent state:
dgmgrl sys/"<password>"@<primary_connect>
DGMGRL> SHOW CONFIGURATION;
# Expected: SUCCESS state for both primary and standby

# 0.4 — Confirm no failover or switchover is in progress:
DGMGRL> SHOW DATABASE '<standby_db_unique_name>' 'StateReport';

Safety gate: If any of the above fails, stop and resolve before proceeding. Never enable ADG offloading on a standby with unresolved redo gaps or an unhealthy broker configuration.

Step 1: Validate Licensing & Prerequisites

Active Data Guard is a licensed option (Oracle Enterprise Edition + ADG option). Confirm entitlement before implementation.

# 1.1 — Confirm the ADG option is enabled:
sqlplus / as sysdba
SELECT value FROM v$option WHERE parameter = 'Active Data Guard';
# Expected: TRUE

# 1.2 — Confirm the standby is a physical standby (not logical):
SELECT database_role FROM v$database;
# Expected: PHYSICAL STANDBY

# 1.3 — Confirm the standby is open in ADG mode:
SELECT open_mode FROM v$database;
# Expected: READ ONLY WITH APPLY

If the standby is in MOUNTED mode, open it for ADG:

-- On the STANDBY:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE OPEN;
-- Then re-enable managed recovery:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Step 2: Configure Standby Redo Logs and TEMP

ADG reporting workloads consume TEMP aggressively (sorting, hash joins, parallel query) and require sufficient standby redo logs to absorb peak redo generation.

-- 2.1 — Add standby redo log groups on the STANDBY.
-- Rule of thumb: at least one more group than primary online redo logs,
-- and each SRL sized >= the largest primary online redo log.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/u01/oradata/STBY/srl11a.log') SIZE 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/u01/oradata/STBY/srl12a.log') SIZE 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/u01/oradata/STBY/srl13a.log') SIZE 2G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/u01/oradata/STBY/srl14a.log') SIZE 2G;

-- Verify:
SELECT group#, thread#, sequence#, bytes, status
FROM   v$standby_log;

-- 2.2 — Add TEMP tablespaces on the STANDBY for reporting concurrency.
-- Size for the peak concurrent reporting sort/hash area, not the primary's TEMP.
CREATE TEMPORARY TABLESPACE temp_report
  TEMPFILE '/u01/oradata/STBY/temp_report01.dbf' SIZE 32G AUTOEXTEND ON NEXT 8G MAXSIZE 128G;

-- 2.3 — Add a dedicated UNDO tablespace on the standby if reporting
-- queries are long-running and primary UNDO is undersized.
CREATE UNDO TABLESPACE undotbs_adg
  DATAFILE '/u01/oradata/STBY/undotbs_adg01.dbf' SIZE 16G AUTOEXTEND ON NEXT 4G MAXSIZE 64G;

-- 2.4 — Assign the new TEMP as the default for the reporting service users:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_report;

Step 3: Create Role-Based Reporting Services

Services are the linchpin of ADG offloading. A service with ROLE=PHYSICAL_STANDBY is only available on the standby; when a switchover occurs, the service automatically relocates to the new primary.

-- 3.1 — On the PRIMARY, create the reporting service (it will be
-- automatically propagated to the standby via the broker):
EXEC DBMS_SERVICE.CREATE_SERVICE(
  service_name => 'REPORT_ADG',
  network_name => 'REPORT_ADG',
  goal         => DBMS_SERVICE.GOAL_SERVICE_TIME,
  clb_goal     => DBMS_SERVICE.CLB_GOAL_LONG,
  failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC,
  failover_type   => DBMS_SERVICE.FAILOVER_TYPE_SELECT,
  failover_retries => 30,
  failover_delay   => 5
);

-- 3.2 — Register the service with the Data Guard broker for role-based
-- startup on the standby:
dgmgrl sys/"<password>"@<primary_connect>
DGMGRL> EDIT DATABASE '<standby_db_unique_name>' SET PROPERTY 'ServiceRegistrationEnabled' = 'TRUE';
DGMGRL> EDIT DATABASE '<standby_db_unique_name>' SET PROPERTY 'StandbyFileManagement' = 'AUTO';

-- 3.3 — Start the service on the standby:
sqlplus / as sysdba
ALTER SYSTEM SET SERVICE_NAMES = 'REPORT_ADG' SCOPE = BOTH;

Step 4: Route Reporting Workloads

Point reporting connections at the standby service. Use connection strings that resolve to the standby host and include failover to the primary in case of switchover.

# 4.1 — JDBC connection string for reporting applications:
# jdbc:oracle:thin:@(DESCRIPTION=
#   (ADDRESS=(PROTOCOL=TCP)(HOST=standby-host)(PORT=1521))
#   (CONNECT_DATA=(SERVICE_NAME=REPORT_ADG)))

# 4.2 — Oracle Net (tnsnames.ora) entry:
REPORT_ADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby-host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = REPORT_ADG)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 30)
        (DELAY = 5))))

-- 4.3 — Verify sessions are landing on the standby:
-- On the STANDBY:
SELECT inst_id, username, service_name, program, module
FROM   gv$session
WHERE  service_name = 'REPORT_ADG'
  AND  type = 'USER';

-- 4.4 — Confirm no reporting sessions remain on the primary:
-- On the PRIMARY:
SELECT service_name, COUNT(*) AS session_count
FROM   gv$session
WHERE  service_name = 'REPORT_ADG'
GROUP  BY service_name;
-- Expected: 0 rows

Step 5: Validate Offloading Effectiveness

After routing, measure the actual reduction in primary load and confirm the standby is keeping up.

-- 5.1 — On the PRIMARY, compare before/after host CPU utilization:
SELECT ROUND((b.value / (b.value + i.value)) * 100, 2) AS cpu_busy_pct
FROM   (SELECT value FROM v$osstat WHERE stat_name = 'BUSY_TIME') b,
       (SELECT value FROM v$osstat WHERE stat_name = 'IDLE_TIME') i;

-- 5.2 — On the STANDBY, confirm apply lag is within SLA:
SELECT name, value
FROM   v$dataguard_stats
WHERE  name IN ('transport lag', 'apply lag');

-- 5.3 — On the STANDBY, capture an AWR snapshot for baseline:
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 5.4 — On the PRIMARY, confirm the top reporting SQL is no longer executing:
SELECT sql_id, executions, ROUND(elapsed_time/1000000, 2) AS elapsed_sec
FROM   v$sql
WHERE  sql_id IN ('<sql_id_1>', '<sql_id_2>')
  AND  parsing_schema_name NOT IN ('SYS', 'SYSTEM');

Step 6: Monitor & Tune

ADG offloading is an ongoing operation. Establish monitoring thresholds and tune apply performance.

-- 6.1 — Create a monitoring query for apply lag (run every 5 minutes):
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') AS check_time,
       name, value
FROM   v$dataguard_stats
WHERE  name IN ('transport lag', 'apply lag')
UNION ALL
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'),
       'apply_finish_time',
       value
FROM   v$dataguard_stats
WHERE  name = 'apply finish time';

-- 6.2 — Tune apply parallelism if apply lag is persistent:
-- On the STANDBY (requires restart of managed recovery):
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION PARALLEL 16;
-- Note: PARALLEL applies to 19c; in 23ai, use
-- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION USING PARALLELISM 16;

-- 6.3 — Monitor TEMP pressure from reporting queries:
SELECT tablespace_name, ROUND(SUM(blocks*block_size)/1024/1024, 2) AS temp_mb,
       COUNT(*) AS active_sorts
FROM   v$tempseg_usage
GROUP  BY tablespace_name
HAVING ROUND(SUM(blocks*block_size)/1024/1024, 2) > 1024;

-- 6.4 — Set up alerts for lag thresholds (via OEM or custom script):
-- Alert when APPLY_LAG > 30 seconds or TRANSPORT_LAG > 15 seconds.

Step 7: Patch Alignment for ORA-00600 Prevention

Patch drift between primary and standby is a leading cause of ORA-00600 internal errors during broker reconfiguration, redo transport failover, or apply. The most common symptom is:

ORA-00600: internal error code, arguments: [krsu_validate_conn], [0], ...

followed by broker state transitions to UNKNOWN and redo transport gaps.

# 7.1 — Verify patch alignment on BOTH primary and standby:
sqlplus / as sysdba
SELECT patch_id, action, status, TO_CHAR(action_time, 'DD-MON-YYYY') AS applied_on
FROM   dba_registry_sqlpatch
ORDER  BY action_time;

# 7.2 — If the latest Quarterly CPU/PSU patch is missing on either site,
# apply it during the next maintenance window. Use OPatch:
#   $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME /path/to/patch
#   $ORACLE_HOME/OPatch/opatch lsinventory

# 7.3 — After patching, restart the Data Guard broker on both sites:
dgmgrl sys/"<password>"@<primary_connect>
DGMGRL> DISABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
# Expected: SUCCESS for both databases

# 7.4 — If ORA-00600 persists after patching, collect diagnostics:
#   - Alert log on both sites
#   - trc files from the DIAG destination
#   - dgmgrl SHOW CONFIGURATION VERBOSE
#   - MOS Note 1061463.1 (ORA-00600 troubleshooting) and Note 209768.1

Step 8: Rollback / Fallback Plan

Every production change needs a rollback path. If ADG offloading causes instability, revert in this order:

# 8.1 — Stop the reporting service on the standby:
sqlplus / as sysdba
ALTER SYSTEM SET SERVICE_NAMES = '' SCOPE = BOTH;

# 8.2 — Redirect reporting connections back to the primary service:
# Update tnsnames.ora / JDBC URLs to point to the primary service name.

# 8.3 — If the standby itself is unstable, stop managed recovery and
# return it to MOUNTED mode:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

# 8.4 — Verify the primary is fully healthy and the broker is clean:
dgmgrl sys/"<password>"@<primary_connect>
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE '<primary_db_unique_name>' 'StateReport';

Rollback gate: Do not leave the standby in a half-configured state. Either the standby is in READ ONLY WITH APPLY with the reporting service active, or it is in MOUNTED with managed recovery running. Any other state is a DR risk.

📚 Official Documentation & Technical References

Oracle Documentation

• Oracle Data Guard Concepts and Administration (19c)
• Oracle Data Guard Concepts and Administration (23ai)
• Active Data Guard Real-Time Query
• Oracle Data Guard Broker (DGMGRL)
• Using Services with Data Guard

My Oracle Support

• MOS Note 1061463.1 — Troubleshooting ORA-00600 Errors
• MOS Note 209768.1 — Data Guard: ORA-00600 and ORA-10458 Troubleshooting
• MOS Note 454942.1 — Active Data Guard Real-Time Query: Setup and Best Practices

Related Resources

• Oracle Critical Patch Update Advisory

---

Need help designing or troubleshooting your ADG offloading architecture? Our enterprise database engineers have deployed ADG offloading for Fortune 500 financial, healthcare, and retail environments. We handle architecture design, patch alignment, performance tuning, and 24/7 incident response.

Contact our Oracle experts · Explore our database services