X

Best Practices from Oracle Development's A‑Team

Using Oracle Database Redo Transport Services in Private Networks

Validated July 22, 2021 with Oracle Database 19c and 21c

Introduction

Redo Transport Services performs the automated transfer of redo data from one databases 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.

This post is a step-by-step guide for demonstrating redo transport services on Oracle Cloud Infrastructure with private databases in separate regions using public load balancers as proxies. It is part of a series that includes Adding an Encrypted Channel to Redo Transport Services using Transport Layer SecurityUsing 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.

Validations

July 22, 2021 with Oracle Database 19c and 21c

June 24 with Oracle Database 19c

Topics

Before You Begin

Deploying Additional OCI Components

Deploying the Databases

Adjusting the Databases

Validating Redo Transport Services

 

Before You Begin and Assumptions ℘

Acronyms

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

 

This post uses the terms Source and R1 for the database and region sending the redo and Downstream and R2 for the database and region receiving the redo.

Privileges

A user account and compartment in an OCI tenancy for managing database and network resources.

Ensure to use the same compartment for all resources.

 

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
VCN Hosts the Source and Downstream Database Systems link
SUBNET Hosts LB and DB Resources link
ACCESS CONTROL Allows Ingress and Egress for VCN Traffic link

 

VCN

Create two VCNs in separate regions with non-overlapping CIDR blocks using the VCN QuickStart wizard e.g.

VCN CIDR
R1-VCN 10.225.10.0/23
R2-VCN 10.125.10.0/23

 

Subnet

Specify CIDR blocks for the wizard to create a public and private subnet in each VCN e.g.

Subnet CIDR
R1VCN-PUB-SN 10.225.10.0/25
R1VCN-PRV-SN 10.225.11.0/25
R2VCN-PUB-SN 10.125.10.0/25
R2VCN-PRV-SN 10.125.11.0/25

 

ACL RULES

Add additional rules in each subnet's security list for the DBs.

SUBNET TYPE SOURCE / DESTINATION PROTOCOL SOURCE / DESTINATION Notes
All Ingress 1521 TCP All For Development Purposes Only

Additional Component State

 

Deploying the Databases

VM database systems are used in this post because they remove the need for DB software installations, OS configurations and DB creations.

Download the Client Profile

Download it from hereSave it to your HOME directory with the name .client_profile Note the period in front of the name.

Provision a Private VM Database System in Region 1

The documentation can be referenced here.

From the OCI console navigate to Oracle Database > Oracle Database / Bare Metal, VM, and Exadata
Choose your Compartment and the Region for the source DB aka R1
Click Create DB System

DB System Information

Enter SOURCE as the DBS Name
Accept the default Software Edition i.e. Enterprise Addition High Performance for an Active Data Guard deployment
Select Logical Volume Manager for a quick deployment
Generate a new SSH key pair or Upload an existing public key
Select License Included
Select the VCN created earlier i.e. R1VCN
Select the private Client Subnet e.g. Private Subnet-R1VCN, R1VCN-PRV-SN
Enter SRC as the Hostname Prefix
Click Next

Database Information

Enter SOURCE as the Database Name
Enter SOURCE as the Database Unique Name Suffix
Enter pdb1 as the PDB name
Enter the Password and Confirm e.g. YOurPwd123_#
Click Create DB System

When the DB System reaches the Available state, Make a note of the Password and add it to the client profile..

Make a note of the IP address found on the Nodes page for use with the load balancer.

DBS SUBNET EXAMPLE IP UNIQUE NAME
SOURCE R1-VCN Private SN 10.225.10.74 SOURCE_SOURCE

 

Provision a Private VM Database System in Region 2

Use the same process for the Downstream DB in region 2. Change the name and unique suffix to "Downstream"

 

Make a note of the IP address found on the Nodes page for use with the load balancer.

DBS SUBNET EXAMPLE IP EXAMPLE UNIQUE NAME
  R2-VCN Private SN 10.125.10.112 DOWNSTR_DOWNSTREAM

 

Deployed Database State

 

Adjusting the Databases

Adjust the Database Components to use public Load Balancers

Create the Public Load Balancers

Public Load Balancers act as proxies directing incoming public traffic to the private DBs

The documentation can be referenced here

Create a public load balancer in each region with two listeners, two backend sets, and two backends. One listener is for SSH traffic on port 22 and one for DB traffic on port 1521. The backends are both for the private DB IP.

Make a note of the LB Public IP addresses and update the client_profile file.

LB SUBNET PROTOCOL PORT BACKEND IP Notes
R1-LB R1-VCN-PUB-SN TCP 1521 SOURCE SQL*net
R1-LB R1-VCN-PUB-SN TCP 22 SOURCE SSH
R2-LB R2-VCN-PUB-SN TCP 1521 DOWNSTREAM Sql*net
R2-LB R2-VCN-PUB-SN TCP 22 DOWNSTREAM SSH

 

Update the Profile Template

Enter the LB public IPs, the DB unique names, and the password.

Run it to have the variables take effect. Note the space between the first period and the $ sign.

Create SSH Configuration File Entries

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

Note: Windows users may need to use puTTY.

Enhance SSH Privileges for Oracle

Grant direct SSH access to the Oracle user

Source DB

 

Downstream DB

 

Upload the Profile Template and Add it to the Login Profile

Source DB

Downstream DB

Copy the Oracle Password File to the Downstream DB

Copy the Oracle Password File

Source DB

Client

Downstream DB

Export and Share the TDE master key

Export and share the TDE master key, restart the Downstream DB

Source DB

Client

Downstream DB

Share Environmental Variables

Export and share the UNQNAME, SID, and Service Names

Source DB

Downstream DB

Client

Downstream DB

Create Standby Redo Logs

Create standby redo logs on the Downstream DB.

This script assumes the database system was deployed with three redo log groups which leads to four standby groups. If this is not the case, adjust the script.

Downstream DB

Add Parameters for RTS

Add the necessary parameters for RTS.

Downstream DB

Source DB

SQL*Net components

Change the entries in the tnsnames.ora file to use the LB. Add entries for the pluggable databases. Change the sqlnet.ora to use Ezconnect.

Downstream DB

Source DB

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

Downstream DB

Adjusted Database State

 

Validating Redo Transport Services

Using Database Views

Note: It can take 15 minutes for RTS to make the initial connections to each database.

Because the Downstream RTS target is not a standby database, DB views and the Downstream DB's file system are the best validation options. Many views listed here contain relevant RTS information.

V$ARCHIVE_DEST

A very useful one is V$ARCHIVE_DEST on the source DB. It offers a quick view of the RTS status. The following query shows the RTS destination and it's status.

The desired status value is VALID for the Downstream destination.

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

V$DATAGUARD_PROCESS

Using this view on both DBs shows how the Downstream DB receiving process is linked to the process ID for the source DB writing process.

You see the process linkage as well as the both databases are processing the same log sequence #.

Downstream Standby Archive Logs

Listing the standby archive log files shows that redo data from the source arrived into a standby redo log and was archived.

You see the last archived file shows the last completed log sequence # (It should be the same as or one less than the current)

℘ RTS Flow ℘

Summary

This post provided a step-by-step guide for for demonstrating redo transport services on Oracle Cloud Infrastructure with private databases in separate regions using public load balancers as proxies. It also showed the same using dynamic routing gateways.

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