X

Best Practices from Oracle Development's A‑Team

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

Validated August 30, 2021 with GoldenGate 21c and Oracle Database 21c

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 21c 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 posts on Using Native GoldenGate with Encrypted Redo Transport Services and a Downstream Mining Database and Using GoldenGate Marketplace 19c 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

August 30, 2021 with GoldenGate Marketplace 21c and Oracle Database 21c

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
Downstream Private Subnet 1522 TCP Downstream Private Subnet Access From GG and the LB
Downstream Public Subnet 1522 TCP Clients Client Access to the LB
Downstream Private Subnet 443 TCP Downstream Public Subnet Access from the LB and SSH
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 21c 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 21.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. GGMP21
Click Next

Configure Variables

Enter a Display Name e.g. GGMP21
Enter a Host DNS Name e.g. ggmp21

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

Enter a Deployment Name i.e. GGMP21
Check the Deployment - Autonomous Database box
Choose the Deployment  - Autonomous Database Compartment 
Choose the Deployment  - Autonomous Database Instance provisioned above
Paste the same 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 Job 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 LB 443 TCP GoldenGate private IP address

 

℘ Preparing the Downstream Mining Database ℘

Add additional parameters for GoldenGate, zip the TLS wallet contents for later use 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 andAdministrative Server. 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.

Deployment Administration Server

Click on the 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 Deployment SQL*Net Components

During Provisioning the Deployment - Autonomous Database box was checked. This instructed GG to obtain the client credentials from the ADB. The TLS required files were placed in a directory under the one specified by the TNS_ADMIN variable. The sqlnet.ora and tnsnames.ora file were placed in the TNS_ADMIN directory.

Adjust the sqlnet.ora and tnsnames.ora files by appending entries for the source and downstream databases.

Append Parameters to sqlnet.ora

Obtain the Source / Downstream TLS Wallet Files

Download the TLS zip file created previously, upload it to the GG instance and extract the contents to the TLS wallet directory specified by the WALLET_LOCATION variable.

Create and Append the Source and Downstream TNS entries

Retrieve necessary information from the Source and Downstream database instances, create TNS entries for both and append them to the tnsnames.ora file.

 

℘ Preparing the Target Autonomous Database ℘

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

 

 

℘ Preparing the Source Database ℘

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

Prepare the Source CDB

Prepare the Source PDB

 

 

Preparing GoldenGate Database Access 

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

Target ATP

Checking the box for the ADB in the GG provisioning process resulted in GG creating a credential. What remains is to set the credential password of the ADB GG user and to validate the connection.


 

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

 

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 validates both the table creation (DDL) and the row insertion (DML). **

 

℘ Flow ℘

Summary

This step-by-step guide demonstrated GoldenGate Marketplace 21c 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