Data Guard

58.1 Concepts

The primary ships redo to one or more standby databases (redo transport → redo apply).

Physical standby (block-for-block, Redo Apply/MRP). Logical standby (SQL Apply). Snapshot standby (temporarily read-write for testing).

Active Data Guard (licensed option): read-only queries while applying redo, real-time query, automatic block repair, DML redirection (19c), far sync, global data services, RMAN backups from the standby with block change tracking.

Far Sync instance: zero data loss over long distances.

58.2 Protection Modes

Impact on Primary Maximum Performance (default) Maximum Availability SYNC / FASTSYNC (AFFIRM / NOAFFIRM) Zero (unless double failure)

Small latency, falls back if the standby is unreachable Maximum Protection

Primary shuts down if no standby acknowledges

58.3 Setup Outline (Physical Standby)

Primary: ARCHIVELOG, FORCE LOGGING, standby redo logs (SRLs: one more group than online redo per thread), DB_UNIQUE_NAME, LOG_ARCHIVE_CONFIG, LOG_ARCHIVE_DEST_2, FAL_SERVER, STANDBY_FILE_MANAGEMENT=AUTO, password file copied to the standby, TNS entries on both sides.

Create the standby: RMAN DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

Start redo apply: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Configure the Data Guard Broker (DG_BROKER_START=TRUE).

58.4 Data Guard Broker (DGMGRL)

Rolling upgrades with a transient logical standby (DBMS_ROLLING). Standby-first patching.

23ai: PDB-level Data Guard (DGPDB).

dgmgrl sys@prim
DGMGRL> CREATE CONFIGURATION dgconf AS PRIMARY DATABASE IS prim CONNECT IDENTIFIER IS prim;
DGMGRL> ADD DATABASE stby AS CONNECT IDENTIFIER IS stby MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE stby;
DGMGRL> VALIDATE DATABASE stby;
DGMGRL> SWITCHOVER TO stby;          -- planned, no data loss
DGMGRL> FAILOVER TO stby;            -- unplanned
DGMGRL> REINSTATE DATABASE prim;     -- needs Flashback Database
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
DGMGRL> ENABLE FAST_START FAILOVER;  -- automatic failover with an Observer
DGMGRL> CONVERT DATABASE stby TO SNAPSHOT STANDBY;

-- Monitoring
SELECT name, value, time_computed FROM v$dataguard_stats;        -- transport lag, apply lag
SELECT process, status, thread#, sequence# FROM v$managed_standby;   -- (V$DATAGUARD_PROCESS in 12.2+)
SELECT dest_id, status, error FROM v$archive_dest_status;
SELECT database_role, open_mode, protection_mode, switchover_status FROM v$database;