Oracle Data Guard Physical Standby Configuration

 

Oracle Data Guard Configuration (Physical Standby) – Step by Step

This example assumes:

• Oracle Database 19c

• Primary DB: PRIMDB

• Standby DB: STBYDB

• Primary Host: primary-server

• Standby Host: standby-server

• Data Guard Broker enabled (recommended by Oracle) (Oracle Docs)

---

Step 1: Verify Prerequisites

Both servers should have:

• Same Oracle version and patch level

• Oracle software installed

• Password file configured

• Listener running

• ARCHIVELOG mode enabled

• FORCE LOGGING enabled

Check on Primary:

SELECT log_mode FROM v$database;
SELECT force_logging FROM v$database;

If not enabled:

ALTER DATABASE FORCE LOGGING;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

---

Step 2: Configure Primary Database Parameters

Check unique name:

SHOW PARAMETER db_name;
SHOW PARAMETER db_unique_name;

Example:

db_name=PRIMDB
db_unique_name=PRIMDB

Configure Data Guard parameters:

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMDB,STBYDB)' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
'LOCATION=USE_DB_RECOVERY_FILE_DEST
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=PRIMDB'
SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=STBYDB ASYNC
 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=STBYDB'
SCOPE=BOTH;

ALTER SYSTEM SET FAL_SERVER=STBYDB SCOPE=BOTH;
ALTER SYSTEM SET FAL_CLIENT=PRIMDB SCOPE=BOTH;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

---

Step 3: Create Standby Redo Logs

Check online logs:

SELECT GROUP#, BYTES/1024/1024 SIZE_MB
FROM V$LOG;

Create one more standby redo log group than online redo groups.

Example:

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11
('/u01/oradata/srl11.log') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 12
('/u01/oradata/srl12.log') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 13
('/u01/oradata/srl13.log') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 14
('/u01/oradata/srl14.log') SIZE 500M;

Oracle recommends standby redo logs for real-time apply. (Oracle Docs)

---

Step 4: Configure Listener and TNS

listener.ora

Ensure listener is running on both servers.

lsnrctl status

tnsnames.ora

On both servers:

PRIMDB =
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=primary-server)(PORT=1521))
   (CONNECT_DATA=
      (SERVICE_NAME=PRIMDB)
   )
 )

STBYDB =
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=standby-server)(PORT=1521))
   (CONNECT_DATA=
      (SERVICE_NAME=STBYDB)
   )
 )

Test:

tnsping PRIMDB
tnsping STBYDB

---

Step 5: Create Password File

Copy password file from primary to standby.

Primary:

orapwd file=$ORACLE_HOME/dbs/orapwPRIMDB password=oracle entries=10

Copy to standby:

scp orapwPRIMDB oracle@standby-server:$ORACLE_HOME/dbs/orapwSTBYDB

---

Step 6: Create Standby Parameter File

On primary:

CREATE PFILE='/tmp/initSTBYDB.ora' FROM SPFILE;

Copy to standby.

Modify:

db_name=PRIMDB
db_unique_name=STBYDB

FAL_SERVER=PRIMDB
FAL_CLIENT=STBYDB

LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMDB,STBYDB)'

Create spfile:

CREATE SPFILE FROM PFILE='/tmp/initSTBYDB.ora';

---

Step 7: Create Standby Using RMAN Duplicate

Start standby instance:

STARTUP NOMOUNT;

From standby server:

rman target sys/password@PRIMDB auxiliary sys/password@STBYDB

Execute:

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;

This copies database files and creates the standby automatically. Oracle recommends RMAN Active Duplicate for standby creation. (Oracle Docs)

---

Step 8: Start Managed Recovery

On standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE
DISCONNECT FROM SESSION;

Verify:

SELECT PROCESS, STATUS
FROM V$MANAGED_STANDBY;

---

Step 9: Verify Log Shipping

On primary:

ALTER SYSTEM SWITCH LOGFILE;

On standby:

SELECT SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;

Check transport status:

SELECT DEST_NAME, STATUS
FROM V$ARCHIVE_DEST_STATUS;

---

Step 10: Enable Data Guard Broker (Recommended)

On both databases:

ALTER SYSTEM SET DG_BROKER_START=TRUE;

---

Step 11: Create Broker Configuration

Connect using DGMGRL:

dgmgrl sys/password@PRIMDB

Create configuration:

CREATE CONFIGURATION dgconfig
AS PRIMARY DATABASE IS PRIMDB
CONNECT IDENTIFIER IS PRIMDB;

Add standby:

ADD DATABASE STBYDB
AS CONNECT IDENTIFIER IS STBYDB
MAINTAINED AS PHYSICAL;

Enable:

ENABLE CONFIGURATION;

Oracle recommends Broker for management, monitoring, switchover, and failover operations. (Oracle Docs)

---

Step 12: Validate Configuration

SHOW CONFIGURATION;

Expected:

Configuration Status:
SUCCESS

Validate standby:

VALIDATE DATABASE STBYDB;

Oracle documentation recommends checking SHOW CONFIGURATION and VALIDATE DATABASE after setup. (Oracle Docs)

---

Step 13: Test Switchover

Check readiness:

SHOW CONFIGURATION;

Perform switchover:

SWITCHOVER TO STBYDB;

Verify roles:

SELECT DATABASE_ROLE FROM V$DATABASE;

Broker automates the role transition process and validates prerequisites before switchover. (Oracle Docs)

---

Useful Verification Queries

Primary / Standby Role

SELECT DATABASE_ROLE, OPEN_MODE
FROM V$DATABASE;

Archive Gap

SELECT * FROM V$ARCHIVE_GAP;

Data Guard Statistics

SELECT NAME, VALUE
FROM V$DATAGUARD_STATS;

Broker Status

dgmgrl
SHOW CONFIGURATION;
SHOW DATABASE VERBOSE PRIMDB;
SHOW DATABASE VERBOSE STBYDB;

---

Recommended Production Features

• Data Guard Broker

• Flashback Database

• Fast-Start Failover (FSFO)

• Real-Time Apply

• Standby Redo Logs

• RMAN backup from standby

• Active Data Guard (licensed feature) for read-only reporting (Oracle Docs)

If you're using Oracle 19c on Linux, I can also provide a complete end-to-end lab setup with actual listener.ora, tnsnames.ora, RMAN duplicate scripts, and Broker commands for a two-node environment.