Difference between using a TNS name and a service name in a JDBC connection

Question

I have a java based server (Tomcat) that connects to an Oracle database using a JDBC connection. There are multiple ways to connect to the database: SID, TNS name, Service name.

I would like to understand what is the difference between each of these connections and what would be the recommended connection (SID, TNS, or service) if connecting to a clustered database. Here is the TNS name we have for the database:

MY_NICE_TNS_NAME.MY_COMPANY.COM =

(DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = myhostname)(PORT = 1521))

  (LOAD_BALANCE = YES)

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = MY_NICE_SERVICE_NAME.MY_COMPANY.COM)

   (FAILOVER_MODE =

   (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)

   )

  )

)

Thanks!

as long as you have only ONE hostname specified there is practically no difference. If you were connected to RAC(OPS) or DataGuard then yes. SID is unique, not changeable and ends with number. While the SERVICE_NAME can be present on one or more nodes. And it can also move between nodes — ibre5041
Thanks, great to know. See my comment below. We do have a RAC. — Luis Garcia
if you use RAC then you should specify hostname for every node. see: stackoverflow.com/questions/13424385/… — ibre5041

Premraj Premraj · Accepted Answer · 2015-12-12T11:30:03

SERVICE_NAME is a alias to a database instance (or many instances). The main purpose of this is if you are running a cluster. Using this we can connect specific database within a cluster. And other way, using SID (System IDentifier) we can connect to a database instance, which is a unique name for an Oracle database instance.

In short, SID = the unique name of your DB, SERVICE_NAME = the alias used when connecting.

There are several ways to provide database information like Directly Specified, tnsnames.ora (i.e. TNS name), LDAP Directory, Network Information Services.

A TNS (Transparent Network Substrate) name is the name of the entry in tnsnames.ora file which is kept in $ORACLE_HOME/network/admin
This file contains the information which is used by the system to connect to oracle database. Using this a client can fetch server associated information transparently. It contains the following information

PROTOCOL
HOST IP ADDRESS
PORTNO
SID  or SERVICE_NAME

E.g

 mydb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.35.15.1)(PORT = 1521))
    (CONNECT_DATA = (SID = mydb))

JDBC drivers connect with a connection string using TNS as follows

System.setProperty("oracle.net.tns_admin", PATH_TO_TNSNAMES.ORA);
Class.forName ("oracle.jdbc.OracleDriver");
dbUrl = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="+IPHOST+")(PORT="+PORT+"))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME="+DBNAME+")))"

conn = DriverManager.getConnection(dbUrl, USERNAME, PASSWORD);

Difference between using a TNS name and a service name in a JDBC connection

2 Answers