What is Tnsnames.ora and sample Tnsnames.ora in Oracle

What is TNSNAMES.ORA
The TNSNAMES.ORA is a configuration file and the entry inside this file is used to connect to a oracle database server. This file exist in oracle client pc and from the client computer this file entry is used to resolve oracle service to establish connection to oracle server. It is nothing but contains net service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses. Note that we can easily connect to oracle database server without the help of tnsnames.ora. We can connection to oracle database server using easy naming service which is discuss in the topic Easy Naming Service.

Inside the TNSNAMES.ORA an entry is called name service name or simply TNS entry. It is an alias mapped to a database network address contained in a connect descriptor. A connect descriptor contains the location of the listener through a protocol address and the service name of the database to which to connect.

Find out the location of TNSNAMES.ORA
Based on the operating system default path of tnsnames.ora file is mentioned below.

i)Windows 3.x client
ORAWIN\NETWORK\ADMIN directory

ii)Windows 95/98 client
SQL*Net 2.x - ORAWIN95\NETWORK\ADMIN
Net8 - ORAWIN95\NET80\admin
Net8i - ORACLE\ORA81\NETWORK\ADMIN

iii)Windows NT client
SQL*Net 2.x - ORANT\NETWORK\ADMIN
Net8 - ORANT\NET80\ADMIN
Net8i - ORACLE\ORA81\NETWORK\ADMIN

iv)UNIX Client
$ORACLE_HOME/NETWORK/ADMIN
or /etc
or /var/opt/oracle

But if we set the TNS_ADMIN environment variable or registry value then that location override the default location.

Sample TNSNAMES.ORA

<alias>= [ (DESCRIPTION_LIST =  # Optional depending on whether u have 
    # one or more descriptions
    # If there is just one description, unnecessary ]
   (DESCRIPTION=
     [ (SDU=2048) ] # Optional, defaults to 2048
    # Can take values between 512 and 32K
     [ (ADDRESS_LIST=    # Optional depending on whether u have
    # one or more addresses
    # If there is just one address, unnecessary ]
       (ADDRESS=
  [ (COMMUNITY=) ] 
  (PROTOCOL=tcp)
  (HOST=)
  (PORT=)
       )
       [ (ADDRESS=
    (PROTOCOL=ipc)
    (KEY=) 
  )
       ]
       [ (ADDRESS=
    [ (COMMUNITY=) ]
    (PROTOCOL=decnet)
    (NODE=)
    (OBJECT=)
  )
       ]
              ... # More addresses
     [ ) ] # Optional depending on whether ADDRESS_LIST is used or not 
     [ (CONNECT_DATA=
  (SID=)
  [ (GLOBAL_NAME=) ]
       )
     ]
     [ (SOURCE_ROUTE=yes) ]  
   )
   (DESCRIPTION=     
     [ (SDU=2048) ] # Optional, defaults to 2048
    # Can take values between 512 and 32K
     [ (ADDRESS_LIST= ] # Optional depending on whether u have more
    # than one address or not
    # If there is just one address, unnecessary
       (ADDRESS
  [ (COMMUNITY=) ]
  (PROTOCOL=tcp)
  (HOST=)
  (PORT=)
       )
       [ (ADDRESS=
    (PROTOCOL=ipc)
    (KEY=)
          )
       ]
       ...   # More addresses
     [ ) ]   # Optional depending on whether ADDRESS_LIST  
    # is being used
     [ (CONNECT_DATA=
  (SID=)
  [ (GLOBAL_NAME=) ]
       )
     ]
     [ (SOURCE_ROUTE=yes) ]
   )
   [ (CONNECT_DATA=
       (SID=)
       [ (GLOBAL_NAME=) ]
     )
   ]

 where  ...   # More descriptions 
 [ ) ] # Optional depending on whether DESCRIPTION_LIST is used or not

From the above sample TNSNAMES.ORA syntax you can easily made your own TNSNAMES.ORA alias.

Following is an example of TNSNAMES.ORA from my machine.

LISTENER_LOCAL =
  (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = ARJU)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.1)(PORT = 1522))
  )

ORCL =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ARJU)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.1)(PORT = 1522))
      (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
      )
   )

First one is for resolving listener name and second one is the tns alias resolving net service name. Based on your server setting you can simply use one ADDRESS from the two shown above and for your client computer you only need to use ORCL like tns alias. To make it easy understandable I have made the color red which need to be changed for your environment setting.

For example if your
database machine IP Address is 10.1.1.2 and
database service name is PROD8
database running on listener port 1521
then in your client computer the TNS entry inside the TNSNAMES.ORA file will look like below,

TNS_ALIAS=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.1.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD8)
    )
  )

Then you can use TNS_ALIAS name to connect to database server from a client machine.


The following example shows a tnsnames.ora file configured for client load balancing:

sales_server=
(DESCRIPTION=
 (ADDRESS_LIST=
  (LOAD_BALANCE=on)
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
   (CONNECT_DATA=
   (SERVICE_NAME=sales.com)))

The following example shows a tnsnames.ora file configured for connect-time failover:

sales_server=
(DESCRIPTION=
 (ADDRESS_LIST=
  (LOAD_BALANCE=off)
  (FAILOVER=ON)
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
     (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
 (CONNECT_DATA=(SERVICE_NAME=sales.com)))

Related Documents
http://arjudba.blogspot.com/2010/01/service-names-using-ldap-fails-with-tns.html
http://arjudba.blogspot.com/2010/01/troubleshoot-ora-12154-tns-12154-tns.html
http://arjudba.blogspot.com/2009/11/list-of-oracle-networking-components.html
http://arjudba.blogspot.com/2009/11/network-connection-in-oracle-using.html
http://arjudba.blogspot.com/2008/10/ora-28547-connection-to-server-failed.html
http://arjudba.blogspot.com/2008/08/ora-12560-tnsprotocol-adapter-error-on.html
http://arjudba.blogspot.com/2008/06/ora-12154-tnscould-not-resolve-connect.html
http://arjudba.blogspot.com/2008/06/ora-12641-authentication-service-failed.html