Oracle Data Guard Interview Questions – Complete DBA Guide

Quick Summary

Oracle Data Guard is Oracle’s disaster recovery (DR) and high availability (HA) solution that maintains one or more standby databases as synchronized copies of a primary database.
It protects databases from data loss, downtime, and site failures, while also enabling read-only workloads on standby systems.

1. DATA GUARD FUNDAMENTALS

What is Oracle Data Guard?

Definition:
Oracle Data Guard is Oracle’s disaster recovery solution that creates, manages, and maintains one or more standby databases to protect the primary database against failures.

Simple Explanation (Interview-Friendly):
👉 Think of Data Guard like a backup office building.
If your main office catches fire, employees immediately move to the backup office and continue working.
Similarly, if the primary database fails, Data Guard allows you to switch to the standby database with minimal or no downtime.

Core Purpose of Data Guard

✔ High Availability (HA) – Minimize downtime
✔ Disaster Recovery (DR) – Protect against site-level failures
✔ Data Protection – Prevent data loss
✔ Offload Operations – Use standby for:

• Reporting
• Backups
• Read-only queries

Primary vs Standby Database

Primary Database

• Production database
• Handles all read/write transactions
• Generates redo data
• Acts as the source of truth

Standby Database

• Exact copy of the primary database
• Receives redo data from the primary
• Applies redo to stay synchronized
• Can be located in a different geographic region
• Takes over when the primary fails

Primary–Standby Relationship (Flow Diagram)

PRIMARY DATABASE (Production Site)
        ↓  (Redo Transport)
STANDBY DATABASE (DR Site)
        ↓  (Redo Apply)
Up-to-date copy ready for failover

Interview Tip 💡

One-liner you can use in interviews:
“Oracle Data Guard ensures business continuity by maintaining synchronized standby databases that can quickly take over when the primary database fails.”

2. TYPES OF STANDBY DATABASES

A. Physical Standby Database

(Most Common and Most Preferred in Production)

What it is

• Exact block-by-block copy of the primary database
• Same data files, control files, and structure
• Maintained using Redo Apply (media recovery)

How it works

Primary DB
 → Generates Redo
 → Sends Redo to Standby
 → MRP (Media Recovery Process) applies redo
 → Physical Standby stays synchronized

Key Characteristics

• Exact physical replica of primary
• Can be opened READ ONLY while redo is applying (Active Data Guard)
• Fastest recovery option
• Most reliable for disaster recovery

Use Cases

• Disaster recovery
• High availability
• Patch and upgrade testing using standby
• Offloading backups to standby

Advantages

• No data divergence (exact copy)
• Fastest switchover and failover
• Patches can be tested on standby first
• Backup and reporting workload can be offloaded

Limitations

• Cannot be opened READ WRITE
• Requires same OS and platform
• Read-only access during redo apply requires Active Data Guard license

Interview One-Liner

“A physical standby is a block-level replica of the primary database that uses redo apply and provides the fastest and safest disaster recovery option.”

B. Logical Standby Database

What it is

• Logical copy of the primary database
• Same logical data, but structure can differ
• Uses SQL Apply instead of redo apply

How it works

Primary DB
 → Generates Redo
 → Redo sent to Standby
 → LogMiner converts redo to SQL
 → SQL Apply executes statements
 → Logical Standby updated

Key Characteristics

• Can be opened READ WRITE while applying redo
• Can contain additional tables, indexes, schemas
• Can run on different OS (same platform family)
• More flexible but more complex

Use Cases

• Reporting database with write access
• Testing environment
• Data subsetting
• Rolling database upgrades

Advantages

• Open READ WRITE while receiving redo
• Supports additional objects like indexes and materialized views
• More flexibility compared to physical standby

Limitations

• Slower than physical standby
• Not all data types supported (e.g., LONG, BFILE)
• SQL Apply adds overhead
• More complex administration and troubleshooting

Interview One-Liner

“A logical standby applies redo as SQL statements, allowing read-write access and schema flexibility, but with higher complexity and lower performance.”

C. Snapshot Standby Database

What it is

• A temporary, fully READ WRITE copy of a physical standby
• Primarily used for testing purposes
• Can be reverted back to a physical standby at any time
• All changes are discarded when converted back

How it works

Physical Standby
 → Convert to Snapshot Standby
 → Fully READ WRITE (Testing Phase)
 → Convert back to Physical Standby
 → All test changes discarded

Key Characteristics

• Fully updateable (READ WRITE)
• Uses Flashback Database to revert changes
• Redo is received but NOT applied (redo is queued)
• When converted back, queued redo is applied to resynchronize

Use Cases

• Testing patches before applying to primary
• Testing application changes
• UAT (User Acceptance Testing)
• Performing “what-if” scenarios safely

Conversion Commands

Convert Physical Standby to Snapshot Standby

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;

Convert Snapshot Standby back to Physical Standby

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Advantages

• Full READ WRITE access for testing
• Simple and fast conversion back to physical standby
• No impact on primary database
• Ideal for patch and upgrade testing

Limitations

• Not a disaster recovery solution (changes are discarded)
• Redo apply is stopped, redo gap can increase
• Flashback Database must be enabled

Interview One-Liner

“A snapshot standby is a temporary read-write version of a physical standby used for testing, which can be safely reverted back using flashback.”

Quick Comparison of Standby Types

3. DATA GUARD ARCHITECTURE

Overall Architecture Flow

┌─────────────────────────────────────────────────────┐
│                PRIMARY DATABASE                     │
│  ┌──────────┐    ┌──────────┐    ┌──────────┐     │
│  │  LGWR    │───→│  ARCH    │───→│  Redo    │     │
│  │          │    │          │    │  Logs    │     │
│  └──────────┘    └──────────┘    └──────────┘     │
│        ↓ (Redo Transport Services)                  │
└─────────────────────────────────────────────────────┘
                         ↓
              ┌──────────────────────┐
              │       NETWORK        │
              └──────────────────────┘
                         ↓
┌─────────────────────────────────────────────────────┐
│                STANDBY DATABASE                     │
│  ┌──────────┐    ┌──────────┐    ┌──────────┐     │
│  │  RFS     │───→│  Standby │───→│  MRP/LSP │     │
│  │ (Receive)│    │  Redo    │    │  (Apply) │     │
│  └──────────┘    └──────────┘    └──────────┘     │
└─────────────────────────────────────────────────────┘

Key Processes in Data Guard

Processes on the Primary Database

1. LGWR (Log Writer)

• Writes redo entries to online redo logs
• Sends redo directly to standby in SYNC (real-time) mode
• Essential for zero data loss configurations

Interview Point:
In SYNC mode, a commit waits until redo is written on standby.

2. ARCH (Archiver)

• Archives filled online redo logs
• Sends archived redo logs to standby in ASYNC mode
• Helps resolve gaps if real-time redo shipping fails

Interview Point:
ARCH is mainly used when LGWR is not involved in redo transport.

3. LNS (Log Network Server)

• Network process responsible for redo transmission
• Sends redo data from primary to standby
• One LNS process per standby destination

Interview Point:
LNS works with LGWR or ARCH depending on transport mode.

4. FAL (Fetch Archive Log) Client

• Detects redo gaps on standby
• Requests missing archived logs
• Ensures standby stays synchronized

Processes on the Standby Database

1. RFS (Remote File Server)

• Receives redo data from primary
• Writes redo into:
  • Standby Redo Logs (SRL), or
  • Archived redo logs
• One RFS process per primary connection

2. MRP (Managed Recovery Process)

(Physical Standby Only)

• Applies redo to physical standby database
• Performs media recovery
• Supports real-time apply

Interview Point:
MRP continuously applies redo to keep physical standby in sync.

3. LSP (Logical Standby Process)

(Logical Standby Only)

• Uses LogMiner to mine redo logs
• Converts redo into SQL statements
• Applies SQL to logical standby database

4. FAL (Fetch Archive Log) Server

• Responds to gap requests from primary or standby
• Sends missing archived redo logs
• Works with FAL client for gap resolution

Interview One-Liner

“Data Guard architecture relies on redo transport from primary to standby using LGWR or ARCH, received by RFS and applied using MRP or LSP depending on the standby type.”

4. REDO TRANSPORT MODES

Redo transport mode defines how and when redo data is transmitted from the primary database to the standby database and how it affects data loss and performance.

A. SYNC (Synchronous Mode)

(Used with Maximum Protection / Maximum Availability)

How it works

1. User commits a transaction on primary
2. LGWR writes redo to local online redo log
3. LGWR sends redo to standby
4. Primary waits for standby acknowledgment
5. Commit completes only after acknowledgment

Configuration Example

ALTER SYSTEM SET log_archive_dest_2=
'SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30';

Key Characteristics

• Zero data loss guaranteed
• Primary waits for standby confirmation
• If standby is unavailable:
  • Primary may hang or shut down (Maximum Protection)
• Network latency directly impacts performance

Use When

• Zero data loss is mandatory
• Standby is located nearby (low latency network)
• RPO = 0

Interview One-Liner

“SYNC mode guarantees zero data loss by waiting for redo confirmation from standby before committing transactions.”

B. ASYNC (Asynchronous Mode)

(Maximum Performance – Most Common in Production)

How it works

1. User commits a transaction on primary
2. LGWR writes redo locally
3. Commit completes immediately
4. ARCH/LNS sends redo to standby in background
5. Primary does not wait for acknowledgment

Configuration Example

ALTER SYSTEM SET log_archive_dest_2=
'SERVICE=standby ASYNC NOAFFIRM';

Key Characteristics

• Minimal or no performance impact on primary
• Small data loss possible (redo in transit)
• Primary continues even if standby is unavailable
• Network issues do not affect commit performance

Use When

• Performance is critical
• Small data loss is acceptable (seconds or minutes)
• Standby is geographically distant
• RPO > 0

Interview One-Liner

“ASYNC mode prioritizes performance by not waiting for standby acknowledgment, allowing minimal data loss.”

C. FASTSYNC (Fast Sync Mode)

(Introduced in 19c – Uses Far Sync)

What it is

• Hybrid of SYNC and ASYNC
• Uses a Far Sync standby instance
• Achieves zero data loss with lower latency

How it works

PRIMARY (SYNC)
      ↓
FAR SYNC (ASYNC)
      ↓
STANDBY

Configuration Example

Primary to Far Sync (SYNC)

ALTER SYSTEM SET log_archive_dest_2=
'SERVICE=farsync SYNC AFFIRM';

Far Sync to Standby (ASYNC)

ALTER SYSTEM SET log_archive_dest_2=
'SERVICE=standby ASYNC';

Key Characteristics

• Far Sync is lightweight (no data files, only redo)
• Primary commits quickly (Far Sync is nearby)
• Zero data loss achieved
• Standby can be geographically distant

Interview One-Liner

“Fast Sync uses a Far Sync instance to provide zero data loss without impacting primary performance, even with distant standbys.”

Redo Transport Mode Comparison

5. DATA GUARD BROKER

What is Data Guard Broker?

Definition:
Data Guard Broker is a management and automation framework that simplifies configuration, monitoring, and administration of Oracle Data Guard environments.

Without Data Guard Broker (Manual Management)

• Failover and switchover require multiple manual SQL commands
• Monitoring is manual
• Configuration changes are complex
• Higher risk of human error
• Time-consuming during outages

With Data Guard Broker

DGMGRL> SWITCHOVER TO standby_db;

A single command performs:

• Role transition
• Redo apply coordination
• Service and state validation

Benefits of Data Guard Broker

• Automated switchover and failover
• Centralized monitoring and control
• Fast-Start Failover (FSFO) support
• Simplified configuration management
• Built-in health checks and validation
• GUI integration with Enterprise Manager

Interview One-Liner

“Data Guard Broker automates and simplifies Data Guard operations, reducing manual effort and human error during role transitions.”

Key Data Guard Broker Concepts

1. Broker Configuration

• A named collection of:
  • One primary database
  • One or more standby databases
• Configuration metadata stored in binary files
• Files exist on each database server
• Managed using DGMGRL or Enterprise Manager

2. Fast-Start Failover (FSFO)

• Enables automatic failover without DBA intervention
• Triggered when primary becomes unreachable
• Uses a time threshold (for example, 30 seconds)
• Prevents prolonged outages

Failover Logic:

Primary unreachable > Threshold
 → FSFO triggered
 → Standby promoted automatically

3. Observer

• Lightweight monitoring process
• Continuously checks primary and standby health
• Initiates automatic failover under FSFO
• Must run on a separate server
• Not installed on primary or standby database servers

Interview One-Liner

“Fast-Start Failover uses an observer process to automatically promote a standby when the primary is unreachable beyond a configured threshold.”

Common Data Guard Broker Commands

Connect to Data Guard Broker

DGMGRL> CONNECT sys/password

Show Broker Configuration

DGMGRL> SHOW CONFIGURATION;

Show Database Details

DGMGRL> SHOW DATABASE primary_db;
DGMGRL> SHOW DATABASE standby_db;

Perform Switchover

DGMGRL> SWITCHOVER TO standby_db;

Perform Failover

DGMGRL> FAILOVER TO standby_db;

Enable Fast-Start Failover

DGMGRL> ENABLE FAST_START FAILOVER;

Start Observer

DGMGRL> START OBSERVER;

Check Redo Lag

DGMGRL> SHOW DATABASE standby_db 'ApplyLag';
DGMGRL> SHOW DATABASE standby_db 'TransportLag';

Interview Tip

Broker commands abstract complex internal steps into a single validated operation, making them safer during outages.

6. SWITCHOVER vs FAILOVER

Switchover (Planned Role Reversal)

What it is

• Planned role reversal
• Primary becomes standby
• Standby becomes primary
• Zero data loss
• Fully reversible

When to Use

• Planned maintenance on primary
• Testing disaster recovery procedures
• Data center migration
• Patch or upgrade testing

Switchover Process

1. Verify standby is fully synchronized
2. Convert primary to standby role
3. Convert standby to primary role
4. Redirect applications to new primary

Command (Using Broker)

DGMGRL> SWITCHOVER TO standby_db;

Key Characteristics

• No data loss
• Graceful role transition
• Easy to switch back
• Brief application downtime (seconds to minutes)

Interview One-Liner

“Switchover is a planned, zero–data-loss role reversal used for maintenance or testing.”

Failover (Unplanned Emergency)

What it is

• Unplanned emergency operation
• Primary database is unavailable
• Standby is promoted to primary
• Possible data loss (depends on redo mode)
• Not easily reversible

When to Use

• Primary database crash
• Primary site disaster
• Complete system failure
• Network isolation of primary

Failover Process

1. Confirm primary is truly down
2. Promote standby to primary
3. Redirect applications
4. Rebuild old primary later as new standby

Command (Using Broker)

DGMGRL> FAILOVER TO standby_db;

Key Characteristics

• Used only in emergencies
• Possible data loss (redo in transit)
• Old primary must be rebuilt
• More complex recovery process

Interview One-Liner

“Failover is an emergency operation that promotes a standby when the primary is lost, potentially with some data loss.”

Switchover vs Failover Comparison

7. MONITORING DATA GUARD

Effective monitoring ensures the standby database is synchronized, redo is flowing, and recovery processes are healthy.

Key Dynamic Performance Views

Monitoring on Primary Database

Check Archive Destinations

SELECT dest_id, status, destination, error
FROM v$archive_dest
WHERE status != 'INACTIVE';

• Verifies redo destinations
• Quickly identifies transport errors

Check Log Transport Status

SELECT dest_id, status, type,
       database_mode, recovery_mode
FROM v$archive_dest_status;

• Confirms whether redo transport is working
• Shows transport and recovery modes

Check Archive Gap

SELECT * FROM v$archive_gap;

• Detects missing archived logs on standby

Monitoring on Standby Database

Check Database Role and Open Mode

SELECT database_role, open_mode
FROM v$database;

• Confirms standby role
• Validates READ ONLY or MOUNT mode

Check Apply and Transport Lag

SELECT name, value, unit, time_computed
FROM v$dataguard_stats
WHERE name IN ('apply lag', 'transport lag');

• Most important health indicators

Check MRP Status (Physical Standby)

SELECT process, status, thread#, sequence#, block#
FROM v$managed_standby
WHERE process = 'MRP0';

• Confirms redo is actively being applied

Check Archive Gap

SELECT * FROM v$archive_gap;

• Ensures no redo gaps exist

Check Last Applied Archived Log

SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
WHERE applied = 'YES'
ORDER BY sequence# DESC;

• Verifies how current the standby is

Important Data Guard Metrics

1. Transport Lag

• Time difference between redo generated on primary and received on standby
• Expected value: seconds

2. Apply Lag

• Time difference between redo received and applied on standby
• Expected value: seconds to minutes

3. Archive Gap

• Missing archived redo log sequence
• Must be resolved immediately

Single Command to Check Overall Lag

SELECT name, value, datum_time, time_computed
FROM v$dataguard_stats;

Health Interpretation (Interview Focus)

Good Health

apply lag      : 0 seconds
transport lag  : 0 seconds

Problem Scenario

apply lag      : 2 hours     → MRP slow or stopped
transport lag  : 30 minutes → Network or transport issue

Interview One-Liner

“Transport lag shows redo delivery delay, while apply lag shows recovery delay; both should normally be near zero in a healthy Data Guard setup.”

8. COMMON ISSUES AND TROUBLESHOOTING

This section covers frequently asked interview scenarios and real-world Data Guard issues with clear symptoms, causes, and solutions.

Issue 1: Archive Gap

Symptoms

SELECT * FROM v$archive_gap;

• Query returns missing redo sequence numbers

Common Causes

• Network interruption
• Standby database down during log switch
• Archive destination full on primary or standby

Solutions

Manual Resolution

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/path/to/archive.arc';

• Copy missing archived logs from primary to standby
• Register them manually

Automatic Resolution

• Ensure FAL client and FAL server are properly configured
• FAL automatically requests missing logs

Interview One-Liner

“An archive gap means redo logs are missing on standby and must be resolved immediately to resume recovery.”

Issue 2: MRP Not Running

Symptoms

SELECT process
FROM v$managed_standby
WHERE process = 'MRP0';

• No rows returned

Common Causes

• MRP stopped manually
• Error during redo apply
• Standby database not in correct state (MOUNT / READ ONLY)

Solutions

Start Managed Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Start Real-Time Apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;

Interview One-Liner

“If MRP is not running, redo apply stops and the standby falls behind the primary.”

Issue 3: High Apply Lag

Symptoms

SELECT value
FROM v$dataguard_stats
WHERE name = 'apply lag';

• Lag shows minutes or hours instead of seconds

Common Causes

• MRP is slow or stalled
• Heavy DML activity on primary
• Insufficient CPU, I/O, or memory on standby
• Delayed apply configured intentionally

Solutions

Check Recovery Progress

SELECT * FROM v$recovery_progress;

Enable Parallel Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
PARALLEL 4 DISCONNECT;

Additional Checks

• Review standby alert log
• Check system resource utilization

Interview One-Liner

“High apply lag usually indicates recovery slowness or resource constraints on the standby.”

Issue 4: Standby Redo Logs Missing

Symptoms

• Real-time apply not working
• Apply lag increasing

Cause

• Standby Redo Logs (SRL) not configured

Solution

Add Standby Redo Logs

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('/path/srl_11.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/path/srl_12.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/path/srl_13.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/path/srl_14.log') SIZE 512M;

Sizing Rule

• Same size as primary redo logs
• (Number of online redo log groups + 1) per thread

Interview One-Liner

“Standby redo logs are mandatory for real-time apply and should always be configured in Data Guard.”

Issue 5: Switchover Fails

Symptoms

DGMGRL> SWITCHOVER TO standby_db;
ORA-16525: database not ready for switchover

Common Causes

• Standby not fully synchronized
• Archive gap exists
• Standby not in correct state

Solutions

Check Switchover Status

SELECT database_role, switchover_status
FROM v$database;

Expected Values

• Primary: TO STANDBY or SESSIONS ACTIVE
• Standby: TO PRIMARY

Resolve Archive Gaps

SELECT * FROM v$archive_gap;

Ensure MRP Is Running

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Interview One-Liner

“A switchover fails if the standby is not synchronized or redo apply is not active.”

9. INTERVIEW QUESTIONS

Q1: What is Oracle Data Guard?

Answer:
Oracle Data Guard is a disaster recovery and high availability solution that maintains one or more standby databases as synchronized copies of a primary database.
It protects against data loss and downtime by shipping and applying redo data from primary to standby.
If the primary database fails, a standby can take over quickly with minimal downtime and minimal or zero data loss.

Q2: Difference between Physical and Logical Standby?

Answer:

Physical Standby

• Block-by-block exact copy of the primary
• Uses Redo Apply (MRP – media recovery)
• Cannot be opened READ WRITE
• Fastest and most commonly used
• Best choice for disaster recovery

Logical Standby

• Logical copy of primary database
• Uses SQL Apply (redo converted to SQL)
• Can be opened READ WRITE
• More flexible but slower
• Commonly used for reporting while receiving redo

Q3: What is the difference between SYNC and ASYNC redo transport?

Answer:

SYNC (Synchronous)

• Primary waits for standby acknowledgment before commit
• Guarantees zero data loss (RPO = 0)
• Has performance impact on primary
• Primary may hang if standby is unavailable

ASYNC (Asynchronous)

• Primary does not wait for standby
• Commit completes immediately
• Minimal performance impact
• Possible data loss if primary fails while redo is in transit
• Most common mode in production

Selection depends on:
Distance between sites, network latency, and RPO requirements.

Q4: Explain Switchover vs Failover.

Answer:

Switchover

• Planned role reversal
• Zero data loss
• Primary becomes standby and standby becomes primary
• Fully reversible
• Used for maintenance and testing
• Downtime: seconds to a few minutes

Failover

• Unplanned emergency operation
• Primary is unavailable
• Standby is promoted to primary
• Possible data loss (depends on redo mode)
• Old primary must be rebuilt
• Used during disasters

Q5: What is Data Guard Broker?

Answer:
Data Guard Broker is a management and automation framework that simplifies Data Guard administration. It provides:

• Automated switchover and failover using a single command
• Fast-Start Failover (automatic failover without DBA intervention)
• Centralized monitoring and configuration
• Built-in health checks and validation
• Integration with Enterprise Manager
• Reduced risk of human error during DR operations

Without Broker, Data Guard operations must be performed manually using multiple SQL commands.

9. INTERVIEW QUESTIONS (Continued)

Q6: What is Fast-Start Failover (FSFO)?

Answer:
Fast-Start Failover (FSFO) is an automatic failover feature of Data Guard Broker that performs a failover without DBA intervention when the primary database becomes unavailable.

Key Components

• Observer – Monitors health of primary and standby
• Threshold – Time to wait before failover (for example, 30 seconds)
• Failover Target – Pre-configured standby database

How FSFO Works

1. Observer detects that the primary is unreachable
2. Observer waits for the configured threshold
3. Automatic failover to the standby occurs
4. Applications reconnect to the new primary

Requirements

• Data Guard Broker configured
• Observer process running
• FSFO enabled
• Flashback enabled (for reinstating old primary)

Interview One-Liner

“FSFO enables automatic, zero-touch failover using an observer when the primary is unavailable beyond a configured threshold.”

Q7: How do you check apply lag on standby?

Answer:

Method 1: Using V$DATAGUARD_STATS

SELECT name, value, unit, time_computed
FROM v$dataguard_stats
WHERE name IN ('apply lag', 'transport lag');

Method 2: Using V$ARCHIVED_LOG

SELECT ARCH.thread#, ARCH.sequence#, ARCH.first_time,
       APPL.sequence#, APPL.first_time,
       (ARCH.sequence# - APPL.sequence#) gap
FROM (SELECT thread#, MAX(sequence#) sequence#, MAX(first_time) first_time
      FROM v$archived_log
      WHERE archived = 'YES'
      GROUP BY thread#) ARCH,
     (SELECT thread#, MAX(sequence#) sequence#, MAX(first_time) first_time
      FROM v$archived_log
      WHERE applied = 'YES'
      GROUP BY thread#) APPL
WHERE ARCH.thread# = APPL.thread#;

Method 3: Check MRP Progress

SELECT process, status, thread#, sequence#
FROM v$managed_standby
WHERE process LIKE 'MRP%';

Health Interpretation

• Good: Apply lag = 0 seconds, Transport lag = 0–2 seconds
• Problem: Apply lag > 5 minutes requires investigation

Q8: What is an archive gap and how do you resolve it?

Answer:
An archive gap occurs when the standby database is missing one or more archived redo log files in sequence.

Check for Archive Gap

SELECT * FROM v$archive_gap;

Common Causes

• Network interruption
• Standby database was down
• Archive destination full on primary

Resolution

Automatic (Recommended – FAL Configured)

• FAL client on standby requests missing logs
• FAL server on primary sends the logs

Manual Resolution

scp /arch/1_234_123456.arc standby:/arch/

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/arch/1_234_123456.arc';

• MRP automatically applies the registered logs

Prevention

• Reliable network
• Adequate archive destination space
• Proper FAL configuration

Q9: How do you perform a manual switchover?

Answer:

Using Data Guard Broker (Recommended)

DGMGRL> CONNECT sys/password
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SWITCHOVER TO standby_db;

Manual Switchover (Without Broker)

Step 1: On Primary

SELECT database_role, switchover_status FROM v$database;

• Status should be TO STANDBY

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

Step 2: On Standby

SELECT database_role, switchover_status FROM v$database;

• Status should be TO PRIMARY or SESSIONS ACTIVE

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP;

Step 3: On Old Primary (Now Standby)

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Step 4: Verification

SELECT database_role, open_mode FROM v$database;

Q10: What are Standby Redo Logs (SRL) and why are they needed?

Answer:
Standby Redo Logs (SRL) are online redo logs on the standby database that receive redo directly from the primary’s LGWR in real time.

Purpose

• Enable real-time apply
• Reduce apply lag to near zero
• Required for zero data loss modes
• Mandatory for Fast-Start Failover

Redo Flow Comparison

Without SRL

Primary LGWR → Archive → Network → Standby Archive → MRP
(Lag = minutes)

With SRL

Primary LGWR → Network → Standby SRL → MRP
(Lag = seconds)

Configuration

• Same size as primary redo logs
• (Number of primary redo log groups + 1) per thread

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 11 ('/u01/standby/srl_11.log') SIZE 512M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 12 ('/u01/standby/srl_12.log') SIZE 512M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 13 ('/u01/standby/srl_13.log') SIZE 512M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 14 ('/u01/standby/srl_14.log') SIZE 512M;

Verification

SELECT group#, thread#, bytes/1024/1024 mb
FROM v$standby_log;

Best Practice

Always configure Standby Redo Logs on physical standbys, even if not using real-time apply initially.

Q11: What is Active Data Guard?

Answer:
Active Data Guard is a licensed Oracle feature that allows a physical standby database to be opened READ ONLY while continuously applying redo from the primary database.

Without Active Data Guard

• Physical standby must be MOUNTED to apply redo
• Standby cannot be queried while redo is applying
• No read-only workload possible

With Active Data Guard

• Standby is OPEN READ ONLY
• MRP applies redo in real time
• Users can run queries and reports
• Read-only workload is offloaded from primary

Use Cases

• Reporting and analytics on standby
• Running backups from standby
• Testing queries on production-like data
• DR testing without impacting production

Enable Active Data Guard (on Standby)

SHUTDOWN IMMEDIATE;
STARTUP;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;

Verification

SELECT database_role, open_mode
FROM v$database;

Expected:

• DATABASE_ROLE = PHYSICAL STANDBY
• OPEN_MODE = READ ONLY WITH APPLY

Licensing

• Requires a separate Active Data Guard license
• In addition to Oracle Enterprise Edition

Interview One-Liner

“Active Data Guard allows real-time redo apply on an open read-only physical standby, enabling reporting without impacting the primary.”

Q12: How do you monitor Data Guard lag?

Answer:

Check Transport Lag

SELECT name, value
FROM v$dataguard_stats
WHERE name = 'transport lag';

Check Apply Lag

SELECT name, value
FROM v$dataguard_stats
WHERE name = 'apply lag';

Detailed Gap Analysis

SELECT thread#,
       MAX(sequence#) FILTER (WHERE archived = 'YES') archived_seq,
       MAX(sequence#) FILTER (WHERE applied = 'YES') applied_seq,
       MAX(sequence#) FILTER (WHERE archived = 'YES') -
       MAX(sequence#) FILTER (WHERE applied = 'YES') gap
FROM v$archived_log
GROUP BY thread#;

Check MRP Performance

SELECT * FROM v$recovery_progress;

Using Data Guard Broker

DGMGRL> SHOW DATABASE standby_db 'ApplyLag';
DGMGRL> SHOW DATABASE standby_db 'TransportLag';

Alert Thresholds

• Transport lag > 30 seconds → Network issue
• Apply lag > 5 minutes → MRP slow or stopped
• Gap > 10 archives → Critical, investigate immediately

Q13: What happens if the primary crashes before sending all redo to standby?

Answer:

ASYNC Mode (Most Common)

• Redo in transit is lost
• Data loss equals redo not yet shipped
• Typically seconds to minutes
• RPO > 0

SYNC Mode

• No redo is lost
• Primary waits for standby acknowledgment
• Zero data loss guaranteed
• RPO = 0

After Failover

Redo lost (ASYNC)
 → Failover to standby
 → Old primary rebuilt
 → Operations resume with data gap

Mitigation Strategies

• Use SYNC mode for critical systems
• Use Far Sync for distant standbys
• Monitor apply and transport lag closely
• Regularly test DR procedures

Strong Interview Closing Line

“Redo transport mode directly determines data loss behavior during failures, making SYNC, ASYNC, and Far Sync key design decisions in Data Guard.”

Q14: Explain the Data Guard protection modes.

Answer:
Data Guard protection modes define the level of data protection versus availability and performance by controlling how redo is transported from the primary to the standby database.

1. Maximum Protection

(Zero Data Loss, Primary Stops if Standby Is Unavailable)

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Key Characteristics

• Uses SYNC redo transport
• Primary shuts down if no standby is available
• Guarantees absolute zero data loss
• Requires at least one standby with Standby Redo Logs
• Highest data protection, lowest availability

Use Case

• Extremely critical systems where data loss is unacceptable under any circumstance

2. Maximum Availability

(Zero Data Loss with Continued Availability)

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Key Characteristics

• Uses SYNC redo transport during normal operation
• Automatically switches to ASYNC if standby becomes unavailable
• Zero data loss while operating in SYNC mode
• Possible data loss only if failover occurs during ASYNC period
• Best balance between protection and availability

Use Case

• Mission-critical systems requiring zero data loss without sacrificing uptime

3. Maximum Performance

(Best Performance, Possible Data Loss – Default Mode)

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Key Characteristics

• Uses ASYNC redo transport
• No performance impact on primary
• Primary continues regardless of standby status
• Possible data loss during failover
• Most widely used mode in production

Use Case

• Systems where performance is more important than zero data loss

Protection Mode Comparison

Check Current Protection Mode

SELECT protection_mode, protection_level
FROM v$database;

Interview One-Liner

“Maximum Protection prioritizes zero data loss, Maximum Availability balances protection and uptime, and Maximum Performance prioritizes performance with acceptable data loss.”

Q15: What is a Far Sync instance?

Answer:
A Far Sync instance is a lightweight remote Oracle instance (introduced in Oracle 12c) that receives redo from the primary database and forwards it to distant standby databases.
It enables zero data loss without impacting primary database performance, even when the standby is geographically far away.

Problem It Solves

• Primary in Mumbai, standby in London (high network latency)
• SYNC mode is too slow due to long-distance acknowledgment
• ASYNC mode risks data loss

Solution Architecture

PRIMARY (Mumbai)
     ↓  SYNC (low latency)
FAR SYNC (Nearby location)
     ↓  ASYNC (long distance)
STANDBY (London)

Key Benefits

• Zero data loss (SYNC transport to nearby Far Sync)
• No noticeable performance impact on primary
• Supports geographically distant standby databases
• Enables long-distance replication safely

What a Far Sync Instance Contains

• Control file
• Standby redo logs
• Archived redo logs
• No datafiles
• Requires minimal CPU, memory, and storage

Use Cases

• Long-distance disaster recovery
• Zero data loss with acceptable performance
• Multi-region DR architectures

Configuration Example

Primary to Far Sync (SYNC)

LOG_ARCHIVE_DEST_2='SERVICE=farsync SYNC AFFIRM'

Far Sync to Standby (ASYNC)

LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC'

Interview One-Liner

“Far Sync enables zero data loss by synchronously shipping redo to a nearby instance and asynchronously forwarding it to distant standbys.”

Q16: How do you rebuild a failed primary as a new standby?

Answer:
After a failover, the old primary must be converted into a standby for the new primary. There are three common methods, depending on availability of flashback and backups.

Method 1: Using Flashback Database

(Fastest and Recommended)

Prerequisite

• Flashback Database must have been enabled on the old primary before the failure

Steps

STARTUP MOUNT;

Flashback to pre-failover state

FLASHBACK DATABASE TO BEFORE SCN <failover_scn>;
-- or
FLASHBACK DATABASE TO TIMESTAMP <time_before_failover>;

Convert to Physical Standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Verification

SELECT database_role FROM v$database;

Expected:

• PHYSICAL STANDBY

Method 2: Using RMAN Restore

(When Flashback Is Not Available)

Steps

1. Take backup of new primary

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

2. Restore on old primary

RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM '<backup_location>';
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE NOREDO;

3. Convert to Physical Standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Method 3: Rebuild from Scratch

(Last Resort)

Steps

• Create standby using RMAN DUPLICATE
• Reconfigure Data Guard from beginning
• Start managed recovery

Best Practice

Always enable Flashback Database on the primary to allow fast and safe reinstatement after failover.

ALTER DATABASE FLASHBACK ON;

Final Interview Power Line

“Far Sync solves long-distance zero data loss, and Flashback Database makes reinstating a failed primary fast and reliable.”

Q17: What are the prerequisites for Data Guard setup?

Answer:
Before configuring Oracle Data Guard, several software, database, network, and configuration prerequisites must be met.

1. Software and Licensing

• Oracle Enterprise Edition on both primary and standby
• Same Oracle version (or one release apart for rolling upgrades)
• Same OS platform or compatible platform family (for example, Linux x64 → Linux x64)
• Active Data Guard license required for READ ONLY standby with redo apply

2. Primary Database Configuration

ARCHIVELOG Mode (Mandatory)

ARCHIVE LOG LIST;

• Must show: Database log mode: Archive Mode

Force Logging (Strongly Recommended)

ALTER DATABASE FORCE LOGGING;

• Prevents nologging operations from breaking standby consistency

Standby Redo Logs

• Required for real-time apply
• (Number of online redo log groups + 1) per thread

ALTER DATABASE ADD STANDBY LOGFILE GROUP X SIZE 512M;

Archive Destinations

ALTER SYSTEM SET log_archive_dest_1=
'LOCATION=/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';

ALTER SYSTEM SET log_archive_dest_2=
'SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)';

3. Network Configuration

TNS Entries (on both servers)

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary-host)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby-host)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL_STBY))
  )

Static Listener Registration (Standby)

Required to connect when the database is in MOUNT state.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_STBY)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )

4. Password File

• Password files must be identical on primary and standby

scp $ORACLE_HOME/dbs/orapwORCL standby:$ORACLE_HOME/dbs/

5. Initialization Parameters

On Primary

DB_NAME=ORCL
DB_UNIQUE_NAME=ORCL_PRIMARY

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_PRIMARY,ORCL_STANDBY)'
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_PRIMARY'
LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STANDBY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE

DB_FILE_NAME_CONVERT='/ORCL_PRIMARY/','/ORCL_STANDBY/'
LOG_FILE_NAME_CONVERT='/ORCL_PRIMARY/','/ORCL_STANDBY/'

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=ORCL_STANDBY
FAL_CLIENT=ORCL_PRIMARY

On Standby

DB_NAME=ORCL
DB_UNIQUE_NAME=ORCL_STANDBY

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_PRIMARY,ORCL_STANDBY)'
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STANDBY'
LOG_ARCHIVE_DEST_2='SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_PRIMARY'

DB_FILE_NAME_CONVERT='/ORCL_PRIMARY/','/ORCL_STANDBY/'
LOG_FILE_NAME_CONVERT='/ORCL_PRIMARY/','/ORCL_STANDBY/'

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=ORCL_PRIMARY
FAL_CLIENT=ORCL_STANDBY

6. Storage Requirements

• Disk space equivalent to primary database
• Archive log space at least 2× daily redo generation

7. Resource Requirements

• CPU and memory comparable to primary (especially for Active Data Guard)
• Network bandwidth sufficient for redo generation rate

Interview One-Liner

“Data Guard requires ARCHIVELOG mode, proper redo transport configuration, network connectivity, and synchronized initialization parameters.”

Q18: How do you create a physical standby database from scratch?

Answer:
A physical standby is typically created using RMAN DUPLICATE from the primary database.

High-Level Steps

1. Prepare the primary database
2. Create standby using RMAN DUPLICATE
3. Configure Data Guard parameters
4. Start Managed Recovery Process (MRP)
5. Verify synchronization

Step 1: Prepare Primary Database

ALTER DATABASE FORCE LOGGING;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 SIZE 512M;

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(ORCL_PRIMARY,ORCL_STANDBY)';
ALTER SYSTEM SET log_archive_dest_2=
'SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=ORCL_STANDBY';

Step 2: Create Standby Using RMAN

export ORACLE_SID=ORCL
rman TARGET sys/password@primary AUXILIARY /

RUN {
  ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
  ALLOCATE AUXILIARY CHANNEL stby DEVICE TYPE DISK;
  DUPLICATE TARGET DATABASE
    FOR STANDBY
    FROM ACTIVE DATABASE
    DORECOVER
    SPFILE
      SET DB_UNIQUE_NAME='ORCL_STANDBY'
      SET FAL_SERVER='ORCL_PRIMARY'
      SET FAL_CLIENT='ORCL_STANDBY'
      SET LOG_ARCHIVE_DEST_2='SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                              DB_UNIQUE_NAME=ORCL_PRIMARY'
      SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_PRIMARY,ORCL_STANDBY)'
      SET STANDBY_FILE_MANAGEMENT='AUTO'
    NOFILENAMECHECK;
}

Step 3: Add Standby Redo Logs on Standby

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 SIZE 512M;

Step 4: Start Managed Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 5: Verify Standby Health

SELECT database_role, open_mode FROM v$database;

SELECT process, status
FROM v$managed_standby
WHERE process = 'MRP0';

SELECT name, value
FROM v$dataguard_stats
WHERE name IN ('apply lag', 'transport lag');

SELECT * FROM v$archive_gap;

Step 6: Configure Data Guard Broker (Optional)

DGMGRL> CREATE CONFIGURATION 'DG_CONFIG'
  AS PRIMARY DATABASE IS 'ORCL_PRIMARY'
  CONNECT IDENTIFIER IS primary;

DGMGRL> ADD DATABASE 'ORCL_STANDBY'
  AS CONNECT IDENTIFIER IS standby
  MAINTAINED AS PHYSICAL;

DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;

Interview One-Liner

“A physical standby is created using RMAN DUPLICATE, followed by redo apply using MRP to keep it synchronized with the primary.”

Q19: Common Data Guard Errors and Solutions

This question is frequently asked to assess real-world troubleshooting experience.

Error 1: ORA-16191

Primary log shipping client not logged on standby

Cause

• Network connectivity issue
• Incorrect or missing TNS configuration
• Listener not reachable on standby

Solution

Test connectivity from primary

tnsping standby

Verify listener on standby

lsnrctl status

Check static listener registration

• Ensure GLOBAL_DBNAME and SID_NAME are correctly defined in listener.ora

Test direct connection

sqlplus sys/password@standby as sysdba

Interview Tip

This error almost always points to a network or listener configuration problem.

Error 2: ORA-16014

Log sequence# not archived, no available destinations

Cause

• All archive destinations failed
• Standby unreachable
• Local archive destination is full

Solution

Check archive destination status

SELECT dest_id, status, error
FROM v$archive_dest;

If archive destination is full

ALTER SYSTEM SET log_archive_dest_state_2=DEFER;
-- Free disk space
ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

If network issue

ALTER SYSTEM SET log_archive_dest_state_2=DEFER;
-- Fix network issue
ALTER SYSTEM SET log_archive_dest_state_2=ENABLE;

Interview Tip

Deferring the destination prevents primary database hangs while issues are fixed.

Error 3: ORA-16766

Redo Apply is stopped

Cause

• MRP stopped manually
• Error occurred during redo apply
• Standby database not in correct state

Solution

Check error details

SELECT message
FROM v$dataguard_status
WHERE severity = 'Error';

Restart MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Start real-time apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;

Interview Tip

Always check the alert log and v$dataguard_status before restarting MRP.

Error 4: ORA-00308

Cannot open archived log

Cause

• Archived redo log missing
• File corrupted
• Incorrect file path

Solution

Check for archive gap

SELECT * FROM v$archive_gap;

Manually resolve

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/path/to/archive.arc';

Automatic resolution

• Ensure FAL client/server parameters are configured correctly

Interview Tip

This error often accompanies archive gaps and missing redo files.

Error 5: ORA-16198

Timeout: cannot get status from instance

Cause

• Data Guard Broker cannot communicate with database
• Listener or database is down
• Broker configuration files inaccessible

Solution

Check listener

lsnrctl status

Check database process

ps -ef | grep pmon

Verify broker files

ls -l dr*.dat

Restart Broker

DGMGRL> DISABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;

Interview One-Liner

“Most Data Guard errors are caused by network issues, missing redo, or stopped recovery processes.”

10. BEST PRACTICES

These best practices reflect real production environments and are frequently discussed in Oracle DBA interviews.

1. Always Enable Flashback on Primary

ALTER DATABASE FLASHBACK ON;

• Enables fast reinstatement of old primary after failover
• Simplifies recovery from logical or user errors
• Strongly recommended for all Data Guard setups

2. Configure Standby Redo Logs

• Mandatory for real-time apply
• Formula: (Number of online redo log groups + 1) per thread
• Same size as primary online redo logs
• Required for zero data loss and FSFO

3. Use Data Guard Broker

• Simplifies Data Guard management
• Reduces manual errors during switchover/failover
• Enables Fast-Start Failover
• Provides centralized monitoring and validation

4. Monitor Data Guard Regularly

Daily Checks

SELECT name, value
FROM v$dataguard_stats
WHERE name IN ('apply lag', 'transport lag');

SELECT * FROM v$archive_gap;

Weekly Checks

SELECT sequence#, applied
FROM v$archived_log
ORDER BY sequence# DESC
FETCH FIRST 100 ROWS ONLY;

5. Test DR Procedures Periodically

• Perform switchover quarterly or monthly
• Test failover at least annually
• Document all procedures
• Train team members to avoid panic during real outages

6. Archive Log Management

Set Retention Policy

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

Regular Cleanup

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

• Prevents archive destination from filling up
• Avoids ORA-16014 errors

7. Network Configuration

• Use bonded or redundant network interfaces
• Prefer a dedicated network for Data Guard traffic
• Continuously monitor network latency and packet loss

8. Resource Allocation

• Standby should have comparable CPU and memory as primary
• Critical for Active Data Guard workloads
• Prevents apply lag during heavy redo generation

9. Choose the Right Protection Mode

• Maximum Performance – Default and most common
• Maximum Availability – Critical systems requiring zero data loss
• Maximum Protection – Only when primary shutdown is acceptable

10. Regular Archive Gap Checks

Automated Monitoring

SELECT COUNT(*)
FROM v$archive_gap;

• Any value greater than zero requires immediate action

Interview Closing One-Liner

“A well-designed Data Guard environment combines proper redo configuration, continuous monitoring, regular testing, and automation using Broker.”

11. QUICK REFERENCE COMMANDS

Check Database Role and Open Mode

SELECT database_role, open_mode FROM v$database;

Check Switchover Status

SELECT database_role, switchover_status FROM v$database;

Start Managed Recovery (MRP)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

With Real-Time Apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;

Stop Managed Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check MRP Status

SELECT process, status, thread#, sequence#
FROM v$managed_standby
WHERE process = 'MRP0';

Check Apply and Transport Lag

SELECT name, value, unit
FROM v$dataguard_stats
WHERE name IN ('apply lag', 'transport lag');

Check Archive Destinations

SELECT dest_id, status, destination, error
FROM v$archive_dest
WHERE status != 'INACTIVE';

Check Archive Gaps

SELECT * FROM v$archive_gap;

Open Standby READ ONLY (Active Data Guard)

ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT;

Convert to Snapshot Standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;

Convert Snapshot Back to Physical Standby

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Data Guard Broker – Common Commands

Show Configuration

DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE primary_db;
DGMGRL> SHOW DATABASE standby_db;

Switchover

DGMGRL> SWITCHOVER TO standby_db;

Failover

DGMGRL> FAILOVER TO standby_db;

Enable Fast-Start Failover

DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> START OBSERVER;

12. FINAL TIPS FOR INTERVIEW

What Interviewers Focus On

1. Core Concepts (WHY, not just HOW)

• Why physical vs logical standby
• Why SYNC vs ASYNC
• Why Standby Redo Logs are mandatory
• Why Broker simplifies operations

2. Troubleshooting Ability

Expect questions like:

• “Standby is lagging by 2 hours — what do you check first?”
• “Primary crashed — how do you proceed?”
• “Archive gap detected — how do you resolve it?”

3. Best Practices

Always mention:

• Flashback Database for reinstatement
• Data Guard Broker for automation
• Regular monitoring and testing

4. Hands-On Experience

• Explain what you have actually done
• Mention DR drills you participated in
• Talk about issues faced and how you resolved them

Common Interview Flow

Basic       → Physical vs Logical Standby
Intermediate→ Monitoring lag and health
Advanced    → Failover and recovery process
Scenario    → Primary crashed with lag on standby

Prepare These Scenarios Thoroughly

1. Switchover for Maintenance

• Pre-checks
• Commands
• Validation
• Rollback plan

2. Failover After Disaster

• Emergency response
• Data loss handling
• Rebuilding old primary

3. High Apply Lag

• Diagnosis (MRP, resources, network)
• Resolution (parallel apply, tuning)

4. Archive Gap Resolution

• Detection
• Manual copy and register
• FAL-based resolution

5. Network Failure

• Impact on redo transport
• Monitoring indicators
• Recovery steps

Final Interview Power Line

“Oracle Data Guard is not just about setup—it’s about monitoring, testing, and being ready to recover confidently during failures.”