X

Best Practices from Oracle Development's A‑Team

Privately Replicating Autonomous Databases Using GoldenGate Marketplace

Validated April 22, 2021 with GG19.1

Introduction

 

Oracle GoldenGate can now be deployed in a private subnet via Oracle Cloud Infrastructure Marketplace and autonomous databases can be deployed with a private endpoint. Prohibiting direct access from the internet increases security for cloud services.

Oracle GoldenGate is a comprehensive software package for enabling the replication of data in heterogeneous data environments. The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems.

Oracle Cloud Infrastructure's Autonomous Database is a fully managed, preconfigured database environment with three workload types available, Autonomous Transaction Processing, Autonomous Data Warehouse and Autonomous JSON Database. 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 Oracle GoldenGate Marketplace Microservices Edition deployed in a private subnet. It builds upon the post Deploying GoldenGate Marketplace in a Private Subnet and is a companion future posts on using GoldenGate natively in OCI with a private endpoint.

Using autonomous databases for replication demonstrates the use of database listeners configured with TLS/SSL security and the TCPS protocol.

Validations

April 22, 2021 with GG 19.1

Topics

Before You Begin

Deploying Additional Components

Preparing GoldenGate for Autonomous Databases

Preparing Autonomous Databases for 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
SCN Database System Change Number
OS Object Storage
CS Cloud Shell
SG Service Gateway
   
 

This post assumes:

GG marketplace has been deployed in a private subnet on OCI and is accessible via SSH and a public bastion compute instance.

The Source and Target ATPs are hosted in separate OCI regions in the same tenancy
There is no private connection (VPN, FastConnect, Remote Peering) between the regions and between clients and OCI. 
Access to the private resources is provided by public load balancers acting as proxies.

Privileges

A user account in an OCI tenancy for managing compute, database, object storage and network resources.
SSH private key and GG credentials for access to GG.
Admin credentials if using existing ATPs.

Commands

The individual Shell and SQL commands used in this post are consolidated in the Appendix

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.

The post examples use GG and the Source ATP in one OCI region (Phoenix)and the target ATP in another (Ashburn)

COMPONENT USE REFERENCE
VCN Host Target Resources link
INTERNET GATEWAY For Public Subnet Outbound Traffic link
NAT GATEWAY For GG Private Subnet Outbound Traffic link
SERVICE GATEWAY For GG Private Access to Object Storage link
SUBNET Host LB and ATP Resources link
LOAD BALANCER Proxy for GG and ATP Access link
NSG Required for ATP provisioning link
ATP Source and Target Databases link
ROUTE TABLE Routes Source and Target VCN Traffic link
ACCESS CONTROL Allows Ingress and Egress for VCN Traffic   link
OS BUCKET Stores Initial Exported ATP Source Data link
AUTH TOKEN For Access to OS link
     

 

Component Key Values

The sections following use component key values as variables. They are read from a parameter file obtained here. Download the file as my-gg19-components.sh and update it with your key values as you deploy the components. An example file is below:

export SOURCE_LB_IP=nnn.nnn.nnn.nnn
export TARGET_LB_IP=nnn.nnn.nnn.nnn
export SOURCE_ATP_IP=nnn.nnn.nnn.nnn
export SOURCE_ATP_WALLET_LB=Wallet_GGSOURCE
export SOURCE_ATP_TNS_PREFIX=ggsource_low
export TARGET_ATP_IP=nnn.nnn.nnn.nnn
export TARGET_ATP_WALLET_LB=Wallet_GGTARGET
export TARGET_ATP_TNS_PREFIX=ggtarget_low
export NAMESPACE=xxxxxxxx
export OS_REGION_FQDN=objectstorage.us-phoenix-1.oraclecloud.com
export OS_BUCKET=myBucket
export AUTH_TOKEN=sv_zABOk2XYZnvIpwZZ9
export AUTH_TOKEN_USER=akennedy

Additional Component State

The additional components are depicted below.

VCN

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

VCN CIDR
PHX-VCN 10.221.8.0/23 
ASH-VCN 10.221.12.0/23

 

Subnet

Subnet CIDR
PHX-VCN Public SN 10.221.8.0/27 
PHX-VCN Private SN 10.221.8.32/27 
ASH-VCN Public Subnet 10.221.12.0/27
ASH-VCN Private SN 10.221.12.32/27

 

Gateways

VCN TYPE Public IP
PHX-VCN Internet  
PHX-VCN NAT 134.70.8.6
PHX-VCN Service  
ASH-VCN Internet  

 

Note the Public IP address of the NAT Gateway for route table use

 

Create the Load Balancers

Load Balancer Public IP PROTOCOL PORT TARGET IP
PHX-VCN-PUB-LB 132.228.22.22 TCP 443 GG  10.221.8.34
PHX-VCN-PUB-LB 132.226.22.22 TCP 1522 ATP 10.221.12.34
ASH-VCN-PUB-LB 132.226.12.112 TCP 1522 ATP 10.221.8.34

 

Create a flexible load balancer in each region. This post has GG and the Source ATP in one region and the target ATP in another. Navigate to Networking > Load Balancers, select the Compartment and Region and click Create Load Balancer. 

Only initial configurations are created here. After the ATPs are provisioned they are updated.

Select Load Balancer Type
   Choose Load Balancer
   Click Create Load Balancer
Add Details
   Choose your Compartment, VCN, and Public Subnet
   Click Next
Choose Backends
   Change the Health Check Policy Protocol to TCP
   Change the Port to 1522
   Click Next
Configure Listener
   Change the Listener Name to LSNR-TCP-1522
   Change the Listener Traffic Type to TCP
   Change the Port to 1522
   Click Submit

Update the Key Component File with the Public IP address

! Repeat for the Target Region

Create NSGs

A Network Security Group must exist in each VCN where an ATP is deployed. However they do not require configuration. The examples use Security Lists rather than NSGs.

Create a NSG in both the source and target VCNs. Navigate to Networking > Virtual Cloud Networks, select the Compartment and Region and click the VCN Name.

Under Resources click Network Security Groups
Click Create Network Security Group
Enter a Name and click Next
Click Create without providing rules

Provision ATP Instances

ATP Private IP
PHX-VCN-SRC-ATP 10.221.8.36
ASH-VCN-TRGT-ATP 10.221.12.36

 

In the OCI Console navigate to Autonomous Transaction Processing, create an ATP in the source and target VCNs and download the credentials from OCI. 

Update the Key Component File with the Private IP addresses, Wallet Names and  TNS Prefixes for the _low service.

 

Modify the ATP Credentials to use the Load Balancers

For both ATPs, uncompress the downloaded credential packages (.zip file) and change the hostnames and ports in the tnsnames.ora files to the appropriate LB listening IP address and port. 

Source

Target

Recompress the packages with a new name.

Update the Key Component File with the modified Wallet Names

Update the Load Balancers

Add an ATP Backend

Update the load balancer in each region. Navigate to Networking > Load Balancers, select the Compartment and source Region and click on your load balancer Name. 

Under Resources click Backend Sets and then click the Backend Set Name
Under Resources click Backends and then click Create Backend

Choose IP Addresses
Enter the ATP IP Address e.g. 10.nnn.nnn.nnn
Enter 1522 as the Port
Click Add

Repeat for the Target Region

Add a GG Listener, Backend Set and Backend (GG Region Only)

Navigate to Networking > Load Balancers, select the Compartment and Source Region and click on your load balancer Name.

Under Resources click Backend Sets and then click Create Backend Set
   Enter BKND-SET-443 as the Name
   Change the Health Check Protocol to TCP
   Change the Port to 443
   Click Create Backend Set

Click the new Backend Set Name
   Under Resources click Backends and then click Add Backend
      Choose Compute Instances
      Check your GG instance
      Check Manually configure security lists
      Click Add

Return to Load Balancer Details.

Under Resources click Listeners and then click Create Listener
   Enter  LSNR-TCP-443 as the Listener Name
   Change the Protocol to TCP
   Change the Port to 443
   Select BKND-SET-443 from the Backend Set dropdown
   Click Create Listener

Object Storage

NAMESPACE REGION API Public IP CIDR Bucket
xxxxxxxx PHX https://objectstorage.us-phoenix-1.oraclecloud.com 134.70.0.0/19 PHXDATAPUMP

 

Object Storage is used for the initial source ATP backup before GG extraction

Create OS Bucket

Navigate to Object Storage > Object Storage, select the Compartment and Source Region and click Create Bucket

Enter a Bucket Name and click Create

Determine Namespace

Click on the bucket Name and note the Namespace

Determine OS Region API

Find the source region's object storage API here.

Update the Key Component File with the Bucket Name, Namespace and Region API

 

AUTH TOKEN

Auth tokens are Oracle-generated token strings that are used to authenticate when accessing Object Storage

Determine an Auth Token User

Identify a user with read and write access to the OS bucket created above e.g. dcarley

Note: You might consider using a local IAM user

 

Create an Auth Token

Login to OCI as the above user, click on the User icon  and then click User Settings.

Under Resources Click Auth Token
Click Generate Token
Add a Description and click Generate Token
Click Show and Copy the token to a notepad as it is not shown again
Click Close

Update the Key Component File with the Auth Token User and the Auth Token

 

Route Tables

Attach an existing or new route table with the following rules to each subnet. Change the CIDR blocks for your network.

SUBNET DESTINATION TARGET DESCRIPTION
SRC-VCN-PUBLIC 172.58.0.0/16 Internet Gateway Internet Clients
SRC-VCN-PRIVATE OCI <Source Region> Object Storage Service Gateway Source Initial Backup Before Extraction
SRC-VCN-PRIVATE 132.226.12.112/32 NAT Gateway Target Load Balancer
TRGT-VCN-PUBLIC 132.226.11.186/32 Internet Gateway GG VCN NAT Gateway

 

Access Control

Attach existing or new security lists with the following rules to each subnet. Change the CIDR blocks for your network.

SUBNET TYPE SOURCE / DESTINATION PORT PROTOCOL DESCRIPTION
SRC-VCN-PUBLIC Ingress 172.58.0.0/16 22 TCP Internet Client SSH to Bastion
SRC-VCN-PUBLIC Ingress 172.58.0.0/16 443 TCP Internet Client Browser to LB
SRC-VCN-PUBLIC Egress 10.221.8.34/32 22 TCP LB SSH to GG 
SRC-VCN-PUBLIC Egress 10.221.8.34/32 443 TCP LB Browser to GG 
SRC-VCN-PUBLIC Egress 172.58.0.0/16 443 TCP Internet Client Responses
SRC-VCN-PRIVATE Ingress 10.221.8.3:22 22 TCP From Bastion to GG
SRC-VCN-PRIVATE Ingress 10.221.8.0/27 443 TCP From LB Subnet to GG
SRC-VCN-PRIVATE Ingress 10.221.8.34/32 1522 TCP From GG to Source ATP
SRC-VCN-PRIVATE Egress 10.221.8.36/32 1522 TCP From GG to Source ATP
SRC-VCN-PRIVATE Egress 134.70.0.0/19 443 TCP From GG to Object Storage
SRC-VCN-PRIVATE Egress 10.221.8.0/27 443 TCP From GG to Source LB Subnet
SRC-VCN-PRIVATE Egress 132.226.12.112/32 1522 TCP From GG to Target LB
TRGT-VCN-PUBLIC Ingress 132.226.11.186/32 1522 TCP From GG VCN NAT Gateway
TRGT-VCN-PUBLIC Egress 10.221.12.36/32 1522 TCP To ATP from LB
TRGT-VCN-PRIVATE Ingress 10.221.12.0/27 1522 TCP From LB Subnet to ATP

 

 

Preparing GoldenGate for Autonomous Databases

This section describes the GG updates required for replication.

Upload the Deployed Components File and the ATP Credentials

Note: The Source ATP modified credentials are not necessary for GG because they are in the same or peered VCN. However they are still useful for access from a remote client. For GG use the original source credentials.

On Your Client copy the deployed component file and the modified compressed ATP credential files to your client home directory. Copy them to the GG server using the SSH configuration file created in the GG deployment post.

SSH into GG

Set Environmental Variables

Add Additional Environmental Variables and Exit

SSH back into GG for the variables to take effect

Change the GG Administrative Credentials

Display the GG LB Service Manager URL and default credentials

Use the URL in a browser e.g. Firefox, login with the default credentials, click on the User icon  and then click Change Password. This post uses YOurPwd123_# for development. You are logged out.

Log in to the Service manager again with the new password. Click on the Source Administration Port link. Login to the Administration Server with the default password. Click on the User icon  and then click Change Password. Change it to the same new password. You are logged out.

Change the URL back to the Service Manager URL. Login in with new password if prompted. Click on the Target Administration Port link. Login to the Administration Server with the default password. Click on the User icon  and then click Change Password. Change it to the same new password. You are logged out.

Prepare GG DB Credentials

Use the GG Admin Client to create a credential for the GGADMIN database user and password. This user is used in the Admin Client to connect to the Autonomous Database to perform commands that require a database connection. It is also used in the USERIDALIAS parameter for the Extract database connection.

GG Marketplace is installed with a self-signed certificate. Admin client allows connections when the server uses a self-signed certificate though this is not the default. Admin Client does not allow connecting to a server through HTTPS when the self-signed certificate is invalid. To override this behavior, use the "!" modifier with the CONNECT command.

Display the Source Alter Credential Statement

Start the Admin Client and Paste the Commands

Display the Target Alter Credential Statement 

Start the Admin Client and Paste the Commands

Prepare the Source ATP Credentials

Prepare the Target ATP Credentials

 

 Preparing Autonomous Databases for GoldenGate℘

This section describes the ATP updates required for extraction and replication. They are applied via the DB client in the GG instance.

SSH into GG if Necessary

Source ATP

Prepare A Credential for Object Storage

Create the Credential

Unlock the GG Administrator Account

Add Minimal Supplemental Log Data

Create a Schema to be Replicated

Create a Table to be Replicated

Obtain the Current SCN, Set a Variable and Exit

Sample result: 37180061349397

Backup the Source ATP Schema for the Replication Starting Point

Copy the Backup to Object Storage

 

Target ATP

Prepare a Credential for Object Storage

Create the Credential

Unlock the GG Administrator Account

Restore the Backup to the Target ATP

 

 Configuring the GoldenGate Extraction 

This section describes the GG source extraction configuration. The extract is configured but not started.

SSH into GG if Necessary

Prepare the GG Extract Parameter File

Display and Copy the Commands to Add the Extract

Start the Admin Client and Paste the Commands

 

 Configuring the GoldenGate Replication 

This section describes the GG target replication configuration. The replication is configured but not started.

Prepare the GG Replication Parameter File

Display and Copy the Commands to Add the Replication

Start the Admin Client and Paste the Commands

 

 Validating Autonomous Database Replication 

This section describes updating the source ATP table data, starting the extract and replication processes, creating a new source table and viewing the replicated changes.

SSH into GG if Necessary

Create Additional Source Rows

This mimics changes that occur in the source ATP before the GG extract and replication processes have started.

Display Target Rows before Replication

Display and Copy the Commands to Start the GG Extract Process

Start the Admin Client and Paste the Commands

Display and Copy the Commands to Start the GG Replication Process

Start the Admin Client and Paste the Commands

Display Target Rows After Replication Start

Create Additional Source Rows and Create New Table

Creating a new table demonstrates DDL replication.

Display Post-Start Rows and New Table

 

℘ Connection Flow ℘

The replication flow is shown below.

 

 

 Summary 

This post provided a step-by-step guide for replicating autonomous databases with private endpoints using Oracle GoldenGate Marketplace Microservices Edition deployed in a private subnet.

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

 

⁂ Appendix ⁂

Below is the client list of commands used in this post. It is available for download here or send them to your clipboard by clicking in the text area and then clicking the copy button.

Below is a list of the GG commands. It is available for download here or send them to your clipboard using the copy button.

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