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
Note: only a member of this blog may post a comment.