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.
Refer here for details on creating DV connections to private autonomous databases.
May 19, 2020 with OAC105.6
May 18, 2020 with ADW 19c and OAC, RDG, Developer Client Tool 105.5
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.
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.
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
If you provisioned the ADB, use those credentials (username and password) or obtain the credentials from an ADB administrator.
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. Note the RDG hostname / IP address and HTTP port for use in sections below.
If the RDG is deployed in a different Virtual Cloud Network than the ADB, refer here for additional information.
This post uses a JDBC connect string available in Oracle JDBC driver 18c and higher. Contact Oracle Support for additional details.
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
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
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
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.
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.
Loading Java Data Sources requires an accessible RDG installation. If necessary download and install RDG on the same Windows host you are using.
Start the Developer Client Tool Using the Start Menu or via a Command Line:
<Your Installation Directory>\BI\BITOOLS\BIN\ADMINTOOL.CMD
Note: Do not open an RPD yet.
Click Load Java Data Sources from the File drop-down
Enter the Hostname or IP address of the RDG host
Enter the RDG Port found above
Use any name for User e.g. admin, dummy, etc.
Leave Password blank
Leave SSL unchecked
Click OK on the confirmation screen.
Right-Click on the Database Connection Name and click Duplicate.
This requires changes to both the General and Miscellaneous tabs. 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 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.
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
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.
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.
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