X

Best Practices from Oracle Development's A‑Team

Connecting to Real Application Clusters using Connection Manager and Oracle Analytics

Validated February 11 , 2021 with OAC 5.9

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 Oracle Real Application Clusters (RAC) databases. This post is a step-by-step guide for creating data visualization connections to RAC databases using Connection Manager and Oracle Analytics Private Access Channel. It is part of the Oracle Analytics Private Access Channel Series.

About RAC Databases

RAC databases comprise multiple instances with each running on one of multiple nodes. The service names of these databases may be assigned to one or more instances.

 

Single Client Access Name (SCAN) provides load-balancing and failover for RAC databases. Behind the scenes, the SCAN host name stored in DNS returns one of three SCAN Listener IP addresses in a round-robin fashion. The SCAN listener chosen forwards the request to an available local listener using a virtual IP address assigned to a running node.

This is another view of the flow:

Flow Details:

  • OAC issues a connection request using the SCAN fully qualified domain name
  • DNS resolves the name with the next SCAN IP from the round-robin pool
  • A net services load balancer determines the virtual IP with the lightest load 
    • (A Virtual IP is usually associated with a node IP but will "float" to another node during failover)
  • The node IP currently associated with the virtual IP is returned to the load balancer
  • The request arrives at the local listener running on the database node with that IP

Usually database administrators hide this complexity by providing only the SCAN hostname, the SCAN listener port (which can differ from the local listener ports) and a service name. This allows an easy connect string like: <rac-scan-fqdn>:<rac-scan-port>/<service name

This what is usually viewable by database users:

RAC Databases with Oracle Analytics

Oracle Analytics Using Private Access Channel

Currently, Private Access Channel instances are unable to reach RAC databases via a SCAN hostname.

The Oracle Connection Manager method is used in this post.

You may be able to use the Advanced Connection String option above if you are able to obtain the following:

  • The fully qualified domain names in DNS that are associated with the virtual or node IP addresses
  • The port number each local listener on that node is using 
    • (they can be different from each other and different than the SCAN port)
  • Access is allowed to the local listener ports
    • (they may only be open to internal database processes)

Note: using the Advanced Connection String option negates the flexibility provided by SCAN. There is no guarantee that nodes will not be de-commissioned and their names removed from DNS, new nodes added, and / or local listener port numbers changing.

 

Oracle Analytics Using Remote Data Gateway

Remote Data Gateway instances can use SCAN hostnames.

Native Oracle Analytics

Oracle Analytics Instances without Remote Data Gateway and Private Access Channel are unable to use SCAN hostnames usually because they are not resolvable via DNS and the private IP addresses returned are not accessible.

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.

 

 

Connection Manager Use Cases

Private egress and access control are two described in this post.

Private Egress: Connection Manager is used as a proxy to certain databases for Private Access Channel connections.

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 11, 2021 with 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

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
RAC Real Application Clusters
SCAN Single Client Access Name
DNS Domain Name System
   

 

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 RAC DB.
The RAC SCAN hostname, port and service name.

OAC

An OAC instance with PAC having CMAN's domain defined as a data source.

 

CMAN

A CMAN deployed as described here and listening for connections.

Domain Name System

DNS provides a worldwide, distributed directory service for translating FQDNs to their 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 CMAN names outside of your VCN.

Networking Gateways

A networking gateway is required to connect to CMAN instances residing outside of OAC's 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.

Public OAC Initial State

 

Private OAC Initial State

 

 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 PRIVATE 10.10.10.64/27
VCN1-OAC-SN PRIVATE 10.10.10.0/27

 

CMAN Compute Instance

VCN SUBNET TYPE FQDN
VCN1 VCN1-APP-SN LINUX 7 yourcman.vcn1appsn.vcn1vcn.oraclevcn.com

 

Database System

Create or use an existing RAC database system in the DB-Subnet. Make a note of the Easy Connect connection string.

DB SYSTEM TYPE VCN SUBNET CONNECT STRING
VCN1-RAC-DB VM 2 Node VCN1 VCN1-DB-SN <your-rac-scan>.<your-db-subnet>.<your-VCN>.oraclevcn.com:1521/<Your Service name>

 

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 from CMAN to the database subnet
VCN1-APP-SL INGRESS 10.10.10.0/27 TCP 1521 VCN1-APP-SN Ingress to CMAN from the OAC subnet
VCN1-OAC-SL EGRESS 10.0.0.64/27 TCP 1521 VCN1-OAC-SN Egress to CMAN subnet

 

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

 

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

Public OAC Enabled State

 

Private OAC Enabled State

 

 Preparing the CMAN RAC Connect Descriptor 

A connect descriptor is contained within a DESCRIPTION construct. Create an advanced connect descriptor that contains a SOURCE_ROUTE construct, ADDRESS constructs for the CMAN listener and database and the CONNECT_DATA construct for the service name. Refer here for documentation on connect descriptors and here for documentation on connecting to databases on OCI.

This post uses Linux / Mac OS shell commands. Ensure to keep the indentation provided. The complete descriptor follows the constructs.

Add the Initial Descriptor Construct

 

Add the SOURCE_ROUTE Construct

Add the SOURCE_ROUTE construct.

 

Add the CMAN ADDRESS Construct

Add the CMAN ADDRESS construct using its FQDN and port. The host must be the FQDN.

 

Add the RAC ADDRESS Construct

Add the RAC ADDRESS construct using its SCAN FQDN and port. The host must be the SCAN FQDN.

 

Add the CONNECT_DATA Construct

Add the CONNECT_DATA construct using the RAC application or PDB service name

 

The Complete 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 

Public OAC Connection Flow

  • OAC sends the credentials and the advanced connect descriptor to CMAN.
  • CMAN receives them from OAC. 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 returns the response to OAC.

Note: OAC connects only to CMAN. CMAN makes the connection to the DB. Only CMAN is white-listed in the DB.

 

Private OAC Connection Flow

The flow is the same as the public flow.

 

 Summary 

This post provided a step-by-step guide for creating data visualization connections to Real Application Cluster databases using Connection Manager and Oracle Analytics Private Access Channel. 

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