shashi: Oracle Data Guard Operations Runbook

Version: 1.0 Applies to: Oracle 19c / 21c / 23ai / 26ai Physical Standby Scope: Monitoring, Switchover, Failover, Gap Resolution, Broker Operations, Reinstatement

1. Purpose

This runbook provides:

Daily health verification
Transport/apply lag monitoring
Archive gap troubleshooting
Planned switchover procedure
Emergency failover procedure
Reinstate failed primary
Broker (DGMGRL) operations
Standby-first patching workflow

Primary Goal: Execute Data Guard operations with zero guesswork during maintenance windows or production incidents.

2. Architecture Assumptions

Component	Example (Sanitized)
Primary DB	`PRIM`
Standby DB	`STDBY`

Protection Mode	Maximum Availability

3. Daily Health Check (Mandatory)

Run every morning on both databases.

3.1 Overall Data Guard Status

Script: `dg_status.sql`

SET LINESIZE 140 PAGESIZE 100 FEEDBACK OFF

COLUMN db_unique_name    FORMAT A16
COLUMN database_role     FORMAT A18
COLUMN open_mode         FORMAT A22
COLUMN protection_mode   FORMAT A22
COLUMN protection_level  FORMAT A22
COLUMN switchover_status FORMAT A22
COLUMN force_logging     FORMAT A8
COLUMN flashback_on      FORMAT A12
COLUMN dataguard_broker  FORMAT A10

SELECT db_unique_name,
       database_role,
       open_mode,
       protection_mode,
       protection_level,
       switchover_status,
       force_logging,
       flashback_on,
       dataguard_broker
FROM   v$database;

Expected Output

4. Transport and Apply Monitoring

4.1 Check Real RPO (Lag)

Script: `dg_lag_check.sql`

Run on standby.

SET LINESIZE 140 PAGESIZE 100

COLUMN name  FORMAT A18
COLUMN value FORMAT A20

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

4.2 Check Apply Process

Script: `dg_apply_status.sql`

SET LINESIZE 160 PAGESIZE 100

COLUMN process FORMAT A8
COLUMN status  FORMAT A35
COLUMN sequence# FORMAT 999999

SELECT process,
       status,
       thread#,
       sequence#
FROM   v$managed_standby
ORDER  BY process;

Healthy Output

MRP0 APPLYING_LOG
RFS IDLE
Sequence numbers advancing

Unhealthy

WAIT_FOR_GAP
IDLE for long periods
MRP not running

5. Archive Gap Resolution Runbook

Severity: High (RPO increasing)

5.1 Detect Gap

Script: `dg_archive_gap.sql`

SELECT thread#,
       low_sequence#,
       high_sequence#
FROM   v$archive_gap;

Example

5.2 Confirm Apply Stalled

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

Expected: WAIT_FOR_GAP

5.3 Restore Missing Logs from Primary

Run on primary.

RMAN TARGET /

RESTORE ARCHIVELOG
  FROM SEQUENCE 48205
  UNTIL SEQUENCE 48211
  THREAD 1
  FORMAT '/tmp/dg_gap_%U.arc';

Copy files to standby.

5.4 Register Logs on Standby

ALTER DATABASE REGISTER LOGFILE '/u02/arch/dg_gap_01.arc';
ALTER DATABASE REGISTER LOGFILE '/u02/arch/dg_gap_02.arc';
-- repeat for all files

5.5 Resume Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

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

5.6 Verify

v$archive_gap returns no rows
MRP0 APPLYING_LOG
Apply lag decreasing

6. Planned Switchover Runbook (Zero Data Loss)

Use for: Patching, maintenance, DR drill

6.1 Pre-Checks (Both Databases)

Run `dg_role_check.sql`

SELECT db_unique_name,
       database_role,
       open_mode,
       switchover_status
FROM   v$database;

Required

Database	Required Status
Primary	`TO STANDBY`
Standby	`TO PRIMARY`

6.2 Broker Validation

DGMGRL /

VALIDATE DATABASE 'ATL_PRIMARY';
VALIDATE DATABASE 'DAL_STANDBY';

Both must report Ready for Switchover.

6.3 Execute Switchover

DGMGRL /

SWITCHOVER TO 'DAL_STANDBY';

Wait for completion.

6.4 Verify Roles

SHOW CONFIGURATION;

Expected:

DAL_STANDBY → PRIMARY
ATL_PRIMARY → PHYSICAL STANDBY

6.5 Validate with SQL

SELECT db_unique_name, database_role, open_mode
FROM   v$database;

6.6 Verify Services

SELECT name, network_name
FROM   v$active_services
WHERE  name IN ('OLTP_SVC','RPT_SVC');

OLTP service must be active only on new primary.

7. Emergency Failover Runbook

Use only when primary is unavailable.

7.1 Confirm Primary Is Unreachable

Host down
Storage failure
Cluster unavailable
No chance of immediate recovery

Document incident start time.

7.2 Estimate Data Loss

Run on standby.

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

Record expected RPO.

7.3 Validate Standby

DGMGRL /

VALIDATE DATABASE 'DAL_STANDBY';

Ensure no unresolved gaps.

7.4 Execute Failover

DGMGRL /

FAILOVER TO 'DAL_STANDBY';

7.5 Verify New Primary

SELECT database_role, open_mode
FROM   v$database;

Expected:

PRIMARY
READ WRITE

7.6 Start Application Services

srvctl start service -db ORADEMO -service OLTP_SVC

Coordinate with application team.

8. Reinstate Failed Primary (Fastest Recovery)

Requirement: Flashback Database enabled before failover.

8.1 Verify Failed Primary Is Back Online

Mount only.

STARTUP MOUNT;

8.2 Reinstate Using Broker

DGMGRL /

REINSTATE DATABASE 'ATL_PRIMARY';

Broker performs flashback automatically.

8.3 Verify Configuration

SHOW CONFIGURATION;

Expected: SUCCESS

8.4 Validate Apply

On reinstated standby:

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

Expected: APPLYING_LOG

9. Broker Operations Reference

9.1 Check Configuration

DGMGRL /

SHOW CONFIGURATION;

Status must be SUCCESS.

9.2 Detailed Database Status

SHOW DATABASE 'ATL_PRIMARY';
SHOW DATABASE 'DAL_STANDBY';

9.3 Diagnose Warnings

SHOW DATABASE 'DAL_STANDBY' StatusReport;

Use this before any role transition.

9.4 Enable/Disable Broker

ALTER SYSTEM SET dg_broker_start=TRUE;
ALTER SYSTEM SET dg_broker_start=FALSE;

10. Standby-First Patching Runbook

Minimizes primary downtime.

Step 1 — Switchover to Standby

Use Section 6.

Step 2 — Patch Former Primary (Now Standby)

Apply RU
Run datapatch if required
Restart standby
Verify MRP running

Step 3 — Switchover Back

Use Section 6 again.

Step 4 — Patch Remaining Standby

Patch the other node while production runs on primary.

11. Command Cheat Sheet

Daily Health

@dg_status.sql@dg_lag_check.sql@dg_apply_status.sql

Switchover

DGMGRL> VALIDATE DATABASE 'ATL_PRIMARY';DGMGRL> SWITCHOVER TO 'DAL_STANDBY';

Failover

DGMGRL> FAILOVER TO 'DAL_STANDBY';

Reinstate

DGMGRL> REINSTATE DATABASE 'ATL_PRIMARY';

Gap Resolution

SELECT * FROM v$archive_gap;ALTER DATABASE REGISTER LOGFILE '...';

Final Operational Rule

Before every role change:

No archive gap
Transport lag acceptable
Apply lag acceptable
Broker status SUCCESS
Flashback ON
Both databases show correct switchover status

Thursday, July 16, 2026

Oracle Data Guard Operations Runbook