Introduction

This post details the steps required to create connections in Oracle Data Sync to the Autonomous Data Warehouse Cloud (ADWC) 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 (ADWC) which uses the JDBC Thin driver.

For additional details about ADWC, 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

Updates

Validated February 27, 2019 with ADW 18.4.0.0 and Data Sync 2.5

Past Validations June 12, 2018 with ADWC 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

Topics

Preparing to Connect to the Autonomous Data Warehouse Cloud

Creating a Connection using the JDBC OCI Driver

Versions Used

Data Sync 2.4 and 2.5

ADWC 18.1.1.0, 18.1.4, and 18.1.5

Windows 7 SP 1

Preparing to Connect to the Autonomous Data Warehouse Cloud

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.

Creating a Connection using the JDBC OCI Driver

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.

Topics

Preparing the Network Configuration Files

Adding an Additional Library

Creating the Connection

Preparing the Network Configuration Files

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>

Adding an Additional Library

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.

Notes:

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.

Unzip the Windows-64 Instant Client zip files into a single directory e.g.

C:\Oracle\IC\instantclient_12_x — where x is 1 or 2 based on the notes above

Add the Instant Client folder to the beginning of the PATH environmental variable. Note: Use the Control Panel to set the PATH variable as a System Environmental Variable for future sessions.

SET PATH=C:\Oracle\IC\instantclient_12_x;%PATH%

Restart Data Sync if it is running.

Creating the Connection

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.

Summary

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 Oracle A-Team Chronicles for BICS and Oracle A-Team Chronicles for OAC.