Chetan Yadav: Data Guard

Showing posts with label Data Guard. Show all posts

Thursday, March 12, 2026

Oracle Data Guard Protection Modes: Zero Data Loss Design Strategy

Production HA/DR Architecture for DBAs and Architects

12 March 2026

Chetan Yadav — Senior Oracle & Cloud DBA

⏱️ 12–13 min read

⏱️ Estimated Reading Time: 12–13 minutes

Maximum Protection • Maximum Availability • Maximum Performance — Choose Wisely

Oracle Data Guard Protection Modes architecture diagram showing Primary, Standby, GCS, GES, redo transport and RPO RTO comparison

⚙️ Production Environment Referenced in This Article

Oracle Database: 19.18.0.0.0 & 23ai Enterprise Edition • Data Guard Broker: Enabled with Fast-Start Failover
Primary: 2-Node RAC, 6 TB OLTP • Standby: Physical Standby, separate data center (120 km)
Network: Dedicated 10 GbE WAN link • RTO Target: < 30 seconds • RPO Target: Zero data loss
Application: Core banking transaction processing system

2:14 AM. A storage array at the primary data center suffered a catastrophic controller failure. The entire primary RAC cluster went offline. No warning. No graceful shutdown. Just gone.

Within 23 seconds, Oracle Data Guard Fast-Start Failover had detected the outage, confirmed quorum with the observer, and automatically activated the physical standby database. The application connection pool reconnected. Transactions resumed. The on-call team received the alert after the failover had already completed.

That outcome — 23 seconds, zero data loss, no manual intervention — was not luck. It was the result of choosing the right Data Guard protection mode, designing the redo transport correctly, and validating failover under load before the incident happened. Most organizations running Data Guard never validate their failover until disaster strikes. By then it is too late to discover the configuration was wrong.

This guide explains Oracle Data Guard protection modes in depth: what each mode actually guarantees, how redo transport works under the hood, when to use each mode, and the production design decisions that determine whether your standby database saves you or fails you at the worst possible moment.

 Table of Contents

Data Guard Architecture: How It Actually Works
The Three Protection Modes Explained
Redo Transport Deep Dive: SYNC vs ASYNC
Standby Database Types: Physical vs Logical vs Snapshot
Fast-Start Failover: Automatic DR Activation
Active Data Guard: Read-Only Standby While Applying Redo
Data Guard Broker: Centralized Configuration Management
Real Production Failover: What Actually Happens
Protection Mode Decision Framework
FAQ
Related Reading from Real Production Systems

1. Data Guard Architecture: How It Actually Works

Oracle Data Guard maintains one or more synchronized copies of a primary database called standby databases. When the primary fails, a standby can take over as the new primary — either automatically via Fast-Start Failover or manually via a DBA-initiated failover command.

The core mechanism is redo log shipping. Every change made to the primary database is recorded in redo logs. Data Guard ships those redo records to the standby in real time, where they are applied to keep the standby synchronized.

Key Data Guard Components

Component	Location	Role
LGWR / DMON	Primary	Captures redo and initiates shipping to standby
NSA / NSS Process	Primary	Network Server process — sends redo over the network
RFS Process	Standby	Remote File Server — receives redo from primary
MRP Process	Standby	Managed Recovery Process — applies redo to standby
LSP Process	Standby (logical)	LogMiner Server Process — applies SQL for logical standby
DMON Process	Both	Data Guard Monitor — manages broker configuration
Observer	Third site	Monitors primary for Fast-Start Failover quorum

SQL — Verify Data Guard Configuration Status

-- Check Data Guard status on primary
SELECT name,
       db_unique_name,
       database_role,
       protection_mode,
       protection_level,
       switchover_status,
       dataguard_broker
FROM   v$database;

-- Check redo transport status to all standbys
SELECT dest_id,
       dest_name,
       status,
       target,
       archiver,
       schedule,
       destination,
       applied_scn,
       error
FROM   v$archive_dest
WHERE  status != 'INACTIVE'
ORDER  BY dest_id;

-- Check standby apply lag
SELECT name,
       value,
       datum_time
FROM   v$dataguard_stats
WHERE  name IN ('transport lag', 'apply lag', 'apply finish time')
ORDER  BY name;

2. The Three Protection Modes Explained

Oracle Data Guard offers three protection modes. Each makes a different trade-off between data loss guarantee, performance impact, and availability. Choosing the wrong mode for your workload is one of the most common — and most dangerous — Data Guard mistakes.

Maximum Protection

Every redo record must be written to at least one standby redo log on the standby database before the primary acknowledges the transaction commit. If the standby becomes unreachable, the primary database shuts itself down rather than risk any data divergence.

Transport: SYNC — synchronous redo shipping
Affirm: AFFIRM — standby must confirm disk write
RPO: Zero — absolutely no data loss under any failure scenario
RTO: Fast — standby is fully synchronized at all times
Risk: Primary shuts down if standby is unreachable — availability depends on standby health
Use case: Regulatory compliance, core banking, financial settlement systems

Production Reality — Maximum Protection:

A core banking client ran Maximum Protection mode. During a routine standby server reboot for OS patching, the primary database shut itself down — taking the entire production system offline for 18 minutes. The team had forgotten that Maximum Protection means the primary cannot run without the standby. Always maintain a second standby or use Maximum Availability instead for most production workloads.

Maximum Availability

The default recommended mode for most production systems. Like Maximum Protection, it uses synchronous redo shipping. However, if the standby becomes unreachable, the primary automatically falls back to asynchronous mode and continues running. When the standby reconnects, it resynchronizes and the primary returns to synchronous mode automatically.

Transport: SYNC — synchronous when standby is available
Affirm: AFFIRM — confirms disk write on standby
RPO: Zero under normal conditions; seconds of exposure during fallback
RTO: Fast — standby synchronized within seconds of primary failure
Risk: Brief data loss window during the fallback period
Use case: Enterprise OLTP, e-commerce, healthcare systems

Maximum Performance

The default mode when Data Guard is first configured. Redo is shipped asynchronously — the primary does not wait for the standby to confirm receipt before acknowledging the commit. This provides the best primary performance but at the cost of a potential data loss window.

Transport: ASYNC — asynchronous redo shipping
Affirm: NOAFFIRM — no standby disk write confirmation required
RPO: Seconds to minutes depending on network latency and redo volume
RTO: Fast but standby may lag behind primary
Risk: Data loss equal to the apply lag at time of failure
Use case: Reporting standbys, development/test environments, geographically distant DR sites

SQL — Change Protection Mode (via Data Guard Broker)

-- Connect to DGMGRL as SYSDG or SYSDBA
-- dgmgrl sys/password@primary

-- Check current protection mode
SHOW CONFIGURATION;
SHOW DATABASE VERBOSE primary_db;

-- Change to Maximum Availability (recommended for most production)
EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

-- Change to Maximum Protection (zero data loss, primary shuts down without standby)
EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;

-- Change to Maximum Performance (async, best performance)
EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

-- Verify after change
SHOW CONFIGURATION;

3. Redo Transport Deep Dive: SYNC vs ASYNC

The protection mode you choose determines the redo transport mechanism. Understanding the difference between SYNC and ASYNC transport is essential for making the right design choice.

Synchronous Transport (SYNC)

With synchronous transport, the primary database Log Writer (LGWR) process sends the redo record to the standby at the same time it writes to the local online redo log. The commit does not complete until both the local write and the standby acknowledgment have been received. This introduces latency equal to the round-trip time between primary and standby.

Network Latency Rule for SYNC Transport

For synchronous redo transport, the round-trip network latency between primary and standby should be under 5 ms for OLTP workloads. Beyond 5 ms, commit latency becomes noticeable to applications. Beyond 20 ms, SYNC transport typically causes unacceptable performance degradation. In our production environment with a 120 km dark fiber link, round-trip latency is 1.8 ms — well within acceptable range for Maximum Availability mode.

Asynchronous Transport (ASYNC)

With asynchronous transport, the primary LGWR writes to the local redo log and acknowledges the commit immediately. A separate background process (NSA — Network Server Async) ships redo to the standby independently. The standby typically lags behind the primary by the amount of redo generated during the network transfer time.

SQL — Configure Redo Transport (LOG_ARCHIVE_DEST)

-- Maximum Availability: SYNC transport with AFFIRM
ALTER SYSTEM SET log_archive_dest_2 =
    'SERVICE=standby_db
      SYNC
      AFFIRM
      NET_TIMEOUT=30
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=standby_db'
SCOPE=BOTH SID='*';

-- Maximum Performance: ASYNC transport with NOAFFIRM
ALTER SYSTEM SET log_archive_dest_2 =
    'SERVICE=standby_db
      ASYNC
      NOAFFIRM
      NET_TIMEOUT=30
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=standby_db'
SCOPE=BOTH SID='*';

-- Verify redo transport configuration
SELECT dest_id,
       dest_name,
       target,
       archiver,
       net_timeout,
       affirm,
       async_blocks,
       status
FROM   v$archive_dest
WHERE  dest_id = 2;

Standby Redo Logs (SRL) — Critical Requirement

Standby Redo Logs (SRLs) are required for real-time apply and for synchronous transport. They must be the same size as the primary online redo logs and there must be at least one more SRL group per thread than the number of online redo log groups on the primary.

SQL — Add Standby Redo Logs on Standby Database

-- Check existing online redo log sizes on primary
SELECT group#,
       members,
       bytes/1024/1024  AS size_mb,
       status
FROM   v$log
ORDER  BY group#;

-- Add standby redo logs on standby (same size, one extra group per thread)
-- For a 2-node RAC primary with 3 redo groups per thread:
-- Add 4 SRL groups per thread (3 + 1 = 4)
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
    GROUP 11 ('+DATA/stdby/srl_t1_g11.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
    GROUP 12 ('+DATA/stdby/srl_t1_g12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
    GROUP 13 ('+DATA/stdby/srl_t1_g13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
    GROUP 14 ('+DATA/stdby/srl_t1_g14.log') SIZE 200M;

-- Verify SRL configuration
SELECT group#,
       thread#,
       sequence#,
       bytes/1024/1024  AS size_mb,
       archived,
       status
FROM   v$standby_log
ORDER  BY thread#, group#;

4. Standby Database Types: Physical vs Logical vs Snapshot

Type	Apply Method	Open for Reads?	Best Use Case
Physical Standby	Block-for-block redo apply (MRP)	Yes (Active Data Guard, extra license)	Primary DR target, zero data loss
Logical Standby	SQL Apply via LogMiner (LSP)	Yes (read/write, some restrictions)	Reporting, DDL testing, heterogeneous environments
Snapshot Standby	Redo buffered but not applied	Yes (full read/write)	Testing, QA, patch validation against production data

DGMGRL — Convert Physical Standby to Snapshot Standby

-- Convert to snapshot standby (opens for read/write, buffers redo)
CONVERT DATABASE standby_db TO SNAPSHOT STANDBY;

-- Verify conversion
SHOW DATABASE standby_db;

-- Convert back to physical standby (discards all changes, resumes apply)
CONVERT DATABASE standby_db TO PHYSICAL STANDBY;

5. Fast-Start Failover: Automatic DR Activation

Fast-Start Failover (FSFO) enables Oracle Data Guard to automatically fail over to the standby database without any DBA intervention when the primary becomes unavailable. This is what enabled the 23-second failover described in the introduction.

FSFO Requirements

Data Guard Broker must be enabled and configured
Observer process running on a third independent host
Protection mode must be Maximum Availability or Maximum Protection
Standby Redo Logs must be configured on the standby
Flashback Database recommended for reinstating the old primary

DGMGRL — Configure Fast-Start Failover

-- Enable Fast-Start Failover
ENABLE FAST_START FAILOVER;

-- Set failover threshold (seconds primary must be unreachable before failover)
EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 30;

-- Set lag limit (max apply lag allowed when FSFO is enabled)
EDIT CONFIGURATION SET PROPERTY FastStartFailoverLagLimit = 30;

-- Start the observer (run on third independent host)
-- dgmgrl sys/password@primary
START OBSERVER;

-- Verify FSFO configuration
SHOW FAST_START FAILOVER;
SHOW CONFIGURATION VERBOSE;

-- Simulate failover for testing (without data loss)
-- Run from observer host
FAILOVER TO standby_db;

Production FSFO Design Tip:

Always run the FSFO observer on a third independent host — not on the primary server and not on the standby server. If both the primary and the observer are on the same network segment and that segment fails, the standby may not receive quorum to activate FSFO. In our production setup, the observer runs on a small VM in a separate availability zone with network access to both primary and standby. Test FSFO every quarter by deliberately shutting down the primary during a low-traffic window.

6. Active Data Guard: Read-Only Standby While Applying Redo

Active Data Guard (ADG) allows the physical standby database to be open in read-only mode while redo apply continues in the background. This means the standby can serve reporting queries, offload read traffic from the primary, and still be ready to fail over at any moment.

Licensing Note

Active Data Guard requires the Oracle Active Data Guard option, which is a separately licensed Enterprise Edition add-on. A physical standby open in read-only mode without redo apply is available without ADG license. Real-time redo apply while open read-only requires the ADG license. Always verify licensing before enabling ADG in production.

SQL — Enable Active Data Guard (Real-Time Apply + Read-Only)

-- On standby database: open read-only with real-time apply
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
    USING CURRENT LOGFILE DISCONNECT FROM SESSION;

-- Verify ADG status
SELECT open_mode,
       database_role,
       db_unique_name
FROM   v$database;

-- Monitor apply lag on ADG standby
SELECT name,
       value,
       unit,
       time_computed
FROM   v$dataguard_stats
WHERE  name IN ('transport lag',
                'apply lag',
                'apply finish time',
                'estimated startup time')
ORDER  BY name;

-- Check active sessions using standby for reads
SELECT COUNT(*)         AS read_sessions,
       module,
       action
FROM   gv$session
WHERE  status = 'ACTIVE'
AND    type   = 'USER'
GROUP  BY module, action
ORDER  BY read_sessions DESC
FETCH FIRST 10 ROWS ONLY;

7. Data Guard Broker: Centralized Configuration Management

Data Guard Broker provides a unified interface for managing the entire Data Guard configuration. Without Broker, you manage each database individually through LOG_ARCHIVE_DEST parameters and manual commands. With Broker, you manage the entire configuration through a single DGMGRL command-line interface.

SQL & DGMGRL — Data Guard Broker Setup and Health Check

-- Enable Data Guard Broker on both primary and standby
ALTER SYSTEM SET dg_broker_start = TRUE SCOPE=BOTH SID='*';

-- Connect to broker (run on primary or standby)
-- dgmgrl /

-- Create broker configuration
CREATE CONFIGURATION dg_config
    AS PRIMARY DATABASE IS primary_db
    CONNECT IDENTIFIER IS primary_db;

-- Add standby database to configuration
ADD DATABASE standby_db
    AS CONNECT IDENTIFIER IS standby_db
    MAINTAINED AS PHYSICAL;

-- Enable the configuration
ENABLE CONFIGURATION;

-- Full health check
SHOW CONFIGURATION;
SHOW DATABASE VERBOSE primary_db;
SHOW DATABASE VERBOSE standby_db;

-- Validate configuration (checks for issues proactively)
VALIDATE DATABASE primary_db;
VALIDATE DATABASE standby_db;

DGMGRL — Switchover (Planned Role Transition)

-- Switchover: planned, zero data loss, reversible
-- Use for maintenance, patching, or DR testing

-- Verify configuration is ready for switchover
VALIDATE DATABASE primary_db;
SHOW CONFIGURATION;

-- Initiate switchover to standby
SWITCHOVER TO standby_db;

-- After switchover: verify new primary is active
SHOW CONFIGURATION;
SHOW DATABASE VERBOSE standby_db;   -- now primary
SHOW DATABASE VERBOSE primary_db;   -- now standby

-- Switchover back when maintenance is complete
SWITCHOVER TO primary_db;

8. Real Production Failover: What Actually Happens

These are real Data Guard incidents from production environments and the lessons they produced.

Incident 1: Protection Mode Mismatch Caused Silent Data Loss

A financial services client had Data Guard configured but in Maximum Performance mode (the default). They believed they had zero data loss protection. During a primary storage failure, the standby was 47 seconds behind the primary due to ASYNC transport lag. 47 seconds of financial transactions were permanently lost.

Lesson: Always verify protection mode AND transport mode with SELECT protection_mode, protection_level FROM v$database. The protection_level column shows the actual effective mode — it differs from protection_mode when the standby has fallen back to async.

Incident 2: Standby Redo Logs Not Configured — Real-Time Apply Silently Disabled

A team set up Data Guard correctly — or so they thought. Failover testing revealed the standby was 8 hours behind the primary. Investigation showed Standby Redo Logs had never been created. Without SRLs, real-time apply cannot function and the standby only catches up during log archive shipping, leaving a massive gap.

Lesson: Always verify SRLs exist and are active: SELECT * FROM v$standby_log. If this view is empty, real-time apply is not running.

Success: 23-Second Automatic Failover (The Introduction Story)

Configuration that made it work: Maximum Availability mode, SYNC/AFFIRM transport on 1.8 ms latency link, Standby Redo Logs sized identically to primary, FSFO enabled with threshold of 15 seconds, observer on independent host, Flashback Database enabled on both primary and standby, and quarterly failover testing under full production load.

Key lesson: Fast-Start Failover only works reliably when every component is validated together under real load — not just configured and assumed to work.

SQL — Data Guard Health Check Queries

-- 1. Verify effective protection mode (must match intended mode)
SELECT name,
       db_unique_name,
       database_role,
       protection_mode,    -- configured mode
       protection_level    -- actual effective mode (may differ)
FROM   v$database;

-- 2. Check transport lag and apply lag
SELECT name,
       value,
       datum_time
FROM   v$dataguard_stats
WHERE  name IN ('transport lag', 'apply lag')
ORDER  BY name;

-- 3. Verify standby redo logs exist and are active
SELECT group#,
       thread#,
       sequence#,
       bytes/1024/1024  AS size_mb,
       archived,
       status
FROM   v$standby_log
ORDER  BY thread#, group#;

-- 4. Check MRP (apply) process is running on standby
SELECT process,
       status,
       thread#,
       sequence#,
       block#,
       blocks
FROM   v$managed_standby
WHERE  process LIKE 'MRP%'
OR     process LIKE 'RFS%'
ORDER  BY process;

9. Protection Mode Decision Framework

Use this framework to choose the right protection mode for your workload.

Choose Maximum Protection if ALL of these are true:

Regulatory zero data loss is a hard legal requirement (banking, finance, government)
You have a second standby or can tolerate primary shutdown if standby fails
Network latency between primary and standby is under 2 ms
Your application can tolerate slightly higher commit latency

Choose Maximum Availability if ANY of these are true:

You need near-zero data loss but cannot accept primary shutdown risk
Network latency is under 5 ms and workload is standard OLTP
You want Fast-Start Failover with automatic activation
This is your primary DR configuration for an enterprise system

Choose Maximum Performance if ANY of these are true:

This standby is for reporting or dev/test only — not primary DR
The standby is geographically distant (WAN latency > 10 ms)
Some data loss is acceptable and primary performance is the priority
You are running a second standby in addition to a sync standby

10. FAQ

What is the difference between Switchover and Failover?

Switchover is a planned, graceful role transition. Both primary and standby participate cooperatively. There is zero data loss and the operation is fully reversible. Use switchover for planned maintenance, patching, or DR testing.

Failover is an unplanned activation of the standby when the primary is unavailable. Data loss depends on the protection mode in effect at the time. The old primary must be reinstated (using Flashback Database) before it can rejoin as a standby. Fast-Start Failover automates this process.

Does Data Guard protect against logical corruption?

No. Data Guard replicates every redo record including those caused by user errors like accidental DROP TABLE or DELETE without WHERE. The corruption is applied to the standby within seconds. For protection against logical corruption, use Flashback Database (which can rewind both primary and standby to a point before the error) or maintain a time-delayed standby using the DELAY parameter in LOG_ARCHIVE_DEST.

Can Data Guard work with Oracle RAC on the primary?

Yes. RAC primary with a single-instance physical standby is a very common and fully supported architecture. The standby receives redo from all RAC threads and applies them in order. The standby can also be a RAC database for maximum availability on both sides. This RAC + Data Guard combination is called MAA (Maximum Availability Architecture) — Oracle's reference architecture for mission-critical systems.

How often should I test failover?

At minimum, quarterly — and ideally monthly for critical systems. Test under realistic load, not during off-peak hours with zero activity. A failover that works at 2 AM with no users connected may fail at 2 PM under full load due to session cleanup issues, long-running transactions, or application reconnection pool exhaustion. Document RTO measurements from every test and track them over time.

Should I mention Data Guard on my resume?

Absolutely — with specifics. Instead of "Oracle Data Guard experience," write: "Designed and maintained Oracle Data Guard Maximum Availability configuration with Fast-Start Failover for a 6 TB core banking database. Achieved RTO of 23 seconds and RPO of zero through SYNC/AFFIRM transport on dedicated 10 GbE WAN link. Conducted quarterly failover validation under production load." Metrics and architecture decisions demonstrate real expertise.

About the Author

Chetan Yadav

Chetan Yadav is a Senior Oracle, PostgreSQL, MySQL, and Cloud DBA with 15+ years of hands-on experience managing production databases across on-premises, hybrid, and cloud environments. He specializes in high availability architecture, performance tuning, disaster recovery, and database migrations.

Throughout his career, Chetan has designed and implemented Oracle Data Guard configurations for mission-critical systems in finance, healthcare, and e-commerce sectors. He has architected zero data loss DR solutions with sub-30-second RTO targets and validated them through real failover testing under production load.

This blog focuses on real-world DBA problems, career growth, and practical learning — not theoretical documentation or vendor marketing.

Thursday, November 27, 2025

Stop Guessing: The "One-Shot" Script to Check Oracle Data Guard Health (Lags, Gaps & MRP)

Oracle Data Guard Health Check Script Dashboard Primary Standby

If you are a DBA, you know the panic of a "Quiet Standby." The alerts are silent. The phone isn't ringing. But deep down, you wonder: Is my Disaster Recovery (DR) site actually in sync, or has it been stuck on Sequence #10452 since last Tuesday?

Too many monitoring tools (like OEM or Zabbix) only trigger an alert when the lag hits a threshold (e.g., "Lag > 30 Mins"). By then, it’s often too late. You don't just want to know if there is a lag; you need to know where the lag is.

Is it the Network (Transport Lag)? Or is it the Disk/CPU (Apply Lag)?

Below is the exact script I use in my daily health checks. It consolidates 4 different dynamic performance views (v$dataguard_stats, v$managed_standby, v$archive_gap, v$database) into one single "Truth" report.

The Script (`dg_health_check.sql`)

Save this as dg_health_check.sql and run it on your Standby Database.

SQL
SET LINESIZE 200 PAGESIZE 1000 CHECK OFF FEEDBACK OFF ECHO OFF VERIFY OFF
COL name FORMAT a30
COL value FORMAT a20
COL unit FORMAT a30
COL time_computed FORMAT a25
COL process FORMAT a10
COL status FORMAT a15
COL sequence# FORMAT 99999999
COL block# FORMAT 999999
COL error_message FORMAT a50

PROMPT ========================================================
PROMPT  ORACLE DATA GUARD HEALTH CHECK (Run on Standby)
PROMPT ========================================================

PROMPT
PROMPT 1. DATABASE ROLE & PROTECTION MODE
PROMPT ----------------------------------------
SELECT name, db_unique_name, database_role, open_mode, protection_mode 
FROM v$database;

PROMPT
PROMPT 2. REAL-TIME LAG STATISTICS (The Source of Truth)
PROMPT ----------------------------------------
-- Transport Lag = Delay in receiving data (Network Issue)
-- Apply Lag     = Delay in writing data (IO/CPU Issue)
SELECT name, value, unit, time_computed 
FROM v$dataguard_stats 
WHERE name IN ('transport lag', 'apply lag', 'estimated startup time');

PROMPT
PROMPT 3. MRP (MANAGED RECOVERY PROCESS) STATUS
PROMPT ----------------------------------------
-- IF NO ROWS SELECTED: Your recovery is STOPPED.
-- Look for 'APPLYING_LOG' or 'WAIT_FOR_LOG'
SELECT process, status, thread#, sequence#, block# 
FROM v$managed_standby 
WHERE process LIKE 'MRP%';

PROMPT
PROMPT 4. GAP DETECTION
PROMPT ----------------------------------------
-- If rows appear here, you have a missing archive log that FAL_SERVER could not fetch.
SELECT * FROM v$archive_gap;

PROMPT
PROMPT 5. RECENT ERRORS (Last 10 Events)
PROMPT ----------------------------------------
SELECT TO_CHAR(timestamp, 'DD-MON-RR HH24:MI:SS') as err_time, message 
FROM v$dataguard_status 
WHERE severity IN ('Error','Fatal') 
AND timestamp > sysdate-1
ORDER BY timestamp DESC
FETCH FIRST 10 ROWS ONLY;

PROMPT ========================================================
PROMPT  END OF REPORT
PROMPT ========================================================

How to Analyze the Output (Like a Senior DBA)

Scenario A: High Transport Lag

What you see: Transport Lag is high (e.g., +00 01:20:00), but Apply Lag is low.
What it means: Your Primary database is generating Redo faster than your network can ship it.
The Fix: Check your network bandwidth. If you are using Oracle 19c or 23ai, consider enabling Redo Compression in your Data Guard broker configuration (EditDatabase Set Property RedoCompression='ENABLE').

Scenario B: High Apply Lag

What you see: Transport Lag is near 0, but Apply Lag is climbing (e.g., +00 00:45:00).
What it means: The data is there (on the standby server), but the database can't write it to disk fast enough. This often happens during batch loads or index rebuilds on the Primary.
The Fix: Check I/O stats on the Standby. Ensure you are using Real-Time Apply so the MRP (Managed Recovery Process) reads directly from Standby Redo Logs (SRLs) rather than waiting for archive logs to be finalized.

Scenario C: MRP Status is "WAIT_FOR_GAP"

What you see: In Section 3, the status is WAIT_FOR_GAP.
What it means: A severe gap has occurred. The Standby is missing a specific sequence number and cannot proceed until you manually register that file.
The Fix: Run the query in Section 4 (v$archive_gap) to identify the missing sequence, restore it from backup, and register it.

Why this works in 2026

Old school scripts relied on v$archived_log, which only tells you history. In modern Oracle Cloud (OCI) and Hybrid environments, v$dataguard_stats is the only view that accurately calculates the time difference between the Primary commit and the Standby visibility.

Chetan Yadav is a Senior Oracle, PostgreSQL, MySQL & Cloud DBA with 14+ years of experience supporting high-traffic production environments across AWS, Azure, and on-premise systems. His core expertise includes Oracle RAC, ASM, Data Guard, performance tuning, HA/DR design, monitoring frameworks, and real-world troubleshooting.

He also trains DBAs globally through deep-dive technical content, hands-on sessions, and automation workflows using n8n, AI tools, and modern monitoring stacks. His mission is to help DBAs solve real production problems and grow into high-paying remote roles worldwide.

Chetan regularly publishes expert content across Oracle, PostgreSQL, MySQL, and Cloud DBA technologies—including performance tuning guides, DR architectures, monitoring tools, scripts, and real incident-based case studies.

Explore More Technical Work

LinkedIn (Professional Profile & Articles)
https://www.linkedin.com/in/chetanyadavvds/

YouTube – Oracle Foundations Playlist
https://www.youtube.com/playlist?list=PL5TN6ECUWGROHQGXep_5hff-2ageWTp4b

Telegram – LevelUp_Careers DBA Tips
https://t.me/LevelUp_Careers

Instagram – Oracle/Cloud Learning Reels
https://www.instagram.com/levelup_careers/

Facebook Page – OracleDBAInfo
https://www.facebook.com/OracleDBAInfo

These platforms feature guides, scripts, diagrams, troubleshooting workflows, and real-world DBA case studies designed for database professionals worldwide.

Wednesday, April 26, 2023

Step-by-step guide with commands for configuring Oracle 19c Data Guard Physical Standby

Step 1: Install Oracle 19c software on both primary and standby servers.

Download the Oracle Database software for Oracle 19c from the Oracle website.
Follow the installation instructions for your operating system to install the Oracle Database software on both the primary and standby servers.

Step 2: Create a standby database on the standby server.

On the standby server, create an empty Oracle database using the Database Configuration Assistant (DBCA) or manually with SQL scripts.
Example SQL script to create a standby database:

sql
CREATE DATABASE <database_name>
USER SYS IDENTIFIED BY <sys_password>
USER SYSTEM IDENTIFIED BY <system_password>
LOGFILE GROUP 1 ('<log_file_directory>/redo01.log') SIZE 100M,
GROUP 2 ('<log_file_directory>/redo02.log') SIZE 100M,
GROUP 3 ('<log_file_directory>/redo03.log') SIZE 100M;

Step 3: Enable archivelog mode on the primary database.

Connect to the primary database with a privileged user.
Enable archivelog mode using the following SQL command:

sql
ALTER DATABASE ARCHIVELOG;

Step 4: Configure log shipping on the primary database.

Connect to the primary database with a privileged user.
Configure log shipping to automatically transfer archived redo logs to the standby server using the following SQL command:

sql
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='<standby_server_directory>' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

Step 5: Create standby redo logs on the standby database.

Connect to the standby database with a privileged user.
Create standby redo logs using the following SQL command:

sql
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('<standby_redo_log_directory>/standby_redo01.log') SIZE 100M,
GROUP 5 ('<standby_redo_log_directory>/standby_redo02.log') SIZE 100M,
GROUP 6 ('<standby_redo_log_directory>/standby_redo03.log') SIZE 100M;

Step 6: Configure Data Guard properties on the primary database.

Connect to the primary database with a privileged user.
Configure Data Guard properties using the following SQL command:

sql
ALTER SYSTEM SET DB_UNIQUE_NAME='<primary_db_unique_name>' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='<standby_server_directory>' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(<primary_db_unique_name>,<standby_db_unique_name>)' SCOPE=BOTH;

Step 7: Start redo log apply on the standby database.

Connect to the standby database with a privileged user.
Start redo log apply using the following SQL command:

sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step 8: Monitor Data Guard configuration.

Monitor the Data Guard configuration using Oracle Database utilities, such as Data Guard Broker, Enterprise Manager, or SQL queries, to check the status and performance of the primary and standby databases.

Step 9: Test failover and switchover scenarios.

Perform failover and switchover scenarios to test the Data Guard configuration and ensure its reliability, following Oracle's documentation and best practices.

That's it! You have successfully configured Oracle 19c Data Guard Physical Standby using the above commands. Remember to customize the commands according to your specific environment and requirements, and refer to Oracle's documentation for detailed guidance.

Looking forward to your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Pages

Thursday, March 12, 2026

Oracle Data Guard Protection Modes: Zero Data Loss Design Strategy

Oracle Data Guard Protection Modes: Zero Data Loss Design Strategy

1. Data Guard Architecture: How It Actually Works

Key Data Guard Components

2. The Three Protection Modes Explained

Maximum Protection

Maximum Availability

Maximum Performance

3. Redo Transport Deep Dive: SYNC vs ASYNC

Synchronous Transport (SYNC)

Asynchronous Transport (ASYNC)

Standby Redo Logs (SRL) — Critical Requirement

4. Standby Database Types: Physical vs Logical vs Snapshot

5. Fast-Start Failover: Automatic DR Activation

FSFO Requirements

6. Active Data Guard: Read-Only Standby While Applying Redo

7. Data Guard Broker: Centralized Configuration Management

8. Real Production Failover: What Actually Happens

9. Protection Mode Decision Framework

Choose Maximum Protection if ALL of these are true:

Choose Maximum Availability if ANY of these are true:

Choose Maximum Performance if ANY of these are true:

10. FAQ

11. Related Reading from Real Production Systems

About the Author

Thursday, November 27, 2025

Stop Guessing: The "One-Shot" Script to Check Oracle Data Guard Health (Lags, Gaps & MRP)

The Script (dg_health_check.sql)

How to Analyze the Output (Like a Senior DBA)

Scenario A: High Transport Lag

Scenario B: High Apply Lag

Scenario C: MRP Status is "WAIT_FOR_GAP"

Why this works in 2026

Explore More Technical Work

Wednesday, April 26, 2023

Step-by-step guide with commands for configuring Oracle 19c Data Guard Physical Standby

The Script (`dg_health_check.sql`)