Connecting Oracle Data Integrator Studio to the Autonomous Data Warehouse

Introduction

This post details the steps required to create connections in Oracle Data Integrator (ODI) to the Autonomous Data Warehouse Cloud (ADW) (referred to as Cloud Service in the remainder of the post).

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

Updates

Validated

October 4, 2018 with 12.2.1.3.0

June 5, 2018 with 12.2.1.3.0

Updated

August 31, 2018 to clarify Java directories

      May 16, 2018 to include connecting with a standalone agent.

Topics

Preparing to Connect to the Cloud Service

Preparing Windows to Connect to the Cloud Service

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, 18.1.4 and 18.1.5

Preparing to Connect to the Cloud Service

If your Java JDK Version is less than 8u161 then be sure to follow the notes in the link below regarding the JCE policy files.

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.

Preparing Windows to Connect to the Cloud Service

Topics

Preparing for the JDBC Thin Driver

Preparing for the JDBC OCI Driver

Restarting the Standalone Agent and Studio

Preparing for the JDBC Thin Driver

Connecting to the Cloud Service requires a certified version of Java JDK 8 with unlimited strength JCE policy files. Both ODICS and DIPC meet the requirements. For more information, see About JDK Requirements for an Oracle Fusion Middleware Installation.

The Thin driver requires an additional Java security provider to support the use of the Cloud Service wallet.

Determine the Java JDK directory location that ODI Studio is using. It must be a JAVA JDK directory.

Set the JAVA_HOME variable to the directory found above.  Backup and Edit the java.security file in the %JAVA_HOME%\jre\lib\security folder. Use notepad or another editor to find the section listing the providers and add the line in blue to the end of the list: Note: The number assigned (11) is one greater than the previous entry. Example CMD commands are below. Save the file and exit the editor.

set JAVA_HOME=C:\Oracle\Java\jdk1.8.0_161

cd %JAVA_HOME%\jre\lib\security
copy java.security java.security.orig
notepad java.security

The result looks like this:

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

Preparing for the JDBC OCI Driver

The JDBC Oracle Call Interface (OCI) Driver uses network configuration files and the Oracle Instant Client.

Topics

Preparing the SQL*Net Configuration Files

Preparing the Instant Client

Preparing the SQL*Net Configuration Files

The SQL*Net configuration files are located in the same directory as the client credentials.

Do the following. For details on using the vi editor refer to Using the vi Editor.

Set the TNS_ADMIN variable for the current session. 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 >

Change to the credentials directory. Make a backup of and edit the sqlnet.ora file to change the wallet location to point to the credentials directory.

cd %TNS_ADMIN%
copy sqlnet.ora sqlnet.ora.orig
notepad sqlnet.ora

Save and exit and the file should look like this:

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = < Your Client Credentials Folder >)))
SSL_SERVER_DN_MATCH=yes

Preparing the Instant Client

The JDBC OCI driver requires the ocijdbc12.dll library which is not shipped with the ODI versions tested. The Oracle Instant Client provides this library and is downloaded as multiple zip files.

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

For ODI Studio

Add the instant client folder to the beginning of the PATH environmental variable. Use the Control Panel to set the PATH variable as a System Environmental Variable for future sessions.

For the Standalone Agent

Backup and edit the commODIEnv.cmd script and add the instant client folder to the ODI_ADDITIONAL_JAVA_OPTIONS variable.

SET ODI_INSTALL_DIR=< Yours >
CD %ODI_INSTALL_DIR%\odi\agent\internal\bin
COPY commODIEnv.cmd commODIEnv.cmd.orig
NOTEPAD commODIEnv.cmd

Add “< Your Instant Client Folder >” to the  -Djava.library.path portion of the variable setting. The result looks like this:

set ODI_ADDITIONAL_JAVA_OPTIONS=%ODI_ADDITIONAL_JAVA_OPTIONS% -Djava.library.path=”< Your Instant Client Folder >”;”%ODI_HOME%\..\sdk\lib”

Version 12.2.1.3.0 introduced a change that requires additional modifications.

Backup and edit the commBaseEnv.cmd script, add the instant client folder to the beginning of the LOCAL_PATH variable, and add the TNS_ADMIN variable.

CD %ODI_INSTALL_DIR%\oracle_common\common\bin
COPY commBaseEnv.cmd commBaseEnv.cmd.orig
NOTEPAD commBaseEnv.cmd

Insert two lines after the :cont_path label.

Add set LOCAL_PATH=< Your Instant Client Folder >; %LOCAL_PATH%

Add set TNS_ADMIN=< Your Client Credentials Folder >

The result looks like this:

:cont_path
set LOCAL_PATH=< Your Instant Client Folder >;%LOCAL_PATH%
set TNS_ADMIN=< Your Client Credentials Folder >

Restarting the Standalone Agent and ODI Studio

Close and restart ODI Studio and the Agent to inherit the changes made above. The agent is restarted differently depending on whether it was started with the Node Manager or not. Refer to Starting a Standalone Agent for details.

Creating a Data Server using the JDBC Thin Driver

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

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