Connecting Oracle Data Integrator Studio to the Exadata Express Cloud

Introduction

This post details the steps required to create connections in Oracle Data Integrator (ODI) to the Exadata Express Cloud (referred to as Cloud Service in the remainder of the post). It is a companion post to Connecting ODICS and DIPC Studios to the Exadata Express Cloud which uses ODI Cloud Services.

For additional details about Exadata Express, refer to Exadata Express Cloud Service. 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 visit Installing and Configuring Oracle Data Integrator

Updates

Updated May 16, 2018 to include testing 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 Thin Driver

Creating a Data Server using the JDBC Oracle Call Interface Driver

Versions Used

ODI Studio 12.2.1.2.6 and 12.2.1.3.0

Exadata Express 18.1.4

Windows 7 SP 1

Preparing to Connect to the Cloud Service

The steps described in the post Preparing to Connect to the Exadata Express Cloud Service 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.

Backup and Edit the java.security file in the %JAVA_HOME%\jre\lib\security folder. 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.

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 Instant Client

Preparing the SQL*Net Configuration Files

Testing with SQL*Plus

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 an additional modification.

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

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

Insert a line after the :cont_path label. Add SET LOCAL_PATH=< Your Instant Client Folder >; %LOCAL_PATH% The result looks like this:

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

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=false

Testing with SQL*Plus

Testing with SQL*Plus verifies that the OCI subsystem can connect to the Exadata Express Cloud before creating the ODI data server.

For SQL*Plus testing in the current session only, set the PATH variable to point to the Instant Client directory.

set PATH=< Your Instant Client Folder >;%PATH%

Choose a TNS Net Service Name from the tnsnames.ora file in the client credentials directory to use e.g. ATEAM_low

Run SQL*Plus as follows using the Exadata Express username, password and TNS Net Service Name

sqlplus admin@ATEAM_low

Enter the password and a successful connection looks like this:

sqlplus admin@ATEAM_low

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 5 21:35:31 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:
Last Successful login time: Thu Apr 05 2018 21:34:12 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL>

Exit SQL*Plus.

exit

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 ODI Studio 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 User and Password.

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=(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 tests both the Local (No Agent) and the Standalone Agent.

4

Creating a Data Server using the JDBC Oracle Call Interface Driver

From the ODI Studio 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 User and Password.

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 descriptorsyntax

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 Exadata Express Cloud. 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

Exadata Express Cloud Service

Oracle Data Integrator Overview

Installing and Configuring Oracle Data Integrator

Connecting ODICS and DIPC Studios to the Exadata Express Cloud

About JDK Requirements for an Oracle Fusion Middleware Installation

Preparing to Connect to the Exadata Express Cloud Service

Starting a Standalone Agent

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

Oracle A-Team Chronicles for ODI

Add Your Comment