X

Best Practices from Oracle Development's A‑Team

Privately Replicating Autonomous Databases Using Native GoldenGate

Validated May 24, 2021 with GG 21.1

Introduction

Oracle Cloud Infrastructure Oracle GoldenGate is now available and can be deployed natively with a public or private endpoint.

Oracle Cloud Infrastructure GoldenGate is a fully managed, native cloud service that moves data in real-time, at scale. OCI GoldenGate processes data as it moves from one or more data management systems to target databases. You can also design, run, orchestrate, and monitor data replication tasks without having to allocate or manage any compute environments.

Oracle Cloud Infrastructure's Autonomous Database is a fully managed, preconfigured database environment with several workload types available, including Autonomous Transaction Processing and Autonomous Data Warehouse. You do not need to configure or manage any hardware or install any software.

Refer here for GoldenGate documentation and here for Autonomous Database Private Endpoint documentation.

This post is a step-by-step guide for replicating autonomous databases with private endpoints using Native GoldenGate deployed with a private endpoint. It builds upon the post Deploying Native GoldenGate in the Oracle Cloud.

The intent of this post is to demonstrate how the Oracle Cloud Infrastructure networking components provide the foundation for private GoldenGate replications. The GoldenGate configuration may not align with accepted best practices. The examples in this post are for development purposes only. They omit many important steps required for a production implementation.

Validations

May 24, 2021 with GG 21.1

Topics

Before You Begin

Deploying Additional Components

Preparing Autonomous Databases for GoldenGate

Registering the ATPs with GoldenGate

Configuring the GoldenGate Extraction

Configuring the GoldenGate Replication

Validating Autonomous Database Replication

 

 Before You Begin and Assumptions ℘

Acronyms

GG GoldenGate
PE Private Endpoint
VCN Virtual Cloud Network
TLS Transport Layer Security
LB Load Balancer
OCI Oracle Cloud Infrastructure
FQDN Fully Qualified Domain Name
HTTPS Hypertext Transfer Protocol Secure
ATP Autonomous Transaction Processing
SSH Secure Shell
DNS Domain Name System
ACL Access Control List - Security List
ADB Autonomous Database
CMAN Oracle Connection Manager
 

This post assumes:

Native GG has been deployed with both a private and public endpoint on OCI.
The Source and Target ATPs are hosted in separate OCI regions in the same tenancy. The Target ATP is in the same VCN as the Native GG PE.
There is no private connection (VPN, FastConnect, Remote Peering) between the regions and between clients and OCI. 
Access to the Source ATP is provided by an instance of Oracle Connection Manager acting as a proxy.
Access to the Target ATP is provided internally by GoldenGate.

Privileges

A user account in an OCI tenancy for managing GoldenGate, database, and network resources.
GG credentials and URL for access to the GG console.

Initial State

 

 

℘ Deploying Additional Components ℘

This section describes the additional OCI components necessary for the examples used in the post. The following table lists them with links for deployment reference.

COMPONENT USE REFERENCE
CMAN Public Connection Manager for the Source ATP link
NSG Required for ATP provisioning link
ATP Source and Target Databases link
ACCESS CONTROL Allows Ingress and Egress for VCN Traffic   link

 

CMAN

Deploy CMAN in the source region (different from GG). Follow the examples in Deploying Connection Manager as a Proxy for Oracle Services and Connecting to Private Autonomous Databases using Connection Manager. These create the following components:

Source VCN, Subnets, and NSG
Source ATP
CMAN instance
Source VCN Security Lists 

VCN

The following tables show component examples both needed and previously deployed (greyed out).

VCN Notes
TARGET-VCN The VCN hosting GG
SOURCE-VCN Created with the CMAN deployment

 

Subnet

Subnet Notes
Target VCN Public SN Not Used except for Client Bastion
Target VCN Private SN Hosting GG and the Target ATP
Source VCN Public Subnet Hosting CMAN. Created with the CMAN deployment
Source VCN Private SN Hosting Source ATP. Created with the CMAN deployment

 

NSG

VCN NOTES
TARGET-VCN Create. It must exist for ATP provisioning but is not used
SOURCE-VCN Created with the CMAN deployment. It must exist for ATP provisioning but is not used

 

ATP

NAME SUBNET Notes
TARGET-ATP Target VCN Private SN Create an ATP with the name REMOTE-ATP
SOURCE ATP Source VCN Private SN Created with the CMAN deployment.

 

Access Control

Create the following rules where noted.

SUBNET TYPE SOURCE / DESTINATION PORT PROTOCOL Notes
TRGT-VCN-PUBLIC Ingress CLIENT/ ATP PROXY 1522 TCP Create. Depending on proxy type
TRGT-VCN-PUBLIC Egress ATP PROXY / ATP 1522 TCP Create. Depending on proxy type
TRGT-VCN-PRIVATE Ingress ATP PROXY / ATP 1522 TCP Create. Depending on proxy type
SRC-VCN-PUBLIC Ingress GG Native Service / CMAN 1521 TCP Update. Created by CMAN deployment.
SRC-VCN-PUBLC Ingress CLIENT / CMAN 1521 TCP Created by CMAN deployment.
SRC-VCN-PUBLIC Egress CMAN / ATP 1522 TCP Created by CMAN deployment.
SRC-VCN-PRIVATE Ingress CMAN / ATP 1522 TCP Created by CMAN deployment.

 

Additional Component State

 

 

 Preparing Autonomous Databases for GoldenGate℘

This section describes the ATP updates required for GG extraction and replication.

Update the Source ATP

Use a SQL client e.g. sql*plus, sql*developer and connect as the ADMIN user via the CMAN proxy. Issue these SQL commands.

Supplemental Log Data is used to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables.

The GGADMIN user comes with ATP and has the necessary privileges for GG processing. GG connects to the ATP as this user.

The NativeGG_User schema is used to validate that DDL and DML changes are replicated to the target.

Update the Target ATP

Use a SQL client e.g. sql*plus, sql*developer and connect as the ADMIN user. Issue these SQL commands.

If your client VCN is not peered to the Target VCN in OCI, enable a compute instance in the Target VCN with an Oracle client installed or enable a proxy using SSH local port forwarding or a public load balancer.

 

 Registering the ATPs with GoldenGate 

Registering a database enables networking and connectivity for a source or target database. 

A database registration creates a reverse private endpoint for egress between OCI GoldenGate service and your VCN. When you register a database, you provide the database's IP network location and a fully-qualified domain name (FQDN) that OCI GoldenGate uses to reference the database. After creating a database registration, it becomes available to all OCI GoldenGate deployments within that compartment.

Database registrations also capture and synchronize database credentials to OCI GoldenGate. Any change made to the credential, such as updating or deleting, is synchronized to OCI GoldenGate.

Database registrations can be used with different deployments, if the database registrations and deployments are in the same tenancy compartment and the user currently logged in to the Oracle Cloud Infrastructure GoldenGate Deployment Console can access the database. Database registrations allow you to reuse the same networking and connection information for each deployment you want to use the database registration with.

Sign into the OCI Console and navigate to Oracle Databases > GoldenGate. Select the Region and Compartment hosting the GG service. Under GoldenGate click Registered Databases

Register the Source ATP

Click Register Database
   Enter a Name e.g. SOURCE-ATP.
   Enter an Alias Name. e.g. SOURCEATP. This becomes the USERIDALIAS credential name in GG parameter files.
   Ensure the Compartment is the GG service compartment.
   Select Enter Database Information as the Source ATP is not in the GG region
   Enter the CMAN public IP address as the FQDN
   Enter the Advanced Connect Descriptor created in the Connecting to Private Autonomous Databases using Connection Manager post as the Database Connection String
   Enter GGADMIN as the Database User Name
   Enter the Database User Password
   Click Register

Register the Target ATP

Click Register Database
   Enter a Name e.g. REMOTE-ATP.
   Enter an Alias Name e.g. REMOTE. This becomes the USERIDALIAS credential name in GG parameter files.
   Ensure the Compartment is the GG service compartment.
   Select Select Database as the Target ATP is in the GG region
   Select Autonomous Database from the Database Type dropdown
   Select REMOTE-ATP from the Database dropdown as that is the example name used to create the ATP
   Accept the Database FQDN
   The user GGADMIN is pre-populated into the Database User Name and is read-only
   Enter the Database User Password
   Check the Network Connectivity via Private Endpoint box
   Accept the Database IP
   Accept the Subnet
   Click Register

Update Access Control

The Remote-ATP registration created a Reverse Connection Private IP address for private egress. Make a note of the IP address for the private subnet security list.

SUBNET TYPE SOURCE / DESTINATION PORT PROTOCOL Notes
TRGT-VCN-PRIVATE Egress GG Reverse PE/ ATP 1522 TCP Create
TRGT-VCN-PRIVATE Ingress GG Reverse PE/ ATP 1522 TCP Create.

 

Validate the Registrations

Log in to the GoldenGate Deployment Console if you aren't already logged in. The URL can be found on the GG deployment page in OCI.
  In the navigation menu, click Configuration.
  Click the Connect icon for the Target ATP to validate the connection

Inspect the User ID entry for the Source ATP.
     If you see double slashes after the @ sign
           Click the Edit icon and remove them
                Renter the Database Password
              Click Submit

 Click the Connect icon for the Source ATP to validate the connection

 

 Configuring the GoldenGate Extraction 

An Extract is the GoldenGate process to capture transaction data

Add Transaction Information

Before creating an extract, enable supplemental logging at the table level by adding TRANDATA for the schema i.e. NATIVEGG_USER replication.

In the GoldenGate Deployment Console navigation menu, click Configuration.
   Click the Connect icon for the Source ATP
      Click the plus (+) icon next to TRANDATA Information.
         Enter the Schema Name i.e. NATIVEGG_USER 
         Click Submit.

Create an Extract

From the GoldenGate Deployment Console click Overview from the navigation menu if necessary
   Click the Add Extract (+ icon)
      Select Integrated Extract and click Next
         Enter a Process Name e.g. Extract1
         Enter a two-character Trail Name e.g. DC
            Make a note of it as it is used in the Replication process
         Select OracleGoldenGate from the Credential Domain dropdown
         Select the Source ATP alias i.e. SOURCEATP from the Credential Alias dropdown
         Click Next

         
         
         Enter these two lines to the Parameter File to enable DDL capture on the schema
            ddl include mapped
            TABLE NATIVEGG_USER.*;

         Check the Register Extract in the background? box
         Click Create and Run

          

 

 Configuring the GoldenGate Replication 

Replicat is the GoldenGate term for the replication process that maintains synchronization between source and target tables.

Add a Checkpoint Table

Replicat maintains checkpoints that provide a known position in the trail from which to start after an expected or unexpected shutdown. To store a record of its checkpoints, Replicat uses a checkpoint table in the target database. This enables the Replicat checkpoint to be included within the Replicat transaction itself, to ensure that a transaction will only be applied once, even if there is a failure of the Replicat process or the database process.

Before creating a replication, add a checkpoint table in GG.

In the GoldenGate Deployment Console navigation menu, click Configuration.
   Click the Connect icon for the Target ATP
      Click Add Checkpoint (+).
         Enter a two-part Checkpoint Table name e.g. GGADMIN.CHECKPOINT
         Click Submit

Add a Replicat

From the GoldenGate Deployment Console click Overview from the navigation menu if necessary
   Click Add Replicat (+).
      Select Nonintegrated Replicat and click Next
         Enter a Process Name e.g. Repcat1
         Select OracleGoldenGate from the Credential Domain dropdown
         Select the Target ATP alias i.e. REMOTE from the Credential Alias   dropdown
         Enter the Extract two-character Trail Name e.g. DC
         Select the checkpoint table from the Checkpoint Table dropdown i.e. GGADMIN.CHECKPOINT
         Click Next
                  


         Change the last line in the Parameter File to specify the schema e.g.
         MAP NATIVEGG_USER.*, TARGET NATIVEGG_USER.*;
         Click Create and Run

          

Configured State

 Validating Autonomous Database Replication 

Connect to the Source ATP

Use a SQL client e.g. sql*plus, sql*developer to connect as the GGADMIN user. 

Initiate a DDL Change

Initiate a DML Change

Connect to the Target ATP

Use a SQL client e.g. sql*plus, sql*developer to connect as the GGADMIN user. 

Validate Replication

This validates both the table creation (DDL) and the row insertion (DML).

℘ Connection Flow ℘

 

 Summary 

This post described replicating autonomous databases with private endpoints using Native GoldenGate deployed with a private endpoint.

For other posts relating to analytics and data integration visit http://www.ateam-oracle.com/dayne-carley

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha