Environment Architecture

The deployment involves a primary cluster operating in an active-passive configuration and a two-node physical standby cluster. The primary database instances reside on prod-node1 (active) and prod-node2 (passive). The standby instances are hosted on std-node1 and std-node2.

Role	Hostname	DB Unique Name	IP Address
Primary 1	prod-node1	PROD	192.168.10.101
Primary 2	prod-node2	PROD	192.168.10.102
Standby 1	std-node1	STDBY_A	192.168.10.201
Standby 2	std-node2	STDBY_B	192.168.10.202

1. Enable Force Logging on Primary

Ensure all changes are logged to support standby synchronization.

ALTER DATABASE FORCE LOGGING;
SELECT name, log_mode, force_logging FROM v$database;

2. Propagate Password Files

Copy the authentication file from the primary node to all standby nodes to ensure consistent SYS credentials.

cd $ORACLE_HOME/dbs
scp orapwPROD oracle@192.168.10.201:$ORACLE_HOME/dbs/orapwSTDBY_A
scp orapwPROD oracle@192.168.10.202:$ORACLE_HOME/dbs/orapwSTDBY_B

3. Configure Standby Redo Logs

Create standby redo log groups on the primary database to support real-time apply. Ensure the size matches the online redo logs.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/u01/oradata/PROD/sredo11a.log', '/u01/oradata/PROD/sredo11b.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/u01/oradata/PROD/sredo12a.log', '/u01/oradata/PROD/sredo12b.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/u01/oradata/PROD/sredo13a.log', '/u01/oradata/PROD/sredo13b.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/u01/oradata/PROD/sredo14a.log', '/u01/oradata/PROD/sredo14b.log') SIZE 200M;

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

4. Initialize Parameter Configuration

Ganerate a text parameter file from the server parameter file, modify it for Data Guard settings, and distribute it across all nodes.

CREATE PFILE FROM SPFILE;

4.1 Parameter Adjustments

Edit the PFILE to include Data Guard specific parameters. Note the unique names and archive destinations.

*.db_name='PROD'
*.db_unique_name='PROD'
*.log_archive_config='DG_CONFIG=(PROD,STDBY_A,STDBY_B)'
*.log_archive_dest_1='LOCATION=/u01/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD'
*.log_archive_dest_2='SERVICE=STDBY_A LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY_A'
*.log_archive_dest_3='SERVICE=STDBY_B LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY_B'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.fal_server='STDBY_A','STDBY_B'
*.fal_client='PROD'
*.standby_file_management='AUTO'
*.db_file_name_convert='/u01/oradata/STDBY_A','/u01/oradata/PROD','/u01/oradata/STDBY_B','/u01/oradata/PROD'
*.log_file_name_convert='/u01/oradata/STDBY_A','/u01/oradata/PROD','/u01/oradata/STDBY_B','/u01/oradata/PROD'
*.dg_broker_start=TRUE

4.2 Distribute and Activate Parameters

Copy the modified PFILE to all nodes, renaming it appropriately for each unique name, then recreate the SPFILE.

scp initPROD.ora oracle@prod-node2:$ORACLE_HOME/dbs/initPROD.ora
scp initPROD.ora oracle@std-node1:$ORACLE_HOME/dbs/initSTDBY_A.ora
scp initPROD.ora oracle@std-node2:$ORACLE_HOME/dbs/initSTDBY_B.ora

On each node:

SHUTDOWN IMMEDIATE;
CREATE SPFILE FROM PFILE;
STARTUP;
SHOW PARAMETER spfile;

5. Network Configuration

5.1 Static Listener Registration

Update listener.ora on all four nodes to include static service registration for the database and the Data Guard broker.

Primary Node Example:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PROD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
      (SID_NAME = PROD)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PROD_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
      (SID_NAME = PROD)
    )
  )

Standby Node Example:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = STDBY_A)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
      (SID_NAME = PROD)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = STDBY_A_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
      (SID_NAME = PROD)
    )
  )

Reload the listener after changes:

lsnrctl reload

5.2 TNS Names Configuration

Configure tnsnames.ora on all nodes to resolve the primary and standby services.

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.101)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = PROD))
  )

STDBY_A =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.201)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = STDBY_A))
  )

STDBY_B =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.202)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = STDBY_B))
  )

6. Standby Directory Structure

Create the necessary directory paths on the standby servers for diagnostics, administration, and data files.

mkdir -p /u01/app/oracle/admin/STDBY_A/adump
mkdir -p /u01/oradata/STDBY_A
mkdir -p /u01/oradata/arch
chown -R oracle:oinstall /u01/app/oracle
chown -R oracle:oinstall /u01/oradata
chmod -R 775 /u01/app/oracle
chmod -R 775 /u01/oradata

Repeat for STDBY_B adjusting paths accordingly.

7. Instantiate Standby Databases

Start the standby instances in NOMOUNT mode using the created SPFILE.

-- On std-node1 and std-node2
STARTUP NOMOUNT;

Use RMAN to duplicate the active primary database to the standby locations.

# On std-node1
rman target sys/SecurePass@PROD auxiliary sys/SecurePass@STDBY_A
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;

# On std-node2
rman target sys/SecurePass@PROD auxiliary sys/SecurePass@STDBY_B
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;

Verify the state is MOUNTED:

SELECT status FROM v$instance;

8. Enable Log Apply

Start the managed recovery process on the standby nodes.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ARCHIVE LOG LIST;

9. Set Protection Mode

Configure the primary database for Maximum Availability.

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

SELECT name, log_mode, open_mode, protection_mode FROM v$database;

10. Data Guard Broker Configuration

Enable the broker process and configure the topology using DGMGRL.

ALTER SYSTEM SET dg_broker_start = TRUE;

Connect to the broker and create the configuration:

dgmgrl sys/SecurePass@PROD

DGMGRL> CREATE CONFIGURATION DGConfig AS PRIMARY DATABASE IS PROD CONNECT IDENTIFIER IS PROD;
DGMGRL> ADD DATABASE STDBY_A AS CONNECT IDENTIFIER IS STDBY_A MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE STDBY_B AS CONNECT IDENTIFIER IS STDBY_B MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;

10.1 Troubleshooting Property Inconsistencies

If warnings regarding DbFileNameConvert or LogFileNameConvert appear, update the properties to match the database settings.

DGMGRL> EDIT DATABASE PROD SET PROPERTY DbFileNameConvert = '/u01/oradata/STDBY_A, /u01/oradata/PROD, /u01/oradata/STDBY_B, /u01/oradata/PROD';
DGMGRL> EDIT DATABASE PROD SET PROPERTY LogFileNameConvert = '/u01/oradata/STDBY_A, /u01/oradata/PROD, /u01/oradata/STDBY_B, /u01/oradata/PROD';
DGMGRL> EDIT DATABASE STDBY_A SET PROPERTY DbFileNameConvert = '/u01/oradata/PROD, /u01/oradata/STDBY_A, /u01/oradata/STDBY_B, /u01/oradata/STDBY_A';
DGMGRL> EDIT DATABASE STDBY_A SET PROPERTY LogFileNameConvert = '/u01/oradata/PROD, /u01/oradata/STDBY_A, /u01/oradata/STDBY_B, /u01/oradata/STDBY_A';

Restart the instances to apply changes and verify the configuration status.

DGMGRL> SHOW CONFIGURATION;

11. Failover and Reinstate Testing

To test high availability, perform a failover to one of the standbys.

DGMGRL> FAILOVER TO STDBY_A;

Once the former primary is back online, reinstate it to return to the original topology.

# On former primary node
sqlplus / as sysdba
STARTUP MOUNT;

DGMGRL> REINSTATE DATABASE PROD;
DGMGRL> SHOW CONFIGURATION;

If reinstatement fails due to flashback issues, ensure Flashback Database is enabled on all nodes before attempting future failovers. If necessary, rebuild the standby using the RMAN duplicate method outlined previously.