Using TNSNAMES.ORA File in PL/SQL Developer and SQLPlus

Tnsnames.ora is an SQL*Net/Oracle Network Manager configuration file that contains client-side network configuration parameters and describes the available Oracle databases and how to connect to them. The tnsnames.ora file is used to resolve your database alias to the full Oracle server database address from a client device. The file contains the database connection name, protocol, address (IP or FQDN), network port number, and instance name. The tnsnames.ora file translates the Oracle Net service alias in the connection string into a network address, protocol, service, and instance name.

What is the TNSNAMES.ORA File in Oracle DB?

The tnsnames.ora plain text file is located on both the client and the server. This file is usually located in the oracle_home\network\admin directory.

By default, tnsnames.ora resides in the following location:

• Windows — Oracle_HOME\network\admin;
• Linux — Oracle_HOME/network/admin.

In addition, the tnsnames.ora file location can be defined by the TNS_ADMIN environment variable.

You can make changes to the file using any text editor. On Windows, you can use notepad.exe, on *NIX systems — vi.

Hint. If you prefer a GUI, you can use the Network Configuration Assistant GUI (called NETCA, formerly SQL*Net Easy Configurator) to edit the TNSNAMES.ORA file.

Let’s look at an example of simple tnsnames.ora file that describes one an Oracle service alias.

de_mun_sap12 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(Host = de-mun-sap12.theitbros.com])(Port = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = de_mun_sap12)

)

)

This file describes one alias de_mun_sap12 that a user can use to connect to an Oracle database. In this example, the Oracle database is running on the remote host de-mun-sap12.theitbros.com and listening on TCP port 1522.

You can get the Oracle database connection settings from the listener.ora file. The listener.ora resides on the Oracle server and contains server-side network configuration parameters.

Hint. The tnsnames.ora file is syntax sensitive. Always check the new settings are correct when making changes to this file.

In some cases, SQL Developer does not list available DB connections, even if you have specified the correct settings in the tnsnames.ora file and checked the correctness of the connection using tnsping.

Note. If you have installed the Oracle client software and configured connections in tnsnames.ora, Oracle SQL Developer automatically populates the Connections navigator pane. The alias list contains net service names defined in tnsnames.ora.

By default, Oracle SQL Developer looks for the tnsnames.ora file in the oracle_home /network/admin/tnsnames.ora folder and in the following registry keys:

• HKLM\SOFTWARE\ORACLE\ORACLE_HOME_KEY
• HKLM \SOFTWARE\ORACLE\ORACLE_HOME

On Windows, the TNS_ADMIN environment variable is used to point to a specific TNSNAMES.ORA file when multiple Oracle client versions are installed. You can use the following command to list the current contents of the tnsnames.ora file set in the environment variable:

type "%TNS_ADMIN%\tnsnames.ora"

The problem is that most database hosts don’t have a TNS_ADMIN environment variable by default. However, you can use the ORACLE_HOME variable to point to it.

Set %TNS_ADMIN% = %ORACLE_HOME%\network\admin\

Hint. On Windows, you can use both the system and local versions of the tnsnames.ora file. The system version can be found in the oracle_home\network\admin\tnsnames.ora. The local version of the tnsnames.ora file may be located in the current working directory from which the application is running. This allows you to have multiple local tnsnames.ora files in different directories from which you run your application (although this is not recommended).

Importing TNSNAMES.ORA to SQL Developer

PL/SQL Developer looks for a tnsnames.ora file in the following file system and registry locations:

• $HOME/.tnsnames.ora;
• $TNS_ADMIN/tnsnames.ora;
• TNS_ADMIN lookup key in the registry;
• /etc/tnsnames.ora (Linux environment);
• $ORACLE_HOME/network/admin/tnsnames.ora;
• HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME_KEY;
• HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME.

You can run the show tns command in the worksheet to get the tnsnames.ora file used by SQL Developer.

If the list of connections is empty, it means that SQL Developer didn’t find the tnsnames.ora file in the specified directories. In this case, you can manually specify the path to the tnsnames.ora file.

1. Open the PL/SQL Developer and go to Tools > Preferences;
   
2. In the Preferences option, expand the Database component, click on Advanced, and under the “Tnsnames Directory” browse to the folder containing your tnsnames.ora file;
   
3. Restart the SQL Developer;
4. Now in SQL Developer select Connections and click New Connection;
5. Select the TNS connection type from the drop-down list;
   
6. And you are done! Now all network aliases from tnsnames.ora will appear in the list of available connections.

Test Oracle Database Connectivity with TNSPING

If the specified Oracle instances are not available in SQL Developer, verify connectivity to them using the tnsping tool. The tnsping command uses information from the tnsnames.ora file to check connectivity with the Oracle database host.

Note. Learn how to truncate SQL server transaction logs.

For example, use the following command for the tnsnames.ora example above:

tnsping de_mun_sap12

The following lines should appear:

Used TNSNAMES adapter to resolve the alias
 Attempting to contact ….

If the connection is successful, OK will appear. If incorrect Net Service Names are specified in the tnsname file, you will get an error:

TNS-03505: Failed to resolve name

If the file contains a valid server address but does not have a listener running on it, the tnsping command will return an error:

TNS-12541: TNS:no listener

You can also use the trcroute utility, which shows the successful tracing route packet that passed from the client to the Oracle listener.

trcroute de_mun_sap12

If the listener is not available, this command will return errors:

TNS-12543: TNS:unable to connect to destination

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-03601: Failed in route information collection

If your tnsnames.ora file is missing or does not contain a connection string for the Oracle server listener, you can check the connection to the database by manually entering the connection options.

tnsping (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= de_mun_sap12.theitbros.com)(PORT=1522)))(CONNECT_DATA=(SID= de_mun_sap12)))