SID or Service Name with JDBC Thin Drivers?

Using SID in the application connection strings instead of Service Name is never a good idea. In fact, up to Oracle database version 12c, this was not a problem. The below connection string works fine with JDBC Drivers, however after we upgraded the database to 19c, it stopped working.

## Failing Connection
connection string: jdbc:oracle:thin:@hostname:1521/DB_NAME

## Successful Connection
jdbc:oracle:thin:@//hostname:1521/DB_NAME

SID or Service Name?

Let’s understand our connection strings first.

Connection String 1

jdbc:oracle:thin:@[host]:[tcpPort]:[SIDname]
- [host]: host name of the database server
- [tcpPort]: database listener port
- [SIDname]: system identifier for the database

Connection String 2

jdbc:oracle:thin:@//[host]:[tcpPort]/[service_name]- [host]: host name of the database server
- [tcpPort]: database listener port
- [service_name]: system identifier for the database

Finding documentation about Connection String 1 was not straightforward. I had to dig through documentation and then found references here. In addition to understanding the differences between Connection String 1 and Connection String 2, it’s essential to grasp the implications of these changes in Oracle’s architecture and JDBC support.

In the first connection string, the DB_NAME is translated as the ORACLE_SID and in the second string the DB_NAME is considered a service. Since there is a default service for the sid, the use of “DB_NAME” doesn’t make the difference obvious. This is yet another reason to use customized meaningful services on the database for your application.

Now, we understand that Oracle no longer supports SID-based connections. Further investigation revealed that Oracle deprecated Service IDs since Oracle 10g. Despite functioning until Oracle 19c, this feature has now been disabled, mandating the use of the Thin-style service name.

Use the following SQL to identify sessions connecting without a service name.

column username format a30
column osuser format a20
column service_name format a15
column module format a45
column machine format a30
set lines 200 pages400

select nvl(s.username, '(oracle)') as username,
       s.osuser,
       s.sid,
       s.service_name,
       s.machine,
       s.program,
       s.module,
       s.action,
       s.client_info,
       s.client_identifier,sci.CLIENT_CONNECTION,
       sci.CLIENT_OCI_LIBRARY
from   v$session s,
       v$session_connect_info sci
where  s.sid = sci.sid
and    s.serial# = sci.serial#
and    sci.service_name is not null AND type != 'BACKGROUND'
order by s.username, s.osuser;

You should standardize service names and phase out legacy identifiers like SIDs. This will streamline database management and future-proof your environment. Some benefits of using Service Names include flexibility to change the underlying instance name configuration, high availability, and load balancing. Service Names also open up the world of Transparent Application Failover (TAF) and Application Continuity. Unless you have a special use case like connecting to PDBs with RMAN it is always a good idea to stick with Service Names.

This shift underscores the importance of staying abreast of updates and best practices in database administration. While these changes may initially pose challenges for users accustomed to older conventions, they ultimately pave the way for a more robust database infrastructure.