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!

Answer 1

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

Answer 2

Oracle SID is the unique name that uniquely identifies your instance/database where as Service name is the TNS alias that you give when you remotely connect to your database and this Service name is recorded in Tnsnames.ora file on your clients and it can be the same as SID and you can also give it any other name you want.

SERVICE_NAME is the new feature from oracle 8i onwards in which database can register itself with listener. If database is registered with listener in this way then you can use SERVICE_NAME parameter in tnsnames.ora otherwise - use SID in tnsnames.ora.

Also if you have OPS (RAC) you will have different SERVICE_NAME for each instance.

SERVICE_NAMES specifies one or more names for the database service to which this instance connects. You can specify multiple services names in order to distinguish among different uses of the same database. For example:

SERVICE_NAMES = sales.acme.com, widgetsales.acme.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

In an Oracle Parallel Server environment, you must set this parameter for every instance.

The TNS is the sql*net configuration file that defines datbases address for establishing connection to them.