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
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.
January 20, 2021 for OAC 5.9
October 19, 2020 with OAC 5.8
May 19, 2020 with OAC 5.6
May 18, 2020 with OAC 5.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
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.
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.
Start RDG if it is not running. 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.
An advanced JDBC driver may be required for RDG. Refer to Using an Advanced JDBC Driver with Private Autonomous Databases for more information.
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 >
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 >
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.
Start the Developer Client Tool Using the Start Menu or via a Command Line:
<Your Installation Directory>\BI\BITOOLS\BIN\ADMINTOOL.CMD
Refer to the section Creating an ADW Data Source in the RPD in this post for details.
Complete the business model and presentation layer in the RPD and check consistency.
Loading Java Data Sources requires an accessible RDG installation. If necessary download and install RDG on the same Windows host you are using.
Do not exit the client tool.
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.
Right-Click on the Database Connection Name and click Duplicate. This preserves the original connection pool for future metadata imports.
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>
Note: For a Windows host enter the path with double slashes e.g.C:\\Oracle\\Wallet_PHXHUBADWPRV
For Required Cartridge Version enter 12.1
Ensure Javads Server URL is Blank
Enter the ADB Driver Class oracle.jdbc.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