X

Best Practices from Oracle Development's A‑Team

Adding an Encrypted Channel to Redo Transport Services using Transport Layer Security

Validated June 9, 2021 with Oracle Database 19c

Introduction

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.

This post builds upon the examples used in Using Oracle Database Redo Transport Services in Private Networks.

This post is a step-by-step guide for creating a TLS encrypted channel in Oracle Cloud Infrastructure between databases in different regions and in private subnets participating in Redo Transport Services. It is the second in a three-part series. A future posts describes enabling GoldenGate to use TLS enabled Redo Transport Services and using the receiving database as a "mining database" for filtering changes and additional replication.

Validations

June 9, 2021 with Oracle Database 19c

Topics

Before You Begin

Deploying Additional OCI Components

Preparing the TLS Components

Exchange the TLS Certificates

Adjusting the Firewalls

Adjusting the Load Balancers

Adjusting the Databases

Validating Redo Transport Services with TLS

 

 Before You Begin and Assumptions ℘

Acronyms

RTS Redo Transport Services
VCN Virtual Cloud Network
DB Database
LB Load Balancer
OCI Oracle Cloud Infrastructure
DRG Dynamic Routing Gateway
SSH Secure Shell
RPC Remote Peering Connection
ACL Access Control List - Security List Rule
RTR Route Table Rule
TLS Transport Level Security

 

This post uses the terms Source and R1 for the database and region sending the redo and Remote and R2 for the database and region receiving the redo. It assumes:

Components

All components, credentials and privileges created in the post Using Oracle Database Redo Transport Services in Private Networks are available for use for the examples in this post.

Initial State

 

 

 Deploying Additional OCI 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
ACCESS CONTROL Allows Ingress and Egress for VCN Traffic   link

 

ACL

Add additional ingress rules in each subnet's security list for the DB Listener TLS port. These examples use the Oracle officially registered port number of 2484 for TCP/IP with TLS.

SUBNET DESTINATION PROTOCOL SOURCE Notes
R1VCN-PUB-SN 2484 TCP R2-VCN-PRV-SN and Client CIDR From the Remote DB and Client
R1VCN-PRV-SN 2484 TCP R1-VCN-PUB-SN CIDR From R1 LB
R2VCN-PUB-SN 2484 TCP R1-VCN-PRV-SN and Client CIDR From the Source DB and Client
R2VCN-PRV-SN 2484 TCP R2-VCN-PUB-SN CIDR From the R2 LB

 

 

 

℘ Preparing the TLS Components 

The TLS wallet and certificate components need to be created for each DB. Documentation on the utility used can be found here

For development purposes, self-signed certificates are used in this post. The best practice is to create a certificate signing request and have it signed by a trusted certificate authority.

Prepare the Wallets and Certificates

Create a wallet location for each DB, create a wallet, add a self-signed certificate, and export the certificate for downloading. Also create variable export statements for the DB Unique Names and the FQDNs.

Source DB

Remote DB

 

 

 

℘ Exchange the TLS Certificates 

Add the source certificate to the remote database wallet and add the the remote certificate to the source database wallet.

Download the DB Certificates and Variables

From your client download both certificates. Download and merge the variable export statements

 

Upload the Certificates and Variables and add the Partner DB Certificate

Source DB

Upload the Remote certificate to the Source and add it to the Source TLS wallet.  Change the permissions on the wallet directory files.

Remote DB

Upload the Source certificate to the Remote and add it to the Remote TLS wallet.

 

 

℘ Adjusting the Firewalls 

VM database systems are not provisioned with port 2484 open. SSH to each DB as the opc user, get a yum repository, install the firewall utility and open port 2484

Source DB

Remote DB

 

 

℘ Adjusting the Load Balancers 

Adjust the Load Balancers to Listen for TLS connections. The documentation can be referenced here

In each region, add a third listener, backend set and backend to the public load balancer. Specify port 2484 for the listener and backend. The protocol remains TCP. Do not specify SSL. The backend IPs remain the private DB IPs. Below is a pic of the first region.

 

 

℘ Adjusting the Databases 

Adjust the Database Networking Components to use TLS. These examples use the protocol value TCPS for TCP/IP with TLS as documented here.

Stop the Databases and the Listeners

Source DB

Determine the encryption wallet location, set the Source DB to defer (stop) redo transport services and shut it down. Stop the listener as well.

Ensure the encryption_wallet_location is correct.

Remote DB

Determine the encryption wallet location, set the Remote DB to cancel (stop) redo apply services and shut it down. Stop the listener as well.

Ensure the encryption_wallet_location is correct.

Adjust the SQL*net Components

Source DB

TNSnames.ora

Prepare DB specific sets of TLS connect descriptors for later downloading. Use the original names for TLS entries. This eliminates the need to change the database parameters that reference the original names. Rename the original entries with a -TCP suffix. 

Listener.ora

Prepare a listener.ora file. Add the TCPS address construct, wallet location and set ssl_client_authentication=false as documented here.

SQLnet.ora

Prepare a sqlnet.ora file. Add the wallet_location and set ssl_client_authentication=false to match the listener.ora setting. Set ssl_server_dn_match=yes to enforce server-side certification validation through distinguished name (DN) matching.

Remote DB

TNSnames.ora

Listener.ora

SQLnet.ora

Merge and Upload the TNSname Entries

Download the entries from both DBs, merge them, and upload the merged set to each DB

Restart the Databases and Listeners

Source DB

Start the listener. Startup the Source DB and set it to enable (start) redo transport services.

Remote DB

Start the listener. Mount the Source DB and set it to recover (start) redo apply services.

Configured State

Below is the TLS configured state with configured items shown in green. Click here for a short clip of the configuration sequence.

 

Validating Redo Transport Services with TLS

Modifying Source DB Content

The following SQL run on the source pluggable DB creates a new user, a new table and inserts a row.

Using Database Views

If the RTS target is not a standby database, DB views may be the primary validation option. Many views listed here contain relevant RTS information. A very useful one is V$ARCHIVE_DEST on the source DB. It offers a quick view of the RTS status. Usually the destination id is 2 for a standby destination but the following query shows all destinations with an id less than 5.

The desired status value is VALID for the remote destination.

ID STATUS    Archive_dest                                       Error
-- ---------        -------------------------                              -----------------------------------------------------------------
 1 VALID        USE_DB_RECOVERY_FILE_DEST
 2 VALID        SRCDGPRV_phx1rs
 3 INACTIVE
 4 INACTIVE
 5 INACTIVE
 

Using Redo Apply Services

With standby databases RAS applies the changes received from RTS to the database. The default state of a physical standby database is mounted but not open. To view changes it must be opened in READ ONLY mode.

On the standby DB stop receiving redo, open the pluggable DB in read only mode, restart receiving redo and query the source changes

You see:

Database altered.
Database altered.
Database altered.
Pluggable database altered.
Connected
CO COUNTRY_NAME                  REGION_ID
-- ---------------------------------------- ----------
KN Kingdom of the North                  5

This result shows the user creation, table creation and row insertion were replicated to the standby database using RTS and TLS.

 

℘ TLS Flow ℘

Below is the TLS connection flow. Click here for a short clip of the flow sequence.

 Summary 

This post provided a step-by-step guide for creating a TLS encrypted channel in Oracle Cloud Infrastructure between databases in different regions and in private subnets participating in Redo Transport Services.

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