X

Best Practices from Oracle Development's A‑Team

Using GoldenGate Marketplace 19c with Encrypted Redo Transport Services and a Downstream Mining Database

Validated July 19, 2021 with GoldenGate 19c 19.1.0.0.210420

Introduction

 

This post is another in a series that builds upon the principles and examples shown in Using Oracle Database Redo Transport Services in Private Networks and Adding an Encrypted Channel to Redo Transport Services using Transport Layer Security. It is a step-by-step guide demonstrating GoldenGate Marketplace 19c extracting data from a downstream mining database that is receiving real time changes from a source database via redo transport services through an encrypted transport layer security channel. It then demonstrates the application of these changes to an autonomous database. It is a close companion to the post on Using Native GoldenGate with Encrypted Redo Transport Services and a Downstream Mining Database

Oracle GoldenGate on Oracle Cloud Marketplace is the cloud-based version of Oracle GoldenGate and provides the same benefits, scalability, security, and robustness that you can rely on for enterprise replication. The Marketplace image contains the latest Oracle GoldenGate release that is available at the time of provisioning an instance.

A downstream mining database allows you to offload the redo log processing from a source database. When online logs are shipped to a downstream database, real-time capture is possible. Changes are captured as though the GoldenGate extract process is reading from the source database redo logs.

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.

Redo Transport Services performs the automated transfer of redo data from one database to another. Refer here for an introduction. The concept first became widely available 33+ years ago in 1988 with version 8i of the Oracle Database. Oracle Data Guard uses the feature the most although it is not required.

Transport Layer Security, or TLS, is a widely adopted security protocol designed to facilitate privacy and data security for communications over the Internet. It is the successor of the now-deprecated Secure Sockets Layer (SSL). Its use as the Security layer in HTTPS remains the most publicly visible. The Oracle documentation is here.

Validations

July 19, 2021 with GoldenGate Marketplace 19c and Oracle Database 19c

Topics

Before You Begin

Deploying a Target Autonomous Database

Provisioning GoldenGate Marketplace

Adjusting the Downstream Load Balancer 

Preparing the Downstream Mining Database

Configuring GoldenGate Marketplace

Preparing the Target Autonomous Database

Preparing the Source Database

Preparing GoldenGate Database Access

Preparing the GoldenGate Extract

Preparing the GoldenGate Replication

Validating the GoldenGate Extract and Replication

 

Before You Begin and Assumptions ℘

Acronyms

ATP Autonomous Transaction Processing
SSH Secure Shell
GG GoldenGate
CMAN Connection Manager
RTS Redo Transport Services
VCN Virtual Cloud Network
DB Database
LB Load Balancer
OCI Oracle Cloud Infrastructure
SSH Secure Shell
ACL Access Control List - Security List Rule
RTR Route Table Rule
TLS Transport Level Security

 

This post uses the terms Source / R1 for the database and region sending the redo, Downstream / R2 for the database and region receiving the redo and Target / R2 for the database having the source changes applied. It assumes the following are in place or are available.

Privileges

Administrator / Root privilege on your client to update the etc/hosts file.

Components

All components, environmental variables, credentials and privileges created in the post Using Oracle Database Redo Transport Services in Private Networks and Adding an Encrypted Channel to Redo Transport Services using Transport Layer Security are available for use with the examples in this post.

Deploy Additional OCI Components

This section describes the additional OCI components necessary for the examples used in the post

ACL

Add additional ingress rules for access to the Target ATP and GG. Refer here for documentation.

SUBNET DESTINATION PROTOCOL SOURCE Notes
Target ATP Subnet 1522 TCP Target ATP Subnet Access From the GG Instance
Target ATP Subnet 1522 TCP Target ATP VCN Public Subnet Access from the Load Balancer
Downstream Public Subnet 1522 TCP Clients Client Access to the LB
GG Subnet 443 TCP Target ATP VCN Public Subnet Access from the Load Balancer
Downstream Public Subnet 443 TCP Clients Client Access to the LB

Initial State

It is assumed that the source and downstream databases are up and running with RTS enabled and functioning.

 

℘ Deploying a Target Autonomous Database ℘

In the downstream region, provision an ATP database with a private endpoint in the same private subnet as the downstream database. Download the Client Credentials to your client's home directory with the name Wallet_Target_ATP.zip

Refer to Privately Replicating Autonomous Databases Using Native GoldenGate for guidance and links to documentation.

Use the Admin password set in the PSSWRD variable i.e. YOurPwd123_#

Make a Note of the Private Endpoint IP and the Private Endpoint URL (hostname)


℘ Provisioning GoldenGate Marketplace ℘

In the downstream region, provision a GoldenGate marketplace 19c instance in the same private subnet as the downstream database and Target ATP

From the OCI console navigate to Marketplace > All Applications
  Search for GoldenGate
    Click Oracle GoldenGate for Oracle
      Change the Version to 19.1... (MicroServices Edition - v1.0) (6/1/2021) **NOTE THE VERSION **
      Choose the Compartment used for the downstream DB and Target ATP
      Check the box to review and accept the terms
      Click Download to view the latest Patch version 
        If it is different complete the download and review the document Upgrading Oracle GoldenGate Marketplace Image. You can apply the patch after GoldenGate is provisioned.       
      Click Launch Stack
  

Stack Information

Enter a Name e.g. GGMP19
Click Next

Configure Variables

Enter a Display Name e.g. GGMP19
Enter a Host DNS Name e.g. gg19

Choose the VCN Network Compartment used for the Downstream DB and the Target ATP
Choose the VCN used for the Downstream DB and the Target ATP
Choose the Subnet Network Compartment used for the Downstream DB and the Target ATP
Choose the Subnet used for the Downstream DB and the Target ATP

Select an Availability Domain
Accept the default Compute Shape for development purposes
Uncheck the Assign Public IP box

Accept the default Deployment 1 - Name i.e. Source
Change the Deployment 1 - Database to Oracle 19c
Accept the default Deployment 2 - Name i.e. Target
Change the Deployment 2 - Database to Oracle 19c (Assuming that is your ATP version provisioned above)
Check the Deployment 2 - Autonomous Database box
Choose the Deployment 2 - Autonomous Database Compartment 
Choose the Deployment 2 - Autonomous Database Instance provisioned above
Paste the SSH Public Key used for the downstream database
Click Next

Review the information and click Create.

When the Apply job for the stack succeeds, click on the job Name, click Associated Resources and click the Name in the row with Type oci_core_instance

Make a Note of the Private IP Address and the Internal FQDN (hostname)

Deployed State

℘ Adjusting the Downstream Load Balancer ℘

Adjust the load balancer in the downstream region for client access to the GG GUI. Refer here for documentation.

Update the Client Hosts File

Update the client etc/hosts file. Location of the file differs depending on the type of client. Example entries are below.



Adjust the Load Balancer

LB LISTENER
PORT
PROTOCOL BACKEND
Downstream Region LB 443 TCP GoldenGate private IP address

 

℘ Preparing the Downstream Mining Database ℘

Add additional parameters for GoldenGate, zip the TLS wallet contents for later and create the GoldenGate Administrative Schema

 

℘ Configuring GoldenGate Marketplace ℘

Prepare SSH

Create a temporary config file on your client with the entries below. Append these to the SSH config file. The default location is $HOME/.ssh/config. 

Create an SSH Configuration Entry

SSH into GG and Accept the Host Key

Manually exit back to your client.

Alter the Administrative Passwords

Change the passwords for the Service Manager, Source and Target deployments. Login using the user name and temporary password displayed by the following.

Service Manager

 Open a browser and enter the following URL:

Click the User icon and click Change Password
Enter the New Password i.e. YOurPwd123_# and click Save
You are logged out. Log back in.

Source Administration Server

Click on the Source deployment's Administration Server Port link
Click the User icon and click Change Password
Enter the New Password i.e. YOurPwd123_# and click Save
You are logged out. Log back in.

Target Administration Server

Return to the Service Manager and Click on the Target deployment's Administration Server Port link
Click the User icon and click Change Password
Enter the New Password i.e. YOurPwd123_# and click Save
You are logged out. Log back in.

Set Environmental Variables

Set the environment variables needed by GG

 

Configure the Source Deployment SQL*Net Components

Download the downstream tnsnames file and zipped wallet components, upload them and create a sqlnet.ora file

 

℘ Preparing the Target Autonomous Database ℘

Enable the GoldenGate administration schema and a schema used for replication.

Enable GoldenGate on the ATP

Connect to the ATP with a SQL client such as sql*plus or sql*developer.

Enable the GoldenGate Administrative Schema

Create the Replication Target Schema

 

℘ Preparing the Source Database ℘

Add additional parameters for GoldenGate, create the CDB GoldenGate Administrative Schema, create the schema to be replicated and create a PDB GoldenGate Administrative Schema.

Prepare the Source CDB

Prepare the Source PDB

 

Preparing GoldenGate Database Access 

Create, modify and validate credential connection strings to the data sources

Modify the Target Credential

Target ATP

Checking the box for the Target ATP in the GG provisioning process resulted in GG creating the target credential, extracting the wallet from ATP and configuring GG to use the wallet. What remains is to set the credential password of the ATP GG user and to validate the connection.


 

Create Source Credentials

Create credentials in the Source deployment and validate the following:

Source PDB

Source

Downstream

 

Preparing the GoldenGate Extract

Create heartbeat tables, specify the schema be replicated to GG and create the Extract with additional extract parameters

Add Heartbeat Tables

Heartbeat tables and functions monitor end-to-end replication lag. Heartbeats records are sent from each source database into the replication streams. The replication process uses these records and updates the information in them.

Add SCHEMA TRANDATA details

TRANDATA is a GG concept that enables unconditional logging of the primary key and the conditional supplemental logging of all unique key(s) and foreign key(s) of the specified table or tables in a designated schema to ensure row uniqueness in source and target tables. Specify the schema option to ensure replication continuity should DML occur on an object for which DDL has just been performed.

Add an Extract Group

An Extract Group consists of objects designed to capture source transaction data.

Register the Extract

Register the extract group to enable integrated capture mode and specify options for a multitenant container database

Add an Extract Trail

Create a trail for online processing on the local system and associate it with the Extract group.

Add Extract Parameters

Create an extract parameter file

Validate the Extract Parameters

Run the checkprm validation utility for an initial assessment of the specified parameter file. The result is PASS or FAIL.

Start the Extract

Start the extract, wait ten seconds, check the status and view the report

 

Preparing the GoldenGate Replication

Create a checkpoint table and the Replication with modified replication parameters

Create a Checkpoint Table

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

Add a Non-Integrated Replicat Group

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

Add Replicate Parameters

Create replicate parameter file

Validate the Replicat Parameters

Run the checkprm validation utility for an initial assessment of the specified parameter file. The result is PASS or FAIL.

Start the Replicat

Start the Replicat, wait ten seconds, check the status and view the report

Configured State

Click here to download a short clip of the configuration sequence.

 

Validating the GoldenGate Extract and Replication

Create a table and insert a row in the source PDB. Then query the table in the target DB.

Modify the Source PDB

Initiate a DDL Change

Initiate a DML Change

Query the Target ATP

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

 

℘ Flow ℘

Click here to download a short clip of the flow sequence.

Summary

This step-by-step guide demonstrated GoldenGate Marketplace 19c extracting data from a downstream mining database that is receiving real time changes from a source database via redo transport services through an encrypted transport layer security channel. It then demonstrated the application of these changes to an autonomous database.

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