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, usingSID
(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
E.g
JDBC drivers connect with a connection string using TNS as follows