Oracle Data Guard Setup Guide - Standby Database Configuration

Oracle Data Guard Setup Guide

Comprehensive guide to Oracle Data Guard physical standby database setup and management.

Prerequisites Checklist

-- On Primary: Check archive log mode
SELECT LOG_MODE FROM V$DATABASE;

-- Enable archive log mode if needed
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Enable force logging
ALTER DATABASE FORCE LOGGING;

-- Check force logging
SELECT FORCE_LOGGING FROM V$DATABASE;

---

Primary Database Configuration

Configure Initialization Parameters

-- Set unique name
ALTER SYSTEM SET DB_UNIQUE_NAME='PRIMARY' SCOPE=SPFILE;

-- Configure log archive destination for local
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY' SCOPE=SPFILE;

-- Configure log archive destination for standby
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY' SCOPE=SPFILE;

-- Enable archive dest state
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;

-- Remote archive log destinations
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)' SCOPE=SPFILE;

-- Standby file management
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;

-- FAL (Fetch Archive Log) configuration
ALTER SYSTEM SET FAL_SERVER='STANDBY' SCOPE=SPFILE;
ALTER SYSTEM SET FAL_CLIENT='PRIMARY' SCOPE=SPFILE;

-- Log archive format
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

Create Standby Redo Logs

-- Add standby redo logs (one more group than online redo logs)
-- Check existing redo log sizes
SELECT GROUP#, BYTES/1024/1024 AS size_mb FROM V$LOG;

-- Add standby redo log groups
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 200M;

-- Verify
SELECT GROUP#, TYPE, MEMBER FROM V$LOGFILE ORDER BY GROUP#;

---

Network Configuration

Listener.ora (Both Servers)

# Primary server
LISTENER_PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
  )

SID_LIST_LISTENER_PRIMARY =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = primary)
      (ORACLE_HOME = /u01/app/oracle/product/19c/db_1)
      (SID_NAME = orcl)
    )
  )

Tnsnames.ora (Both Servers)

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

---

Create Physical Standby

Method 1: RMAN Duplicate

# On Standby: Create required directories
mkdir -p /u01/oradata/standby
mkdir -p /u01/app/oracle/fast_recovery_area

# Start listener
lsnrctl start

# Create password file (copy from primary or create)
orapwd file=$ORACLE_HOME/dbs/orapwstandby password=oracle entries=10

# Create pfile for standby
# Edit and adjust parameters for standby

-- On Standby: Create pfile with these changes
*.db_unique_name='STANDBY'
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
*.log_archive_dest_2='SERVICE=PRIMARY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'

-- Start standby in nomount
STARTUP NOMOUNT PFILE='?/dbs/initstandby.ora';

# Run RMAN duplicate from primary
rman TARGET sys/password@PRIMARY AUXILIARY sys/password@STANDBY

RMAN> DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET DB_UNIQUE_NAME='STANDBY'
    SET LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
    SET FAL_SERVER='PRIMARY'
    SET FAL_CLIENT='STANDBY'
  NOFILENAMECHECK;

Method 2: Backup-Based

# On Primary: Create backup
rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/standby_ctl.bkp';

# Copy backups to standby server
scp /backup/* standby_host:/backup/

# On Standby: Restore
rman target /
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/backup/standby_ctl.bkp';
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;

---

Start Managed Recovery

Real-Time Apply (Recommended)

-- On Standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

-- Check status
SELECT PROCESS, STATUS, SEQUENCE#, BLOCK#, DELAY_MINS
FROM V$MANAGED_STANDBY;

Standard Apply

-- Apply archived logs only
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Stop Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

---

Monitoring Data Guard

Check Sync Status

-- On Primary: Check transport lag
SELECT DEST_ID, STATUS, DESTINATION, ERROR, ARCHIVED_SEQ#, APPLIED_SEQ#
FROM V$ARCHIVE_DEST
WHERE DEST_ID IN (1, 2);

-- Check current sequence
SELECT THREAD#, SEQUENCE#, ARCHIVED, APPLIED FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
ORDER BY SEQUENCE# DESC
FETCH FIRST 10 ROWS ONLY;

Standby Database Status

-- On Standby: Check apply status
SELECT DATABASE_ROLE, OPEN_MODE, PROTECTION_MODE, SWITCHOVER_STATUS
FROM V$DATABASE;

-- Check recovery progress
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#
FROM V$MANAGED_STANDBY
WHERE PROCESS IN ('MRP0', 'RFS', 'ARCH');

-- Check apply lag
SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS
WHERE NAME IN ('transport lag', 'apply lag');

Archive Gap Detection

-- Check for gaps
SELECT * FROM V$ARCHIVE_GAP;

-- On Primary: Find missing archives
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#
FROM V$ARCHIVE_GAP;

---

Data Guard Broker (Optional)

Enable Broker

-- On both Primary and Standby
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

Configure Using DGMGRL

# Connect to broker
dgmgrl sys/password@PRIMARY

# Create configuration
DGMGRL> CREATE CONFIGURATION 'DG_Config' AS
        PRIMARY DATABASE IS 'PRIMARY'
        CONNECT IDENTIFIER IS PRIMARY;

# Add standby
DGMGRL> ADD DATABASE 'STANDBY' AS
        CONNECT IDENTIFIER IS STANDBY
        MAINTAINED AS PHYSICAL;

# Enable configuration
DGMGRL> ENABLE CONFIGURATION;

# Show configuration
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE 'PRIMARY';
DGMGRL> SHOW DATABASE 'STANDBY';

---

Switchover

Prerequisites Check

-- On Primary
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
-- Should be "TO STANDBY" or "SESSIONS ACTIVE"

-- On Standby
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
-- Should be "NOT ALLOWED" until primary switches

Perform Switchover

-- Step 1: On Primary - Convert to standby
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

-- Step 2: On Standby - Convert to primary
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP;

Using Broker

dgmgrl sys/password@PRIMARY
DGMGRL> SWITCHOVER TO 'STANDBY';

---

Failover (When Primary is Lost)

-- On Standby: Cancel recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Activate standby
ALTER DATABASE ACTIVATE STANDBY DATABASE;

-- Open database
ALTER DATABASE OPEN;

-- Note: After failover, old primary requires FLASHBACK DATABASE
-- or must be rebuilt from new primary

Using Broker

dgmgrl sys/password@STANDBY
DGMGRL> FAILOVER TO 'STANDBY';

---

Reinstate Failed Primary

-- On Old Primary (after failover): Use flashback
SHUTDOWN ABORT;
STARTUP MOUNT;

-- Flashback to before failover
FLASHBACK DATABASE TO RESTORE POINT BEFORE_FAILOVER;

-- Or flashback to SCN
FLASHBACK DATABASE TO SCN &scn;

-- Convert to standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

-- Start recovery
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

---

Protection Modes

-- Maximum Performance (default, async)
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Availability (sync, failover on failure)
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Protection (sync, primary shuts down if standby unavailable)
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

-- Check current mode
SELECT PROTECTION_MODE FROM V$DATABASE;

---

Related Resources

• RMAN Commands Cheat Sheet
• ORA-16038: Log Sequence Cannot Be Archived
• ORA-01034: Oracle Not Available
• Administration Scripts