Validated February 5 , 2021 with FAW 5.8 and OAC 5.9
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.
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.
February 5, 2021 with FAW 5.8 and OAC 5.9
Before You Begin
Deploying Additional Components
Preparing the Connect Descriptor
Creating a Connection via Connection Manager
|FAW||Fusion Applications Warehouse|
|OAC||Oracle Analytics Cloud|
|OCI||Oracle Cloud Infrastructure|
|FQDN||Fully Qualified Domain Name|
|PAC||Private Access Channel|
|OSN||oracle Services Network|
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.
A FAW or OAC-PE instance.
A CMAN deployed as described here and listening for connections.
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.
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.
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 Internet Gateway in the CMAN VCN for return traffic from CMAN to OAC.
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|
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||220.127.116.11/32||TCP||1521||VCN1-APP-SN||Ingress to CMAN from the OAC OSN NAT gateway|
|ROUTE TABLE||VCN||DESTINATION CIDR||TARGET||ATTACHED TO||NOTE|
|VCN1-APP-RT||VCN1||18.104.22.168/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. 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.
Create or use an existing descriptor using its host, port, and service name. The host may be an IP address or a FQDN.
Add the SOURCE_ROUTE construct after DESCRIPTION=
After the SOURCE_ROUTE construct add the CMAN ADDRESS construct using its host and port. The host must be the public IP address.
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
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.
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