One Oracle JDBC JAR (ojdbc17.jar or ojdbc11.jar or ojdbc8.jar) for All Use Cases
To simplify diagnosability, security, and user experience, and increase productivity for Java applications using the Oracle Cloud Database Services, customers can use the production Oracle JDBC Thin driver (ojdbcX.jar) to enable diagnosability. Starting from Oracle Database 23ai, one JDBC jar file (ojdbc17.jar, ojdbc11.jar, or ojdbc8.jar) provided for each JDK build supports production, debug, and DMS use cases. This simplifies the debugging of Java applications by removing the need to switch from the production jars (ojdbcX.jar) to the debug jars (ojdbcX_g.jar or ojdbcXdms.jar or ojdbcXdms_g.jar with dms.jar) for debugging purposes.
The improved self-driven diagnosability encompasses the following features:
- Observability: It enables users who are external to JDBC to access summary information about JDBC’s execution and performance.
- Diagnosability: It records the critical execution state in the event of a failure. Ideally, such a state is sufficient to diagnose the first occurrence of a failure and come up with a resolution for the problem causing the failure. You must balance the amount of state information recorded against the cost of recording such a state.
- Execution Trace: It records the execution sequence details. As execution trace has a significant cost involved, you must enable it only in limited contexts.
Diagnose on First Failure Feature:
Diagnosing on First Failure is a new feature that enables capturing the logs of the failure on its first occurrence. This feature captures only the most critical information that provides a reasonable chance of diagnosing the most likely problems. This feature is enabled by default. You can disable it either by setting the property to false.
oracle.jdbc.diagnostic.enableDiagnoseFirstFailure=false or using property CONNECTION_PROPERTY_ENABLE_DIAGNOSE_FIRST_FAILURE = FALSE or using related operations through the DiagnosticsMXBean interface.
For more details, refer to 40.2 The Diagnose First Failure Feature chapter of the JDBC Developer’s Guide.
Diagnose with Public and Sensitive Modes:
The diagnosability feature has two modes: public and sensitive. In the public mode, debugging features do not record or persist sensitive information. In the sensitive mode, debugging features record and persist sensitive information. You must have privileged user access to enable sensitive mode, which is controlled through these two properties.
-Doracle.jdbc.diagnostic.permitSensitiveDiagnostics=true --> To permit sensitive diagnostics
-Doracle.jdbc.diagnostic.enableSensitiveDiagnostics=true --> To enable sensitive diagnostics
Sensitive diagnostics can also be enabled or disabled by invoking related operations defined in DiagnosticsMXBean.
Loggers and Logging Level Details
Logging can be controlled at the package level as well as at the logging level. At the package level, you can specify which packages should be included, and by choosing the right logging level, you can control the volume of the logs that get generated. Refer below for more details.
1. Different Loggers Available to Use
The amount of logs can be restricted by choosing the particular packages that you want to trace. Below is the list of loggers along with their descriptions. Also, note that the root logger is provided as an empty string that includes all packages, which will generate a lot of logs if chosen. example., .level = SEVERE.
JDBC Related Packages:
oracle.jdbc: Almost all Oracle JDBC messages
oracle.jdbc.driver: The core driver code
oracle.jdbc.pool: DataSources and Connection Pooling
oracle.jdbc.aq: Advanced Queuing
oracle.jdbc.rowset: RowSets
oracle.jdbc.xa: Distributed Transactions
oracle.sql: Complex SQL datatypes
UCP Related Packages:
oracle.ucp: Includes both required and optional callback interfaces used to implement connection pool features.
oracle.ucp.admin: Includes interfaces for using a connection pool manager and MBeans using JMX operations
oracle.ucp.jdbc: Includes interfaces and classes that work with JDBC connections.
2. Different Logging Levels
Different logging levels control the amount of logs generated and hence must be chosen based on the requirement. Any levels higher than FINER will generate a large volume of logs. Example., To reduce the amount of log details, you can choose CONFIG that will log the SQL that is executed and any errors or warnings. You can use OFF logging level to turn off the logging.
| Logging Level | Description | Expected Volume |
| OFF | Disables Logging for a specific logger | NA |
| SEVERE | Program error, e.g., an unexpected value encountered in a switch statement. Error conditions that usually lead to catastrophic or unrecoverable results. | Low (Default option) |
| WARNING | Error conditions that are usually recoverable | Low |
| INFO | Highlight the progress of the application | Low |
| CONFIG | Fine-grained events that are useful for debugging (SQL statements) | Low |
| FINE | User code calls to the public API | Medium |
| FINER | Calls to internal methods and internal calls to public API | Medium |
| FINEST | Detail debug information with internal calls and debug details | Highest |
Use case 1: Enable JDBC and UCP logging with FileHandler
The FileHandler is used when you want the logs to be captured in a file. The first step is to create a logging.config file and choose the correct handler, log name, level, size, etc. Refer to FileHander.html for a list of properties. The next step is to use the two system properties as shown below to enable logging. If you don’t provider a formatter then the default formatter of XMLSimpleFormatter is used.
-Doracle.jdbc.diagnostic.enableLogging=true (23ai onwards)
-Djava.util.logging.config.file=./logging.config
Note: The property name (-Doracle.jdbc.Trace=true) was different to enable logging in the older JDBC releases before 23ai. You cannot use this property for enabling logging in 23ai.
# Contents of the logging.config file
handlers = java.util.logging.FileHandler
# Formats into a standard XML format - Default formatter
#java.util.logging.FileHandler.formatter=oracle.jdbc.diagnostics.XMLSimpleFormatter
# Standard format
java.util.logging.FileHandler.formatter= oracle.jdbc.diagnostics.OracleSimpleFormatter
# Custom Formatter for UCP
#java.util.logging.FileHandler.formatter=oracle.ucp.util.logging.UCPFormatter
# For using a directory, [DIR]\\<filename> in case of windows
java.util.logging.FileHandler.pattern = client.log
# File size is 100MB
java.util.logging.FileHandler.limit = 100000000
java.util.logging.FileHandler.count = 1000
java.util.logging.FileHandler.level = FINEST
# Choose different loggers based on the use case
oracle.ucp.level = FINEST
oracle.jdbc.level = FINEST
#sample for class level
oracle.ucp.jdbc.oracle.OracleDatabaseInstanceInfo.level = ALL
oracle.ucp.jdbc.oracle.OracleDatabaseInstanceInfoList.level = ALL
Use case 2: Enable JDBC and UCP logging with ConsoleHandler
The basic configuration, that is the ConsoleHandler, outputs the logs to the console. The first step is to create a logging.config file and choose the right handler, log name, level, size, etc., Refer to ConsoleHander.html for a the description of the properties that can be used. Next, use the two system properties below to enable logging.
-Doracle.jdbc.diagnostic.enableLogging=true (23ai onwards)
-Djava.util.logging.config.file=./logging.config
Note: The property name (-Doracle.jdbc.Trace=true) was different to enable logging in the older JDBC releases before 23ai. You cannot use this property for enabling logging in 23ai.
# Contents of the logging.config file
handlers = java.util.logging.ConsoleHandler
# Formats into a standard XML format - Default formatter
#java.util.logging.ConsoleHandler.formatter= oracle.jdbc.diagnostics.XMLSimpleFormatter
# Standard format
java.util.logging.ConsoleHandler.formatter = oracle.jdbc.diagnostics.OracleSimpleFormatter
# Custom Formatter for UCP
#java.util.logging.ConsoleHandler.formatter=oracle.ucp.util.logging.UCPFormatter
# For using directory, [DIR]\\<Filename>
java.util.logging.ConsoleHandler.pattern = client.log
java.util.logging.ConsoleHandler.level = FINEST
# Choose different loggers based on the usecase
oracle.ucp.level = FINEST
oracle.jdbc.level = FINEST
Use case 3: Enable JDBC and UCP Packet/Sqlnet Tracing
In some scenarios, the issues are not uncovered through JDBC logging messages. It will be helpful to look at the network packets the driver exchanges with the server. To generate network-level trace information, use the root logger (.level = ALL) in the logging.config file and also set the below two properties to enable network tracing. These two steps are in addition to the steps highlighted in Usecase 1 and Use case 2. Note that network tracing will log the byte content of every packet that crosses the network, and the log volume will be high.
# Enable network-level logging
.level = ALL // Use the Root logger
-Doracle.jdbc.diagnostic.permitSensitiveDiagnostics=true
-Doracle.jdbc.diagnostic.enableSensitiveDiagnostics=true
Use case 4: Enable Logging programmatically
JDBC and UCP logging can be configured programmatically as shown below. You will need to set the loggers and the levels through APIs.
import java.util.*;
import java.util.logging.*;
try{
// set the file handler
handler= new FileHandler("client.log");
} catch(Exception e)
{ System.out.println(e);}
// designate the module to be traced
Logger logger = Logger.getLogger("oracle.jdbc");
// set the tracing level
logger.setLevel(Level.ALL);
SimpleFormatter format=new SimpleFormatter();
handler.setFormatter(format);
// Add file handler to the desired logger
logger.addHandler(handler);
Use case 5: Enable JDBC and UCP Logging using MBeans
Logging can be enabled dynamically through the com.oracle.jdbc.diagnosability JDBC MBean. This MBean defines multiple operations such as enableLogging, disableLogging, or enableLoggingByConnectionIdPrefix, and so on. All these operations can also be called programmatically by invoking the relevant operation.
void enableLogging(boolean enable) {
try {
Object loader = oracle.jdbc.driver.OracleDriver.class.getClassLoader();
String loaderName = (loader == null ? "nullLoader" : loader.getClass().getName());
String name = loaderName + "@" + Integer.toHexString((loader == null ? 0 : loader.hashCode()));
// If the same class loader loads the JDBC drivers multiple times, then each
// subsequent MBean increments the value of the loader.hashCode() method, so as to
// create a unique name. It may be problematic to identify which MBean is
// associated with which JDBC driver instance.
javax.management.ObjectName diagnosticMBeanObjectName = new javax.management.ObjectName("com.oracle.jdbc:type=diagnosability,name=" + name);
// get the MBean server
javax.management.MBeanServer mbs = java.lang.management.ManagementFactory.getPlatformMBeanServer();
// find out if logging is enabled or not
System.out.println("LoggingEnabled = " + mbs.getAttribute(diagnosticMBeanObjectName, "LoggingEnabled"));
// enable logging
if(enable)
mbs.invoke(diagnosticMBeanObjectName, "enableLogging", null, null);
else
mbs.invoke(diagnosticMBeanObjectName, "disableLogging", null, null);
} catch (Exception e) {
e.printStackTrace();
}
}
Final Thoughts
Open Telemetry is an observability framework and toolkit designed to facilitate the generation, export, and collection of telemetry data such as traces, metrics, and logs. It is open source and can be used with any observability backends such as Jaegar and Prometheus.
The Oracle JDBC Open Telemetry Provider offers an integration between the Oracle JDBC Driver and Open Telemetry. The provider implements the TraceEventListener interface that gets notified when the database events are triggered in the driver. These events are then published to Open Telemetry. Currently the events supported are as shown below. More events will follow.
- roundtrips to the database server
- AC begin and sucess
- VIP down event
References:
JDBC and UCP Landing Page
JDBC Developer’s Guide – Diagnosability in JDBC
Episode 8: Explore Oracle JDBC Logging and Packet Tracing
Episode 9: How to enable Oracle UCP Logging?