Recently was helping a out a situation to setup up a datasource in WebSphere Application Server to connect using the Oracle OCI JDBC Driver. At a first look, for anybody who has configured a datasource this would be a pretty simple configuration, where you will have to replace the word "thin" from the conventional URL to "OCI" and the URL would look like "jdbc:oracle:oci:@//hostname:1521/dbname". However this was not the case as they were getting the famous java.lang.UnsatisfiedLinkError: ocijdbc12 (Not found in java.library.path) and this was in spite of setting the LIBPATH pointed to the appropriate Instant Client directory that was housing all the required OCI Libraries. Interestingly the same configuration when tested in my local environment worked all good like an angel. We were assuming that the Instant client files are one and the same between the failing environment and the passing environment and after a extensive workout we realized they weren't. Can you make out ?
Problematic directory listing of the OCI driver files :
-rw-r--r-- 1 oracle install 1560715 Sep 12 2014 libsqlplusic.so
-rw-r--r-- 1 oracle install 109543376 Oct 30 2014 libociei.so
drwxr-xr-x 2 oracle install 256 Jul 01 15:51 light
lrwxrwxrwx 1 oracle install 54 Jul 01 15:53 ojdbc6.jar -> /u01/app/oracle/product/121/client/jdbc/lib/ojdbc6.jar
lrwxrwxrwx 1 oracle install 54 Jul 01 15:53 libocijdbc12.so -> /u01/app/oracle/product/121/client/lib/libocijdbc12.so
lrwxrwxrwx 1 oracle install 49 Jul 01 15:53 libocci.so -> /u01/app/oracle/product/121/client/lib/libocci.so
lrwxrwxrwx 1 oracle install 50 Jul 01 15:53 libnnz12.so -> /u01/app/oracle/product/121/client/lib/libnnz12.so
lrwxrwxrwx 1 oracle install 55 Jul 01 15:53 libheteroxa12.so -> /u01/app/oracle/product/121/client/lib/libheteroxa12.so
lrwxrwxrwx 1 oracle install 55 Jul 01 15:53 libclntshcore.so -> /u01/app/oracle/product/121/client/lib/libclntshcore.so
lrwxrwxrwx 1 oracle install 51 Jul 01 15:53 libclntsh.so -> /u01/app/oracle/product/121/client/lib/libclntsh.so
Working directory listing of the OCI driver files :
-rw-r--r-- 1 root system 63169440 Oct 30 2014 libclntsh.a
-rwxr-xr-x 1 root system 61808612 Oct 30 2014 libclntsh.so
-rw-r--r-- 1 root system 4298017 Oct 30 2014 libclntshcore.a
-rwxr-xr-x 1 root system 4181574 Oct 30 2014 libclntshcore.so
-r-xr-xr-x 1 root system 8059635 Oct 30 2014 libnnz12.so
-rw-r--r-- 1 root system 6444907 Oct 30 2014 libocci.a
-rwxr-xr-x 1 root system 5006916 Oct 30 2014 libocci.so
-rwxr-xr-x 1 root system 109543376 Oct 30 2014 libociei.so
-r-xr-xr-x 1 root system 879037 Oct 30 2014 libocijdbc12.so
-r-xr-xr-x 1 root system 427341 Oct 30 2014 libons.so
-rwxr-xr-x 1 root system 83295045 Oct 30 2014 libttsh12.so
-r--r--r-- 1 root system 3692106 Oct 30 2014 ojdbc6.jar
-r--r--r-- 1 root system 3698883 Oct 30 2014 ojdbc7.jar
-rwxr-xr-x 1 root system 705199 Oct 30 2014 uidrvci
-rw-r--r-- 1 root system 71202 Oct 30 2014 xstreams.jar
In the failing environment the OCI library path that was configured were actually symbolic links to the actual files and this was preventing the libraries from getting loaded properly resulting in the java.lang.UnsatisfiedLinkError. Once the LIBPATH was pointed to the actual files instead of the symbolic links, the problem went away and the connectivity worked all good. This concluded that symbolic links to the OCI JDBC libraries were not allowing them to get loaded properly within the JVM.
Also I felt listing down the steps required to configure Oracle OCI JDBC driver in WebSphere Application Server may help whoever needs it .
Steps to configure the Oracle OCI JDBC Driver in WebSphere Application Server :
1) Download a fresh copy of the Oracle 12c OCIJDBC Drivers :
Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications
Download instantclient-basic-aix.ppc64-22.214.171.124.0.zip (119,744,345 bytes) (cksum - 1223715379)
Note : The AIX environment used in our lab is a AIX ppc64 bit machine and hence we have used the above files. You will have to pick the appropriate Instant Client package.
2) Extract the instantclient-basic-aix.ppc64-126.96.36.199.0.zip to a directory say /home/instantclient_12_1 and issue " chmod +X * " to all the files under instantclient_12_1
3) Created a JDBC Provider for Oracle and configure the classpath ie ORACLE_JDBC_DRIVER_PATH variable to /home/instantclient_12_1 so that the datasource picks up the ojdbc6.jar from the instant client directory. Also define the native library path on the JDBC Provider configuration pointing to /home/instantclient_12_1
4) Configure the OCI URL to the non-RAC format like below.
5) Also ensure the j2c auth alias configured for the datasource has all the required privileges to connect to the database.
6)Create an environment entry for LIBPATH to point to the Instant Client directory on all the JVMs [ dmgr, nodeagent, server ]
Application servers > server1 > Process definition > Environment Entries
Node agents > nodeagent > Process definition > Environment Entries
Deployment Manager > Process definition > Environment Entries [ Optional ]
Name : LIBPATH
Value : /home/instantclient_12_1
7) Stop the dmgr, nodeagent & server.
8) On the terminal,before starting the dmgr, nodeagent and server, export the LIBPATH variable pointing to the Instant Client directory. [ optional ] [ you may add to the profile so that the LIBPATH is applied automatically for all the terminal sessions created ]
# export LIBPATH=/home/instantclient_12_1
9) start all the JVMs
# ./startServer.sh server1
10) Login to the admin console & invoke TestConnection for the newly created datasource.
An elaborate documentation around OCI is available in the WebSphere Knowledge center :