• Arun Kumar

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 scott@ORA12c

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


Read more about Oracle easy connect method here.



Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback