Translate

Monday, 10 July 2017

Shahed

ODI 12c - OCI Connection to Studio and not JDBC ( Thick Client and not Thin )

Install Oracle 12c Client –  To Avoid the double connection needed with SCAN Addresses for JDBC – Try an OCI Connection which is Native.

We have been noticing intermittent connection issues when connecting from studio to a scan clustered oracle database, where you would have to click the connect button twice to connect in studio. So we just installed a 12c oracle home using native OCI and this seems to work flawlessly .

Download a Oracle Client for 12c from Oracle website either 32bit or 64 bit depending on your windows installation.



This installation is based on the installation location of : C:\Oracle\ORACLECLIENT12C – adjust the path in the below edits as and when needed as it depends on your installation location.


Open Regedit and add the following string entries within the ORACLE folder in below locations.













Registry Entries in Locations Might exist : If they do then leave as is ..... 

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
inst_loc      C:\Program Files\Oracle\Inventory

I copied this path and put it into a new string value in this registry entry:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE]
inst_loc      C:\Program Files\Oracle\Inventory


The 2 registry entries need to be there for the installation to even start.

Installation Option –I used Runtime toolset and oracle home location bottom option was set to C:\Oracle\ORACLECLIENT12C , I left base location as my d drive or whatever you want.


Implementation-Version: 12.1.0.2.0

Once this is done a few more things needed. Add the following line in


File Location : <<ODI_HOME>>\odi\studio\bin\odi.conf\odi.conf

Add the following Entry using Notepad ++ or equivalent tool

AddVMOption -Djava.library.path=C:\Oracle\ORACLECLIENT12C\BIN

The second thing is to set the system variables for – My Computer – Advanced Settings
Again test to see if your ODI studio is connecting with oci and if it is ... then you can stop here.













ORACLE_HOME= C:\Oracle\ORACLECLIENT12C

Below 2 not really needed : but if errors then add them
LD_LIBRARY_PATH=%ORACLE_HOME%\lib value
PATH=%ORACLE_HOME%\bin;%PATH% value


ODI Studio Connection 







In ODI Use the connection as follows

jdbc:oracle:oci:@DEVREPO

DEVREPO - Is my SID / Service Name in a standard TNS file.

Optional
TNS File can be stored separately with connection details to various databases by adding an environment variable like the ORACLE_HOME


TNS_ADMIN

About Authors

Shahed Munir

Krishna Udathu

Shahed and Krishna are Oracle / Big Data Experts