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

jdbcoracletomcat

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!

Best Answer

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);
Related Topic