X

Best Practices from Oracle Development's A‑Team

Connecting to Private Autonomous Databases using Connection Manager

Validated May 25, 2021

Introduction

Oracle services may require a proxy to reach data sources for various reasons including security, routing and privacy. One reason may be that the service is unable to use an Oracle Single Client Access Name (SCAN) IP Address. Another may be the service cannot provide the necessary wallet files required for Transport Level Security. For use cases requiring sophisticated functionality, Oracle's Connection Manager provides database transparency, high-availability, protocol conversion and enhanced security, scalability and performance.

This post is a step-by-step guide for configuring Connection Manager for autonomous databases and creating a TNS connect descriptor that can be used in Oracle services e.g. Analytics, GoldenGate to reach private autonomous databases. 

Refer to Understanding Oracle Connection Manager Architecture for details on the architecture shown below.

 

Use Cases

Private egress and access control are described in this post.

Private Egress provides access to private databases using Connection Manager as a proxy.

Access Control provides solutions for database access when its subnet allows ingress only from designated application subnets. A Connection Manager instance in such a subnet receives connection requests from remote services and connects to the databases on their behalf.

Validations

May 25, 2021

Topics

Before You Begin

Deploying Additional Components

Preparing CMAN for the ADB

Preparing the Advanced Connect Descriptor

Creating a Connection via Connection Manager

 

 Before You Begin and Assumptions  

Acronyms

GG GoldenGate
OAC Oracle Analytics Cloud
PE Private Endpoint
CMAN Connection Manager
ADB Autonomous Database
LB Load Balancer
OCI Oracle Cloud Infrastructure
FQDN Fully Qualified Domain Name
PAC Private Access Channel
OSN Oracle Services Network
TNS Oracle Transparent Network Substrate

 

Privileges

An account in an OCI tenancy for managing GoldenGate, Database, and Networking components.
Credentials for the ADB
The TNS connect descriptor for the ADB

CMAN

A CMAN deployed as described here and listening for connections.

Initial State

 

 Deploying Additional Components 

These additional components must exist before using CMAN to connect to an ADB

COMPONENT USE REFERENCE
NSG Must exist for ATP provisioning but is not used Link
ACCESS RULES Facilitates network traffic between the Oracle Service, CMAN and the ADB Link
ADB An ADB listening for connections and providing sessions to users Link
ACCESS RULES Facilitates network traffic between the Oracle Service, CMAN and the ADB Link

 

The following tables show components both needed and previously deployed (greyed out).

Virtual Cloud Network

REGION VCN Notes
Region 1 VCN1 The VCN hosting CMAN

 

Subnet

SUBNET TYPE Notes
VCN1-PRIVATE-SN PRIVATE Hosts the ADB
VCN1-PUBLIC-SN PUBLIC Hosts CMAN

 

NSG

VCN NOTES
VCN1 Create. It must exist for ATP provisioning but is not used

 

ADB

NAME TYPE SUBNET Notes
CMAN-ATP ATP VCN1-PRIVATE-SN Create an ATP with a PE in the CMAN VCN. Download the DB Connection Zip File

Save the private IP address. It is used for the CMAN configuration.

 

Access Control

Add Security List Rules to the default lists provided by the networking wizard.

SECURITY LIST TYPE FROM TO PROTOCOL PORT
VCN1-PUBLIC-SL INGRESS CLIENT CMAN TCP 1521
VCN1-PUBLIC-SL EGRESS CMAN ATP TCP 1522
VCN1-PRIVATE-SL INGRESS CMAN ATP TCP 1522
           

 

Route Rules

The default rules provided by the networking wizard are sufficient.

 

 Preparing CMAN for the ADB 

CMAN requires the wallet for the ATP(s) it connects to. The CMAN deploying post example used an entry in the SSH configuration file named CMAN-HOST

Copy the ATP Credential Zip File to the CMAN instance

SSH into the CMAN instance

Unzip it to the $TNS_ADMIN directory

Create a sqlnet.ora file with the Wallet Location

Prune the cman.ora File

Append the Next Hop Clause to the cman.ora File

Append the Wallet Location to the cman.ora File

Restart CMAN

CMAN for ATP Deployed State

 

℘ Preparing the Advanced Connect Descriptor 

A connect descriptor is contained within a DESCRIPTION construct. Using an editor on your client, create an advanced connect descriptor that contains a SOURCE_ROUTE construct, the ADDRESS construct for the CMAN listener, and the CONNECT_DATA and security constructs for the ADB. Refer here for documentation on connect descriptors.

Display the ADB Connect Descriptors for Reference

Begin the Advanced Connect Descriptor

Note: Some tools require the connect descriptor to be on a single line. 

Begin a file with the descriptor

Add the SOURCE_ROUTE Construct

Append to the end of the line the SOURCE_ROUTE construct

Append the CMAN ADDRESS Construct

Append to the end of the line the CMAN Address construct using its host and port. The host must be the public IP address.

Append the Connect and Security DB Constructs including the closing parentheses

          Append to the end of the line the last part of the connect descriptor found in the tnsnames file for the desired net service name. Starting with "(connect_data"

The Completed Advanced Connect Descriptor

For example:

The above result is one line but  is wrapped in the browser window.

 Creating a Connection via Connection Manager 

 

Use SQL*Plus to Create a Connection

Execute a sqlplus command in the form of sqlplus admin/<your password>@<your connect descriptor> e.g.

This forces the connection to go through CMAN where the ATP host, port and wallet are added before sending the connection request to ATP.

 

 Summary 

This post provided a step-by-step guide for creating a TNS connect descriptor that leverages Connection Manager.

For other posts relating to analytics and data integration visit http://www.ateam-oracle.com/dayne-carley