Configuring OCI GoldenGate with Data Guard enabled databases

September 7, 2023 | 5 minute read
Eloi Lopes
Cloud Engineer
Text Size 100%:

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.

Overall Architecture

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”:

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

RAC Connection Creation

Repeat the above steps for the Standby Database.

Standby RAC DB Connection

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 Connect icon icon to connect.

    If the connectivity test is successful, you will see this screen.
    Add Checkpoint
  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.

RAC DB Credential

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):

Extract Creation

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.

Extract - Managed Options

Click next and provide the parameters:

Extract 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.

Eloi Lopes

Cloud Engineer

Eloi is a Cloud Solution Engineer for Data Integration and Analytics. He has experience in developing ELT / ETL projects and delivering analytics solutions to multiple customers across different industries. He is an eager learner and will keep improving his skills with main focus on real time data solutions.


Previous Post

Using Custom Properties for Confluent Kafka in OCI Goldengate Service Replication Setup

Sunil Vernekar | 3 min read

Next Post


Connecting OCI GoldenGate to Snowflake Private Endpoints

Deniz Sendil | 5 min read