Connecting Oracle Data Integrator Cloud Service to the Autonomous Data Warehouse

Introduction

This post details the steps required to create connections in Oracle Data Integrator Cloud Service (ODICS) on Oracle Cloud Infrastructure (OCI) to the Autonomous Data Warehouse (ADW). It demonstrates both the Thin and Oracle Call Interface (oci) versions of the Oracle JDBC driver. It is a companion post to Connecting Oracle Data Integrator Studio to the Autonomous Data Warehouse which uses ODI Studio on Windows and Connecting DIPC ODI Studio to the Autonomous Data Warehouse which uses ODI Studio in the Data Integration Platform Cloud (DIPC).

ODICS is a service level of Java Cloud Service (JCS). For additional details about JCS, refer to About Java Cloud Service Instances in Oracle Cloud Infrastructure. This means that you specify the ODI service level when you provision JCS.

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

This post assumes a JCS instance attached to a public subnet with ODI, Agents, VNC Server/Viewer, and ODI Studio installed and configured.

For information on creating a Java Cloud Service Instance attached to a public subnet visit Create an Oracle Java Cloud Service Instance Attached to a Public Subnet on Oracle Cloud Infrastructure.

This post assumes ODICS and the agents were provisioned using Provisioning Oracle Data Integrator Cloud Service and Installing and Configuring Oracle Data Integrator. These agent names are used:

J2EE Agent                                  OracleDIAgent

Standalone Collocated Agent       OracleDIAgent1

Updates

Published and Validated December 21, 2018 for JCS 18.2.3.0.180427

Versions

JCS                    18.2.3.0.180427
DBCS RCU DB 12.1.0.2
ODI                   12.2.1.2.6
JAVA JDK         1.8.0_191
Instant Client    12.1.0.2.0
ADW DB          18.0.3.3

Topics

Preparing to Connect to the Autonomous Data Warehouse Cloud

Preparing ODICS to Connect to the Autonomous Data Warehouse Cloud

Preparing ODICS to use the JDBC drivers

Creating a Data Server using the JDBC Oracle Call Interface Driver

Creating a Data Server using the JDBC Thin Driver

___________________________________________________________________________

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. This post uses Windows 7 to receive the files.

Downloaded and unzipped client credentials folder containing the wallet and network configuration files used by ODI to make the connections.

Downloaded Instant Client zip files.

___________________________________________________________________________

Preparing ODICS to Connect to the Autonomous Data Warehouse

This post uses PuTTY and PSCP SSH applications on Windows to connect to ODICS. The PuTTY programs may be downloaded from https://www.putty.org/. For details on using these tools refer to Connecting to a Compute Node Using the PuTTY Program on WindowsIf you are not using Windows substitute SCP for PSCP and SSH for PuTTY.

Topics

Obtaining the ODICS Private Key and Administrator Credentials

Obtaining the ODICS Public IP Address

Uploading the Client Credentials to ODICS

Preparing the Credential Files

_______________________________________________________________________________________

Obtaining the ODICS Private Key and Administrator Credentials

Obtain the WebLogic administrator console URL, username and password from the Cloud Instance Administrator. They are used to access the WebLogic Administrative Console to stop and start the WebLogic servers.

Obtain the SSH private key file for ODICS from the Cloud Instance Administrator and convert the private key file to the PuTTY .ppk format. SSH requires the usage of a public / private key pair associated with the instance. Refer to Accessing an Instance Using SSH for details.

To do this:

* Run the PuTTYgen program

* Load the private key file obtained from the Cloud Instance administrator

* Save the private key using the defaults SSH-2 RSA and 2048 for Number of bits. Save as type PuTTY Private Key Files (*.ppk).

_______________________________________________________________________________________

Obtaining the ODICS Public IP Address

Obtain the ODICS public IP address from the Oracle Cloud My Services – Cloud Instance Overview page.

_______________________________________________________________________________________

Uploading the Client Credentials to the ODICS

In a CMD window, change to the PuTTY directory and run the PSCP command to upload the file.

cd C:\Program Files (x86)\PuTTY
pscp -v -i < Your ODICS SSH Private Key > < Your Client Credentials Zip File > opc@< ODICS Public IP >:/tmp

Note: If there is a passphrase for the private key, you are prompted for it.

_______________________________________________________________________________________

Preparing the Credential Files

Start a SSH session in the cloud instance server using PuTTY, the cloud instance public IP address, and the cloud instance private key file (.ppk). Connect as the opc user.

Connect as the oracle user using sudo:

sudo su – oracle

Make a directory for the client credentials files, change to the directory, and unzip the credentials.

mkdir -p /u01/data/credentials
cd /u01/data/credentials; unzip /tmp/< Your Client Credentials Zip File >

___________________________________________________________________________

Preparing ODICS to use the JDBC drivers

The usage of both the Thin and OCI JDBC drivers for ADW require modifications to ODICS.

Topics

Preparing for the JDBC OCI Driver

Preparing for the JDBC Thin Driver

Restarting the Servers and Agents

Restarting the ODI Managed Server

Restarting the VNC Server

Restarting the Standalone Collocated Agent

_______________________________________________________________________________________

Preparing for the JDBC OCI Driver

The JDBC Oracle Call Interface (OCI) Driver uses network configuration files and the Oracle Instant Client provided in the Preparing to Connect to the Autonomous Data Warehouse Cloud section above.

Topics

Preparing the Instant Client

Preparing the SQL*Net Configuration Files

__________________________________________________________________________________________________________

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. Only the instantclient-basic-linux.x64-12.x.x.x.x.zip file is needed here.

ODI version 12.2.1.2.6 requires the 12.1.x version of Instant Client.
ODI Version 12.2.1.3.0 requires the 12.2.x version of Instant Client.
Topics

Uploading the Instant Client zip file

Preparing the Instant Client Files

_____________________________________________________________________________________________________________________
Uploading the Instant Client zip file

In a CMD window, change to the PuTTY directory and run the PSCP command to upload the zip file to the /tmp directory.

cd C:\Program Files (x86)\PuTTY
pscp -v -i < Your ODICS SSH Private Key > < Your Instant Client Zip Directory >\*basic*.zip opc@< ODICS Public IP >:/tmp

If there is a passphrase for the private key, you are prompted for it. Ensure only Instant Client zip files are in the directory.

_____________________________________________________________________________________________________________________
Preparing the Instant Client Files

Start a SSH session in the Cloud instance server using PuTTY, the Cloud public IP address, and the Cloud instance private key file (.ppk). Connect as the opc user.

Connect as the oracle user using sudo:

sudo su – oracle

If needed, refer to the installation instructions at the foot of the Instant Client Download page for additional details.

Ensure there is only one instant*basic*.zip file in the /tmp directory.

ls /tmp/instant*basic*.zip

Choose a parent directory for the Instant Client files and change to it e.g. /u01/data. Unzip the files. Note: Unzipping the files with this command creates a sub-directory for the files e.g. instantclient_12_*. Then change to the new sub-directory.

cd /u01/data
# Unzip the Instant Client Zip file
find /tmp -name instant*basic*.zip -exec unzip ‘{}’ ‘;’
#Change to the new directory
cd instant*

Create symbolic links

ln -s libclntsh.so.12.* libclntsh.so
ln -s libocci.so.12.* libocci.so

Set the LD_LIBRARY_PATH variable for the current session, future sessions, and Node Manager Startups.

# Set the LD_LIBRARY_PATH for the session
export LD_LIBRARY_PATH=`pwd`:$LD_LIBRARY_PATH
# Save the command to a temporary file
echo export LD_LIBRARY_PATH=$LD_LIBRARY_PATH >/tmp/tmpLD
# Make a backup of the .bashrc file
cp ~/.bashrc ~/.bashrc.orig
# Append the command to the bashrc file for future sessions
cat /tmp/tmpLD >>~/.bashrc
# Create a file for use in Node Manager Startups
touch $DOMAIN_HOME/bin/setUserOverrides.sh; chmod +x $DOMAIN_HOME/bin/setUserOverrides.sh
# Append the command to the bashrc file for Node Manager Startups
cat /tmp/tmpLD >>$DOMAIN_HOME/bin/setUserOverrides.sh

For the Standalone Collocated Agent, backup and edit the commODIEnv.sh script to add the instant client folder to the ODI_ADDITIONAL_JAVA_OPTIONS variable.

export ODI_INSTALL_DIR=$MIDDLEWARE_HOME/odi
cd $ODI_INSTALL_DIR/agent/internal/bin
cp commODIEnv.sh commODIEnv.sh.orig
vi commODIEnv.sh

Add < Your Instant Client Folder > to the -Djava.library.path portion of the ODI_ADDITIONAL_JAVA_OPTIONS variable setting.

Save and exit the editor. The result looks like this:

ODI_ADDITIONAL_JAVA_OPTIONS=”${ODI_ADDITIONAL_JAVA_OPTIONS} -Djava.library.path=/u01/data/instantclient_12_x:${ODI_HOME}/../sdk/lib”

__________________________________________________________________________________________________________

Preparing the SQL*Net Configuration Files

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

If needed refer to Using the vi Editor for details on using the vi editor.

Set the TNS_ADMIN variable for the current session, future sessions, and Node Manager Startups.

# For The Current Session
export TNS_ADMIN=/u01/data/credentials
# For Future Sessions
echo export TNS_ADMIN=$TNS_ADMIN >>~/.bashrc
# For Node Manager Startups
touch $DOMAIN_HOME/bin/setUserOverrides.sh; chmod +x $DOMAIN_HOME/bin/setUserOverrides.sh
echo export TNS_ADMIN=$TNS_ADMIN >>$DOMAIN_HOME/bin/setUserOverrides.sh

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 /u01/data/credentials;
cp sqlnet.ora sqlnet.ora.orig
vi sqlnet.ora; cat sqlnet.ora

Save and exit and the file should look like this:

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/data/credentials)))
SSL_SERVER_DN_MATCH=yes

_______________________________________________________________________________________

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. ODICS meets 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 ADW wallet.

Determine the Java JDK directory location that ODI Studio is using. It must be a JAVA JDK directory. If ODI Studio is running the ps -ef | grep java command shows the JDK location.

Set the JAVA_HOME variable to the directory found above.  Backup and Edit the java.security file in the $JAVA_HOME/jre/lib/security directory. Use vi 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 (10) is one greater than the previous entry.

export JAVA_HOME=< directory found above >

cd $JAVA_HOME/jre/lib/security
cp java.security java.security.orig
vi java.security

The result looks like this:

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

_______________________________________________________________________________________

Restarting the Servers and Agents

The servers and agents are restarted to use the new environment variables and the updated java security file.

Topics

Stopping the ODI Managed Server

Stopping the Standalone Collocated Agent

Stopping the Node Manager

Restarting the Node Manager

Restarting the ODI Managed Server

Restarting the VNC Server

__________________________________________________________________________________________________________

Stopping the ODI Managed Server

Refer to Shut down a server instance for details on stopping the managed server using the Administrative console.

__________________________________________________________________________________________________________

Stopping the Standalone Collocated Agent

Run the stopComponent.sh script to stop the agent.

cd $DOMAIN_HOME/bin
./stopComponent.sh OracleDIAgent1

__________________________________________________________________________________________________________

Stopping the Node Manager

Run the stopNodeManager.sh script to stop the Node Manager.

cd $DOMAIN_HOME/bin
./stopNodeManager.sh

__________________________________________________________________________________________________________

Restarting the Node Manager

Run the startNodeManager.sh script  to start the Node Manager.

The Node Manager may also start the Standalone Collocated Agent. It may take 5-10 minutes to start it.

cd $DOMAIN_HOME/bin
nohup ./startNodeManager.sh >startNodeManager.out &

If the Standalone Collocated Agent does not start e.g. After a restart of the VM, then below is the command to start it:

nohup ./startComponent.sh OracleDIAgent1 >startComponent.out &

__________________________________________________________________________________________________________

Restarting the ODI Managed Server

Refer to Start Managed Servers from the Administration Console for details on starting the managed server.

__________________________________________________________________________________________________________

Restarting the VNC Server

Use a PuTTY session to stop and start the VNC Server. Change the geometry and depth to fit your needs.

vncserver -kill :1
vncserver -geometry 1920×1080 -depth 24 -nolock

___________________________________________________________________________

Creating a Data Server using the JDBC Oracle Call Interface Driver

Start a VNC Viewer session.

Start ODI Studio and connect to the repository.

$ < Your ODI Install Directory >/odi/studio/odi.sh -clean -initialize

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

Right Click on the Oracle Technology and click New Data Server.

 

 

 

 

 

 

 

 

In Definition tab, give the server a name.

In the Connection section, enter the ADW schema username and password.

Click the JDBC tab.

 

 

 

 

Enter the JDBC URL changing the driver type to oci and using a TNS Net Service name chosen from the tnsnames.ora file in the client credentials directory.

Click on Test Connection which also saves the Data Server.

Click OK when informed to create a physical schema.
Test using Local (No Agent), OracleDIAgent, and OracleDIAgent1as Physical Agents.

 

4

 

 

Click OK.

 

 

___________________________________________________________________________

Creating a Data Server using the JDBC Thin Driver

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

Start a VNC Viewer session.

Start ODI Studio and connect to the repository.

$ /u01/app/oracle/suite/suite_mw/odi_studio/odi/studio/bin/odi -clean -initialize

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

Right Click on the Oracle Technology and click New Data Server.

 

 

 

 

 

 

 

 

In the Definition tab, give the server a name.

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

Click the JDBC tab.

In the JDBC tab:

Ensure the JDBC Driver is oracle.jdbc.OracleDriver

 

The JDBC URL uses a TNS connect descriptor chosen from the tnsnames.ora file in the client connections folder.

Note: this string may need to be all on a single line with no CR/LF after the string.

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=/u01/data/credentials)))

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.

Click OK when informed to create a physical schema.
Test using Local (No Agent), OracleDIAgent, and OracleDIAgent1as Physical Agents.

 

4

 

 

Click OK.

 

 

 

___________________________________________________________________________

Summary

This post provided the steps required to create connections in Oracle Data Integrator Cloud Service (ODICS) on Oracle Cloud Infrastructure (OCI) to the Autonomous Data Warehouse (ADW). 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

Connecting Oracle Data Integrator Studio to the Autonomous Data Warehouse

Connecting DIPC ODI Studio to the Autonomous Data Warehouse

About Java Cloud Service Instances in Oracle Cloud Infrastructure

Autonomous Data Warehouse Cloud

Oracle Data Integrator Overview

Create an Oracle Java Cloud Service Instance Attached to a Public Subnet on Oracle Cloud Infrastructure

Provisioning Oracle Data Integrator Cloud Service

Installing and Configuring Oracle Data Integrator

Preparing to Connect to the Autonomous Data Warehouse

https://www.putty.org/

Connecting to a Compute Node Using the PuTTY Program on Windows

Accessing an Instance Using SSH

About JDK Requirements for an Oracle Fusion Middleware Installation

Shut down a server instance

Start Managed Servers from the Administration Console

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

Oracle A-Team Chronicles for ODI

Add Your Comment