Tuesday, June 19, 2012

JDBC OCI Data Source Connection For OAS 10.1.3.x/Oracle SOA 10.1.3.x

I came across an article to use Oracle OCI driver to increase Oracle SOA performance and its mentioned it will boost the performance by 35%.

Here I'm depicting The documents which I followed to create OCI connection, The errors I encountered and how resolved it. Hope this will help the people facing similar problem.

Created OCI connection pool as per oracle support note [How to Create a JDBC OCI Data Source Connection For OAS 10.1.3.x [ID 728235.1]]

Used OAS server version 10.1.3.5 running on HP Unix.

After configuring as per [ID 728235.1], Started testing by following below links in OAS.

OC4J->Administration->Services->JDBC Resources-> Select Connection Pool[OCI] -> Test Connection -> Test

But during the test it thrown below error and there is no clue where went wrong.

Error invoking method: testConnection on MBean: oc4j:j2eeType=JDBCResource,name="OCIConnectionPool",J2EEApplication=default,J2EEServer=standalone
oracle.oc4j.admin.jmx.shared.exceptions.JMXRuntimeException: Error invoking method: testConnection on MBean: oc4j:j2eeType=JDBCResource,name="OCIConnectionPool",J2EEApplication=default,J2EEServer=standalone at
oracle.oc4j.admin.jmx.distributed.SimpleInstanceMBeanServerDelegate.invoke(SimpleInstanceMBeanServerDelegate.java:970)

Aternate way to test the connection

OC4J->Administration->JMX->System MBean Browser->J2EE Aplication->default->JDBC Driver->OCI Connection Factory->Operations->testConnection[1 parameter]

In the value field give 'select * from dual' and click on 'Invoke Operation' buttion.

It gives the actual error as below.

Error:
Failed to execute operation. no ocijdbc10 in java.library.path
Caused by: java.lang.UnsatisfiedLinkError: no ocijdbc10 in java.library.path
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1707)
at java.lang.Runtime.loadLibrary0(Runtime.java:822)

Error due to missing library file

Solution: Create a directory '$ORACLE_HOME/jdbc/lib/oci' and copy below set of jar files from Oracle database server[In my case oracle DB is of 10.2.0.4 version] or Point to Database client library files.

libclntsh.so.10.1
libnnz10.so
libocijdbc10.a
libocijdbc10.so

Add below environment value in opmn.xml file.
<environment>
<variable id="TNS_ADMIN" value="$ORACLE_HOME/network/admin"/>
<variable id="SHLIB_PATH" value="$ORACLE_HOME/jdbc/lib/oci"/>
</environment>

Note: Environment variable name for loading library differs on OS type for HP Unix it's SHLIB_PATH and for LINUX it's 'LD_LIBRARY_PATH'

After making this change restarted and tested again using JMX. Failed with below error.

Error:
Failed to execute operation. t2cGetCharSet
Caused by: java.lang.UnsatisfiedLinkError: t2cGetCharSet
at oracle.jdbc.driver.T2CConnection.t2cGetCharSet(Native Method)
at oracle.jdbc.driver.T2CConnection.getCharSetIds(T2CConnection.java:2957)

Solution: Copied ojdbc14dms.jar from Oracle DB server and replaced existing $ORACLE_HOME/jdbc/lib/ojdbc14dms.jar

After making this change restarted and tested again using JMX. Operation executed successfully, but received below error return value.

Error:
Exception occurred testing connection. Exception: java.sql.SQLException: ORA-12705: invalid or unknown NLS parameter value specified


Solution: Add environment variable 'NLS_LANG' as below and retart the server.
<environment>
<variable id="TNS_ADMIN" value="/home3/soaaia/product/10.1.3.1/OracleAS_1/network/admin"/>
<variable id="SHLIB_PATH" value="/home3/soaaia/product/10.1.3.1/OracleAS_1/jdbc/lib/oci"/>
<variable id="NLS_LANG" value=""/>
</environment>

During testing using JMX, got another new error.

Error:
Failed to execute operation. oracle.i18n.text.OraBoot.getCharSetIsFixed()Ljava/util/List;

Caused by: java.lang.NoSuchMethodError: oracle.i18n.text.OraBoot.getCharSetIsFixed()Ljava/util/List;
at oracle.sql.converter.Orai18nCharacterSetMetaData.isFixedWidth(Orai18nCharacterSetMetaData.java:47)
at oracle.sql.converter.CharacterSetMetaData.getRatio(CharacterSetMetaData.java:1244)

Solution: Copy orai18n.jar from Oracle DB server and replaced existing $ORACLE_HOME/jdbc/lib/orai18n.jar, restart the server to take effect.