Data Guard & RMAN Configuration

Oracle 19c Database

Data Guard & RMAN Configuration

Step-by-Step Reference Guide for DBAs

Database Version	Guide Type	Scope
Oracle 19c (19.3+)	Technical / DBA	Physical Standby + RMAN

 

Prerequisites

Before starting, ensure the following are in place on both primary and standby servers:

 

•       Oracle 19c software installed on both primary and standby hosts

•       Same Oracle Home path on both servers (recommended)

•       Network connectivity between primary and standby (port 1521 open)

•       Identical OS users (oracle) and groups (dba, oinstall) on both hosts

•       ARCHIVELOG mode enabled on the primary database

•       Fast Recovery Area (FRA) configured on both hosts

•       Password file (orapwSID) present on primary — will be copied to standby

 

NOTE	All commands below use DB_UNIQUE_NAME=PROD for the primary and DB_UNIQUE_NAME=STDBY for the standby. Replace these with your actual values.

 

Step 1 — Enable ARCHIVELOG Mode on Primary

STEP 1

Enable ARCHIVELOG Mode on Primary

 

If the primary is already in ARCHIVELOG mode, skip to Step 2. Verify first:

 

sqlplus / as sysdba

-- Check current mode

SELECT LOG_MODE FROM V$DATABASE;

-- If NOARCHIVELOG, enable it:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

-- Confirm

ARCHIVE LOG LIST;

 

TIP	Set LOG_ARCHIVE_DEST_1 to USE_DB_RECOVERY_FILE_DEST for automatic archive management using the Fast Recovery Area.

 

Step 2 — Configure Primary Database Parameters

STEP 2

Set Data Guard Init Parameters on Primary

 

Edit the SPFILE parameters on the primary database. Connect as SYSDBA and run:

 

sqlplus / as sysdba

ALTER SYSTEM SET DB_UNIQUE_NAME='PROD' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STDBY)' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=

'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=PROD' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=

'SERVICE=STDBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=STDBY' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

ALTER SYSTEM SET FAL_SERVER=STDBY SCOPE=BOTH;

ALTER SYSTEM SET FAL_CLIENT=PROD SCOPE=BOTH;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;

 

Key parameters explained:

 

Parameter	Value / Example	Description
DB_UNIQUE_NAME	PROD	Unique name per site
LOG_ARCHIVE_CONFIG	DG_CONFIG=(PROD,STDBY)	Lists all DG members
LOG_ARCHIVE_DEST_2	SERVICE=STDBY ASYNC	Redo ship to standby
FAL_SERVER	STDBY	Gap resolution server
STANDBY_FILE_MANAGEMENT	AUTO	Auto-adds datafiles

 

Step 3 — Configure TNS / Listener on Both Hosts

STEP 3

Set Up TNS Entries and Listeners

 

On both primary and standby hosts, edit $ORACLE_HOME/network/admin/tnsnames.ora:

 

# tnsnames.ora (both hosts)

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary-host)(PORT = 1521))

(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = PROD)))

STDBY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby-host)(PORT = 1521))

(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = STDBY)))

# listener.ora (both hosts — add STATIC_SERVICE_NAME)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))))

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PROD/STDBY)   # change per host

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

(SID_NAME = PROD/STDBY)))

 

Reload listeners on both hosts:

 

lsnrctl reload

lsnrctl status

 

Step 4 — Copy Password File to Standby

STEP 4

Copy Oracle Password File

 

Data Guard uses the password file for authentication between primary and standby. Copy it from primary to standby:

 

# On PRIMARY host — locate password file

ls $ORACLE_HOME/dbs/orapwPROD

# SCP to standby (adjust path/hostname)

scp $ORACLE_HOME/dbs/orapwPROD oracle@standby-host:$ORACLE_HOME/dbs/orapwSTDBY

# On STANDBY — verify file exists

ls -lh $ORACLE_HOME/dbs/orapwSTDBY

 

IMPORTANT

The password for the SYS user must be identical on both primary and standby. If you change it on primary later, copy the password file again.

 

Step 5 — Prepare Standby Database (NOMOUNT)

STEP 5

Start Standby Instance in NOMOUNT

 

On the STANDBY host, create a minimal init.ora (RMAN DUPLICATE will replace it). Then start the instance in NOMOUNT:

 

# Create minimal pfile on STANDBY at $ORACLE_HOME/dbs/initSTDBY.ora

DB_NAME=PROD

DB_UNIQUE_NAME=STDBY

# Start instance NOMOUNT

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initSTDBY.ora';

 

Step 6 — Duplicate Database via RMAN (Active Duplication)

STEP 6

RMAN DUPLICATE — Create Physical Standby

 

This is the most important step. RMAN streams the primary database directly to the standby over the network — no backup files needed. Run from the PRIMARY host:

 

rman TARGET sys/SysPassword@PROD AUXILIARY sys/SysPassword@STDBY

RMAN> DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

DORECOVER

SPFILE

SET "db_unique_name"="STDBY"

SET "log_archive_dest_1"=

"LOCATION=USE_DB_RECOVERY_FILE_DEST

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=STDBY"

SET "log_archive_dest_2"=

"SERVICE=PROD ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=PROD"

SET "fal_server"="PROD"

SET "fal_client"="STDBY"

NOFILENAMECHECK;

 

RMAN will:

1.    Connect to both TARGET (primary) and AUXILIARY (standby)

2.    Back up primary datafiles and stream directly to standby

3.    Apply all changes and set standby to MOUNT mode

4.    Configure the SPFILE on standby with correct DG parameters

 

TIME	Duplication time depends on database size. A 500 GB database over 10 Gbps takes roughly 15-30 minutes. Monitor with V$SESSION_LONGOPS.

 

Step 7 — Add Standby Redo Logs (SRL)

STEP 7

Create Standby Redo Log Groups

 

Standby Redo Logs (SRL) are mandatory in Oracle 19c for real-time apply. The number of SRL groups must be (online redo log groups + 1) per thread.

 

-- First, check how many ORL groups exist on primary

SELECT GROUP#, MEMBERS, BYTES/1048576 MB FROM V$LOG;

-- On STANDBY — add SRL groups (one more than ORL count)

-- Example: 3 ORL groups → add 4 SRL groups

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 4 ('/oradata/stdby/srl04.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 ('/oradata/stdby/srl05.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 6 ('/oradata/stdby/srl06.log') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 7 ('/oradata/stdby/srl07.log') SIZE 200M;

-- Verify SRL groups

SELECT GROUP#, THREAD#, SEQUENCE#, BYTES/1048576 MB, STATUS

FROM   V$STANDBY_LOG;

 

RULE	SRL size must match ORL size. If online redo logs are 200M, SRLs must be 200M. Also add SRLs on PRIMARY for future role transitions.

 

Step 8 — Start Managed Recovery Process (MRP)

STEP 8

Start MRP — Begin Redo Apply

 

The Managed Recovery Process (MRP) reads standby redo logs and applies redo data to the standby database. Start it on the STANDBY:

 

-- On STANDBY — start real-time apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

USING CURRENT LOGFILE DISCONNECT FROM SESSION;

-- Verify MRP is running

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#

FROM   V$MANAGED_STANDBY

WHERE  PROCESS = 'MRP0';

-- Check apply and transport lag

SELECT NAME, VALUE, DATUM_TIME

FROM   V$DATAGUARD_STATS

WHERE  NAME IN ('apply lag','transport lag');

-- Check database role and mode

SELECT DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE, PROTECTION_MODE

FROM   V$DATABASE;

 

Expected output after successful start:

 

PROCESS  STATUS     THREAD#  SEQUENCE#

-------- ---------  -------  ---------

MRP0     APPLYING_LOG   1      45

NAME              VALUE    DATUM_TIME

------------      ------   --------------------

transport lag     +00 00:00:01  2024-01-15 09:30:00

apply lag         +00 00:00:02  2024-01-15 09:30:00

 

Step 9 — Configure Data Guard Broker (DGMGRL)

STEP 9

Set Up Data Guard Broker

 

Data Guard Broker (DGMGRL) automates failover, switchover, and health monitoring. Enable it on both primary and standby:

 

-- Enable broker on PRIMARY and STANDBY (both)

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

-- Connect to broker from PRIMARY host

dgmgrl sys/SysPassword@PROD

-- Create broker configuration

DGMGRL> CREATE CONFIGURATION 'DG_CONFIG'

AS PRIMARY DATABASE IS 'PROD'

CONNECT IDENTIFIER IS PROD;

-- Add standby to configuration

DGMGRL> ADD DATABASE 'STDBY'

AS CONNECT IDENTIFIER IS STDBY

MAINTAINED AS PHYSICAL;

-- Enable the configuration

DGMGRL> ENABLE CONFIGURATION;

-- Verify configuration health

DGMGRL> SHOW CONFIGURATION;

DGMGRL> SHOW DATABASE VERBOSE PROD;

DGMGRL> SHOW DATABASE VERBOSE STDBY;

 

Expected output from SHOW CONFIGURATION:

 

Configuration - DG_CONFIG

Protection Mode: MaxPerformance

Members:

PROD  - Primary database

STDBY - Physical standby database

Fast-Start Failover: Disabled

Configuration Status: SUCCESS   (status updated X seconds ago)

 

Step 10 — Configure RMAN Backup from Standby

STEP 10

RMAN Offload Backup — Run from Standby

 

Running RMAN backups from the physical standby offloads I/O and CPU from the production primary. The backups are usable for primary recovery.

 

10.1 — Set up RMAN Recovery Catalog (Recommended)

-- Create catalog schema on a separate catalog DB

CREATE TABLESPACE rman_ts DATAFILE '/oradata/catdb/rman01.dbf' SIZE 500M AUTOEXTEND ON;

CREATE USER rman IDENTIFIED BY CatPassword DEFAULT TABLESPACE rman_ts;

GRANT RECOVERY_CATALOG_OWNER TO rman;

-- Initialize the catalog

rman CATALOG rman/CatPassword@CATDB

RMAN> CREATE CATALOG;

-- Register primary database

rman TARGET sys/SysPassword@PROD CATALOG rman/CatPassword@CATDB

RMAN> REGISTER DATABASE;

 

10.2 — Full Database Backup from Standby

rman TARGET sys/SysPassword@PROD CATALOG rman/CatPassword@CATDB

RMAN> CONNECT AUXILIARY sys/SysPassword@STDBY;

-- Full compressed backup from standby

RMAN> BACKUP DEVICE TYPE DISK

SECTION SIZE 32G

AS COMPRESSED BACKUPSET

DATABASE

FORMAT '/backup/rman/%d_%T_%s_%p.bkp'

TAG 'FULL_STDBY_BKP';

-- Backup archived logs and delete applied logs

RMAN> BACKUP ARCHIVELOG ALL

FORMAT '/backup/rman/arch_%d_%T_%s_%p.bkp'

DELETE INPUT;

 

10.3 — Incremental Backup Strategy

-- Level 0 (full) backup — run weekly

RMAN> BACKUP INCREMENTAL LEVEL 0

DATABASE FORMAT '/backup/rman/L0_%d_%T_%s.bkp'

TAG 'INC_L0_WEEKLY';

-- Level 1 (incremental) backup — run daily

RMAN> BACKUP INCREMENTAL LEVEL 1

DATABASE FORMAT '/backup/rman/L1_%d_%T_%s.bkp'

TAG 'INC_L1_DAILY';

-- Validate backup integrity

RMAN> VALIDATE DATABASE;

RMAN> LIST BACKUP SUMMARY;

 

BEST PRACTICE

Schedule backups using OS cron or Oracle Scheduler. Always test recovery using RESTORE DATABASE PREVIEW before an actual disaster.

 

Step 11 — Switchover and Failover Operations

STEP 11

Switchover / Failover via DGMGRL

 

11.1 — Planned Switchover (Zero Data Loss)

Use switchover for planned maintenance. It gracefully transfers the primary role to the standby:

 

dgmgrl sys/SysPassword@PROD

-- Verify everything is healthy first

DGMGRL> SHOW CONFIGURATION;

DGMGRL> VALIDATE DATABASE STDBY;

-- Perform switchover (STDBY becomes new primary)

DGMGRL> SWITCHOVER TO STDBY;

-- Verify roles after switchover

DGMGRL> SHOW CONFIGURATION;

-- Expected output:

-- STDBY  - Primary database

-- PROD   - Physical standby database

 

11.2 — Failover (Unplanned — Primary Lost)

Use failover only when the primary is completely unavailable and cannot be recovered quickly:

 

dgmgrl sys/SysPassword@STDBY

-- Failover (STDBY becomes primary — data loss possible)

DGMGRL> FAILOVER TO STDBY;

-- After failover, re-instantiate the old primary as new standby

DGMGRL> REINSTATE DATABASE PROD;

 

WARNING

Failover is a one-way operation without Fast-Start Failover configured. Always prefer SWITCHOVER for planned activities. Run VALIDATE DATABASE before any role change.

 

Step 12 — Protection Modes

STEP 12

Choose the Right Protection Mode

 

Data Guard offers three protection modes. Choose based on your RPO (Recovery Point Objective) and performance requirements:

 

Mode	Redo Transport	Data Loss Risk	Performance
Maximum Protection	SYNC + AFFIRM	Zero	Highest impact
Maximum Availability	SYNC + AFFIRM	Zero (degrades gracefully)	Medium impact
Maximum Performance	ASYNC	Seconds of data	Lowest impact

 

Set protection mode on the primary:

 

-- Set Maximum Availability (recommended for most production systems)

sqlplus / as sysdba

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- OR via DGMGRL:

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

 

Step 13 — Health Check Queries

STEP 13

Monitor Data Guard and RMAN Status

 

-- ===== ON PRIMARY =====

-- Archive destination status

SELECT DEST_ID, STATUS, TARGET, DESTINATION, DB_UNIQUE_NAME, ERROR

FROM   V$ARCHIVE_DEST_STATUS WHERE TARGET='STANDBY';

-- Gap detection

SELECT * FROM V$ARCHIVE_GAP;

-- ===== ON STANDBY =====

-- Last 10 applied archive logs

SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME

FROM   V$ARCHIVED_LOG

WHERE  DEST_ID=1 ORDER BY SEQUENCE# DESC

FETCH FIRST 10 ROWS ONLY;

-- MRP process status

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#

FROM   V$MANAGED_STANDBY;

-- DG stats (apply lag, transport lag)

SELECT NAME, VALUE, DATUM_TIME

FROM   V$DATAGUARD_STATS;

-- ===== RMAN CHECKS =====

-- List all backups

RMAN> LIST BACKUP SUMMARY;

-- Check for expired backups

RMAN> CROSSCHECK BACKUP;

RMAN> REPORT OBSOLETE;

 

Quick Reference Summary

Use this checklist to verify a complete Data Guard + RMAN setup:

 

5.    Primary is in ARCHIVELOG mode and FRA is configured

6.    All DG init.ora parameters set on primary and standby

7.    TNS entries and static listeners configured on both hosts

8.    Password file copied from primary to standby

9.    Standby created using RMAN DUPLICATE FROM ACTIVE DATABASE

10.  Standby redo logs (SRL) added on both primary and standby

11.  MRP started with USING CURRENT LOGFILE on standby

12.  Data Guard Broker configured and showing SUCCESS

13.  RMAN recovery catalog created and primary/standby registered

14.  Scheduled backups tested from standby and validated

 

FINAL TIP

Always run DGMGRL> VALIDATE DATABASE STDBY before any planned switchover, and periodically run RMAN> RESTORE DATABASE PREVIEW to verify recoverability.