andersswanson.dev

How to write a custom tracer for Oracle Database JDBC

In this article, we’ll implement a custom JDBC tracer for Oracle Database based on the OJDBC OpenTelemetry provider. Our custom implementation integrates with Spring Boot and adds span attributes like database client info and the system user.

If you’d like to use the Oracle-provided JDBC tracing implementation, check out my prior post: Oracle JDBC Tracing with Spring Boot OpenTelemetry.

Looking for the code? Click Here.

What’s OpenTelemetry and Tracing?

OpenTelemetry is an open standard for collecting telemetry data like logs, metrics, and traces from distributed applications.

Tracing provides observability into how requests flow through complex systems. Enabling JDBC tracing shows spans for query information, connection behavior, and client identity.

By instrumenting JDBC with OpenTelemetry, you can view spans for database operations, correlate with upstream services, and diagnose complex performance issues.

A detailed trace of JDBC operations showing service duration, spans, and annotations for an HTTP POST request to the /flavors endpoint.

Project dependencies

To use Oracle JDBC tracing with Spring Boot, configure your project using these dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.spring</groupId>
    <artifactId>oracle-spring-boot-starter-ucp</artifactId>
    <version>${oracle.starters.version}</version>
</dependency>

<!-- OJDBC Tracing provider -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc-provider-opentelemetry</artifactId>
</dependency>
<!-- Observability / Tracing is implemented in Actuator. -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-opentelemetry</artifactId>
</dependency>

Custom JDBC Trace Event Listener

To write our JDBC tracer, we implement the Oracle JDBC TraceEventListener interface. The JDBC driver calls the TraceEventListener interface initiate and record spans.

In each span, we record information about the connection, SQL query, database client info, system username, and more!

package com.example.tracing.jdbc.custom;

import io.opentelemetry.api.GlobalOpenTelemetry;
import io.opentelemetry.api.trace.*;
import io.opentelemetry.context.Scope;
import oracle.jdbc.TraceEventListener;

import java.sql.SQLException;
import java.time.Instant;
import java.util.EnumMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 * Based on: <a href="https://github.com/oracle/ojdbc-extensions/blob/main/ojdbc-provider-opentelemetry/src/main/java/oracle/jdbc/provider/opentelemetry/OpenTelemetryTraceEventListener.java">OpenTelemetryTraceEventListener.java</a>
 */
public class JDBCTraceEventListener implements TraceEventListener {
    private static final String TRACE_KEY = "clientcontext.ora$opentelem$tracectx";
    private static final Logger logger = Logger.getLogger(JDBCTraceEventListener.class.getName());
    // Number of parameters expected for each execution event
    private static final Map<JdbcExecutionEvent, Integer> EXECUTION_EVENTS_PARAMETERS = new EnumMap<>(
            JdbcExecutionEvent.class) {
        {
            put(JdbcExecutionEvent.AC_REPLAY_STARTED, 3);
            put(JdbcExecutionEvent.AC_REPLAY_SUCCESSFUL, 3);
            put(JdbcExecutionEvent.VIP_RETRY, 8);
        }
    };

    private Tracer tracer;
    private TracingProperties tracingProperties;

    public JDBCTraceEventListener() {
        this.tracer = GlobalOpenTelemetry.get()
                .getTracer(JDBCTraceEventListenerProvider.class.getName());
        this.tracingProperties = TracingProperties.defaultProperties();
    }

    @Override
    public Object roundTrip(Sequence sequence, TraceContext traceContext, Object userContext) {
        if (!tracingProperties.isEnabled())
            return null;
        if (sequence == Sequence.BEFORE) {
            // Create the Span before the round-trip.
            final Span span = initAndGetSpan(traceContext, traceContext.databaseOperation());
            try (Scope ignored = span.makeCurrent()) {
                traceContext.setClientInfo(TRACE_KEY, getTraceValue(span));
            } catch (Exception ex) {
                // Ignore exception caused by connection state.
            }
            // Return the Span instance to the driver. The driver holds this instance and
            // supplies it
            // as user context parameter on the next round-trip call.
            return span;
        } else {
            // End the Span after the round-trip.
            if (userContext instanceof Span span) {
                span.setStatus(traceContext.isCompletedExceptionally() ? StatusCode.ERROR : StatusCode.OK);
                endSpan(span);
            }
            return null;
        }
    }

    @Override
    public Object onExecutionEventReceived(JdbcExecutionEvent event, Object userContext, Object... params) {
        // Noop if not enabled or parameter count is not correct
        if (!tracingProperties.isEnabled())
            return null;
        if (EXECUTION_EVENTS_PARAMETERS.get(event) == params.length) {
            if (event == TraceEventListener.JdbcExecutionEvent.VIP_RETRY) {
                SpanBuilder spanBuilder = tracer
                        .spanBuilder(event.getDescription())
                        .setAttribute("Error message", params[0].toString())
                        .setAttribute("VIP Address", params[7].toString());
                // Add sensitive information (URL and SQL) if it is enabled
                if (tracingProperties.isShowSensitiveData()) {
                    logger.log(Level.FINEST, "Sensitive information on");
                    spanBuilder.setAttribute("Protocol", params[1].toString())
                            .setAttribute("Host", params[2].toString())
                            .setAttribute("Port", params[3].toString())
                            .setAttribute("Service name", params[4].toString())
                            .setAttribute("SID", params[5].toString())
                            .setAttribute("Connection data", params[6].toString());
                }

                if (tracingProperties.isIncludeClientInfo()) {
                    System.out.println("foo");
                }
                return spanBuilder.startSpan();
            } else if (event == TraceEventListener.JdbcExecutionEvent.AC_REPLAY_STARTED
                    || event == TraceEventListener.JdbcExecutionEvent.AC_REPLAY_SUCCESSFUL) {
                SpanBuilder spanBuilder = tracer
                        .spanBuilder(event.getDescription())
                        .setAttribute("Error Message", params[0].toString())
                        .setAttribute("Error code", ((SQLException) params[1]).getErrorCode())
                        .setAttribute("SQL state", ((SQLException) params[1]).getSQLState())
                        .setAttribute("Current replay retry count", params[2].toString());
                return spanBuilder.startSpan();
            } else {
                logger.log(Level.WARNING, "Unknown event received : " + event.toString());
            }
        } else {
            // log wrong number of parameters returned for execution event
            logger.log(Level.WARNING, "Wrong number of parameters received for event " + event.toString());
        }
        return null;
    }

    @Override
    public boolean isDesiredEvent(JdbcExecutionEvent event) {
        // Accept all events
        return true;
    }

    private Span initAndGetSpan(TraceContext traceContext, String spanName) {
        /*
         * If this is in the context of current span, the following becomes a nested or
         * child span to the current span. I.e. the current span in context becomes
         * parent to this child span.
         */
        SpanBuilder spanBuilder = tracer.spanBuilder(spanName)
                .setAttribute("thread.id", Thread.currentThread().threadId())
                .setAttribute("thread.name", Thread.currentThread().getName())
                .setAttribute("Connection ID", traceContext.getConnectionId())
                .setAttribute("Database Operation", traceContext.databaseOperation())
                .setAttribute("Database User", traceContext.user())
                .setAttribute("Database Tenant", traceContext.tenant())
                .setAttribute("SQL ID", traceContext.getSqlId());

        if (tracingProperties.isIncludeClientInfo()) {
            for (String key : tracingProperties.getClientInfoKeys()) {
                try {
                    String value = traceContext.getClientInfo(key);
                    spanBuilder.setAttribute(key, value);
                } catch (SQLException e) {
                    logger.log(Level.WARNING, "Failed to set client info for key " + key);
                }
            }
        }

        if (tracingProperties.isIncludeSystemUsername()) {
            spanBuilder.setAttribute("System Username", System.getProperty("user.name"));
        }

        // Add sensitive information (URL and SQL) if it is enabled
        if (tracingProperties.isEnabled()) {
            logger.log(Level.FINEST, "Sensitive information on");
            spanBuilder.setAttribute("Original SQL Text", traceContext.originalSqlText())
                    .setAttribute("Actual SQL Text", traceContext.actualSqlText());
        }

        // Indicates that the span covers server-side handling of an RPC or other remote
        // request.
        return spanBuilder.setSpanKind(SpanKind.SERVER).startSpan();

    }

    private void endSpan(Span span) {
        span.end(Instant.now());
    }

    private String getTraceValue(Span span) {
        final String traceParent = initAndGetTraceParent(span);
        final String traceState = initAndGetTraceState(span);
        return traceParent + traceState;
    }

    private String initAndGetTraceParent(Span span) {
        final SpanContext spanContext = span.getSpanContext();
        // The current specification assumes the version is set to 00.
        final String version = "00";
        final String traceId = spanContext.getTraceId();
        // parent-id is known as the span-id
        final String parentId = spanContext.getSpanId();
        final String traceFlags = spanContext.getTraceFlags().toString();

        return String.format("traceparent: %s-%s-%s-%s\r\n",
                version, traceId, parentId, traceFlags);
    }

    private String initAndGetTraceState(Span span) {
        final TraceState traceState = span.getSpanContext().getTraceState();
        final StringBuilder stringBuilder = new StringBuilder();

        traceState.forEach((k, v) -> stringBuilder.append(k).append("=").append(v));
        return String.format("tracestate: %s\r\n", stringBuilder);
    }

    public void setTracer(Tracer tracer) {
        this.tracer = tracer;
    }

    public void setTracingProperties(TracingProperties tracingProperties) {
        this.tracingProperties = tracingProperties;
    }
}

If you want to build on these capabilities, implement the TraceEventListener interface or extend this class.

Trace Event Listener Provider

To provide our custom tracer to the JDBC driver, we implement a TraceEventListenerProvider. This class maintains an instance of the TraceEventListener, and provides it to the JDBC driver on request:

package com.example.tracing.jdbc.custom;

import io.opentelemetry.api.trace.Tracer;
import oracle.jdbc.TraceEventListener;
import oracle.jdbc.spi.TraceEventListenerProvider;

import java.util.Map;

public class JDBCTraceEventListenerProvider implements TraceEventListenerProvider {
    private static final String PROVIDER_NAME = "custom-jdbc-trace-event-listener-provider";
    private static final JDBCTraceEventListener TEL = new JDBCTraceEventListener();

    @Override
    public TraceEventListener getTraceEventListener(Map<Parameter, CharSequence> map) {
        return TEL;
    }

    @Override
    public String getName() {
        return PROVIDER_NAME;
    }

    public static void setTracer(Tracer tracer) {
        TEL.setTracer(tracer);
    }

    public static void setTracingProperties(TracingProperties tracingProperties) {
        TEL.setTracingProperties(tracingProperties);
    }
}

We must register our TraceEventListenerProvider implementation as a service discoverable by the JDBC driver, by linking the com.example.tracing.jdbc.custom.JDBCTraceEventListenerProvider class in the resources/META-INF/services/oracle.jdbc.spi.TraceEventListenerProvider file.

To point the JDBC driver at a specific TraceEventListenerProvider, include the provider’s name in the JDBC connection string:

// Modify the database connection with the trace event listener connection parameter:
jdbc:oracle:thin:@localhost:1522/freepdb1?oracle.jdbc.provider.traceEventListener=custom-jdbc-trace-event-listener-provider

Spring Boot configuration

Let’s wire up our JDBC tracing class with Spring Boot!

First, we define TracingProperties and pass them to the TraceEventListener. Spring Boot exposes these properties so we can configure the TraceEventListener to include span attributes such as client info, system username, and SQL query text. This enables V$SESSION identification fields inside span attributes:

package com.example.tracing.jdbc.custom;

import org.springframework.boot.context.properties.ConfigurationProperties;

import java.util.ArrayList;
import java.util.List;

@ConfigurationProperties(prefix = TracingProperties.PREFIX)
public class TracingProperties {
    public static final String PREFIX = "management.tracing.ojdbc";

    private boolean enabled = true;
    private boolean showSensitiveData = false;
    private boolean includeClientInfo = false;
    private boolean includeSystemUsername = false;

    private List<String> clientInfoKeys = new ArrayList<>();

    public static TracingProperties defaultProperties() {
        TracingProperties props = new TracingProperties();

        return props;
    }

    public boolean isEnabled() {
        return enabled;
    }

    public void setEnabled(boolean enabled) {
        this.enabled = enabled;
    }

    public boolean isShowSensitiveData() {
        return showSensitiveData;
    }

    public void setShowSensitiveData(boolean showSensitiveData) {
        this.showSensitiveData = showSensitiveData;
    }

    public boolean isIncludeClientInfo() {
        return includeClientInfo;
    }

    public void setIncludeClientInfo(boolean includeClientInfo) {
        this.includeClientInfo = includeClientInfo;
    }

    public boolean isIncludeSystemUsername() {
        return includeSystemUsername;
    }

    public void setIncludeSystemUsername(boolean includeSystemUsername) {
        this.includeSystemUsername = includeSystemUsername;
    }

    public List<String> getClientInfoKeys() {
        return clientInfoKeys;
    }

    public void setClientInfoKeys(List<String> clientInfoKeys) {
        this.clientInfoKeys = clientInfoKeys;
    }
}

Using the @ConfigurationProperties(prefix = ...) class annotation, we expose these properties with the management.tracing.ojdbc prefix.

Next, we create a TracingConfiguration class to inject these properties in the TraceEventListenerProvider:

package com.example.tracing.jdbc.custom;

import io.opentelemetry.api.GlobalOpenTelemetry;
import io.opentelemetry.api.OpenTelemetry;
import io.opentelemetry.api.trace.Tracer;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.config.BeanPostProcessor;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.Properties;

@Configuration
@EnableConfigurationProperties(TracingProperties.class)
public class TracingConfiguration implements InitializingBean {
    private final OpenTelemetry openTelemetry;
    private final TracingProperties tracingProperties;

    public TracingConfiguration(OpenTelemetry openTelemetry, TracingProperties tracingProperties) {
        this.openTelemetry = openTelemetry;
        this.tracingProperties = tracingProperties;
    }

    @Override
    public void afterPropertiesSet() {
        GlobalOpenTelemetry.set(openTelemetry);

        // Configure the tracer
        Tracer tracer = openTelemetry.getTracer(
                JDBCTraceEventListener.class.getName()
        );
        JDBCTraceEventListenerProvider.setTracer(tracer);
        JDBCTraceEventListenerProvider.setTracingProperties(tracingProperties);
    }
}

Lastly, we ensure the DataSource wrapper knows about our custom tracing properties. I’ll do this with a Spring BeanPostProcessor:

package com.example.tracing.jdbc.custom;

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.config.BeanPostProcessor;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.Properties;

@Component
public class DataSourceProcessor implements BeanPostProcessor {
    @Value("${spring.application.name}")
    private String appName;

    @Override
    public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
        InetAddress address;

        try {
            address = InetAddress.getLocalHost();
        } catch (UnknownHostException e) {
            throw new RuntimeException(e);
        }
        if(bean instanceof DataSource ds) {
            Properties props = new Properties();
            String clientId = "%s@%s".formatted(appName, address.getHostName());
            props.setProperty("OCSID.CLIENTID", clientId);
            return new ClientInfoDataSource(ds, props);

        }
        return BeanPostProcessor.super.postProcessAfterInitialization(bean, beanName);
    }
}

That’s it! The custom tracer is now wired up with Spring Boot. All that remains is to configure the Spring Boot application properties. Note how we use our custom ojdbc properties object and include the tracing connection parameter in the JDBC URL:

# Tracing Configuration
management:
  opentelemetry:
    tracing:
      export:
        otlp:
          endpoint: http://localhost:4318/v1/traces
  tracing:
    ojdbc:
      # Enable the OJDBC Tracer
      enabled: true
      # Show full SQL query text
      show-sensitive-data: true
      # Include the operating system username in spans
      include-system-username: true
      # Include JDBC client info
      include-client-info: true
      # List of client info keys to include in spans
      client-info-keys:
        - OCSID.MODULE
        - OCSID.CLIENTID
        - OCSID.ACTION
    export:
      otlp:
        enabled: true
    sampling:
      # Send 100% of spans
      probability: 1
  otlp:
    metrics:
      export:
        enabled: false
  logging:
    export:
      otlp:
        enabled: false

spring:
  jmx:
    enabled: true
  application:
    name: OJDBC Tracing
  datasource:
    username: testuser
    password: testpwd
    # Docker compose Oracle Free container
    # Use the "custom-jdbc-trace-event-listener-provider" as the trace event listener provider.
    url: jdbc:oracle:thin:@localhost:1522/freepdb1?oracle.jdbc.provider.traceEventListener=custom-jdbc-trace-event-listener-provider
    # Set these to use UCP over Hikari.
    driver-class-name: oracle.jdbc.OracleDriver
    type: oracle.ucp.jdbc.PoolDataSource
    oracleucp:
      initial-pool-size: 1
      min-pool-size: 1
      max-pool-size: 30
      connection-pool-name: UCPSampleApplication
      connection-factory-class-name: oracle.jdbc.pool.OracleDataSource

Start the Example App

To run the example app, you’ll need a Docker-compatible environment, Java 21+, and Maven.

First, clone the sample repository and navigate to the spring-boot-jdbc-custom-tracer directory.

Then, start Oracle Database Free and Grafana LGTM containers:

docker-compose up -d

Fire up the Spring Boot application with Maven:

mvn spring-boot:run

Once the application has started, create a few traces using cURL:

curl -X POST http://localhost:8080/flavors \
  -H "Content-Type: application/json" \
  -d '{"flavor": "Mint Chocolate Chip"}'
  
curl http://localhost:8080/flavors

View Traces

Navigate to http://localhost:9411 to access the Grafana UI, and click “Traces to view all traces. Depending on which APIs you used, you should see traces displayed:

Graphical representation of OJDBC tracing for an HTTP POST request to the '/flavors' endpoint, showing execution durations for various components.

Click on one of the traces and explore each span. Notice spans from the Oracle JDBC driver include a variety of database client data, including the ACTION, CLIENTID, MODULE, and System username attributes:

Screenshot of database query tracing, showcasing execution details including service name, SQL text, connection ID, duration, and user information.

When you’re done, shut down the database and Grafana LGTM containers:

docker-compose down

References

Need more tracing examples? Check out these samples for Java and other programming languages:

Response

  1. Reading List – October 2025 #4 – Gerald on IT

    […] How to write a custom tracer for Oracle Database JDBC – Anders Swanson demonstrates the new Oracle Database JDBC OpenTelemetry provider […]

    Reply

Leave a Reply

Discover more from andersswanson.dev

Subscribe now to keep reading and get access to the full archive.

Continue reading