Connecting ODI 12c to the Oracle Public Cloud

Recently I had the opportunity to subscribe to the Oracle Public Cloud DBaaS, and since I’ve been working with ODI 12c in my test lab, what better thing to do than explore some opportunities to utilize these two technologies. This post will cover the steps necessary to connect on premise ODI to the Oracle Public Cloud.

Toolkit:

Oracle VM VirtualBox
ODI 12c Virtual Machine – download from OTN here.
Oracle Public Cloud (Trial version will work)
SSL tunneling via odi_ssl.jar
SSH Key from when you created your OPC database

This post will not go into signing up for the trial, but I will identify parameters necessary to configure the ODI connection.  Let’s get started shall we?

Get Parameters from your OPC Database:

Public IP: x.x.x.x
SQL *Net Port: 1521 (default)
User Name: oracle (default)
Connect String: <dbname>:1521/PDB1.<identity domain>.oraclecloud.internal
Administrator Password: (this was established when you provisioned the OPC database)

Now that you have this information, let’s get into ODI 12c VM and connect to this database.

From your toolkit, spin up the ODI 12c Getting Started VM.

You now need to do two simple setups before launching ODI 12c in order to enable connectivity to the OPC Database Service.

1. Copy the odi_ssl.jar file referenced in the toolkit to the following directory: /u01/Middleware/Oracle_Home_1213/odi/sdk/lib
2. Copy your private key file to a directory of your choice on your guest OS. ie: /home/oracle/Documents/ODI/MyDBaaSKey.txt
3. Launch and log into ODI 12c Studio.

Now that you have logged in, we will create a New Data Server which will be the connection to the OPC Database Service.

1. Expand the Technologies tree under the Topology tab, right click the Oracle Technology, and create a new Data Server.

2. The Definition tab will open
   • Provide a name for the Data Server
   • Enter your OPC Database Server administrator account and password. I am using system with the password established during the provisioning of the database.
   • Save your setting, and click OK on the Information popup.

Note that the location we copied the odi_ssl.jar file is important as this is where ODI looks for the drivers. Your implementation may have a slightly different location if you ODI_HOME is in a different path, but if you are using the VM from OTN, you should be fine.

Unlike the normal process of using the search feature to find the JDBC Driver, you will have to manually enter the information below.

3. Click the JDBC tab and enter the JDBC Driver and JDBC URL
   • JDBC Driver: oracle.odi.example.SSLDriver
   • JDBC URL: jdbc:odi_ssl_oracle:thin:@localhost:5656/PDB1.<identity domain>.oraclecloud.internal

4. Review your OPC parameters collected earlier and click on the Properties tab and create the Key | Value pairs by clicking on the green +
   • sslHost: <cloud ip address>
   • sslRHost: <cloud ip address>
   • sslUser: oracle
   • sslRPort: 1521
   • sslPrivateKey: <path to your private key>
   • sslPassword: <private key password>
5. Save your connection information and click OK to any Information popups

A more secure way to implement this would be to place the key value pairs
in a file and reference the file here.

6. Test your connection

Click the Test Connection button. You can either choose an Agent or use the Local (No Agent). If everything went well, you should get a Successful Connection notification.

Next we will use this connection to perform some E-LT operations from on premise to our DBaaS.

Enjoy!