X

Best Practices from Oracle Development's A‑Team

Connecting an Oracle Analytics Cloud RPD to Private Autonomous Databases

Last Validation: May 19, 2020 with OAC105.6

Introduction

This post details the steps required to create private Autonomous Database (ADB) data connections in an Oracle Analytics Cloud RPD. ADB refers to both Autonomous Data Warehouse and Autonomous Transaction Processing databases.

For additional details on ADW, visit the Autonomous Data Warehouse page. For additional details on OAC, visit the Analytics Cloud page.

Refer here for details on creating DV connections to private autonomous databases.

Validations

May 19, 2020 with OAC105.6

May 18, 2020 with ADW 19c and OAC, RDG, Developer Client Tool 105.5

Topics

Before You Begin

Copying ADB Wallets to RDG

Creating Private ADB Connection Pools

Validating RPD Connections in OAC

 Before You Begin

Before creating and validating private ADB connection pools, you must have an ADB wallet zip file, credentials for an ADB 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 Wallet Zip File

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

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

Deploying the ADB Wallet files to the OAC Developer Client Tool

Importing ADW metadata requires ADB 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

Obtaining ADB Credentials

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

Deploying Remote Data Gateway

Remote Data Gateway is required by OAC to access most databases with private end points including a private ADB. 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. 

Additional RDG DNS Considerations

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

Additional RDG JDBC Considerations

An advanced JDBC driver is required for RDG. Refer to Using an Advanced JDBC Driver with Private Autonomous Databases for more information.

 Copying ADB Wallets to RDG

Each ADB connection string in the RPD refers to a location on the RDG hosts. This post uses the opc user's home directory: /home/opc

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. 

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

For each ADB, unzip the file to produce the wallet directory. Note the location of the unzipped wallet directories for use in connection strings below. An 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

 Creating Private ADB Connection Pools

Unlike Public ADBs, it may be difficult to connect to and import from a private ADB with the OCI call interface. 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. 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.

Once you have an ADB 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.

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 Connection Pool

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

Complete the ADB 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 

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

Right-Click on the Database Connection Name and click Duplicate. This preserves the original connection pool for future metadata imports.

Configuring the Connection Pool for RDG

This requires changes to both the General and Miscellaneous tabs. Double-Click on the Connection Pool to open it.

On the General Tab

Change the Call Interface to JDBC (Direct Driver)

Change the Connection String to use the JDBC format for private ADBs as shown below: Note: the Net Service Name is found in the tnsnames.ora file in the unzipped credentials directory.

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

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

​​​Change to the Miscellaneous tab.

For Required Cartridge Version enter 12.1

Ensure Javads Server URL is Blank

Enter the ADB Driver Class oracle.jdbc.driver.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 upload it to OAC.

You can now create Analyses in OAC using the remote data source.

 Validating RPD Connections in OAC

Deploy or Publish the RPD to the Cloud. It may take a few minutes before everything is ready. Then create an analysis and validate the private ADW connection is working.

 Summary

This post detailed the steps required to create private Autonomous Database (ADB) data connections.

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

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha