Validated February 11 , 2021 with OAC 5.9
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.
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:
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:
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:
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.
Remote Data Gateway instances can use SCAN hostnames.
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.
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.
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.
February 11, 2021 with OAC 5.9
Before You Begin
Deploying Additional Components
Preparing the Connect Descriptor
Creating a Connection via Connection Manager
|OAC||Oracle Analytics Cloud|
|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|
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.
An OAC instance with PAC having CMAN's domain defined as a data source.
A CMAN deployed as described here and listening for connections.
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.
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.
These additional components must exist before using CMAN to connect to a database.
|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).
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.
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>|
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 TABLE||VCN||DESTINATION CIDR||TARGET||ATTACHED TO||NOTE|
|VCN1-APP-RT||VCN1||220.127.116.11/32||Internet Gateway||VCN1-APP-SN||Response to OAC-OSN|
After identifying or deploying the required components the enabled states look like these:
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 SOURCE_ROUTE construct.
Add the CMAN ADDRESS construct using its FQDN and port. The host must be the FQDN.
Add the RAC ADDRESS construct using its SCAN FQDN and port. The host must be the SCAN FQDN.
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
Note: OAC connects only to CMAN. CMAN makes the connection to the DB. Only CMAN is white-listed in the DB.
The flow is the same as the public flow.
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