X

Best Practices from Oracle Development's A‑Team

Connecting to Dedicated Autonomous Databases using Connection Manager and Oracle Analytics

Validated July 27, 2021 with OAC 6.1

Introduction

Oracle Analytics instances may require a proxy to reach data sources for various reasons including security, routing and privacy. For use cases requiring sophisticated functionality, Oracle's Connection Manager provides database transparency, high-availability, protocol conversion and enhanced security, scalability and performance.

One such case is accessing autonomous databases on dedicated Exadata infrastructure (ADB-D). This post is a step-by-step guide to creating a data visualization connection to an ADB-D using Transport Layer Security (TLS) and Connection Manager as a public proxy.

About Connection Manager

Oracle Connection Manager is a proxy server that forwards connection requests to databases or other proxy servers. It usually resides on an application server separate from the database server and database clients.

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

 

Validations

July 27, 2021 with OAC 6.1

Topics

Before You Begin

Deploying Additional Components

Deploying ATP-D

Deploying Connection Manager

Configuring Connection Manager

Creating a TLS Connection to ATP-D via Connection Manager

Connection Flows

 

 Before You Begin and Assumptions  

Acronyms

OAC Oracle Analytics Cloud
PE Private Endpoint
CMAN Connection Manager
DB Database
DV Data Visualization
OCI Oracle Cloud Infrastructure
FQDN Fully Qualified Domain Name
PAC Private Access Channel
OSN Oracle Services Network
ADB-D Dedicated Autonomous Databases
AEI Autonomous Exadata Infrastructure
ACD Autonomous Container Database
SCAN Single Client Access Name
DNS Domain Name System
ATP-D Dedicated Autonomous Transaction Processing

Note: Connection Manager can be used with Autonomous Data Warehouse and Autonomous Transaction Processing, This post uses an ATP-D  as the ADB in the examples.

Privileges

An account in an OCI tenancy for with compartment privileges to manage autonomous database, network and compute components, and to use existing Autonomous Container Database on Autonomous Exadata Infrastructure.

Existing Autonomous Exadata Infrastructure

An instance of Autonomous Exadata Infrastructure hosting an Autonomous Container Database required for the ATP-D. Refer here and here for documentation.

Existing OAC

An instance of OAC. The OAC can have either a public or private endpoint. This post uses a public endpoint.

Private Endpoints

The ATP-D in these examples has a private endpoint.

Connection manager can be used with an OAC configured with a private endpoint although that would not change the architecture or methods used in the examples. The outbound connection to CMAN would still use the NAT gateway in the Oracle Services Network.

Note: If using OAC with a private endpoint it is assumed that the necessary components are in place for you to access it via a browser.

Remote Data Gateway or Private Access Channel can be used with CMAN but neither are used in this post.

Both allow connection manager to be in a private rather than public subnet.
RDG can connect to a private ATP-D without CMAN. So a requirement different from a proxy must justify the use CMAN.
PAC can not connect to a private ATP-D without CMAN. PAC requires the use of a hostname and at this time a limitation prevents the use of a hostname associated with a SCAN database listener. (ATP-D uses a SCAN database listener).

 

 Deploying Additional 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

 

ACL Rules

Add additional INGRESS rules to the security lists for the ATP-D and CMAN ports.

SECURITY LIST CIDR - Development Only PROTOCOL PORT NOTE
ACD-PUB-SN-SL 0.0.0.0/0 TCP 1630 From OAC and Clients
ACD-PRV-SN-SL 0.0.0.0/0 TCP 2484 From CMAN to the ATPD TCPS Port

 

Initial State

 

 Deploying ATP-D 

Deploy an ATP-D instance in an ACD and download the client credentials zip file to your home directory. Refer here for guidance.

Name the download Wallet_ATP.zip for use in subsequent examples.

 

 

 Deploying Connection Manager 

Deploy Connection Manager into a public subnet of the ATP-D's VCN.

Prepare Connection Manager

Create a Compute Instance

Create a compute instance in the ATP-D's public subnet for CMAN.

Create SSH Configuration File Entries

Prepare the CMAN host

Connection Manager Download

Browser

Click this address to open a browser window,

Search for LINUX.X64_193000_client.zip

Click to Download 
Sign-in if necessary. Accept the License Agreement
If prompted, choose a download Location and save and/or start the download

Find the Download-In-Progress location for your browser and stop or pause the download
Copy the address and paste it into a notepad or text document.

Stop, clear or remove the download.

CMAN Host

Install Connection Manager

Post-Install Steps

Deployed State

 Configuring Connection Manager 

Adjust Connection Manager for the ATP-D Client Credentials

Upload the ATP-D Client Credentials

Deploy the ATP-D Client Credentials

This example uses the *low_tls network alias to obtain the service name. Change the first variable use another.

Configure Connection Manager

Create a CMAN configuration file (cman.ora). The NEXT_HOP parameter forwards connection requests to the ATP-D.

Start Connection Manager

Configured State

 Creating a TLS Connection to ATP-D via Connection Manager 

Show the CMAN Public IP and ATP-D Service Name

Connect to OAC

Create the TCPS Connection

Click Create > Connection from the OAC home screen. Select Oracle Database as the Connection Type. Complete the dialog as shown below and click Save.

Enter a Connection Name 
Enter the CMAN Public IP from the result above as the Host
Enter the CMAN Port i.e. 1630 as the Port
Enter the ATP-D Service Name from the result above as the Service Name
Enter the ATP-D Username and Password

 

 

 Connection Flow 

OAC Connection Flow

 

User requests data from the ATP-D connection.

OAC sends the SQL with the ATP-D credentials and service name to CMAN

CMAN sends the SQL, credentials, service name, and wallet certificate to the ATP-D. 

Note: OAC connects only to CMAN. CMAN connects only to the ATP-D. Only CMAN is white-listed in the ATP-D subnet.

 

 

 Summary 

This post provided a step-by-step guide for creating a data visualization connection to an ADB-D using Transport Layer Security (TLS) and Connection Manager as a public proxy. 

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

Recent Content