X

Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

ODI 12c and DBaaS in the Oracle Public Cloud

David Allan
Architect
This article illustrates how to connect ODI on premise to Oracle in the cloud (OPC), specifically the Database as a Service (DBaaS, see doc here) offering. You will see how easy it is to configure connectivity from within ODI and the use of familiar tools gives you the same consistency from on premise use to the cloud. A big concern for cloud computing is security and ensuring access is restricted and as secure as possible. For ODI on premise the challenge is how to connect to such a secure service. ODI provides tasks for transferring files to and from locations - databases are generally accessed via JDBC.

The initial state of an Oracle DBaaS service restricts remote access to SSL - so you can't just remotely connect by default to an Oracle database listener for example (it is possible to open that up by configuring this within DBaaS). File transfer to the cloud can be done out of the box using sftp capabilities, access to the database in order to load it with data, to transform data within it and to extract data from it can be done with a small bit of SSL tunneling - let's see how. The examples discussed in this article have been developed with ODI 12.1.3, a copy of the driver which performs the SSL tunneling can be found on java.net here. With this driver it is effortless to work with an on premise ODI and an Oracle database in the cloud.

Before we get to the ODI parts let's look at the basics, this is mentioned in the DBaaS documentation but sometimes it's simpler to read what someone has done than follow the doc.....

If you want to be using ODI or other remote capabilities such as ssh, sftp then before creating the Oracle database instance in the cloud you should generate a secure private key-public key pair. The public key gets used when you create the Oracle database instance in the cloud and the private key is used by SSL tools (such as sftp, ssh or the driver used) to securely connect to the cloud. 

When you create the key using something like PUTTY, then ensure you save the public key, private key and export the key using the OpenSSH key option also. ODI actually needs the OpenSSH format right now as the version of a library it depends on supports this.


You can see where the public key is provided below in the Instance Configuration section.....


The great news about the DBaaS capabilities is that it is all very familiar for Oracle database folks also the service itself can be managed from the command line - so as well as the web pages console and EM etc, you can use the command line and work the way you are used to.

Anyway, back on course... when you have the instance up and running it's time to have some fun with it!

File Transfer to the Oracle Public Cloud

In ODI you can use the OdiSftpPut/Get tool in a package, procedure or KM to transfer data to/from the cloud. You can see in the example below the OdiSftpPut tools is being used to transfer a file 'm.csv' from the local filesystem (d:\data) to the directory (/home/oracle) in the cloud. The private key is specified in the property 'SSH Identity File' and the key file password is specified in 'Remote User Password'. The OS user to use for the ftp is specified as 'oracle' in the property 'Remote User Name'.

Very simple. The DBaaS instance has OS users created when initialized you can read more about the users 'opc' and 'oracle' in the DBaaS documentation.

Transforming Data in the Oracle Public Cloud

Mappings are used to transform data from one representation to another. In this example you will see how the file staged in the Oracle Public Cloud is integrated with an Oracle target - just like standard ODI on premise use cases. It is no different. Below you can see the image has the logical mapping at the top, with the file being mapped to an Oracle target table, then the middle part of the image shows the physical design, the map uses the LKM File to Oracle (External Table) KM to create an external table on top of the file in the cloud and then the target is integrated with the Oracle Insert KM. 

When I execute the mapping all of the statements to transform are executed in the OPC - in this particular design everything is executed inside the Oracle database.

The ODI data server definition is using a custom driver (here) which extends the Oracle JDBC driver. The driver creates a SSH tunnel between the host executing the connect and the instance in the cloud. This means all ODI objects such as procedures, mappings and so forth that execute statements on regular Oracle systems can execute them on the cloud instances too. I actually created my demonstration schemas and granted all the permissions using a procedure in ODI. The procedure is shown below, see the snippet of the statements creating the users - the target logical schema was my DBAAS_INSTANCE.

Let's dig under the covers and have a look at how the physical schema is defined. You'll need the driver, and have it copied into your oracledi/userlib directory (or wherever your agent is installed if using an agent). You can then define the connection, specify the database user name and password for that database user;

Then you specify the driver, the driver you need to download and mentioned above. The URL is of the form of the Oracle JDBC driver. The difference is in how you specify the host, port and sid/service. The sid/service are your actual cloud service details. Since we are using the SSH tunnel technique, we actually specify the local host and a port number (default 5656) on the local host.

The properties to configure the SSH tunnel are defined either in the properties panel or in a file specified in the properties panel. I've decided here to use the file approach, so can specify the file name in the property propertiesFile.

In my example, this file contains;

  • sslUser=oracle
  • sslHost=<my_cloud_ip_address>
  • sslRHost=<my_cloud_ip_address>
  • sslRPort=1521
  • sslPassword=my_private_key_password
  • sslPrivateKey=D:\\credentials\\dbcloud12c_private_key_openssh.ppk

That is all that is needed and you can be very creative using all the powers of Oracle in the cloud and ODI for integration. Here is a summary of the properties supported by the driver.

Property Name Property Description
sslUser The OS user to connect via SSL with.
sslHost The address of the host to SSL to.
sslRHost The tunnel can be made between the client through the SSL host to this host. In my case this was the same as the SSL host.
sslRPort The port to tunnel to. The Oracle listener is often run on 1521, so this is the default if this property is not specified.
sslPassword The password for the private key file. In ODI you must use OpenSSH formatted private key file.
sslPrivateKey The SSL private key file location.
sslLPort By default the local port used is 5656, it can be changed with this property. You must reference this port number in the URL also.

The driver is a fairly simple wrapper around the Oracle JDBC driver, it leverages SSL tunneling to forward requests on a secure port to the Oracle TNS listener. This technique enables a very familiar way of connecting and interacting with the Oracle database, the driver is on java.net and is available to try and get feedback on. So try it and let us know what you think. Familiarity and consistency are very important both from the stance of the tooling and leveraging existing knowledge (including modules). This allows ODI users to work with the Oracle Public Cloud DBaaS instance just as they do with their on premise systems. Have fun!

Join the discussion

Comments ( 23 )
  • George Friday, October 31, 2014

    Hi David,

    Thanks for the helpful post.

    You have shown ODI can connect to Oracle DBaaS, move a file from a local environment up to the cloud, and then use the file as a source to perform further data operations (inserts, updates, deleted, etc).

    Question: Does this solution enable ODI to interact with the cloud DBaaS exactly as if it were a normal on-premise database instance?

    (Or is there limited functionality at this time still).

    Thanks,

    George


  • David Friday, October 31, 2014

    Hi George

    With DBaaS you have a regular database ... You can pretty much do whatever you want! In the blog above I've a procedure that creates all the schemas and privileges and all sorts. You can also SSH to the Host and do OS stuff.

    Cheers

    David


  • guest Thursday, November 20, 2014

    Hello David,

    I have opted for the trial version of OPC DB and got an access to DB Schema as Service instead of DBaaS. Here a DB schema in the cloud is already provided by Oracle hence cannot create an instance. Now I am unable to connect to the cloud DB from ODI using JDBC driver though I am successfully connected to this from SQL Developer.

    I am giving the url as:

    jdbc:oracle:thin:@hostservicename.db.us2.oraclecloudapps.com:80:_sqldev

    Can you please let me know where am I mistaken in this?

    Thanks much!

    Naini


  • David Thursday, November 20, 2014

    Hi Naini

    The OPC Database Schema as a service capability does not allow connectivity via JDBC. Can you send me the link you followed for connecting via SQLDeveloper? The documentation illustrates how SQLDeveloper connects to Database Schema as a service here;

    https://docs.oracle.com/cloud/latest/dbcsme_common/CSDBU/GUID-3B14CF7A-637B-4019-AAA7-A6DC5FF3D2AE.htm#CSDBU179

    Cheers

    David


  • Nishant Jain Friday, November 28, 2014

    Hi David,

    Thanks for the post. I am trying to configure it at my end. Somehow, the driver link mentioned in the post doesn't appear in the ODI topology.

    I am ODI 12c, please advise if this is the correct driver as mentioned in this blog.

    Regards

    Nishant Jain


  • David Friday, November 28, 2014

    Hi Nishant

    You must just type the driver name. You have to download from link in blog too beforehand.

    Cheers

    David


  • guest Monday, December 8, 2014

    Hello David,

    I have followed the same steps as mentioned in the url above.

    Also I noticed that the I am being given a read-only access on the objects of that schema.

    Could you please let me know how can I connect to this schema via ODI and do a small PoC on cloud connectivity using this trial subscription?

    Many Thanks,

    Naini


  • David Monday, December 8, 2014

    Which schema do you have read only access to? How are you connecting, which user for authentication etc?

    You have a trial subscription to a DBaaS or a schema as a service?

    Cheers

    David


  • guest Wednesday, April 29, 2015

    David,

    Great post. I noticed the accent on odi 12c. Is this also possible in odi 11g?


  • David Wednesday, April 29, 2015

    Hi

    Yes it is applicable to 11g too.

    Cheers

    David


  • guest Thursday, April 30, 2015

    David,

    Could we use this approach to take on premise data and use ODI to transform and land it in a BICS environment?


  • guest Tuesday, June 2, 2015

    David,

    Looking to take our on premise dw and deploy it as DbaaS. The source data would potentially be on premise in an ERP, extracted and loaded to a staging area in DbaaS(stage schema) and then transformed to the DW also loaded in the DbaaS(dw schema). Do you foresee any issue with this architecture? My big question is Where would the odi agent be placed? Could it be placed in the cloud where the DbaaS is????


  • David Tuesday, June 2, 2015

    Hi

    We are working right now on certifying ODI to run on the Oracle Public Cloud. When the official certification is announced a document will be provided to help users set up ODI to run in this environment, this document should be available soon. The Agent would be deployed on a Java Cloud Service Virtual Image instance inside WebLogic Server 12c.

    Cheers

    David


  • guest Tuesday, June 9, 2015

    Hi David,

    Thanks for the post.

    I have few questions regarding the properties file

    a) sslUser-->Is this the credential to connect to my developer database on cloud?

    b) sslHost--> Is this the host name from where I am connecting to the cloud instance.

    c) Difference between sslHost and sslLPort


  • David Wednesday, June 10, 2015

    Hi

    1. This is the OS user ... DBAAS has a couple of OS accounts that can be used

    2. sslHost is the address of cloud DBAAS host

    3. sslHost and sslRHost in my example were the same - but this allows for them to be different

    Cheers

    David


  • guest Tuesday, December 8, 2015

    Hi David,

    Thanks for the post.

    We are using ODI 11.1.1 version and followed all the above steps to connect it to the DBaaS from ODI but we are getting the ODI-26039: Connection failed error message. Please let me know how to fix this issue.

    Thanks,

    Vamshi


  • guest Thursday, December 10, 2015

    David,

    Trying to get this to work with odi 11g and I am not having any luck. I am receiving the error below. I opened an SR with oracl support but the tech is saying he can't find anything on 11g and DBaaS.

    java.lang.RuntimeException: java.lang.UnsupportedClassVersionError: oracle/odi/example/SSLDriver : Unsupported major.minor version 51.0

    at oracle.odi.ui.framework.adapter.DefaultAdapter.executeBackgroundTask(DefaultAdapter.java:611)

    at oracle.odi.ui.framework.UIFramework.executeBackgroundTask(UIFramework.java:347)

    .....


  • guest Friday, January 1, 2016

    Hi David,

    Once again I revisited your blog and try to configure DBAAS(11g) with ODI. Still, I am not able to establish connectivity. I have followed exact steps which you mentioned. I am getting below error and at the same I am able to establish connection with SQL developer (SSH tunnel).

    java.sql.SQLException: IO Error: The Network Adapter could not establish the connection


  • David Monday, January 4, 2016

    Hi Tom

    On the ODI 11g error, that's why you are getting the UnsupportedClassVersionError error. The driver I uploaded is compiled with a later Java. Can you use ODI 12c?

    Hi Nishant

    It sounds like you have configured the properties incorrectly in ODI. Can you mail me offline more details.

    Cheers

    David


  • vikram Monday, April 18, 2016

    Hi,

    I was trying your blog on "File Transfer to the Oracle Public Cloud" but it failed with the below error.

    I have couple of questions:

    what will go into : Remote User Name and Remote User Password - is this the users in DBaas (i.e. in your example you had a user in DBaas named 'Oracle' and its corresponding password)

    Below is the error trace saying "Could not load private key"

    org.apache.commons.vfs2.FileSystemException: Could not connect to SFTP server


  • Vikram Monday, April 18, 2016

    Hi,

    I was trying your blog on "File Transfer to the Oracle Public Cloud" but it failed with the below error.

    I have couple of questions:

    what will go into : Remote User Name and Remote User Password - is this the users in DBaas (i.e. in your example you had a user in DBaas named 'Oracle' and its corresponding password)

    Below is the error trace saying "Could not load private key"

    org.apache.commons.vfs2.FileSystemException: Could not connect to SFTP server

    Thanks

    Vikram


  • guest Friday, June 17, 2016

    Hi David,

    I have downloaded the driver and placed in the userlib directory. But somehow its not appearing in the driver list of Oracle technology. Tried in manually typing the driver name.This throws me an error driver is not referenced in the list of URL's. Could you please suggest how to go about it.


  • guest Friday, October 21, 2016

    Hi David,

    I am not able to connect to Oracle cloud through ODI 12c. I followed the same steps given in this blog. I am facing below error.

    java.sql.SQLException: IO Error: Got minus one from a read call

    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:144)

    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:73)

    at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java:1258)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.getLocalConnect(SnpsDialogTestConnet.java:207)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$1300(SnpsDialogTestConnet.java:65)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$6.doInBackground(SnpsDialogTestConnet.java:407)

    at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$6.doInBackground(SnpsDialogTestConnet.java:403)

    at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)

    at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:961)

    at java.lang.Thread.run(Thread.java:745)

    Could you please help me through this.

    Thanks

    Arun


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services