Connecting ODICS and DIPC Studios to the Exadata Express Cloud

Introduction

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

For additional details, refer to Exadata Express Cloud Service. For additional details about ODI, refer to Oracle Data Integrator Overview.

This post assumes ODI, Agents, VNC Server/Viewer, and ODI Studio have been installed and configured.

For information on provisioning ODI on ODICS visit Provisioning Oracle Data Integrator Cloud Service and Installing and Configuring Oracle Data Integrator

For information on provisioning ODI on DIPC visit Create a Data Integration Platform Cloud InstanceConnecting to ODI Studio with VNC Server and Installing and Configuring Oracle Data Integrator

Topics

Preparing to Connect to the Exadata Express Cloud

Preparing Cloud Instances to Connect to the Exadata Express Cloud

Preparing Cloud Instances 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

Versions Used

ODI Studio 12.2.1.2.6 on ODICS12.2.1.2.180120

ODI Studio 12.2.1.2.6 on DIPC 18.1.3.0.180122 and 18.2.3.0.180427

Exadata Express 18.1.4

Preparing to Connect to the Exadata Express Cloud

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

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

Preparing Cloud Instances to Connect to the Exadata Express Cloud

This post uses PuTTY and PSCP SSH applications on Windows to connect to ODICS and DIPC. 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 Cloud Service Private Key and Administrator Credentials

Obtaining the Cloud Service Public IP Address

Ensuring the Cloud Service SSH Port is Accessible

Backing Up the Cloud Service Instance

Uploading the Client Credentials to Cloud Services

Preparing the Credential Files

Obtaining the Cloud Instance 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 the Cloud instance 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 Cloud Instance Public IP Address

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

Ensuring the Cloud Instance SSH Port is Accessible

PuTTY uses port 22 for access. This port should be open in the Cloud Services via an access rule by default. For details on how to view the instance access rules, refer to Understanding the Default Access Ports.

Backing Up the Cloud Instance

Backup the Cloud Instance. For details refer to Backing Up and Restoring an Oracle Java Cloud Service Instance.

Uploading the Client Credentials to the Cloud Services

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 Cloud Service SSH Private Key> <Your Client Credentials Zip File> opc@<Cloud Service 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 /u01/data/credentials
cd /u01/data/credentials; unzip /tmp/<Your Client Credentials Zip File>

Preparing Cloud Instances to use the JDBC drivers

Both the Thin and OCI JDBC drivers require modifications to the Cloud Instance.

Topics

Preparing for the JDBC Thin Driver

Preparing for the JDBC OCI Driver

Restarting the ODI Managed Server

Restarting the VNC Server and Viewer

Preparing for the JDBC Thin Driver

Connecting to Exadata Express 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 Exadata Express 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 (10) is one greater than the previous entry.

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

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

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.

Uploading the Instant Client zip files

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

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

Notes:

If there is a passphrase for the private key, you are prompted for it.
Ensure the SQL*Plus Zip File is included.
Ensure only Instant Client zip files are in the directory.
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.
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

Do the following. Refer to the installation instructions at the foot of the Instant Client Download page for additional details.

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_1. Then change to the new sub-directory.

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

Create symbolic links

ln -s libclntsh.so.12.1 libclntsh.so
ln -s libocci.so.12.1 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”\$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

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, 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

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.

export PATH=/u01/data/instantclient_12_1:$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:

[oracle@ateamjava-wls-1 instantclient_12_1]$ 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 WebLogic Servers

The WebLogic managed server is restarted for the agents to use the new environment variables and the updated java security file. Refer to Start Managed Servers from the Administration Console for details on restarting servers.

Restarting the VNC Server and Viewer

Use a PuTTY session to stop and start the VNC Server

vncserver -kill :1
vncserver -nolock

Creating a Data Server using the JDBC Oracle Call Interface Driver

Start a VNC Viewer session.

Note: Once VNC connects you to the remote machine, you want to turn off the screen saver, otherwise you could find yourself locked out of the remote machine. Select System/Preferences/Screensaver menu item. In the resulting Screensaver Preferences dialog box, make sure Activate screensaver when computer is idle is not selected.

Refer to the VNC section in Connecting to ODI Studio with VNC Server for details. This post uses TigerVNC Viewer. TigerVNC is available at https://github.com/TigerVNC/tigervnc/releases

Start a viewer terminal session by selecting Applications>System Tools>Terminal

Start ODI Studio

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

Connect to the Repository. If using DIPC and this is the first attempt at connecting to the repository, refer to Connecting to ODI Studio with VNC Server for details.

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), JREE Agent, and the Standalone Collocated Agent as Physical Agents.

4

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.

Note: Once VNC connects you to the remote machine, you want to turn off the screen saver, otherwise you could find yourself locked out of the remote machine. Select System/Preferences/Screensaver menu item. In the resulting Screensaver Preferences dialog box, make sure Activate screensaver when computer is idle is not selected.

Refer to the VNC section in Connecting to ODI Studio with VNC Server for details. This post uses TigerVNC Viewer. TigerVNC is available at https://github.com/TigerVNC/tigervnc/releases

Start a viewer terminal session by selecting Applications>System Tools>Terminal.

Start ODI Studio

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

Connect to the Repository. If using DIPC and this is the first attempt at connecting to the repository, refer to Connecting to ODI Studio with VNC Server for details.

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.

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. This post uses the Local (No Agent), JREE Agent, and the Standalone Collocated Agent as Physical Agents.

4

Summary

This post provided the steps required to create connections in Oracle Data Integrator Cloud Service (ODICS) and Data Integration Platform Cloud (DIPC) to the Exadata Express Cloud. It demonstrated the use of both the thin and oci JDBC drivers.

For more ODI 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

About JDK Requirements for an Oracle Fusion Middleware Installation

Connecting Oracle Data Integrator Studio to the Exadata Express Cloud

Provisioning Oracle Data Integrator Cloud Service

Create a Data Integration Platform Cloud Instance

Connecting to ODI Studio with VNC Server

Preparing to Connect to the Exadata Express Cloud Service

Tiger VNC Download

Instant Client Download

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