/ Enabling Oracle JDBC Driver Debug ~ Java EE Support Patterns

2.28.2011

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.



10 comments:

Modifying WEBLOGIC_CLASSPATH in /wlserver_10.3/common/bin/commEnv.sh in WebLogic 11g (10.3.4) has no effect because it is override in /bin/setDomainEnv.sh by PRE_CLASSPATH="${COMMON_COMPONENTS_HOME}/modules/oracle.jdbc_11.1.1/ojdbc6dms.jar

Thanks Ziho for pointing that out. You are actually correct, any PRE_CLASSPATH present in setDomainEnv.sh can override any entry in the WEBLOGIC_CLASSPATH; no affect in this case. I will update the post.

ojdbc6_g.jar can be added by either editing the user domain setDomainEnv.sh PRE_CLASSPATH or add the library in commEnv.sh by adding
@rem Add ojdbc6_g.jar first in PRE_CLASSPATH
set PRE_CLASSPATH=%WL_HOME%\server\ext\jdbc\oracle\11g\ojdbc6_g.jar

Hello

I have problem with jdbc debug. I configured CLASSPATH and properties file. I set severitu on oracle.jdbc.level=CUSTOM to trace SQL statment - I see in logs information about my sql but I also get exception java.lang.Throwable: No Error - see below

Do You have any idea where could be the problem ?

Jun 29, 2012 4:56:00 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
CONFIG: 7B8710ED SQL: SELECT * FROM quote.CONTACTS
java.lang.Throwable: No Error
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1640)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:2006)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:1709)
at weblogic.jdbc.wrapper.Statement.executeQuery(Statement.java:506)
at jsp_servlet.__testdatasource._jspService(__testdatasource.java:128)
at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)
at weblogic.servlet.internal.ServletStubImpl.onAddToMapException(ServletStubImpl.java:416)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:327)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:184)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3732)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3696)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2273)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2179)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1490)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)

I have the same problem as the previous poster. I am able to get the SQL statements to print at the CONFIG level, but it prints a full and annoying stack trace with each command. Is there any way to avoid this?

Hi,

The StackTrace purpose is to show you the code & request oginator of the SQL itself. This is a normal behavior. You will see similar stack traces when you turn ON debug from middleware like Weblogic like SSL etc.

Let me investigate if Stack Trace logging can be removed and I will get back to you on this.

Thanks.
P-H

How about this annoying stack trace ?

I decided to move ojdbc6.jar from wlserver_10.3/server/lib/ojdbc6.jar to wlserver_10.3/server/ext/jdbc/oracle/11g. I use a symbolic link wlserver_10.3/server/lib/ojdbc6.jar pointing to either wlserver_10.3/server/ext/jdbc/oracle/11g/ojdbc6.jar or wlserver_10.3/server/ext/jdbc/oracle/11g/ojdbc6_g.jar

Has anyone figured out how to avoid the stack trace yet?

I don't understand why people wish to omit the stack trace from the trace log. As Pierre-Hugues said, this is pretty normal as you do want to know what code has originated the call in the first place.

You want to turn it off because:

(1) creating exception is slow because it has to walk the stack trace
(2) clogs up the logs

logback & log4j can create a stack trace optionally, no choice with this

Post a Comment