• Arun Kumar

tnsnames.ora in Oracle

tnsnames.ora file helps you connect from one database (or client) to another database. The tns file resides under $ORACLE_HOME/network/admin location.


Make sure listener is running on the database server that you want to connect.

What is TNS?


tnsnames.ora file contains the connection details of the remote database that you want to connect. First the request is sent to listener running on the target database and then the connection is established.


On target server (that you want to connect), both listener and database must be up and running. Else, connection will not establish.


Edit tnsnames.ora File


Below is the exact syntax that you must use inside tnsnames.ora file. Make sure to input all the details related to the target server you want to connect.

<connection_name> =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = <service_name>)
 )
)

Where:

  • Connection name is any name that you want to give. Good thumb rule is to use target database SID as connection name. Easy to remember!

  • Hostname is the target server hostname or IP address

  • Port is by default 1521. If target database listener is running on a different port, put that port number

  • Service Name is the target database SID or if you have configured services, add that service name


tnanames.ora Example


Below is a completed tnsnames.ora file where we are trying to connect ORA12C database running on 192.168.0.101 host.

ORA12C =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12C)
 )
) 

There is not restriction as to how many connections you can add to tnsnames.ora file. For example if you want to add multiple database connection entries, you file will look like below:

ORA12C =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12C)
 )
)

ORA11G =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA11G)
 )
)

B6P =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.77)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = B6P)
 )
)

Connect remote database


Now that you have added tns entries to the file, use below syntax to connect remote database

sqlplus scott@ORA12c


Further Read

487 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis