Validated June 9, 2021 with Oracle Database 19c
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.
June 9, 2021 with Oracle Database 19c
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 ℘
|RTS||Redo Transport Services|
|VCN||Virtual Cloud Network|
|OCI||Oracle Cloud Infrastructure|
|DRG||Dynamic Routing Gateway|
|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:
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.
℘ 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.
|ACCESS CONTROL||Allows Ingress and Egress for VCN Traffic||link|
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.
|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.
℘ 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
Upload the Remote certificate to the Source and add it to the Source TLS wallet. Change the permissions on the wallet directory files.
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
℘ 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
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.
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
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.
Prepare a listener.ora file. Add the TCPS address construct, wallet location and set ssl_client_authentication=false as documented here.
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.
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
Start the listener. Startup the Source DB and set it to enable (start) redo transport services.
Start the listener. Mount the Source DB and set it to recover (start) redo apply services.
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
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
Pluggable database altered.
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