Oracle Data Guard daily operations and failure scenarios (19c / 23ai)

Daily operations and failure recovery runbook for an existing Data Guard (physical standby) configuration; for the initial setup use the Data Guard installation guide. If you are not sure what to do, start with 'Status and lag check': the read-only diagnosis points you to the right scenario (planned switchover, failover, reinstating the old primary, archive gap, rolling forward with RECOVER … FROM SERVICE, rebuilding the standby, snapshot standby, apply/read-only, protection mode/FSFO/observer, datafile and redo log tasks, RMAN backup from the standby). Every scenario follows the pattern diagnosis → safety net → execution → verification → follow-up; it gives the broker (DGMGRL) and non-broker (SQL) paths, with the differences for single instance / Oracle Restart / RAC.

Topology: Primary (single instance / Oracle Restart / RAC) + physical standby (single instance / Oracle Restart / RAC) + optional observer

Scenarios

• Status and lag check (is everything all right?) — Read-only: configuration, transport/apply lag, archive gap, processes and alert log; nothing is changed. If you are not sure what to do, start here: the last step tells you the right scenario based on the output.
• Planned switchover (swap the roles) — Primary and standby are healthy and you want to swap the roles in a planned way (maintenance, DR drill, switching the roles back); no data is lost, the application is down for a few minutes. As long as you can reach the primary, always choose this for a role transition.
• Primary lost: fail over to the standby — The primary cannot be reached and will not come back (server, storage or site loss); the standby becomes the primary. Irreversible: in MaxPerformance the data of the last seconds may be lost, and the old primary later needs a reinstate or a rebuild. If you can still reach the primary, choose switchover.
• After a failover, bring the old primary back as a standby (reinstate) — A failover was performed and the old primary server is back; if Flashback Database is enabled, it is turned into a standby of the new primary without a rebuild. The new primary stays open. If flashback is disabled or the flashback window is not long enough, the diagnosis step sends you to a rebuild.
• Archive gap: the standby is waiting for missing archived logs — v$archive_gap returns rows or the alert log shows FAL/gap messages; apply is stuck at a sequence. The databases stay open. If the missing archived logs exist nowhere, a choice in the form switches to the roll-forward path.
• Standby far behind or archived logs gone: roll forward (RECOVER FROM SERVICE) — The archived logs in the gap exist nowhere, NOLOGGING operations corrupted the standby, or the standby is days behind; the standby is synchronized incrementally from the primary without a rebuild (18c+).
• Rebuild the standby from scratch — Reinstate is not possible (no flashback) and neither is a roll forward, or the standby's files/control file are lost; the standby is dropped and created again with DUPLICATE. The primary stays open but is unprotected until the rebuild finishes (unless there is another standby).
• Convert to snapshot standby / convert back (writable copy for testing) — You want to open the standby read-write temporarily for testing, then discard the changes and return to a physical standby; the primary is not affected and redo keeps arriving, but this standby is not ready for failover while it is a snapshot standby.
• Stop / start Redo Apply, change the apply instance, open read-only — You want to stop or restart MRP for maintenance, move apply to another instance on a RAC standby, or open the standby for reporting (Active Data Guard license warning).
• Protection mode, Fast-Start Failover and observer — You want to switch between MaxPerformance / MaxAvailability / MaxProtection, enable or disable FSFO, or manage the observer.
• Adding datafiles, redo logs and standby redo logs / UNNAMED file — A datafile or redo log is to be added on the primary, an UNNAMED file appeared on the standby (ORA-01111/ORA-01274), or standby redo logs are missing.
• Take an RMAN backup from the standby — You want to run RMAN backups on the standby to offload the backup workload from the primary, and set the archived log deletion policy accordingly.

Before you start

• On all servers the profile of the oracle user sets ORACLE_HOME=…, ORACLE_SID (…on RAC per node …1, …2 …………) and PATH; sqlplus, rman…, dgmgrl… work. The configuration was built with the Data Guard installation guide or an equivalent (TNS aliases …, …, static _DGMGRL listener entries, the same password file).
• It has been verified that the roles match the inputs: currently … is the primary, … is the physical standby. If not (e.g. a switchover was done earlier), swap the names in the inputs.
• The SYS password is known (role transitions, restarting the remote instance and RMAN FROM SERVICE need a password-authenticated connection). The password contains no @, /, " or spaces.
• License: Data Guard is included in Enterprise Edition. Using the standby open while apply is running, block change tracking on the standby and Far Sync require an Active Data Guard license. Snapshot standby and opening read-only with apply stopped need no license.
• The change is in an approved maintenance window; the application and monitoring teams have been informed… (connections are interrupted for a few minutes)….
• The failover decision was made by an authorized person; it is guaranteed that the old primary will not be opened again (split-brain): the server is down, isolated from the network, or the instance is held in MOUNT.

Phases of the guide

• Status: configuration, lag and processes (read-only) — Show the broker configuration and role transition readiness · Check the primary side with SQL · Check the standby side with SQL · Scan the alert logs for Data Guard errors · Interpret the result and choose the right scenario
• 1. Diagnosis: is it ready for switchover? — Verify readiness (VALIDATE) · Verify readiness (SWITCHOVER … VERIFY) · Check the standby lag
• 2. Safety net — Save the current configuration (primary) · Save the current configuration (standby)
• 3. Execution: switchover — Switchover: make … the primary (broker) · Start the switchover (primary → standby) · Open the new primary (…) · Update the Clusterware role (new primary) · Open the other instance (new primary node …: …) · Mount the old primary as a standby (…) …
• 4. Verification — Verify the new roles and the configuration · Check apply and lag on the new standby
• 5. Follow-up — Application, job and monitoring checks
• 1. Diagnosis: is a failover really necessary? — Try to reach the primary one last time (from the standby) · Assess the standby state and the possible data loss · Show the state as the broker sees it (from the standby)
• 2. Safety net and remaining redo — Save the current standby settings · Mount the old primary and ship the remaining redo (FLUSH REDO) · Copy the missing archived logs manually (without the broker, if possible)
• 3. Execution: failover — Failover: make … the primary (broker) · Stop Redo Apply and perform the failover (SQL) · Open the new primary (SQL) · Update the Clusterware role (new primary) · Open the other instance (new primary node …: …)
• 4. Verification — Verify the new primary
• 5. Follow-up — Take a full backup of the new primary · If the old primary comes back: DO NOT OPEN IT
• Application service (role-based) — Define and start the service on the … side (Clusterware) · Define the service on the … side (Clusterware) · Define the service and the role trigger (no Grid) · Set up the client connect descriptor with two addresses
• 1. Diagnosis: is a reinstate possible? — Check the new primary and the role transition SCN · Show the broker status (new primary) · Mount the old primary and check the flashback window
• 2. Safety net — Save the old primary's control file and parameters
• 3. Execution: reinstate — Reinstate the old primary (broker) · Start the other instances (old primary, RAC) · Flash back and convert to a physical standby (SQL) · Enable the … destination on the new primary (SQL) · Start Redo Apply and get past the role transition records (SQL)
• 4. Verification — Verify the configuration · Verify apply on the old primary (new standby)
• 5. Follow-up — Restore the original roles (optional) and close the root cause
• 1. Diagnosis: where is the gap, do the archived logs exist? — Show the gap and the processes on the standby · Check the FAL connection and the alert log (standby) · Are the missing archived logs still on the primary? · Are the missing archived logs in an RMAN backup?
• 2. Safety net — Verify that there is space in the standby FRA
• 3. Execution: fetch and register the missing archived logs — If the archived logs are only in a backup: restore them on the primary · Copy the missing archived logs to the file system and send them to the standby (primary) · Register the archived logs on the standby · Restart Redo Apply (standby)
• 4. Verification — Watch the gap close (standby)
• 5. Follow-up — Delete the temporary copies · Close the root cause
• 1. Diagnosis: how far behind is the standby? — Show the standby SCN and state · Check NOLOGGING operations and the COMPATIBLE value on the primary
• 2. Safety net — Back up the standby control file and spfile
• 3. Execution: roll the standby forward — Stop Redo Apply (standby) · Mount the standby… (instance 1 only)… · Roll the standby forward from the primary (RECOVER STANDBY DATABASE FROM SERVICE) · Start the other standby instances · Check the standby redo logs and the flashback status · Start Redo Apply (standby)
• 4. Verification — Verify that the lag has closed
• 5. Follow-up — Close the root cause
• 1. Diagnosis: is a rebuild really necessary? — Show the state of the configuration and the primary
• 2. Safety net — Keep the standby's configuration files
• 3. Execution: drop and rebuild — Remove the standby from the broker configuration · Defer the standby destination on the primary (DEFER) · Drop the old standby database · Clean up the remaining broker, init and password files · Move the old init file aside (standby node …: …) · Recreate the standby with the installation guide …