Uniquely identify source JDBC process in Oracle DB

Question

We are developing microservices using java (Spring and Spring Boot), and the access to our Oracle DB is done via the JDBC Oracle Driver.

The problem is that our DBAs can only see on the Oracle side that a "JDBC Thin Client" is connected. There is no better, logical representation of the connected application. Without such identification, it is hard to know which microservice might be behaving badly. Other non-JDBC clients identify themselves clearly with the hostname.

Is there any way to change the identification String so that it represents a clear identity of the source application/process?

Note: Our system runs on Cloud Foundry which uses containers, so a it's not really possible to provide a machine name or anything like that - a logical application name would be preferred.

Thanks

Quality

Answer 1

Thank you everyone for your suggestions, I have tried them all and sadly they did not work.

This is probably since I am connection using Spring Data and the default Hikari connection pool.

After spending hours on it, the final solution was found here: Spring Boot 1.3.5 with Hikari Connection Pool not able to set program name in v$session

spring:
  datasource:
    hikari:
      data-source-properties:
         v$session.program: AppName

Simple, no code changes, and it works!

Quality

Answer 2

The JDBC connection property "oracle.jdbc.v$session.process" can be set (as a system prop -D) to a value that uniquely identifies your microservice and that can then be retrieved in the V$SESSION view ("process" column).

Creepy

That put me on the right track. Found some documentation here docs.oracle.com/en/database/oracle/oracle-database/12.2/jajdb/… that shows machine, osuser, process, program and terminal as properties that can be set that flow through to v$session. – Incurious

Answer 3

Database users

If you name the Database users used for connection like the Microservice, the DBA should be able to map the connections to the microservices.

IP addresses

For a Database a connection also contains an incomming IP address. Using a table of ip-addresses to microservices, the DBA might be able to map the connections down to the microservice.

Respectfully

It might be a exciting work for DBA to find the Programmers faults. If the realtionship between Programmers and DBAs is harmonized, a talk might solve the problem where they occour (in the code). If no harmonization is possible soon, a more detailed contract or specification might be a solution.

It looks like a Workaround. The bug is in the code, lets find it in the code.

Disqualification of API

If you like to identify the Microservice by the Database connection, you disqualificate the use of a API for the database access. The Database API might be usefull if you like to have the Single-Source-Of-Truth as a Microservice.

Stapler

A typicall harmonization talk looks like follows - Programer: there is a performance problem in the database. DBA: There are some problematic queries, but we do not know, who is initiating them (except for USER and HOST, which are same for all microservices). So aparently some level of E2E instrumentation could be meaningfull... – Ariminum

@MarmiteBomber Very good example. Let them realize that both are right and you have your harmony ... without solving the problem. They have no concrete solution yet. – Stapler

Answer 4

Depending on your Oracle version, the feature is implemented in the method setEndToEndMetrics (It has been deprecated since 12.1 in favor of setClientInfo()) or setClientInfo

Here a small example of the usage. The client (your service) after acquiring a connection (typically from a connection pool) sets the properties action, clientId and module

String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
 metrics[OracleConnection.END_TO_END_ACTION_INDEX] = 'myAction1';
 metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = 'myClient';
 metrics[OracleConnection.END_TO_END_MODULE_INDEX] = 'myModule1';
 con.setEndToEndMetrics(metrics, (short) 0);

and resets them before returning the connection.

The DBA may observe the setting in the V$SESSION with the following query

select sid,  client_info, module, action from v$session

so she is possible to relate the database session no only with the service, but the combination of client / module and action may provide further details of the state of the service.

Three things are important to consider:

This work only if all services establish certain discipline in setting the values. While re-using the sessions from the connection pool it is easy to "inherit" a wrong setting from a predecessor service. I'd recomment to implement it as an aspect of the connection pool resource handling.

Further the Java version, JDBC driver and Oracle Server must have compatible versions, so it is a good practice to test the functionality in a simple script.

Finaly DON'T use for the setting the PL/SQL API (which a PL/SQL developer would naturally do). The great difference is that the PL/SQL API triggers a roundtrip to the database, while JDBC API not (the values are send with the next request).

Ariminum

Answer 5

If you have access to the code that connects to the Oracle Database, you can try:

private static String getProcessId(final String fallback) {
 // Note: may fail in some JVM implementations
 // therefore fallback has to be provided
 // something like '<pid>@<hostname>', at least in SUN / Oracle JVMs
 final String jvmName = ManagementFactory.getRuntimeMXBean().getName();
 final int index = jvmName.indexOf('@');

 if (index < 1) {
   // part before '@' empty (index = 0) / '@' not found (index = -1)
   return fallback;
 }

 try {
   return Long.toString(Long.parseLong(jvmName.substring(0, index)));
   } catch (NumberFormatException e) {
   // ignore
   }
 return fallback;
 }



public void init() {
    java.util.Properties props = new java.util.Properties();
    String javaPid;
    try {
        oracleConnexionPool = new OracleConnectionPoolDataSource();
        oracleConnexionPool.setDriverType(oracle.jdbc.driver.OracleDriver);
        //Java 9+ version:
        //long pid = ProcessHandle.current().pid();
        //Java < 9 version:
        try
        {
            javaPid = getProcessId("<PID>");
            props.put("v$session.process", javaPid);
            props.put("v$session.program", "<Your program name>");
            oracleConnexionPool.setConnectionProperties(props);
        }
        catch (SQLException e) {
            }

        oracleConnexionPool.setURL(<DB URL>);

If you have access to the command-line which is launching java, try:

java ...-Doracle.jdbc.v\$session.process=$$ ...

The available keywords for identifying to whom a session "belongs to" are (Unix-style syntax):

java ...-Doracle.jdbc.v\$session.process=<My PID> \
  -Doracle.jdbc.v\$session.machine="<My machine>" \
  -Doracle.jdbc.v\$session.osuser="<My OS username>" \
  -Doracle.jdbc.v\$session.program="<My program>" \
  -Doracle.jdbc.v\$session.terminal="<My term>" ...

Initial