Tuesday, October 2, 2012

Dynamically enabling (or disabling) the JDBC driver level logging using debug version of Oracle JDBC thin driver (ie ojdbc6_g.jar) on app server

This post is about dynamically enabling (or disabling) the  JDBC driver level logging with debug version of Oracle JDBC thin driver "ojdbc6_g.jar" in place while app server is up and running.

Typically, when this jar is put in place in application server environment, we enable the logging using JAVA system property "-Doracle.jdbc.Trace=true". This approach is static in the sense that application server restart is needed each time we want to enable or disable the logging. This static approach is not at all feasible in a production system where we want to trace the jdbc calls at some particular instance of time.

A programmatic (dynamic) approach to enable/disable driver level logging is also discussed in doc "http://docs.oracle.com/cd/B28359_01/java.111/b31224/diagnose.htm". However this approach is suitable for enabling/disabling the logging from within the application. So this method would essentially only trace JDBC calls at places where application code interacts with DB which might not be sufficient to troubleshoot jdbc issue in application server environment where jdbc connections are actually maintained and managed by application server.

Extending this dynamic enabling/disabling logging idea to application server is a bit tricky. From application server perspective, we can toggle driver level logging at runtime using similar programmatic approach. This would affect the logging at global level (be it app server interactions with DB or any application that is deployed on the app server, all DB interactions would be logged). This can be accomplished on " sun.misc.Launcher$AppClassLoader" instance that lies at "com.oracle.jdbc"/"diagnosability" in MBean tree.

Steps
======
A) Grab the instance name of MBean "com.oracle.jdbc"/"diagnosability" using JConsole, or any other JMX browser utility.

How:
1. Start JAVA_HOME/bin/jconsole or jvisualvm
2. Connect to app server process where logging needs to be enabled/disabled
3. Goto MBeans tab
4. Expand "com.oracle.jdbc"/"diagnosability"
5. Select the available MBean
6. Get the name portion from field "ObjectName" (this would be the loader string, as referenced in sample code here  .

For example, if we have:
  ObjectName="com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@601bb1"

Then loader would be "sun.misc.Launcher$AppClassLoader@601bb1"


B) Target the sample application "JDBCDriverLogger.war" (download from here ) to the application server instance on which you want to enable/disable the driver logging.

C) Create a logging configuration, say "myConfig.properties"

######snippet from myConfig.properties - starts##########
.level=FINER
oracle.jdbc.level=FINER
oracle.jdbc.handlers=java.util.logging.FileHandler
java.util.logging.FileHandler.level=FINER
java.util.logging.FileHandler.pattern=d:\\jdbc.log
java.util.logging.FileHandler.count=1
java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter
######snippet from myConfig.properties - ends##########


D) set the JAVA System property to point to logging configuration file:
         set JAVA_OPTIONS=-Djava.util.logging.config.file=myConfig.properties


E) Restart the application server if necessary. Note this restart might be needed to pickup  file "myConfig.properties" and is one time activity. If this file is already available to the application server, then no need to restart.


F) hit the application on application server instance:
    http://soa_server1ip:port/JDBCDriverLogger/loggerControl.jsp


The IP and Port in above URL will correspond to the app server instance where logging needs to be enabled/disabled, and where the applicaton "JDBCDriverLogger.war" was targetted previously

G) Enter loader name (obtained from step A6) in the text field next to "Enter ClassLoader Object instance name:" on the JSP page, and hit "EnableLogging"

This would enable driver level logging on app server instance

H) to disable the logging, enter the same loader name in the text field next to "Enter ClassLoader Object instance name:" on the JSP page, and hit "DisableLogging"



Ref:
http://docs.oracle.com/cd/B28359_01/java.111/b31224/diagnose.htm

No comments:

Post a Comment