Diagnosability Management with the 11g JDBC Driver

The JDBC diagnosability management feature introduces an MBean, oracle.jdbc.driver.OracleDiagnosabilityMBean. This MBean provides means to enable and disable JDBC logging. You problematically enable it as shown bythe method "enableOracleLogging()"from the code below.

1. First ensure you are using the debug version of the Orace JDBC driver. Given I was using JDK 1.6 I used ojdbc6_g.jar

2. Ensure you register the JDBC driver prior to enabling logging

DriverManager.registerDriver(new OracleDriver());

3. Full Code demo as follows:

package pas.au.jdbc.logging;

import java.lang.management.ManagementFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.util.logging.FileHandler;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;

import javax.management.Attribute;
import javax.management.MBeanServer;
import javax.management.ObjectName;

import oracle.jdbc.OracleDriver;


public class Test
{
  public Test()
  {
  }

  public void run () throws SQLException
  {
    DriverManager.registerDriver(new OracleDriver());
    enableOracleLogging();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    
    try
    {
      conn = getConnection();
      System.out.println("Connection retrieved..");

      stmt = conn.createStatement();
      rset = stmt.executeQuery("select empno from emp");
      while (rset.next())
      {
        System.out.println(rset.getInt(1));
      }
    }
    catch (SQLException sqle)
    {
      sqle.printStackTrace();
    }
    finally
    {
      if (rset != null)
      {
        rset.close();
      }
      
      if (stmt != null)
      {
        stmt.close();
      }
      
      if (conn != null)
      {
        conn.close();
      }
    }
    
    
    System.out.println("Connection closed");
  }
  
  public static Connection getConnection() throws SQLException
  {
    String username = "scott";
    String password = "tiger";
    String thinConn = 
       "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g";
    
    Connection conn =
      DriverManager.getConnection(thinConn, username, password);
    conn.setAutoCommit(false);
    return conn;
  }

  private static void enableOracleLogging() 
  {
    try 
    {
      Handler fh = new FileHandler("./oracle_jdbc_log.log");
      fh.setLevel(Level.ALL);
      fh.setFormatter(new SimpleFormatter());
      Logger.getLogger("").addHandler(fh);
      Logger.getLogger("").setLevel(Level.ALL);
  
      MBeanServer mbs = ManagementFactory.getPlatformMBeanServer();
      String loader = Thread.currentThread().getContextClassLoader().toString().
          replaceAll("[,=:\"]+", "");
      ObjectName pattern = 
          new ObjectName("com.oracle.jdbc:type=diagnosability,name=" + 
                         loader );      
      ObjectName diag = ((ObjectName[])(mbs.queryNames(pattern, null).
          toArray(new ObjectName[0])))[0];

      mbs.setAttribute(diag, new Attribute("LoggingEnabled", true));
      System.out.println("LoggingEnabled = " + 
          mbs.getAttribute(diag, "LoggingEnabled"));
    }
    catch(Exception e) 
    {
      e.printStackTrace();  
    }
  }
   
  public static void main(String[] args) throws Exception
  {
    Test test = new Test();
    test.run();
  }
}

4. Sample Output as follows, most of it ommitted due to the size of the log file given we asked for everything to be logged.

Apr 2, 2010 10:09:30 AM DefaultMBeanServerInterceptor setAttribute
FINER: Object= com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@47858e, attribute=LoggingEnabled
Apr 2, 2010 10:09:30 AM Repository retrieve
FINER: name=com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@47858e
Apr 2, 2010 10:09:30 AM DefaultMBeanServerInterceptor getAttribute
FINER: Attribute= LoggingEnabled, obj= com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@47858e
Apr 2, 2010 10:09:30 AM Repository retrieve
FINER: name=com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@47858e
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.OracleDriver connect
TRACE_1: Public Enter: "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g", {user=scott, password=tiger}
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.OracleDriver oracleDriverExtensionTypeFromURL
TRACE_16: Enter: "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g"
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.OracleDriver oracleDriverExtensionTypeFromURL
TRACE_16: return: 0
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.OracleDriver oracleDriverExtensionTypeFromURL
TRACE_16: Exit
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection
TRACE_16: Enter: "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g", {user=scott, password=tiger}, oracle.jdbc.driver.T4CDriverExtension@cb6009
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection readConnectionProperties
TRACE_16: Enter: "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g", {user=scott, password=tiger}
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection getSystemProperty
TRACE_16: Enter: "oracle.jdbc.RetainV9LongBindBehavior", null
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection$1 run
TRACE_16: return: null
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection getSystemProperty
TRACE_16: return: null
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection getSystemProperty
TRACE_16: Exit
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection getSystemProperty
TRACE_16: Enter: "oracle.jdbc.database", null
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection$1 run