This post details the steps required to create connections in Oracle Data Sync to the Autonomous Data Warehouse Cloud (ADW) using the JDBC Oracle Call Interface (OCI) driver. It is a companion and alternative to the A-Team post Data Sync and the Autonomous Data Warehouse Cloud (ADW) which uses the JDBC Thin driver.
For additional details about ADW, refer to Autonomous Data Warehouse Cloud.
Data Sync is assumed to be installed and configured. For information on downloading and installing Data Sync visit Oracle Analytics Cloud Data Sync
Validated February 27, 2019 with ADW 126.96.36.199 and Data Sync 2.5
Past Validations June 12, 2018 with ADW 18.1.5 using both Data Sync 2.4 and 2.5
Updated June 12, 2018 to include Data Sync 2.5 and ADWC 18.1.5
Preparing to Connect to the Autonomous Data Warehouse Cloud
Creating a Connection using the JDBC OCI Driver
The steps described in the post Preparing to Connect to the Autonomous Data Warehouse Cloud provide the following:
Downloaded and unzipped client credentials folder containing the wallet and network configuration files used by Data Sync to make the connections.
An Oracle Instant Client for OCI libraries.
The JDBC OCI Driver uses network configuration files and OCI libraries provided in the Preparing to Connect to the Autonomous Data Warehouse Cloud section above.
Preparing the Network Configuration Files
Adding an Additional Library
Creating the Connection
Edit the sqlnet.ora file in the client credentials folder and update the wallet location with the location of the client credentials folder e.g.
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="<Your Client Credentials Folder>")))
Refer to Parameters for the sqlnet.ora File for details.
Set the TNS_ADMIN environment variable to the location of the client credentials folder to assist client tools in locating the network configuration files. Note: Use the Control Panel to set the TNS_ADMIN variable as a System Environmental Variable for future sessions.
SET TNS_ADMIN=<Your Client Credentials Folder>
The JDBC OCI driver requires the ocijdbc12.dll library which is not shipped with the Data Sync versions tested. This library exists in the Instant Client files obtained in the Preparing to Connect to the Autonomous Data Warehouse Cloud section above.
Data Sync version 2.4 requires a 12.1.x version of Instant Client.
Data Sync version 2.5 requires a 12.2.x version of Instant Client.
C:\Oracle\IC\instantclient_12_x -- where x is 1 or 2 based on the notes above
The connection requires a TNS Net Service Name for your instance from the tnsnames.ora file in the client credentials folder. Refer to Local Naming Parameters in the tnsnames.ora File for details.
Start Data Sync. From the Connections tab, click New.
Enter a Name, select Oracle(OCI8) as the Type and click OK.
In the Edit section,
Enter the database instance User and Password.
Enter the URL as: jdbc:oracle:oci:@<Your TNS Net Service Name>
Enter the JDBC Driver as: oracle.jdbc.OracleDriver
The completed page looks like this:
Click Save then Test Connection. The test below is from 2.4 using the 12.1 instant client.
This post provided the steps required to create connections in Oracle Data Sync to the Autonomous Data Warehouse Cloud using the JDBC OCI driver.
For more OAC, BICS and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit http://www.ateam-oracle.com/dayne-carley