Note: Private Access Channel is now available in Oracle Analytics and is recommended by Oracle for new connections to private data sources. For more information on the feature and the data sources it supports refer to:
    Connect to Private Data Sources Through a Private Access Channel
    Supported Data Sources
    A-Team Chronicles Private Access Channel Series

Last Validation: August 6, 2020 with OAC 5.7

Introduction

This post details the steps required to create Autonomous Database on Dedicated Exadata Infrastructure (ADB-D) connections in an Oracle Analytics Cloud RPD. ADB refers to both Autonomous Data Warehouse and Autonomous Transaction Processing databases.

For additional details on ADB-D, visit Using Oracle Autonomous Data Warehouse on Dedicated Exadata Infrastructure. For additional details on OAC, visit the Analytics Cloud page.

ADB-D runs on a private subnet, hence an OAC Remote Data Gateway (RDG) is required for connectivity

Refer here for details on creating DV connections to an ADB-D.

Note: With the introduction of private ADBs an RPD can now contain multiple ADB TCPS connections because the ADB wallet(s) are stored on Remote Data Gateway (RDG) hosts - not in OAC

Validations

August 6, 2020 with OAC 5.7

May 19, 2020 with OAC 5.6

May 18, 2020 with OAC 5.5

Topics

Before You Begin

Common Connection Pool Tasks

Creating an ADB-D TCPS Connection Pool

Creating an ADB-D TCP Connection Pool

Validating RPD Connections in OAC

 Before You Begin

Before creating and validating ADB-D connection pools, you must have an ADB-D wallet zip file, credentials for an ADB-D schema owner and Remote Data Gateway (RDG) deployed and connected to OAC.

Deploying the OAC Developer Client Tool

Download the tool from here. Ensure the version is not more than one release greater than the version of RDG. It is best if the versions are the same.

Run the downloaded installer on a Windows machine.

Obtaining an ADB-D Wallet Zip File

Refer here for the steps to obtain an ADB-D Credentials Wallet Zip File. Download credentials for each ADB-D used in the RPD.

Deploying the ADB-D Wallet files to the OAC Developer Client Tool

Importing ADB-D TCPS metadata using the OCI call interface requires ADB-D wallet files to be placed in the Developer Client Tool directories. Refer to the section Preparing Windows for an ADW Connection in the RPD in this post.

Note: In recent releases, importing metadata from ADBs using JDBC is possible if a Windows instance hosting RDG is in the same VCN that is hosting the Developer Client Tool.

Note: If you are using an ADB-D TCP connection, the wallet files do not need to be placed.

Obtaining ADB-D Credentials

If you provisioned the ADB-D, use those credentials (username and password) or obtain the credentials from an ADB-D administrator.

Choosing an ADB-D Connection Type

Dedicated ADB databases differ from Shared ADBs in that they offer TCP services as well as the TCPS services found in Shared ADBs. For more information refer to Predefined Database Service Names for Autonomous Data Warehouse Dedicated Databases

TCP services are used without a wallet file e.g. cwallet.sso. This is useful when connecting with a client that does not support the use of wallets. These services are named like <ADB-D name>_low e.g. dcadwdprv_low

TCPS services require the wallet file. These are the service types found in Shared ADBs. These services are named like <ADB-D name>_low_tls e.g. dcadwdprv_low_tls

Note: Currently, using TCP services in an RPD avoids additional JDBC considerations when using TCPS services.

Providing Network Access to Remote Data Gateway

RDG connects to an ADB-DB using TCP ports specified in the tnsnames.ora file found in the download client credentials zip file. These ports need to be opened for RDG using an ingress rule in either a Security List or a Network Security Group (NSG). Refer here for more information on Security Lists and here for NSGs.

For an RDG IP address of 10.0.3.4 and an ADB-D tnsnames entry (abbreviated) such as below:

dcadwdprv_medium_tls=(DESCRIPTION=[...](PROTOCOL=TCPS)[...](PORT=2484)[...]

An example Security List Rule is:

An example NSG rule is:

Deploying Remote Data Gateway

Remote Data Gateway is required by OAC to access most databases with private end points including an ADB-D. Refer here for various deployment strategies.

Start RDG if it is not running. Note the RDG hostname / IP address and HTTP port for use in sections below. 

SSH Considerations

For Linux RDG deployments, the SSH commands in this post assume the use of an SSH configuration file with an entry named RDG-HOST. Refer here for guidance on SSH configuration files.

Additional RDG DNS Considerations

If the RDG is deployed in a different Virtual Cloud Network than the ADB, refer here for additional information regarding host name resolution.

Additional RPD Considerations

Unlike Public ADBs, it may be difficult to connect to and import from an ADB-D with the OCI call interface. If your Window instance cannot reach the ADB-D, two mitigation options are to use VPN or to deploy the developer client tool on a Windows compute instance in the same VCN as the ADB-D. A third option is to export / import the schemas you desire to another database that is accessible.

This post deploys the developer client tool on a Windows compute instance in the same VCN as the ADB-D.

Once you have an ADB-D database definition in the RPD and the schema metadata is in the place, you can proceed to changing the connection pool to use RDG.

RDG must be is a running state and connected to the OAC using the RPD to validate the connection pool.

Additional RDG JDBC Considerations

This section applies only to Connection Pools using ADB-D TCPS services.

This post uses an JDBC connect string available with versions 18c and higher of the Oracle JDBC8 driver found here. Note: Ensure the download package selected is for ojdbc8 and not ojdbc10.

Contact Oracle Support for additional details on how to apply the driver files.

 Common Connection Pool Tasks

For both TCPS and TCP connection pools, many of the tasks are the same. The following tasks are referenced in sections 2 and 3.

Open or Create a New RPD

 Start the Developer Client Tool Using the Start Menu or via a Command Line:

<Your Installation Directory>\BI\BITOOLS\BIN\ADMINTOOL.CMD​

Creating an ADB-D Connection Pool

Refer to the section Creating an ADW Data Source in the RPD in this post for details. 

Complete the ADB-D Modeling

Complete the business model and presentation layer in the RPD and check consistency.

Save and Close the RPD and Load the Java Data Sources

Loading Java Data Sources requires an accessible RDG installation. If necessary, download and install RDG on the same Windows host you are using.

Save and Close the RPD 

The RPD must be closed before loading java data sources. Save and Close the RPD. Do not exit the client tool.

Loading the Java Data Sources

Click Load Java Data Sources from the File drop-down

Enter the Hostname or IP address of the RDG host noted above. If RDG is on the same Windows server enter localhost.

Enter the RDG Port noted above.

Use any name for User e.g. admin, dummy, etc.

Leave Password blank

Leave SSL unchecked

Click OK on the confirmation screen.

Duplicating the Database Definition

If you imported metadata using the OCI call interface, Right-Click on the Database Connection Name and click Duplicate. This preserves the original connection pool for future metadata imports.

​​​Configuring the ADB-D Connection Pool Miscellaneous Tab.

For Required Cartridge Version enter 12.1

Ensure Javads Server URL is Blank

Enter the Driver Class oracle.jdbc.OracleDriver

For Use SQL over HTTP use true

For RDC Version use 2

Click OK

Saving the Repository

From the File menu click Save.

If the repository is opened in the cloud, then Publish it from the File>Cloud menu.

If opened offline, then Replace it in OAC.

 Creating an ADB-D TCPS Connection Pool

Copying ADB-D Wallets to RDG

Each ADB-D TCPS connection string in the RPD refers to a location on the RDG hosts. 

For Linux, this post uses the opc user's home directory: /home/opc
For Windows: C:\ORACLE

Copy the Credentials Zip File

For each ADB, copy the credentials zip file to the RDG host.

For Windows Servers, Remote Desktop (RDP) users can place the zip file in an RDP redirect folder and then place it in a folder such as C:\ORACLE\Wallet_ASHPRVLPG.zip

For Linux, an example copy command is below:

scp < Location of Credentials Zip File > < Host >:< Desired Directory > e.g.

scp /Users/dcarley/Documents/Oracle/ADW/Wallet_ASHPRVLPG.zip RDG-HOST:/home/opc

Unzip the Credentials Zip File

Connect to the RDG Host. For each ADB, unzip the file to produce the wallet directory. Provide a location for the unzipped wallet directories. 

A Linux example is:

ssh < Host > unzip -d < Desired Directory > < Location of Credentials Zip File > e.g.

ssh RDG-HOST unzip -d Wallet_ASHPRVLPG Wallet_ASHPRVLPG.zip

For Windows extract the contents to a folder e.g.

C:\ORACLE\Wallet_ASHPRVLPG

Open or Create a New RPD

As shown above

Save and Close the RPD and Load the Java Data Sources

As shown above

Duplicating the Database Definition

As shown above

Configuring the ADB-D TCPS Connection Pool General Tab

Double-Click on the Connection Pool to open it.

Change the Call Interface to JDBC (Direct Driver)

​

Change the Connection String to use the JDBC format for an ADB-D TCPS service as shown below: Note: The Net Service Name is found in the tnsnames.ora file in the unzipped credentials directory. Ensure you use a service name ending in "_tls"

jdbc:oracle:thin:@<Net Service Name>?TNS_ADMIN=<Path to Unzipped Client Credentials Folder on the RDG Host> e.g.

For Linux:

jdbc:oracle:thin:@prvadw_medium_tls?TNS_ADMIN=/home/opc/Wallet_ASHPRVLPG

For Windows:

jdbc:oracle:thin:@prvadw_medium_tls?TNS_ADMIN="C:\\ORACLE\\Wallet_ASHPRVLPG"

​

​​​Miscellaneous Tab

As shown above

Saving the Repository

As shown above

 Creating an ADB-D TCP Connection Pool

Open or Create a New RPD

As shown above

Save and Close the RPD and Load the Java Data Sources

As shown above

Duplicating the Database Definition

As shown above

Configuring the ADB-D TCP Connection Pool General Tab

Double-Click on the Connection Pool to open it.

Change the Call Interface to JDBC (Direct Driver)

​

Change the Connection String to use the JDBC format for an ADB-D TCP service as shown below: 

Note: the TNS Connect Descriptor is found in the tnsnames.ora file in the unzipped credentials directory. Ensure you use a service not ending in "_tls". Ensure that the entire string is contained in one line.

jdbc:oracle:thin:@<TNS Connect Descriptor> e.g.

​​​Miscellaneous Tab

As shown above

Saving the Repository

As shown above

 Validating RPD Connections in OAC

Deploy or Publish the RPD to the Cloud. It may take five to ten minutes before everything is ready. Then create an analysis and validate the ADB-D connection is working.

 Summary

You can now create Analyses in OAC using ADB-D data sources.

This post detailed the steps required to create Autonomous Database on Dedicated Exadata Infrastructure (ADB-D) connections in an Oracle Analytics Cloud RPD

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