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 |
|
|
|
DB Unique Name |
|
|
|
Instance Names |
|
|
|
Node 1 IP (Private) |
|
|
|
Node 2 IP (Private) |
|
|
|
Service Name (Global DB Name) |
|
|
|
Oracle Home |
|
|
|
Grid Home |
|
|
|
TDE Wallet Path |
|
|
|
ASM Disk Group (Data) |
|
|
|
ASM Disk Group (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.
Check existing Online Redo Logs (ORLs) on primary:
select inst_id, group#, bytes/1024/1024 SIZE_MB from gv$log order by 1,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.
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
.
Check current
dg_broker
parameters:show parameter dg_broker
You'll likely see them pointing to
$ORACLE_HOME/dbs
.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;
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;
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
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 beTRUE
.
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.
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/
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.
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
Configure RMAN channel for Object Storage: After copying the configuration file, ensure
ORACLE_HOME
andORACLE_SID
are set forPROD01
(orPROD02
) 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 theprod_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.
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
Shutdown the standby database:
srvctl stop database -d PROD_CRDR -o immediate
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.
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
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/
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
Verify the password file in ASM:
asmcmd ASMCMD> cd +DATA/PROD_CRDR/PASSWORD ASMCMD> ls -l
Look for an entry like
pwdbha_iad1pq
orpwdprod_crdr
which is a link to the actual file.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
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.
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.
Post a Comment
Post a Comment