Configuring OCI GoldenGate with Data Guard enabled databases

Oracle Cloud Infrastructure (OCI) GoldenGate is a fully managed service providing a real-time data mesh platform, which uses replication to keep data highly available and enable real-time analysis.

Connecting to Data Guard environments is a typical use case for OCI GoldenGate customers. This article intends to explain how to configure OCI GoldenGate connections to Data Guard and how to configure GoldenGate processes to adapt to switchover or failover changing roles.

In the architecture diagram below, we have deployed the Oracle database in the ‘Data Guard’ subnet and OCI GoldenGate in the ‘GoldenGate’ subnet. The ‘Data Guard’ subnet must have an ingress rule allowing connectivity to port 1521, while the ‘GoldenGate’ subnet requires an ingress rule allowing connectivity to port 443 to open the GoldenGate private endpoint. Port 22 is also necessary for connecting to a private OCI GoldenGate instance through a Bastion. Additionally, OCI GoldenGate automatically assigns port 16000 to RAC environments using a SCAN proxy created by OCI Private Endpoints.

Prerequisites:

• The Oracle database must be prepared for GoldenGate.
• To minimize human interactions, you must configure a unique TNS string. The database service name should be the same to have a unique TNS string for use by GoldenGate extracts. For further instructions, please refer to the “Configuring Service Name” section below.

Configuring Service name

Note: Skip this step if you already have the same service name in both databases.

On your primary database, log in as the oracle user. List the database properties:

srvctl config database -d DB0831_skh_fra -a

Add the new service:

srvctl add service -db <primary database unique name> -service <new service name> -preferred “<database instances>,<database instances>,”

Start the new service and check its status:

srvctl start service -d <primary database unique name> -s racdb_service
srvctl status service -d <primary database unique name> -s racdb_service

Perform a switchover and run the same commands on the “old” standby:

srvctl add service -db <standby database unique name> -service <new service name> -preferred “<database instances>,<database instances>,”
srvctl start service -d < standby database unique name> -s racdb_service
srvctl status service -d < standby database unique name> -s racdb_service

Test the new service name using a SQL client like SQL*Plus. If the connection works, you can move to the next step.

Creating OCI GoldenGate connections

First, we will start by creating the connection to the current Primary database.

Navigate to Oracle Database > GoldenGate in the OCI Console, then click on Connections.

Click on “Create Connection”, provide a connection name, and select the option “Oracle Database”:

Provide a database connection string with a valid format, for example:
<database name>:<database port>/<service_name>

Repeat the above steps for the Standby Database.

Once you have created both connections, please assign them to the OCI GoldenGate deployment.

Configuring the extract

1. Access the GoldenGate Console and click on “Configuration.” You will find the two connections there listed as Credentials. First, test the connection to the primary database (attempting to test the standby connection will fail as the database is not in an open mode). To test the connection, you have 4 icons. Click on the first 

   

   icon to connect.
   
   If the connectivity test is successful, you will see this screen.
   

   

2. You have two credentials for each Real Application Cluster (RAC) at this stage. What you need is to create a credential that utilizes both RACs. To achieve this, you must create another credential from the GoldenGate console. It is necessary because OCI GoldenGate has already registered both databases in its tenancy, allowing access to both, but in separate connections. We must combine them by creating a third credential from the GoldenGate Console.
3. Create a dedicated credential for Data Guard in the GoldenGate console.

TNS String example:

(DESCRIPTION=(RETRY_DELAY=3)(CONNECT_TIMEOUT=30)(FAILOVER=on)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=2)(ADDRESS_LIST=(LOAD_BALANCE=off)(ADDRESS=(PROTOCOL=TCP)(host=<database rac primary >)(port=16000))(ADDRESS=(PROTOCOL=TCP)(host=<database rac standby >)(port=16000)))(SERVICE_NAME=<service name>)))

OCI GoldenGate accesses RAC environments using a SCAN proxy created by OCI Private Endpoints. It uses port 16000 here to access my RAC database which is why I am reusing it here.

Create the Extract by specifying the Process Name, Trail Name, and the Credential you previously created (UniqueTNS):

In the “Managed Options” section, please configure the Extract to auto-start and auto-restart. Adjust the configuration based on your Recovery Time Objective (RTO).
In the example configuration below, the Extract is set to retry 9 times, with a 2-minute wait after each retry. If the Extract fails to connect to the database after 45 minutes, it will stop retrying.

Click next and provide the parameters:

The same configuration can be applied to Replicats.

Conclusion

In this article, we have seen how to configure Oracle database connections with Data Guard enabled and how to configure auto-restart for GoldenGate processes.