tnsnames.ora in Oracle

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

Note: make sure listener is running on the database server that you want to connect.

How it works

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.

Connection Syntax

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

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 [email protected]

Note: you can even connect to a remote database without adding tns entries.

Read more about Oracle easy connect method here.

Was this article helpful?

Related Articles

Comments

Leave a Comment