As describe in the article Integrating Oracle Data Integrator (ODI) On-Premise with Cloud Services, if you are considering connecting to the Cloud and using Oracle DBCS – Oracle Database Cloud Service - one of the good news is that you can use ODI on-premise to do the work. There are 2 ways to connect: either using the native JDBC Driver, either using a specific Driver. In my investigations on how to use ODI in the Cloud I have tested both drivers.

Connect to DBCS using Native JDBC driver

When connecting to Oracle Database Cloud Service (DBCS) from ODI it is possible to use the “default” JDBC driver but, in that case, some additional steps are required to create an SSH tunnel between the machine where the ODI Agent is running and the DBCS Cloud service. We are going to look at those steps.

Define a tunnel

Refer to Creating an SSH Tunnel to a Port in the Virtual Machine but with following changes:

-- the ip you need is the DBCS one

-- the tunnel will be between Local Host 5656 and Remote Port 1521 (or the one defined in your organization as SQL*Net port).

Open the tunnel and then you are ready to go to connect to DBCS safely through SSH.

Use the native Driver

Once you have created your tunnel, ODI Agent can connect to DBCS as any other Oracle database. As the tunnel is set you can connect directly to localhost:5656.

This step is not mandatory when willing to use using LKM File to Oracle (SQLLDR), but as the tunnel now exists it is easy to use it in ODI.
Now, let me share some tips as well on how to use that KM in a DBCS environment.

Steps to use LKM File to Oracle (SQLLDR)

Apply Patch

If you are in ODI 12.1.3 then apply Patch 18330647: ODI JOBS FAILS CALLING SQLLDR ON WINDOWS 7, WINDOWS 2008.

Note: if you are already using LKM File to Oracle (SQLLDR) a “copy of” will be created by the Patch. The LKM build must be 45.1 or higher.

This issue is fixed in ODI 12.2.1

Define the tnsnames.ora entry for DBCS

When using LKM File to Oracle (SQLLDR), the connection to DBCS is made directly through the tnsnames.ora and not the ODI Topology.
So, in order to use SQL Loader, an entry for your DBCS instance must be created in tnsnames.ora (where ODI Studio and/or the Agent are runnning)

MyDBAAS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 5656))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = YourPDB.YourIdentityDomain)
)
)

As we are connecting through the SSH tunnel:

. the Host=localhost and

. the Port=5656 (the local port defined for the tunnel).

Do not forget to set “MyDBAAS” in your ODI Topology as the Instance Name of your Oracle Data Server.

It is then possible to use SQL Loader utility, through LKM File to Oracle (SQLLDR).

Note that, as the connection to DBCS is done through SSH, the performance is not equivalent to an internal network.

Connect to DBCS using SSL tunneling driver (custom driver)

To avoid performing the manual steps defined above, David Allan has published a very interesting article (ODI 12c and DBaaS in the Oracle Public Cloud) about connecting ODI to DBCS using a custom driver which performs the SSL tunneling for us.

Here are the steps I have done to make it work, using my ODI 12.1.3 on-premise Agent.

Get ready!

1- Create an OpenSSH Key

When you created your Database Cloud Service Instance you had to provide a Private Key. You can use either PuttyGen or SSH to convert it in OpenSSH key which is the only format supported by the tunneling driver

2- Download the driver (odi_ssl.jar) from OTN here and save it in any temporary folder.

Install the Driver

1- Stop all ODI processes.

2- Copy odi_ssl.jar into the appropriate directory:

-- For ODI Studio (Local, No Agent), place the files into the "userlib" directory

On UNIX/Linux operating systems, go to the following directory

$HOME/.odi/oracledi/userlib

On Windows operating systems, go to the following

%APPDATA%\odi\oracledi\userlib

%APPDATA% is the Windows Application Data directory for the user (usually C:\Documents and Settings\user\Application Data)

-- For ODI standalone Agent, place the files into the "drivers" directory:

For ODI 12c: $ODI_HOME/odi/agent/lib

For ODI 11g: $ODI_HOME/oracledi/agent/drivers

-- For ODI J2EE Agent, and ODI 12c colocated Agent:

The JDBC driver files must be placed into the Domain Classpath.
For details refer to documentation: http://docs.oracle.com/middleware/1212/wls/JDBCA/third_party_drivers.htm#JDBCA706

Use the Driver

1- Create the properties file with text below and save it (for example c:\dbcs\dbcs.properties)

You need to check the ip of your DBCS instance, from the DBCS console:

sslUser=oracle (DBCS user)
sslHost=<your_dbcs_ip_address>
sslRHost=<your_dbcs_ip_address>
sslRPort=1521 (DBCS Listener Port)
sslPassword=your_private_key_password
sslPrivateKey= <url to OpenSSHKey> (ex: D:\\DBCS\\myOpenSSHKey.ppk)
sslLPort=5656 (Local port used in the JDBC url)

it is possible to define two hosts - one that you SSH to (sslHost) and one where the Oracle listener resides (sslRHost). The DBCS infrastructure today has the same host, but the driver supports different hosts (for example if there is a firewall that you SSH to and then the Oracle listener is on another machine).

2- Create a new Data Server under the Oracle Technology

JDBC driver: oracle.odi.example.SSLDriver
JDBC url: jdbc:odi_ssl_oracle:thin:@localhost:5656/YourPDB.YourIdentityDomain

(ex: jdbc:odi_ssl_oracle:thin:@localhost:5656/ODIPDB1.usoraclexxx.oraclecloud.internal)

Property: PropertiesFile = c:\dbcs\dbcs.properties

You can refer to David’s Blog for more details: ODI 12c and DBaaS in the Oracle Public Cloud

this connection can only be used when there is a direct JDBC connection. It means that, if you are planning to use the SQL Loader utility from ODI, this tunneling driver cannot be used.

Conclusion

Using these methods, it is pretty easy to connect to Oracle Database Cloud Service to load or extract data in the Cloud.

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

Please do not hesitate to add any comment/remark!

Acknowledgements

Special thanks to David Allan, Oracle Data Integration Architect, for his help and support.