Follow Us on Twitter

Using the “thick” OCI drivers in ODI 11g IDE

by Laurens van der Starre on June 21, 2011 · 6 comments

Oracle Data Integrator 11g comes out of the box with a whole bunch of JDBC drivers. For Oracle, this is the “thin” Type-4 driver. However you might not always want to use this thin driver, but instead want to use the ‘thick” OCI Type 2 driver. The OCI driver is said to have a greater performance, and supports more database features like TAF (Transparent Application Failover). In this case for a developer, it is very useful that one can connect to these data sources. In this blog I’ll show you how to install this driver in ODI 11g.

Somehow I wasn’t able to find much information on this topic. If someone has found some official Oracle documentation on this, please drop a note in the comments. Oracle Support ID 602706.1 gives some clues, but somehow it looks like the ODI 10g documentation with some 11g added to it. Anyway …

The ODI Topology interface says it supports Type 2 and 4 drivers when creating a new data server, but “supports” means something else as “works out of the box” here.

If you’d chose the OCI connection string, the well known error

java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path

will haunt you. So we will need the OCI driver.

I did this before in ODI 10g, so I basically applied the same logic to 11g. I use Ubuntu linux, but Windows users should use an analogous approach(use backward slashes instead of forward slashes etc).

First of all it is important that the thin and thick drivers are of the exact same version. To see what version of the thin driver comes with the ODI 11g installation, see the META-INF/MANIFEST.MF file in $ODI_HOME/modules/oracle.jdbc_11.1.1/ojdbc6dms.jar, where $ODI_HOME is your installation directory of ODI 11g (by the way, a jar file can be opened with a unzip tool).

There should be something like this in the file:

Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC DMS
Implementation-Version: 11.2.0.2.0
Repository-Id: JAVAVM_11.2.0.2.0AS11.1.1.5.0_LINUX_110321
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Specification-Version: 4.0
Main-Class: oracle.jdbc.OracleDriver

In this case the driver version is 11.2.0.2.

The next step is to download the correct thick driver. For this you will need the Oracle Client. Go to the download page and chose the correct version (press “See All” for the client downloads). In my case this wasn’t necessary, because I have already an Oracle 11g XE (beta) installed on my machine, which contains the correct libraries I can use. After installing the client, set up your TNSNAMES.

Now we need to add the libraries that come with the Client to our java.library.path. For this, go to $ODI_HOME/oracledi/client/odi/bin . Edit odi.conf and add the following line:

AddVMOption -Djava.library.path=<<ORACLE_HOME>>/lib

Of course change <<ORACLE_HOME>> with your path to the Oracle Client home directory. In my case this line is:

AddVMOption -Djava.library.path=/u01/app/oracle/product/11.2.0/xe/lib

Then, make sure that the following system variables are set:

ORACLE_HOME=<<ORACLE_HOME>>
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:$PATH

Again, change it to your own Oracle Home. On my Ubuntu machine it is for example:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH

On Windows, it is probably something like this on the command line (but forgive my “rusty” Windows skills):

set ORACLE_HOME=C:\oracleclient
set LD_LIBRARY_PATH=%ORACLE_HOME%\lib
set PATH=%ORACLE_HOME%\bin;%PATH%

(Or put it in the system environment variables)

If we now start ODI with these environment settings set, we can use the jdbc:oracle:oci8:@  JDBC URL. Happy coding!

 

Using the "thick" OCI drivers in ODI 11g IDE, 3.7 out of 5 based on 3 ratings
Ratings:
VN:F [1.9.22_1171]
Rating: 3.7/5 (3 votes cast)
Tags: , , , ,

{ 6 comments… read them below or add one }

Homero Garcia June 27, 2012 at 11:25 pm

Hi Laurens,
Your entry is the closest thing to what I require.
I have an ODI 11.1.1.3 “server” installed in an AIX server. Actually we only install the agent binaries on that box, so I don’t have access to the $ODI_HOME/oracledi/client/odi/bin directory.
What I tried was to change the $ODI_HOME/oracledi/agent/bin/odiparams.sh file to add this entry: ODI_ADDITIONAL_JAVA_OPTIONS= -Djava.library.path=$ORACLE_HOME/lib
I restarted the AGENT but is throwing this error:

$ /u01/app/oracle/product/odi_11/DSIIE9O/oracledi/agent/bin/agent.sh[47]: -Djava.library.path=/u01/app/oracle/product/11.2.0/dbhome_1/lib: not found.
2012-06-27 16:13:05.130 NOTIFICATION ODI-1128 Agent DSIIE_Agent is starting. Application Server: STANDALONE. Agent Version: 11.1.1.3.0 – 23/06/2010. Port: 15910. JMX Port: 16910

What can I do in order to configure and use the OCI in ODI connections???

Thanks in advance!!!!

Reply

Laurens van der Starre September 9, 2013 at 7:42 am

Hi,

It is a bit hard to say what goes wrong here. However, the error does suggest that the agent can’t access or find the directory in Unix. What are the permissions on the $ORACLE_HOME/lib path? Does the user which starts the agent have access to that path, or is it somehow jailed?

Regards,

Laurens.

Reply

Maria Bufnea March 9, 2014 at 8:39 am

Hi,

I have the error :
java.lang.Error: Incompatible version of libocijdbc[Jdbc:112030, Jdbc-OCI:112020
at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:3557)
at java.security.AccessController.doPrivileged(Native Method)
at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:3550)
at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:266)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:552)
at oracle.jdbc.driver.T2CConnection.(T2CConnection.java:162)
at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:526)
at oracle.jdeveloper.db.adapter.AbstractConnectionCreator.getConnection(AbstractConnectionCreator.java:153)
at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.getConnection(RaptorConnectionCreator.java:195)
at oracle.dbtools.raptor.dialogs.conn.ConnectionPrompt.promptForPassword(ConnectionPrompt.java:67)
at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:321)
at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:254)
at oracle.jdevimpl.db.adapter.CADatabaseFactory.createConnectionImpl(CADatabaseFactory.java:60)
at oracle.javatools.db.DatabaseFactory.createConnection(DatabaseFactory.java:534)
at oracle.javatools.db.DatabaseFactory.createDatabase(DatabaseFactory.java:208)
at oracle.jdeveloper.db.DatabaseConnections.getDatabase(DatabaseConnections.java:607)
at oracle.dbtools.raptor.utils.Connections$ConnectionInfo.getDatabase(Connections.java:172)
at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1059)
at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1040)
at oracle.dbtools.raptor.navigator.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:115)
at oracle.dbtools.raptor.navigator.AbstractConnectionNode.getConnection(AbstractConnectionNode.java:30)
at oracle.dbtools.raptor.navigator.ConnectionFilter.getFactory(ConnectionFilter.java:94)
at oracle.dbtools.raptor.navigator.ConnectionFilter$1.doWork(ConnectionFilter.java:119)
at oracle.dbtools.raptor.navigator.ConnectionFilter$1.doWork(ConnectionFilter.java:104)
at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:515)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
This error appears when I’m trying to connect in Oracle / as sysdba.I haven’t this problem until now….I’ve been working with ORACLE from 10 months and I haven’ t this problem.What can I do?????

Thanks,Maria

Reply

Laurens van der Starre March 10, 2014 at 9:20 am

Maria,

It’s very important that the downloaded Oracle “thick” driver is of the EXACT version of the thin driver of ODI. See the blog post. If you look at the error: “Jdbc:112030, Jdbc-OCI:112020” it looks like the this driver is 11.2.0.3 and the thick driver is 11.2.0.2. I guess you should upgrade your thick driver to the correct version.

Reply

Maria Bufnea March 16, 2014 at 11:00 am

Thank you for you answer…

Reply

Jim Russell September 14, 2017 at 10:42 am

Hi

I am hoping that after several years you still check this! I’ve got this error – Error 500:java.lang.Error: Incompatible version of libocijdbc[Jdbc:112040, Jdbc-OCI:112030 – which I’m told means the thin and thick clients are on different versions where I need to bring OCI up to 11.2.0.4. I ran tnsping and sqlplus and they stated they were 11.2.0.4 but I’m assuming it is more specifically the Java libraries. This is where my knowledge runs out! I can locate libocijdbc11.so and saw a similar blog mention ojdbc5.jar and ojdbc6.jar but really have no clue how to know what versions they are or how to update them if need be. Any help would be much appreciated, thanks.

Reply

Leave a Comment

 

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter