Connecting DIPC ODI Studio to the Autonomous Data Warehouse

Introduction

This post details the steps required to create connections in the Data Integration Platform Cloud (DIPC) to the Autonomous Data Warehouse (ADW). 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 Autonomous Data Warehouse Cloud which uses ODI Studio on Windows.

For additional details about ADW, refer to Autonomous Data Warehouse Cloud. 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 DIPC visit Create a Data Integration Platform Cloud Instance, Connecting to ODI Studio with VNC Server and Installing and Configuring Oracle Data Integrator

Updates

Validated

October 3rd, 2018 for DIPC 18.3.3.0.201807200708

June 11, 2018 for DIPC 18.2.3.0.180427

Updated

August 31, 2018 to clarify Java directories

June 11, 2018 to remove references to ODICS

Topics

Preparing to Connect to the Autonomous Data Warehouse

Preparing Cloud Instances to Connect to the Autonomous Data Warehouse

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 DIPC 18.1.3.0.180122 and 18.2.3.0.180427

12.2.1.3.1 on DIPC 18.3.3.0.201807200708

Preparing to Connect to the Autonomous Data Warehouse

The steps described in the post Preparing to Connect to the Autonomous Data Warehouse Cloud provide the client credentials zip file containing the wallet and network configuration files used by ODI to make the connections.

Preparing Cloud Instances to Connect to the Autonomous Data Warehouse

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

Preparing for the JDBC Thin Driver

Connecting to ADWC 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 ADWC wallet.

Determine the Java JDK directory location that ODI Studio is using. It must be a JAVA JDK directory. The Java JDK directory location may be stored in the ODI product.conf file. If it is not there and ODI studio is running, the linux ps -ef command shows the JDK directory used to start the studio.

Set the JAVA_HOME variable based on the directory found above. 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.

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

Preparing for the JDBC OCI Driver

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

Topics

Preparing the Oracle Client Libraries

Preparing the SQL*Net Configuration Files

Preparing the Oracle Client Libraries

ODI version 12.2.1.2.6 requires the 12.1.x version of Oracle Client.
ODI Version 12.2.1.3.+ requires the 12.2.x version of Oracle Client.

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

Set the ORACLE_HOME variable for the current session.

export ORACLE_HOME=/u01/app/oracle/suite/ociAdmin

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=/u01/app/oracle/suite/ociAdmin/lib:$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 overrides 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

Restarting the ODI Managed Server

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

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

$ /u01/app/oracle/suite/suite_mw/odi_studio/odi/studio/bin/odi -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

$ /u01/app/oracle/suite/suite_mw/odi_studio/odi/studio/bin/odi -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=(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=/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 Data Integration Platform Cloud (DIPC) to the Autonomous Data Warehouse Cloud (ADWC). 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

Autonomous Data Warehouse Cloud

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 Autonomous Data Warehouse 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 Autonomous Data Warehouse Cloud

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