Connecting Oracle Data Integrator Studio to the Autonomous Data Warehouse Cloud

Introduction

This post details the steps required to create connections in Oracle Data Integrator (ODI) to the Autonomous Data Warehouse Cloud (ADWC).

For additional details about ADWC, refer to Autonomous Data Warehouse Cloud. For additional details about ODI, refer to Oracle Data Integrator Overview.

ODI Studio is assumed to be installed and configured. For information on installing ODI on Windows visit Installing and Configuring Oracle Data Integrator

Topics

Preparing to Connect to the Autonomous Data Warehouse Cloud

Creating a Data Server using the JDBC Oracle Call Interface Driver

Creating a Data Server using the JDBC Thin Driver

Versions Used

ODI Studio 12.2.1.2.6 and 12.2.1.3.0 on Windows 7 SP 1

ADWC 18.1.1.0 and 18.1.4

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 ODI to make the connections.

An Oracle Instant Client for Oracle Call Interface (OCI) libraries.

Certified Java JDK 8 and Java JCE 8.

Creating a Data Server using the JDBC Thin Driver

To use the thin driver, you need a TNS connect descriptor from the tnsnames.ora file located in the unzipped client credentials folder.

Also required is a certified version of Java JDK 8 with unlimited strength JCE policy files. Note: For 12c (12.2.1.2+), the certified JDK is 1.8.0_101 and later. For more information, see About JDK Requirements for an Oracle Fusion Middleware Installation.

Topics

Adding a Java Security Provider

Ensuring the Use of Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files

Creating the Data Server

Adding a Java Security Provider

Edit the java.security file in the %JAVA_HOME%\jre\lib\security folder. Find the section listing the providers and add the line in red to the end of the list: Note: The number assigned (11) is one greater than the previous entry.

security.provider.9=sun.security.smartcardio.SunPCSC
security.provider.10=sun.security.mscapi.SunMSCAPI
security.provider.11=oracle.security.pki.OraclePKIProvider

Ensuring the Use of Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files

The JCE Unlimited Strength policy is required to access ADWC. The use of Java JDK 1.8.0_161 or greater is recommended because the unlimited strength policy is enabled by default. Earlier versions of JDK8 have differing mechanisms for enabling the unlimited strength policy.

If you need to add the unlimited files, copy these two files from the downloaded and unzipped JCE folder, provided in the Preparing to Connect to the Autonomous Data Warehouse Cloud section above, to the %JAVA_HOME%\jre\lib\security folder:

local_policy.jar
US_export_policy.jar

Creating the Data Server

Start ODI Studio. From the Topology tab, create a new data server using the Oracle technology.

1

In the Definition tab, give the server a name.

In the Connection section, enter the database instance username and password.

2

In the JDBC tab:

Ensure the JDBC Driver is oracle.jdbc.OracleDriver

The JDBC URL uses the TNS connect descriptor chosen from the tnsnames.ora file in the client connections folder. An example is below:

jdbc:oracle:thin:@(description= (address=(https_proxy=www-proxy.us.oracle.com)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=<Your Host>))(connect_data=(service_name=<Your Service Name>))(security=(ssl_server_cert_dn=“CN=cman4test.us1.oracletest.com,O=Oracle Corporation Test,L=Redwood Shores,ST=California,C=US”)))

Properties are required. Use the greenplus icon to add properties. Each property has a key and a value.

The first property is the location of the client credentials folder containing the wallet.

Key: oracle.net.wallet_location Value:(SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=<Your Unzipped Client Credentials Folder>)))

Another may be required if an error occurs regarding the handshake. This is needed for 12.2.1.2.6 but not for 12.2.1.3.0.

Key: oracle.net.ssl_version Value: 1.2

The JDBC tab looks like this:

11

Click on Test Connection which also saves the Data Server. This post uses the Local (No Agent) as the Physical Agent.

4

Creating a Data Server using the JDBC Oracle Call Interface Driver

The JDBC Oracle Call Interface (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 Data Server

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>”)))

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 ODI versions tested. This library exists in the Instant Client files obtained in the Preparing to Connect to the Autonomous Data Warehouse Cloud section above. Note: ODI version 12.2.1.2.6 requires a 12.1.x version of Instant Client. ODI Version 12.2.1.3.0 requires the latest 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

Add the Instant Client folder to 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 ODI Studio

Creating the Data Server

Start ODI Studio. From the Topology tab, create a new data server using the Oracle technology.

1

In the Definition tab, give the server a name.

In the Connection section, enter the database instance username and password.

5

In the JDBC tab:

Ensure the JDBC Driver is oracle.jdbc.OracleDriver

The JDBC URL uses the jdbc:oracle:oci:@<TNS net service name> syntax or the jdbc:oracle:oci:@<TNS connect descriptor> syntax

No JDBC Properties are required.

The JDBC tab looks like this:

6

Click on Test Connection which also saves the Data Server. This post uses the Local (No Agent) as the Physical Agent.

4

Summary

This post provided the steps required to create connections in Oracle Data Integrator (ODI) to the Autonomous Data Warehouse Cloud (ADWC). It demonstrated the use of both the thin and oci JDBC drivers.

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 BICSOracle A-Team Chronicles for OAC, and Oracle A-Team Chronicles for ODI

References

Autonomous Data Warehouse Cloud

Oracle Data Integrator Overview

Installing and Configuring Oracle Data Integrator

About JDK Requirements for an Oracle Fusion Middleware Installation

Preparing to Connect to the Autonomous Data Warehouse Cloud

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

Oracle A-Team Chronicles for ODI

 

Add Your Comment