X

Best Practices from Oracle Development's A‑Team

Using Oracle Database Redo Transport Services in Private Networks

Validated June 2, 2021 Oracle Database 19c

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.

The purpose of this post is not how to set up redo transport services as there are dozens of existing blog posts and excellent documentation that do just that. It is to show the networking components required in Oracle Cloud Infrastructure when the databases are in different regions and in private subnets.

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 also shows the same using dynamic routing gateways. This is the first in a three-part series. Part two is Adding an Encrypted Channel to Redo Transport Services using Transport Layer Security. A future post describes enabling GoldenGate to use a duplicate of the receiving database as a "mining database" for filtering changes and additional  replication.

Validations

June 14 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
DRG Dynamic Routing Gateway
SSH Secure Shell
RPC Remote Peering Connection
ACL Access Control List - Security List Rule
RTR Route Table Rule
RAS Redo Apply Services

 

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.

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 Remote Database Systems link
DRG Allows the regional DBs to communicate using private IP addresses link
RPC Connection points for remotely peered VCNS link
SUBNET Hosts LB and DB Resources link
RTR Route Table Rules for the DRG 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.0.0/23 
R2-VCN 10.125.0.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.0.0/26
R1VCN-PRV-SN 10.225.0.64/26
R2VCN-PUB-SN 10.125.0.0/26
R2VCN-PRV-SN 10.125.0.64/26

 

DRG

A DRG acts as a virtual router, providing a path for traffic between your on-premises networks and VCNs, and can also be used to route traffic between VCNs.

Create a DRG in each region. This is required for enabling Data Guard on the source DB when the remote DB is in another region. The DRGs are used to peer the regional networks.

DRG
R1-DRG
R2-DRG

 

DRG ATTACHMENT

To use a DRG, it must be attached to a VCN

Attach the DRGs to the appropriate VCNs

DRG VCN
R1-DRG R1VCN
R2-DRG R2VCN

 

RPC Attachment

An RPC is a component you create on the DRG attached to your VCN. The RPC's job is to act as a connection point for a remotely peered VCN.

Create an RPC attachment for each DRG.

For the R1 region, click on the RPC and Note the OCID for use in peering the RPCs

DRG RPC
R1-DRG R1-DRG-RPC
R2-DRG R2-DRG-RPC

 

PEER the RPCs

Click on the RPC for the R2 region
Click Establish Connection
   Enter the R1 Region
   Enter the R1 RPC OCID
   Click Establish Connection

DRG RPC
R1-DRG R1-DRG-RPC
R2-DRG R2-DRG-RPC

 

RTR

The route tables provisioned by the VCN wizard are a sufficient starting point for development purposes.

Add an additional rule in each subnet's route table for the DRG. 

SUBNET DESTINATION TARGET
R1VCN-PUB-SN R2-VCN-CIDR R1-DRG
R1VCN-PRV-SN R2-VCN-CIDR R1-DRG
R2VCN-PUB-SN R1-VCN-CIDR R2-DRG
R2VCN-PRV-SN R1-VCN-CIDR R2-DRG

 

ACL

The security lists provisioned by the VCN wizard are a sufficient starting point for development purposes. Add additional ingress rules in each subnet's security list for the DBs.

SUBNET DESTINATION PROTOCOL SOURCE Notes
R1VCN-PUB-SN 1521 TCP R2-VCN-PRV-SN and Client CIDR Standby DB and Client
R1VCN-PRV-SN 1521 TCP R1-VCN-PUB-SN CIDR R1 LB
R2VCN-PUB-SN 1521 TCP R1-VCN-PRV-SN and Client CIDR Source DB and Client
R2VCN-PRV-SN 1521 TCP R2-VCN-PUB-SN CIDR R2 LB

 

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. A remote VM database system is enabled because it removes the need for manually configuring a remote database. While interesting, it is not the purpose of this post.

Download the Client Profile

Download it from here. Save 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

 

Enable a Private Standby VM Database System in Region 2

Wait for the Source DBS to enter the AVAILABLE State
   Under Resources click Databases
      Click the Database just created
         Under Resources click Data Guard Associations
            Click Enable Data Guard
                 Enter REMOTE as the Display Name
                 Select a different Region as the remote region aka R2
                 Select an Availability Domain
                 Select the VCN in the R2 region i.e. R2VCN
                 Select the private Subnet e.g. Private Subnet-R2VCN, R2VCN-PRV-SN
                 Enter a  Hostname Prefix e.g. RMT
                 Enter the same Password used for the Source e.g. YOurPwd123_#
                 Click Enable Data Guard

 

This step may take time while the Source DB is in an updating state.

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
REMOTE R2-VCN Private SN 10.125.10.112 SOURCE_phx2pp

 

Deployed Database State

Redo Transport Services are now active between the two databases using DRG-peered VCNs.

 

 

℘ Adjusting the Databases 

Adjust the Database Components to use Load Balancers rather than the DRGs to simulate use cases where the source network cannot be peered with the remote network.

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 R1-SRC-DB SQL*net
R1-LB R1-VCN-PUB-SN TCP 22 R1-SRC-DB SSH
R2-LB R2-VCN-PUB-SN TCP 1521 R2-RMT-DB Sql*net
R2-LB R2-VCN-PUB-SN TCP 22 R2-RMT-DB 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

Standby DB

Upload the Profile Template and Add it to the Login Profile

Source DB

Standby DB

Stop the Databases

Stop the Standby DB

Stop the 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 Standby DB

Change the 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

Restart the Source DB

Restart the Standby DB

Adjusted Database State

 

Validating Redo Transport Services

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 remote 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 remote 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 remote 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. 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 remote database using RTS.

 

℘ 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