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 |
|
Standby DB |
|
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_LOGRFS IDLESequence numbers advancing
Unhealthy
WAIT_FOR_GAPIDLEfor long periodsMRP 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 files5.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_gapreturns no rowsMRP0 APPLYING_LOGApply 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 |
|
Standby |
|
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→ PRIMARYATL_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:
PRIMARYREAD WRITE
7.6 Start Application Services
srvctl start service -db ORADEMO -service OLTP_SVCCoordinate 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
