X

Best Practices from Oracle Development's A‑Team

Using Native GoldenGate with Encrypted Redo Transport Services and a Downstream Mining Database

Validated June 28, 2021 with GoldenGate 21c

Introduction

This post is part of a series that includes Using Oracle Database Redo Transport Services in Private NetworksAdding an Encrypted Channel to Redo Transport Services using Transport Layer Security and Using GoldenGate Marketplace 19c with Encrypted Redo Transport Services and a Downstream Mining DatabaseIt is a step-by-step guide demonstrating native GoldenGate 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.

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

June 28, 2021 with GoldenGate 21c and Oracle Database 19c

Topics

Before You Begin

Provisioning a Native GoldenGate Deployment

Deploying a Target Autonomous Database

Preparing the Downstream Mining Database

Deploying Oracle Connection Manager

Preparing the Source Database

Preparing the Target Autonomous 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.

Components

All components, 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 the ATP TLS port.

SUBNET DESTINATION PROTOCOL SOURCE Notes
R2VCN-PRV-SN 1522 TCP R2-VCN-PRV-SN CIDR From the GG Private Egress Endpoint

 

Initial State

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

 

℘ Provisioning a Native GoldenGate Deployment

In the downstream region, provision a native GoldenGate deployment with a private endpoint in the same private subnet as the downstream database. Deploy it with a public IP address also.

Refer to Deploying Native GoldenGate in the Oracle Cloud for guidance.

℘ 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. Make a note of the private IP address for later use.

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

 

℘ Preparing the Downstream Mining Database ℘

Run the following to SSH into the downstream database server. Add additional parameters for GoldenGate. Compress the TLS wallet contents for later use by Connection Manager. Restart the database.

 

℘ Deploying Oracle Connection Manager ℘

Native GG 21.1.0.0.1 requires the use of a proxy to connect to a non-autonomous Oracle database that uses a TLS enabled listener. Oracle Connection Manager meets this need as it can contain the TLS wallet required for the connection.

Deploy CMAN

Deploy Connection Manager into the public subnet of the Source DB's region and VCN.

Refer to Deploying Connection Manager as a Proxy for Oracle Services for links to documentation and guidance on installing Connection Manager. It's examples produce an SSH entry named CMAN-HOST. Stop after CMAN is installed.

Update the CMAN Configuration

Copy the source private IP

Download the source private IP file and upload it to the CMAN host

Create new configuration files

Download the TLS wallet and upload it to the CMAN host

Deploy the TLS wallet

Unzip the file to the wallet location specified in the configuration files. Startup CMAN.

Deployed State

℘ Preparing the Target Autonomous Database℘

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

Choose a Method for Private Access to the ATP

If you do not have private access to the ATP via VPN or FastConnect, a couple of access methods are (1) adding entries to the downstream load balancer for the ATP. The documentation can be referenced here or (2) use the downstream database server as an SSH local port forward as shown below.

Either method requires 1) modifying the compressed client credentials folder to change the host name or 2) Adding an entry to your /etc/host file for the ATP hostname that resolves to either the LB or the downstream DB hostname in the SSH configuration.

Enable GoldenGate on the ATP

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

Enable the GoldenGate Schema

Create the Replication Target Schema

 

℘ Preparing the Source Database℘

Run the following to SSH into the source database server, add additional parameters for GoldenGate, create the schema to be replicated and restart the database.

 

Preparing GoldenGate Database Access

Using the OCI Console navigate to Oracle Database > GoldenGate, select your compartment and the downstream region and click on Register Databases. Register the source, downstream and target databases. The documentation is here. The process differs depending on whether the DB type and protocol of the DB is in the same region as GG.

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.

Note: For development purposes all roles and privileges are granted to the Administrative Schemas.

Register the Source PDB

Create the GoldenGate Administrative Schema for the Source PDB

This schema contains the GG heartbeat tables.

Register the Schema

Run the following to get the database FQDN and connection string

From the Register Databases page in the OCI GG console, enter the following

Click Register Database
Enter SOURCE-PDB as the Name
Enter SOURCEPDB as the Alias
Ensure the Compartment is correct
Click Enter Database Information
Enter the Database FQDN from above as the Database FQDN
Enter the result from above as the Database Connection String (Including the first and last parentheses)
Enter GGADMIN as the Database User Name
Enter YOurPwd123_# as the Database User Password (this is the PW used in all examples)
Click Register

Register the Source DB

Create the GoldenGate Administrative Schema

Register the Schema

Run the following to get the database FQDN and connection string

From the Register Databases page in the OCI GG console, enter the following

Click Register Database
Enter SOURCE as the Name
Enter SOURCE as the Alias
Ensure the Compartment is correct
Click Enter Database Information
Enter the Database FQDN from above as the Database FQDN
Enter the result from above as the Database Connection String (Including the first and last parentheses)
Enter C##GGADMIN as the Database User Name
Enter YOurPwd123_# as the Database User Password (this is the PW used in all examples)
Click Register

Register the Downstream DB

Create the GoldenGate Administrative Schema

Register the Schema

Because the downstream database is in the same VCN as the GoldenGate private endpoint this example allows GG to look up and provide the database details. Native GG does this using the TCP port. If you require the TCPS port to be used, deploy another CMAN instance in the downstream's VCN public subnet and register the DB as you did for the source DB.

Click Register Database
Enter DOWNSTREAM as the Name
Enter DOWNSTREAM as the Alias
Ensure the Compartment is correct
Click Select Database
Select DB System Database (Bare Metal, VM, Exadata) from the Database Type dropdown
Select DOWNSTREAM from the Database System dropdown
Enter C##GGADMIN as the Database User Name
Enter YOurPwd123_# as the Database User Password (this is the PW used in all examples)
Check the box for Network Connectivity via Private Endpoint
Click Register

Register the Target ATP

Refer to Privately Replicating Autonomous Databases Using Native GoldenGate for guidance and an example.

Preparing the GoldenGate Extract

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

Create the Source PDB 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.

Login to the GG console. If you do not have the URL, open the GG Deployment in the OCI console and click Launch Console
From the default Administration Server page, open the menu in the top left corner and click Configuration
In the Credentials row for the SOURCEPDB Alias click the Connect icon
Click the Heartbeat sign
Accept the defaults and click Submit

Enter the 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.

Navigate to the Administration Server Configuration page
In the Credentials row for the SOURCEPDB Alias click the connect icon
Click the Schema button
Click the TRANDATA sign
Enter GGRTS_BLOG_USER as the Schema Name
Click Submit

Create an Extract

An Extract is the GoldenGate process that captures source transaction data

Find the Time Zone Offset for the Source DB

The Extract needs the parameter specifying the source DB timezone offset from UTC. Run the following. Your results might differ depending on the setting of the NLS_TIMESTAMP_TZ_FORMAT parameter.

ssh SOURCE
sqlplus sys/$PSSWRD AS SYSDBA <<EOF
select systimestamp from dual;
EOF
exit
#

The result shows the UTC offset.

 

Create the Extract

From the GoldenGate Deployment Console click Overview from the Administration Server menu if necessary
Click the Add Extract sign
Select Integrated Extract and click Next
Enter a Process Name e.g. TLSE1
Enter a two-character Trail Name e.g. tl
Make a note of it as it is used in the Replication process
Click the button for Downstream Capture
Select OracleGoldenGate from the Source Database Credential dropdown
Select the source DB alias i.e. SOURCE from the Credential Alias dropdown
Enter PDB1 for Register to PDBs (Above Downstream Capture)
Select OracleGoldenGate from the Downstream Mining Credential dropdown
Select the downstream DB alias i.e. DOWNSTREAM from the Credential Alias dropdown
Click Next


 

Add the following parameters to the extract.

TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)
TRANLOGOPTIONS SOURCE_OS_TIMEZONE -7     (As determined from the query result above)
SOURCECATALOG PDB1
DDL INCLUDE MAPPED
TABLE GGRTS_BLOG_USER.*;

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

 

Preparing the GoldenGate Replication

Create heartbeat tables, a checkpoint table and create the Replication with additional replication parameters

Create the Target ATP 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.

Login to the GG console. If you do not have the URL, open the GG Deployment in the OCI console and click Launch Console
From the default Administration Server page, open the menu in the top left corner and click Configuration
In the Credentials row for the TARGETATP Alias click the Connect icon
Click the Heartbeat sign
Accept the defaults and click Submit

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.

From the default Administration Server page, open the menu in the top left corner and click Configuration
In the Credentials row for the TARGETATP Alias click the Connect icon
Click the Checkpoint sign
Enter a two-part Checkpoint Table name e.g. GGADMIN.CHECKPOINT
Click Submit

Create a Replication

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

From the GoldenGate Deployment Console click Overview from the Administration Server menu if necessary
Click the Add Replicate sign
Select Nonintegrated Replicat and click Next
Enter a Process Name e.g. TLSR1
Select OracleGoldenGate from the Credential Domain dropdown
Select the Target ATP alias i.e. TARGETATP from the Credential Alias dropdown
Enter the two-character Trail Name used in the extract e.g. tl
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 PDB1.GGRTS_BLOG_USER.*, TARGET GGRTS_BLOG_USER.*;
Click Create and Run

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

Use a SQL client e.g. sql*plus, sql*developer, connect to the target ATP and query the table created above

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

 

℘ Flow ℘

 

Summary

This step-by-step guide demonstrated native GoldenGate 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