Manual Cross-Region Data Guard Setup for Oracle RAC DBCS in Oracle Cloud

Oracle Cloud Infrastructure (OCI) Database Cloud Service (DBCS) offers automated Data Guard setup within the same region, cross-region DR topologies currently require a manual approach. This post will guide you through the detailed, step-by-step process of manually configuring Oracle Data Guard between two Oracle RAC DBCS instances in different OCI regions.

This manual setup ensures business continuity even in the event of a regional outage, providing a critical layer of resilience for your Oracle databases.


Architecture Overview

We will be setting up a Physical Standby Data Guard configuration with:

  • Primary Database: A 2-node Oracle RAC DBCS instance in the Phoenix (PHX) region. This primary database already has a Local Standby (PROD_LDR) configured via the OCI Cloud Console.
  • Cross-Region Standby Database: A 2-node Oracle RAC DBCS instance in the Ashburn (IAD) region. This guide focuses on manually adding this third database as a cross-region standby.

Note on OCI Console Limitations: The OCI Cloud Console's automated DR tooling allows for the creation of one local standby. To establish an additional, cross-region standby, manual configuration is necessary as the console does not support creating multiple standbys or cross-region topologies directly for a single primary.




Essential Information for Data Guard Setup

Before proceeding with the Data Guard configuration, it's crucial to consolidate all the necessary details for your database instances. This table provides a clear overview of the key parameters for your primary and standby databases.

Parameter

Primary Database (PROD_PRIM) - Phoenix

Local Standby Database (PROD_LDR) - Phoenix

Cross-Region Standby Database (PROD_CRDR) - Ashburn

Database Name

PROD

PROD

PROD

DB Unique Name

PROD_PRIM

PROD_LDR

PROD_CRDR

Instance Names

PROD01, PROD02

PROD01, PROD02

PROD01, PROD02

Node 1 IP (Private)

10.1.1.1

10.1.1.100

10.2.2.1

Node 2 IP (Private)

10.1.1.2

10.1.1.101

10.2.2.2

Service Name (Global DB Name)

prod_prim.db.phx.oraclevcn.com

prod_ldr.db.phx.oraclevcn.com

prod_crdr.db.iad.oraclevcn.com

Oracle Home

/u01/app/oracle/product/19.0.0.0/dbhome_1

/u01/app/oracle/product/19.0.0.0/dbhome_1

/u01/app/oracle/product/19.0.0.0/dbhome_1

Grid Home

/u01/app/19.0.0.0/grid

/u01/app/19.0.0.0/grid

/u01/app/19.0.0.0/grid

TDE Wallet Path

/opt/oracle/dcs/commonstore/wallets/tde/

/opt/oracle/dcs/commonstore/wallets/tde/

/opt/oracle/dcs/commonstore/wallets/tde/

ASM Disk Group (Data)

+DATA

+DATA

+DATA

ASM Disk Group (RECO)

+RECO

+RECO

+RECO

Step 1: Configure Oracle Network Files

This step ensures that all nodes in both the primary and standby RAC clusters can communicate with each other's database services. You will perform these modifications on all nodes in both the Primary (Phoenix) and Standby (Ashburn) RAC clusters.

Action Required: Perform these modifications on all nodes in both the Primary (Phoenix) and Cross-Region Standby (Ashburn) clusters.

1.1 Modify tnsnames.ora

As the oracle user, edit $ORACLE_HOME/network/admin/tnsnames.ora to ensure all nodes can resolve each other's database services.

# On all nodes of PROD_PRIM (Phoenix) and PROD_CRDR (Ashburn) clusters, as oracle user

PROD_PRIM_SVC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521)(CONNECT_TIMEOUT=60)(RECV_BUF_SIZE=10485760)(SEND_BUF_SIZE=10485760))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.2)(PORT = 1521)(CONNECT_TIMEOUT=60)(RECV_BUF_SIZE=10485760)(SEND_BUF_SIZE=10485760))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod_prim.db.phx.oraclevcn.com)
    )
  )

PROD_CRDR_SVC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.1)(PORT = 1521)(CONNECT_TIMEOUT=60)(RECV_BUF_SIZE=10485760)(SEND_BUF_SIZE=10485760))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.2)(PORT = 1521)(CONNECT_TIMEOUT=60)(RECV_BUF_SIZE=10485760)(SEND_BUF_SIZE=10485760))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod_crdr.db.iad.oraclevcn.com)
    )
  )

1.2 Modify listener.ora

Next, set up static listener entries for Data Guard. This must be done as the grid user, as the listener is owned by Grid Infrastructure in a RAC deployment. Modify the $GRID_HOME/network/admin/listener.ora file (typically /u01/app/19.0.0.0/grid/network/admin/listener.ora) on each node in both clusters.

IMPORTANT: Each node will need individual configuration specific for that node. Ensure GLOBAL_DBNAME and SID_NAME are set correctly.

Example for Node 1 (e.g., PROD01 instance):

# On Node 1 (e.g., 10.1.1.1 for Primary, 10.2.2.1 for Standby), as grid user

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SDU=65535)
      (GLOBAL_DBNAME = prod_prim.db.phx.oraclevcn.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)
      (ENVS="TNS_ADMIN= /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin")
      (SID_NAME = PROD01) # <-- IMPORTANT: Set to instance name for this node (e.g., PROD01)
    )
    (SID_DESC =
      (SDU=65535)
      (GLOBAL_DBNAME = prod_prim_DGMGRL.db.phx.oraclevcn.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)
      (ENVS="TNS_ADMIN= /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin")
      (SID_NAME = PROD01) # <-- IMPORTANT: Set to instance name for this node
    )
  )

Example for Node 2 (e.g., PROD02 instance):

# On Node 2 (e.10.1.1.2 for Primary, 10.2.2.2 for Standby), as grid user

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SDU=65535)
      (GLOBAL_DBNAME = prod_prim.db.phx.oraclevcn.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)
      (ENVS="TNS_ADMIN= /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin")
      (SID_NAME = PROD02) # <-- IMPORTANT: Set to instance name for this node (e.g., PROD02)
    )
    (SID_DESC =
      (SDU=65535)
      (GLOBAL_DBNAME = prod_prim_DGMGRL.db.phx.oraclevcn.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)
      (ENVS="TNS_ADMIN= /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin")
      (SID_NAME = PROD02) # <-- IMPORTANT: Set to instance name for this node
    )
  )

Repeat these listener.ora modifications for all nodes in both the Phoenix and Ashburn clusters, adjusting the GLOBAL_DBNAME to match the local database's service name for its own static registration.

1.3 Restart Listener

After modifying listener.ora on each node, restart the listener on that node as the grid user:

srvctl stop listener
srvctl start listener
lsnrctl status

Verify that the services you added are listed in the lsnrctl status output.

Step 2: Prepare the Primary Database (PROD_PRIM)

Now that networking is configured, let's prepare the primary database for Data Guard. Connect to the primary database (e.g., PROD01 instance) using SQL*Plus as sysdba.

Action Required: Connect to the primary database as sysdba to perform these steps.

2.1 Verify Archive Logging and Flashback

Ensure your primary database is in ARCHIVELOG mode, FORCE_LOGGING is enabled, and FLASHBACK is on.

-- Check Status
select log_mode, force_logging, flashback_on from v$database;

-- Enable if needed
-- ALTER DATABASE FORCE LOGGING;
-- ALTER DATABASE FLASHBACK ON;

2.2 Create Standby Redo Logs (SRLs)

Standby Redo Logs are essential for real-time apply and minimizing data loss. You need one more Standby Redo Log per instance than Online Redo Logs on the primary.

  1. Check existing Online Redo Logs (ORLs) on primary:

    select inst_id, group#, bytes/1024/1024 SIZE_MB from gv$log order by 1,2;

  2. Add Standby Redo Logs: Repeat the ALTER DATABASE ADD STANDBY LOGFILE command for each new SRL needed. Ensure the size matches your ORLs.

    -- Example: If each instance has 4 ORLs, add 5 SRLs.
-- Run this command 5 times:
alter database add standby logfile size 1073741824; -- (1GB)
Database altered.

  3. Verify Standby Redo Logs:

    select inst_id, group#, bytes/1024/1024 SIZE_MB from gv$standby_log;

    You should now see the newly created SRLs for both instances.

2.3 Configure Data Guard Broker Parameters

Note: If you have an existing local Data Guard standby (like PROD_LDR) configured via the OCI console, many of these parameters are likely already set. Please verify your current settings before making changes.

We'll configure the Data Guard Broker configuration files to be stored in ASM, ensuring they are protected and available to all nodes in the RAC cluster. We'll also enable STANDBY_FILE_MANAGEMENT to AUTO and set ARCHIVE_LAG_TARGET.

  1. Check current dg_broker parameters:

    show parameter dg_broker

    You'll likely see them pointing to $ORACLE_HOME/dbs.

  2. Set dg_broker_config_file to ASM:

    alter system set dg_broker_config_file1='+DATA/PROD_PRIM/dr1prod_prim.dat' sid='*' scope=BOTH;
alter system set dg_broker_config_file2='+DATA/PROD_PRIM/dr2prod_prim.dat' sid='*' scope=BOTH;

  3. Set STANDBY_FILE_MANAGEMENT=AUTO: This allows the database to automatically manage standby datafiles, redo logs, and control files.

    show parameter STANDBY_FILE_MANAGEMENT
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*' SCOPE=SPFILE;

  4. Set ARCHIVE_LAG_TARGET (Optional): This parameter specifies the maximum time, in seconds, that the primary database can be ahead of the standby database. Setting it can help in monitoring and alerting, but it is not strictly required for Data Guard functionality.

    ALTER SYSTEM SET ARCHIVE_LAG_TARGET=1800 SID='*' SCOPE=SPFILE; -- 1800 seconds = 30 minutes

  5. Start the Data Guard Broker Process:

    alter system set dg_broker_start=true sid='*' scope=BOTH;
show parameter dg_broker_start

    Expected Output: dg_broker_start should be TRUE.

Step 3: Prepare the Cross-Region Standby Database (PROD_CRDR)

Now, switch to one of the nodes in your Ashburn (Standby) cluster. We'll configure Data Guard parameters, create SRLs, and transfer necessary files from the primary.

Critical Actions: These steps involve copying sensitive files and deleting existing standby data. Proceed with caution.

3.1 Copy TDE Wallet from Primary to Standby

This is vitally important for the standby database to decrypt incoming redo and open in read-only mode.

  1. On Node 1 of the Primary Cluster (Phoenix), as oracle user:

    cd /opt/oracle/dcs/commonstore/wallets/tde/
# List current wallet directory (e.g., dbha_phx1pp)
ls
# Tar and compress the primary's TDE wallet
tar cvzf prod_prim_tde_wallet.tgz PROD_PRIM/
# Securely copy to Node 1 of the Standby Cluster (Ashburn)
scp prod_prim_tde_wallet.tgz oracle@10.2.2.1:/opt/oracle/dcs/commonstore/wallets/tde/

  2. On Node 1 of the Standby Cluster (Ashburn), as oracle user:

    cd /opt/oracle/dcs/commonstore/wallets/tde/
# Backup the original standby wallet (optional but recommended)
tar czvf prod_crdr_tde_wallet_orig.tgz PROD_CRDR/
# Extract the primary's wallet
tar -xzvf prod_prim_tde_wallet.tgz
# Rename the original standby wallet directory
mv PROD_CRDR PROD_CRDR.orig
# Rename the extracted primary wallet directory to match the standby's unique name
mv PROD_PRIM PROD_CRDR

3.2 Configure RMAN SBT_TAPE for Object Storage on Standby

To ensure the standby database can utilize backups from object storage (e.g., for FROM SERVICE operations or if a backup from object storage is needed for recovery), you need to configure the RMAN SBT_TAPE channel on the standby, mirroring the primary's configuration. This typically involves setting up the Oracle Cloud Infrastructure (OCI) Object Storage integration for RMAN.

  1. Copy the RMAN Object Storage Configuration File from Primary: The primary database's RMAN object storage configuration is typically stored in a file within the /opt/oracle/dcs/commonstore/objectstore/opc_pfile/ directory. This file contains the necessary credentials and configuration for RMAN to interact with OCI Object Storage. You need to copy this file to the corresponding location on the standby server.

    • On Node 1 of the Primary Cluster (Phoenix), as oracle user:

      cd /opt/oracle/dcs/commonstore/objectstore/opc_pfile/
# Tar and compress the contents of the opc_pfile directory
# This ensures all necessary config files (e.g., those in GUID subdirectories) are copied
tar cvzf prod_prim_opc_config.tgz *
# Securely copy the tarball to Node 1 of the Standby Cluster (Ashburn)
scp prod_prim_opc_config.tgz oracle@10.2.2.1:/opt/oracle/dcs/commonstore/objectstore/opc_pfile/

    • On Node 1 of the Cross-Region Standby Cluster (Ashburn), as oracle user:

      cd /opt/oracle/dcs/commonstore/objectstore/opc_pfile/
# Extract the tarball. This will recreate the necessary directory structure and config files.
tar xzvf prod_prim_opc_config.tgz

  2. Configure RMAN channel for Object Storage: After copying the configuration file, ensure ORACLE_HOME and ORACLE_SID are set for PROD01 (or PROD02) on the standby. Then, configure the RMAN channel to use the copied configuration.

    # Example RMAN configuration (adjust sbt_library and PARMS as per your primary's setup)
rman target /

# If you are using the Oracle Cloud Infrastructure Object Storage Cloud Module for RMAN
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/oracle/product/19.0.0.0/dbhome_1/lib/libopc.so, ENV=(OPC_PFILE=/opt/oracle/dcs/commonstore/objectstore/opc_pfile/your_opc_config_file.ora)';
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F';
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; # Or your desired policy
EXIT;

    Note: The OPC_PFILE path and the specific .ora file within it (your_opc_config_file.ora) should correspond to the configuration used by the primary database for its object storage backups. This file would have been included in the prod_prim_opc_config.tgz you copied.

3.3 Delete Standby Database Data Files

Before restoring the primary database's files, you need to remove the existing datafiles, tempfiles, and online redo logs of the standby database. We're reusing the database system's shell, not dropping the database.

  1. On Node 1 of the Standby Cluster (Ashburn), as oracle user:

    sqlplus / as sysdba
SQL> set heading off linesize 999 pagesize 0 feedback off trimspool on echo off
SQL> spool /tmp/delete_database_files.sh
SQL> select 'asmcmd rm '||name from v$datafile
union all
select 'asmcmd rm '||name from v$tempfile
union all
select 'asmcmd rm '||member from v$logfile;
SQL> exit

  2. Shutdown the standby database:

    srvctl stop database -d PROD_CRDR -o immediate

  3. Run the deletion script as the grid user:

    sudo su - grid
# Execute the script generated above
/tmp/delete_database_files.sh

    This script will connect to ASM and delete the database files. You will see asmcmd rm commands executing.

3.4 Copy Database Password File from Primary to Standby

Since these are RAC databases, the password file is in ASM. We'll export it from the primary and import it on the standby using ASM commands.

  1. On Node 1 of the Primary Cluster (Phoenix), as grid user:

    asmcmd
ASMCMD> pwcopy +DATA/PROD_PRIM/PASSWORD/pwdprod_prim.ora /tmp/pwdprod_prim.ora
# (Note: The source filename might have a number like .259.1024894007, adjust if needed)
ASMCMD> exit

  2. Transfer the file to Node 1 of the Standby Cluster (Ashburn) as grid user:

    scp oracle@10.1.1.1:/tmp/pwdprod_prim.ora /tmp/

  3. Import the password file into ASM on the Standby Cluster (Ashburn), as grid user:

    asmcmd
ASMCMD> pwcopy --dbuniquename PROD_CRDR /tmp/pwdprod_prim.ora +DATA/PROD_CRDR/PASSWORD/pwdprod_crdr -f
# You might see "ASMCMD-9453: failed to register password file as a CRS resource" - this is expected.
ASMCMD> exit

  4. Verify the password file in ASM:

    asmcmd
ASMCMD> cd +DATA/PROD_CRDR/PASSWORD
ASMCMD> ls -l

    Look for an entry like pwdbha_iad1pq or pwdprod_crdr which is a link to the actual file.

  5. Update the cluster configuration with the new password file: Switch to the oracle user and modify the database configuration.

    sudo su - oracle
srvctl modify database -d PROD_CRDR -pwfile +DATA/PROD_CRDR/PASSWORD/pwdprod_crdr

  6. Verify the password file configuration:

    srvctl config database -d PROD_CRDR

    Ensure Password file: points to the correct ASM path.

Step 4: Restore and Recover the Standby Database (PROD_CRDR)

Now we're ready to restore the primary database's content onto the standby.

Action Required: This step restores the primary database's data to the standby instance using RMAN.

4.1 Start Instance in NOMOUNT

Start one instance of the standby database in NOMOUNT mode to prepare for control file restore.

# On Node 1 of PROD_CRDR (Ashburn) cluster, as oracle user
srvctl start instance -db PROD_CRDR -instance PROD01 -startoption nomount
srvctl status database -d PROD_CRDR

Expected Output: Instance PROD01 is running on node iad1, Instance PROD02 is not running on node iad2.

4.2 Restore Controlfile & Recover Database

Use RMAN to connect to the primary service, restore the standby controlfile, mount the database, and begin the recovery.

rman target /

RMAN> restore standby controlfile from service PROD_PRIM_SVC;

RMAN> alter database mount;

RMAN> recover standby database from service PROD_PRIM_SVC;

4.3 Enable Data Guard Broker on Standby

After the recovery, start the Data Guard Broker process on the standby.

sqlplus / as sysdba
SQL> alter system set dg_broker_start=true scope=both sid='*';
SQL> show parameter dg_broker_start

Expected Output: dg_broker_start should be TRUE.

Step 5: Configure Data Guard Broker

This is the final step to establish the Data Guard configuration and enable redo transport. This can be done from either the primary or standby.

Important Note: As you already have a Data Guard Broker configuration (e.g., for PROD_LDR, your local standby created via the OCI console), you will add the new cross-region standby (PROD_CRDR) to the existing configuration rather than creating a new one.

Connect to DGMGRL as the oracle user.

# On Node 1 of PROD_PRIM (Phoenix) cluster, as oracle user
dgmgrl /

Check if a configuration already exists:

DGMGRL> show configuration;

You should see your existing configuration including PROD_PRIM and PROD_LDR.

Add the cross-region standby database to the configuration: Since the configuration already exists, you will directly add PROD_CRDR.

DGMGRL> add database PROD_CRDR as connect identifier is PROD_CRDR_SVC;
Database "PROD_CRDR" added
  • PROD_CRDR: The DB_UNIQUE_NAME of your cross-region standby.
  • PROD_CRDR_SVC: The TNS alias for your cross-region standby.
Show and Enable the configuration: If the configuration was already enabled for PROD_LDR, adding PROD_CRDR might temporarily disable it. Re-enabling ensures all members are active.
DGMGRL> show configuration

Expected Output (before enabling): It might show Configuration Status: DISABLED temporarily after adding a new database.

DGMGRL> enable configuration;
Enabled.
Verify the enabled configuration:
DGMGRL> show configuration

Expected Output: Configuration Status: ENABLED

You can also check the status of individual databases:

DGMGRL> show database PROD_PRIM verbose;
DGMGRL> show database PROD_LDR verbose; -- Your existing local standby
DGMGRL> show database PROD_CRDR verbose;

Look for Status: SUCCESS and check Transport Lag and Apply Lag to ensure they are minimal and not increasing.

Step 6: Start Remaining Standby Database Nodes and Verify Synchronization

After the initial recovery and Data Guard Broker configuration, it's crucial to start all instances of the standby database and confirm that redo transport and apply are functioning correctly.

Start the remaining standby database instances: Connect as the oracle user on the standby cluster and start any instances that are not yet running.

# On Node 2 of PROD_CRDR (Ashburn) cluster, as oracle user
srvctl start instance -db PROD_CRDR -instance PROD02

Verify all instances are running:

srvctl status database -d PROD_CRDR

Expected Output: Both PROD01 and PROD02 instances should be reported as running.

Verify Data Guard Synchronization: Connect to DGMGRL from either the primary or standby and check the status of the Data Guard configuration.

# Connect to DGMGRL (e.g., from primary)
dgmgrl /

Then, check the configuration and database status:

DGMGRL> show configuration;

Expected Output: The configuration status should be ENABLED, and all databases (PROD_PRIM, PROD_LDR, PROD_CRDR) should show Status: SUCCESS.

To get more detailed synchronization information, query the V$DATAGUARD_STATS view on the primary and standby databases.

On the Primary Database (PROD_PRIM):

sqlplus / as sysdba
SQL> select name, value, unit from v$dataguard_stats where name like '%lag%';

On the Cross-Region Standby Database (PROD_CRDR):

sqlplus / as sysdba
SQL> select name, value, unit from v$dataguard_stats where name like '%lag%';

Look for transport lag and apply lag values that are low and stable, indicating efficient redo transport and apply.

Conclusion

You have successfully established a manual Oracle Data Guard configuration between two RAC DBCS instances across different OCI regions, extending your existing DR capabilities. 





Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment