X

Best Practices from Oracle Development's A‑Team

Using Connection Manager with Fusion Applications Warehouse and Oracle Analytics

Validated February 5 , 2021 with FAW 5.8 and OAC 5.9

Introduction

Fusion Applications Warehouse and native 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.

This post is a step-by-step guide for creating data visualization connections to private databases using Connection Manager in an Oracle Analytics instance unable to use Private Access Channel. It is part of the Oracle Analytics Private Endpoint Series.

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.

For OAC native instances, Private Access Channel is the preferred method for private egress. This post is for instances unable to use it.

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 oracle Analytics and connects to the databases on its behalf.

Validations

February 5, 2021 with FAW 5.8 and OAC 5.9 

Topics

Before You Begin

Deploying Additional Components

Preparing the Connect Descriptor

Creating a Connection via Connection Manager

Connection Flows

 

 Before You Begin and Assumptions  

Acronyms

FAW Fusion Applications Warehouse
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
   

 

Privileges

An account in an OCI tenancy for managing database and networking components.
An account in an OAC instance for creating data visualization (DV) connections.
An account / schema in a DB.
The DB host, port and service name.

FAW and OAC

A FAW or OAC-PE instance.

CMAN

A CMAN deployed as described here and listening for connections.

Domain Name System

Domain Name System (DNS) provides a worldwide, distributed directory service for translating a fully qualified domain name (FQDN) to its associated numerical IP address.

The default DNS in OCI provides resolution for resource names within the same VCN. Refer here for a post describing various DNS scenarios and the components necessary to resolve resource names outside of your VCN.

Networking Gateways

A networking gateway is required to connect to resources residing outside of a Virtual Cloud Network (VCN). A gateway is not required when resources are in the same VCN. Refer here for a post describing various gateway scenarios and the components necessary for network traffic between resources in different VCNs.

FAW Initial State with CMAN Deployed

 

OAC-PE Initial State with CMAN Deployed

 

 Deploying Additional Components 

These additional components must exist before using CMAN to connect to a database.

COMPONENT USE REFERENCE
SUBNET Hosts the database instance Link
ACCESS RULES Facilitates network traffic between CMAN and the DB Link
DATABASE A database listening for connections from CMAN and providing sessions to users Link

 

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

Virtual Cloud Network

VCN CIDR
VCN1 10.10.10.0/23

 

Subnet

Create or use an existing private subnet in the CMAN VCN to host the DB system. This post uses a subnet named DB-Subnet in its examples.

SUBNET TYPE CIDR
VCN1-DB-SN PRIVATE 10.10.10.32/27
VCN1-APP-SN PUBLIC 10.10.10.64/27

 

CMAN Compute Instance

VCN SUBNET TYPE Public IP
VCN1 VCN1-APP-SN LINUX 7 129.146.243.111

 

Internet Gateway

Create or use an existing Internet Gateway in the CMAN VCN for return traffic from CMAN to OAC.

VCN NAME
VCN1 VCN1-IG

 

Database System

Create or use an existing database system in the DB-Subnet. Make a note of the connection descriptor.

DB SYSTEM TYPE VCN SUBNET CONNECT DESCRIPTOR
VCN1-PV-DB VM VCN1 VCN1-DB-SN (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.34)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PHXHUBPV_phx33x.phxhubsndb.phxhubvcn.oraclevcn.com)))

 

Access Control

Define egress and ingress rules for network traffic between CMAN and the DB

SECURITY LIST TYPE CIDR PROTOCOL PORT ATTACHED TO NOTE
VCN1-DB-SL INGRESS 10.10.10.64/27 TCP 1521 VCN1-DB-SN Ingress from the CMAN subnet
VCN1-APP-SL EGRESS 10.10.10.32/27 TCP 1521 VCN1-APP-SN Egress to the database subnet
VCN1-APP-SL INGRESS 147.154.104.165/32 TCP 1521 VCN1-APP-SN Ingress to CMAN from the OAC OSN NAT gateway

 

Route Rules

ROUTE TABLE VCN DESTINATION CIDR TARGET ATTACHED TO NOTE
VCN1-APP-RT VCN1 147.154.104.165/32 Internet Gateway VCN1-APP-SN Response to OAC-OSN

 

FAW Enabled State

After identifying or deploying the required components the enabled states look like these:

 

OAC-PE Enabled 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 both the ADDRESS_TYPE / ADDRESS and CONNECT_DATA constructs for the DB. Refer here for documentation on connect descriptors and here for documentation on connecting to databases on OCI.

Begin the Advanced Connect Descriptor

Enter the initial phrase

Add the SOURCE_ROUTE Construct

Append the SOURCE_ROUTE construct

Append the CMAN ADDRESS Construct

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

Append the Remaining DB Details including the closing parentheses

Append the DB Address construct using its host, port, and service name. The host may be an IP address or a FQDN.

 

The Completed Advanced Connect Descriptor

 

 Creating a Connection via Connection Manager 

Connect to OAC

 

Create the Connection

Click Create > Connection from the OAC home screen. Select Oracle Database as the Connection Type. Complete the dialog using the advanced connect descriptor as shown below.

Enter a Connection Name 
Select Advanced as the Connection Type.
Enter your Advanced Connect Descriptor as the Connection String.
Enter the DB Username and Password

Click Save. 
 

 Connection Flows 

FAW Connection Flow

  • FAW sends the credentials and connection descriptor through its NAT Gateway to CMAN.
  • CMAN receives them from FAW. CMAN's security list allows the ingress on port 1521.
  • CMAN sends the credentials, the second address element in the connect descriptor and the service name to the DB. CMAN's security list allows the egress on port 1521.
  • DB receives them and creates a session. DB's security list allows the ingress on port 1521.
  • DB returns the status to CMAN.
  • CMAN's routes the response though its internet gateway back to FAW's NAT Gateway which sends it back to FAW.

 

OAC-PE Connection Flow

OAC-PE's flow is the same as FAW's.

Note: OAC-PE without PAC is for ingress only. Thus, it does nothing in this traffic flow. Its cluster in the OSN provides the public egress to CMAN and receives the response.

 

 Summary 

This post provided a step-by-step guide for creating data visualization connections to private databases using Connection Manager. You are now ready to create datasets and data flows to extend FAW. 

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