Tnsnames.ora

From Oracle FAQ
Jump to: navigation, search

TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory, but location can be defined by the TNS_ADMIN environment variable.

Configuring TNSNAMES.ora[edit]

Text editor[edit]

Add the following entry in your TNSNAMES.ORA file and change the values shown in brackets to suit your environment:

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

Here is a completed example:

ORA12 =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12)
 )
)

TNSNAMES.ORA files are located on both client and server systems. If you make configuration changes on the server ($ORACLE_HOME/network/admin/tnsnames.ora) ensure you can connect to the database through the listener if you are logged on to the server. If you make configuration changes on the client (c:\oracle\product\12.1.0\db_1\network\admin\tnsnames.ora) ensure you can connect from your client workstation to the database through the listener running on the server.

Network Configuration Assistant[edit]

One can also use the Network Configuration Assistant GUI (called NETCA, previously SQL*Net Easy Configurator) to change TNSNAMES.ORA entries. Most people prefer to edit the TNSNAMES.ORA file with an editor and plug in the required settings. However, others might prefer the GUI. Note the file is usually found in the ORACLE HOME/network/admin/ directory or %USERPROFILE%/AppData/Oracle with Windows 7.

NETCA will guide you through a set of screens (wizard) to add as many entries as you like for each database you need to connect to. Also supply an alias name as prompted to identify each database (the alias name and database name can be different).

Also see[edit]