Enabling Oracle JDBC Driver Debug

Problems with the Oracle JDBC Driver can be hard to pinpoint. Sometimes, a particular SQLException might be a symptom of another problem such as intermittent network latency, packet loss etc. The good news is that Oracle has a “debug” version of the JDBC Driver available which provides a lot of debugging information.

This article will provide you a step by step on how setup and activate the “debug” version of the Oracle JDBC Driver within Weblogic 11g.

Environment specifications

·         Java EE server: Weblogic 11g

·         JDK: HotSpot VM 1.6

·         Oracle JDBC Driver: Oracle JDBC Driver version - 11.1.0.6.0-Production

·          Oracle JDBC Driver Library: ojdbc6_g.jar

Library and configuration file location

The debug library is located within your Weblogic 11g home directory as per below:
<WL11g HOME>/wlserver_10.3/server/ext/jdbc/oracle/11g/ojdbc6_g.jar

You will also need to edit and configure the “OracleLog.properties” file as per your desired logging level along with the output log file name and location. 

The default configuration file is located under:

<WL11g HOME>/wlserver_10.3/server/ext/jdbc/oracle/11g/OracleLog.properties

Installation within Weblogic 11g system classpath

In order to use the debug library, you first have to add it within the Weblogic 11g system classpath. You can add it to the commEnv.sh / commEnv.cmd at the beginning of the WEBLOGIC_CLASSPATH or to the Weblogic PRE_CLASSPATH. 

The commEnv script file is located under:

<WL11g HOME>/wlserver_10.3/common/bin/

 ** Please note that any PRE_CLASSPATH present in setDomainEnv.sh can override any entry in the WEBLOGIC_CLASSPATH; no affect in this case. In that situation, simply add ojdbc6_g.jar to your WL11g user domain setDomainEnv.sh PRE_CLASSPATH or add the library in commEnv.sh by adding the following line below:

# Add ojdbc6_g.jar first in PRE_CLASSPATH
PRE_CLASSPATH=%WL_HOME%\server\ext\jdbc\oracle\11g\ojdbc6_g.jar

Debug library activation

The next step is to add 2 Java System Property parameters to your JVM start-up arguments:

-Doracle.jdbc.Trace=true

-Djava.util.logging.config.file=/tempDir/OracleLog.properties

The first –D parameter will activate the JDBC debug trace.

The second –D parameter is used to specify the location of your choice of the debug library property file (please see detail below).

Once all the above steps are completed, simply shutdown and restart your Weblogic server and monitor your JDBC debug output log for debugging traces.

Find below a snapshot of the OracleLog.properties as a reference.

Finally, if you are not using Weblogic, you can still download the OracleLog.properties from the link below which is part of the Oracle JDBC driver download package within the demo directory.

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Posted in: JDBC,Oracle Weblogic