🛡️ Oracle Data Guard – Guide complet (DR, Broker, Runbooks)

Timeline (minute-by-minute)

Evidence bundle checklist

• Transport status snapshots (before, during, after)
• Lag snapshots (peak + recovery curve)
• Missing sequence range (gap start/end)
• Change reference (network) and remediation steps

Gap resolution playbook

After transport returns, standby may still be missing archived logs. If FAL is configured, it can auto-fetch. Otherwise, copy missing logs manually and register them. Then restart apply and verify catch-up.

-- Standby: last received and last applied per thread
                            SELECT thread#,
                            MAX(sequence#) AS last_received,
                            MAX(CASE WHEN applied='YES' THEN sequence# END) AS last_applied
                            FROM v$archived_log
                            GROUP BY thread#
                            ORDER BY thread#;

                            -- Standby: list gaps (manual inspection approach)
                            SELECT thread#, sequence#, applied
                            FROM v$archived_log
                            WHERE first_time > SYSDATE - 1
                            ORDER BY thread#, sequence#;

SELECT dest_id, status, error, destination
                            FROM v$archive_dest_status
                            WHERE status <> 'INACTIVE'
                            ORDER BY dest_id;

                            -- Broker (optional)
                            -- DGMGRL> SHOW CONFIGURATION;
                            -- DGMGRL> SHOW DATABASE STBY;

Step 3a: FAL auto-fetch (preferred if configured)

-- Standby: confirm FAL settings
                    SHOW PARAMETER fal_server;
                    SHOW PARAMETER fal_client;

                    -- If needed (example)
                    ALTER SYSTEM SET fal_server='PRIM' SCOPE=BOTH;
                    ALTER SYSTEM SET fal_client='STBY' SCOPE=BOTH;

Step 3b: manual copy + register (fallback)

-- Standby: register manually copied archivelogs
                    ALTER DATABASE REGISTER LOGFILE '/path/to/arch_1_12345_*.arc';
                    ALTER DATABASE REGISTER LOGFILE '/path/to/arch_1_12346_*.arc';

Step 4: restart apply and validate

-- Restart apply (real-time)
                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

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

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

KPIs to report (what leadership cares about)

Postmortem structure (template)

One-shot summary commands (copy/paste)

-- Transport status
                    SELECT dest_id, status, error, destination, transmit_mode
                    FROM v$archive_dest_status
                    ORDER BY dest_id;

                    -- Lag snapshot
                    SELECT name, value, unit, time_computed
                    FROM v$dataguard_stats
                    WHERE name IN ('transport lag','apply lag','apply finish time');

                    -- Last applied per thread
                    SELECT thread#,
                    MAX(CASE WHEN applied='YES' THEN sequence# END) AS last_applied
                    FROM v$archived_log
                    GROUP BY thread#
                    ORDER BY thread#;

Standby Redo Logs on ASM (Exadata)

On Exadata, redo and standby redo logs typically reside in ASM disk groups. Placement and redundancy choice impact redo write latency and apply stability.

Example: create SRL on ASM

-- Example using OMF in ASM
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 1024M;

                    -- Verify placement
                    SELECT group#, thread#, bytes/1024/1024 AS size_mb, status
                    FROM gv$standby_log
                    ORDER BY thread#, group#;

IO baseline for standby on Exadata

Active Data Guard + heavy reporting can stress IO. Establish a measurable IO baseline to ensure redo apply never starves.

Apply visibility

-- Apply rate visibility
                    SELECT process, status, thread#, sequence#
                    FROM gv$managed_standby
                    ORDER BY process;

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

Common Exadata + Data Guard pitfalls

Quick health snapshot

SELECT database_role, open_mode, protection_level
                    FROM v$database;

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

                    SELECT dest_id, status, error, transmit_mode
                    FROM v$archive_dest_status
                    ORDER BY dest_id;

Licensing caveats (practical)

Active Data Guard is a separately licensed option in many Oracle distributions. What matters operationally: do not assume read-only open + apply is covered by "basic Data Guard". Align with your Oracle licensing policy and your contract terms.

Enable ADG (standby operations)

ADG requires the standby to be opened read-only, and apply to continue. A typical flow: start managed recovery, open read-only (or open then start apply, depending on version and policy).

-- Standby: if mounted
                            ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

                            -- Open read-only
                            ALTER DATABASE OPEN READ ONLY;

-- Start redo apply while open read-only (ADG)
                            ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
                            USING CURRENT LOGFILE
                            DISCONNECT FROM SESSION;

Validate state

-- Role and open mode
                    SELECT database_role, open_mode
                    FROM v$database;

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

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

Best practices (battle-tested)

Operational guardrails

Common pitfalls

• Opening standby read-only for reporting without resource controls => apply lag spikes.
• Routing too much traffic to standby => standby becomes critical path and hurts DR posture.
• Missing SRLs (or wrong SRL sizing) => real-time apply not effective.
• Not rehearsing service cutover => app outages during planned switchovers.

Quick monitoring block (copy/paste)

-- ADG: verify open read-only and apply running
                    SELECT database_role, open_mode FROM v$database;

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

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

                    SELECT dest_id, status, error, transmit_mode
                    FROM v$archive_dest_status
                    ORDER BY dest_id;

SRL per thread: sizing and count

Baseline rule: for each thread on the standby, create SRLs with the same size as the primary online redo logs, and with a count of (#online redo log groups per thread + 1). This enables real-time apply and reduces lag.

Example: create SRLs for 2-thread RAC

Example assumes each thread has 3 online redo groups on primary; create 4 SRLs per thread on standby. Adjust SIZE to match your environment.

-- Standby: create SRLs for THREAD 1
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1024M;

                    -- Standby: create SRLs for THREAD 2
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 1024M;

SRL health checks

-- Standby: SRL inventory and state
                    SELECT thread#, group#, sequence#, bytes/1024/1024 AS size_mb, status
                    FROM gv$standby_log
                    ORDER BY thread#, group#;

                    -- Standby: apply processes visibility
                    SELECT inst_id, process, status, thread#, sequence#
                    FROM gv$managed_standby
                    ORDER BY inst_id, process;

Services: clean application cutover

In RAC, client connectivity should be service-based. During role transitions, services must be started on the new primary and stopped on the new standby. Prefer cluster-managed services (srvctl) and align service placement with instance roles.

Service inspection (database)

-- Services visible inside the database
                    SELECT name, network_name, pdb
                    FROM dba_services
                    ORDER BY name;

                    -- Role/open mode check (for gating service changes)
                    SELECT database_role, open_mode
                    FROM v$database;

Service control (cluster - example)

# Example commands (run as grid/oracle depending on setup)
                    # List services
                    srvctl status service -d 

                    # Start/stop service
                    srvctl start service -d  -s APP_RW
                    srvctl stop  service -d  -s APP_RW

Switchover gates (RAC-specific)

Thread and gap quick checks

-- Standby: confirm sequences are being received/applied for all threads
                    SELECT thread#,
                    MAX(sequence#) AS last_seq,
                    MAX(CASE WHEN applied='YES' THEN sequence# END) AS last_applied_seq
                    FROM v$archived_log
                    GROUP BY thread#
                    ORDER BY thread#;

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

RPO / RTO (pratique)

Composants cle

• Primary: production, generates redo (LGWR/ARCH).
• Standby: physical standby (recovery apply) or logical (SQL apply, legacy).
• Redo transport: network shipping via LOG_ARCHIVE_DEST_n.
• Apply: MRP (Managed Recovery Process) + real-time apply with SRLs.
• Broker: DGMGRL framework for config + monitoring + transitions.
• Observer: FSFO external process watching health to auto-failover.

Key data guard views

V$DATABASE
                    V$ARCHIVE_DEST_STATUS
                    V$ARCHIVED_LOG
                    V$DATAGUARD_STATS
                    V$MANAGED_STANDBY
                    V$STANDBY_LOG

Patterns frequents

SYNC vs ASYNC (what actually changes)

Standby Redo Logs sizing (SRL)

SRLs should match online redo log size and count. General baseline: (#online redo log groups + 1) per thread on the standby. For RAC: do it per thread.

-- Example (single instance)
                    -- If primary has 3 online redo log groups, create 4 SRLs
                    ALTER DATABASE ADD STANDBY LOGFILE SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE SIZE 1024M;
                    ALTER DATABASE ADD STANDBY LOGFILE SIZE 1024M;

Network checklist (ops)

• Latency budget measured (p95/p99), not guessed.
• Stable DNS/SCAN or static endpoints for TNS.
• Firewall rules: listener ports + broker if used.
• Packet loss monitoring (loss kills SYNC effectiveness).
• Time sync (NTP/chrony) on all hosts.

RMAN duplicate from backup

-- 1) Take backup on primary (including archivelogs)
                    rman target /
                    BACKUP DATABASE PLUS ARCHIVELOG;

                    -- 2) Restore on standby host, then duplicate
                    rman target sys@PRIM auxiliary /

                    DUPLICATE TARGET DATABASE
                    FOR STANDBY
                    BACKUP LOCATION '/backup/location'
                    NOFILENAMECHECK;

Controlfile method (manual baseline)

Useful as a mental model or last resort. RMAN is recommended for correctness and speed.

-- Primary
                    ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stby.ctl';

                    -- Copy controlfile + datafiles via backup/restore strategy
                    -- On standby: mount with standby controlfile

Post-build steps

• Set LOG_ARCHIVE_DEST_n on both ends.
• Create SRLs on standby.
• Start managed recovery (MRP).
• Verify apply/transport lag is stable.

-- Standby: start apply
                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

                    -- Check MRP/RFS
                    SELECT process, status, thread#, sequence# FROM v$managed_standby;

Lag metrics (official)

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

Real-time apply requirements

• Standby redo logs present
• Transport configured correctly
• MRP started with USING CURRENT LOGFILE

SELECT group#, thread#, sequence#, bytes/1024/1024 AS size_mb, status
                    FROM v$standby_log
                    ORDER BY thread#, group#;

Common symptoms and first checks

Create broker configuration

DGMGRL> CREATE CONFIGURATION dg_cfg AS
                    PRIMARY DATABASE IS PRIM
                    CONNECT IDENTIFIER IS PRIM;

                    DGMGRL> ADD DATABASE STBY AS
                    CONNECT IDENTIFIER IS STBY
                    MAINTAINED AS PHYSICAL;

                    DGMGRL> ENABLE CONFIGURATION;

Validate and inspect

DGMGRL> SHOW CONFIGURATION;
                    DGMGRL> SHOW DATABASE PRIM;
                    DGMGRL> SHOW DATABASE STBY;

                    DGMGRL> VALIDATE DATABASE PRIM;
                    DGMGRL> VALIDATE DATABASE STBY;

Operational commands

-- Switchover (planned)
                    DGMGRL> SWITCHOVER TO STBY;

                    -- Failover (unplanned)
                    DGMGRL> FAILOVER TO STBY;

                    -- Reinstate old primary (after failover)
                    DGMGRL> REINSTATE DATABASE PRIM;

                    -- Start/stop apply via broker
                    DGMGRL> EDIT DATABASE STBY SET STATE='APPLY-ON';
                    DGMGRL> EDIT DATABASE STBY SET STATE='APPLY-OFF';

Failover (unplanned) — what matters

• Decide: failover now vs attempt to recover primary.
• If potential data loss is unacceptable, stop and escalate.
• Prefer broker failover if available; else manual.

-- Broker failover
                    DGMGRL> FAILOVER TO STBY;

                    -- After failover:
                    DGMGRL> SHOW CONFIGURATION;
                    -- Then evaluate REINSTATE for old primary when ready.

Go/No-go gates (template)

Application cutover (practical)

• Prefer service-based routing (DB services) rather than hard-coded hostnames.
• Use DNS TTL discipline or VIP/SCAN abstractions.
• Plan connection draining and retry logic on app side.

-- Example: verify role and open mode
                    SELECT database_role, open_mode FROM v$database;

                    -- Example: services (if used)
                    SELECT name, network_name, pdb FROM dba_services;

Gap playbook (manual path)

1. Identify missing sequences on standby.
2. Fetch missing archivelogs from primary (copy or FAL).
3. Register logs if needed.
4. Restart MRP.

-- Find missing sequences (example approach)
                    SELECT thread#, MIN(sequence#) AS min_seq, MAX(sequence#) AS max_seq
                    FROM v$archived_log
                    GROUP BY thread#
                    ORDER BY thread#;

                    -- Register log on standby if manually copied
                    ALTER DATABASE REGISTER LOGFILE '/path/to/archivelog.arc';

                    -- Restart apply
                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Apply stuck (common causes)

Broker warnings (pattern)

DGMGRL> SHOW CONFIGURATION;
                    DGMGRL> SHOW DATABASE STBY;
                    DGMGRL> VALIDATE DATABASE STBY;