Oracle Data Guard Configuration (Physical Standby) – Step by Step
This example assumes:
Oracle Database 19c
Primary DB:
PRIMDBStandby DB:
STBYDBPrimary Host:
primary-serverStandby Host:
standby-serverData 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.
