The TNSNAMES.ORA file is an important file when working with Oracle. Learn all about what it is, its location, and how to create and edit it in this article.
In this article, we’ll cover:
- What is TNSNAMES.ORA?
- Where is the TNSNAMES.ORA file?
- What order does Oracle check these locations
- TNSNAMES Location on Unix
- TNSNAMES Location on Windows
- What Is The Syntax?
- Modifying a TNSNAMES.ORA file
- Creating a TNSNAMES.ORA file
- TNSNAMES Example
- SQL Developer and TNSNAMES.ORA
What Is TNSNAMES.ORA?
TNSNAMES.ORA is a configuration file that the Oracle database uses. It allows users and applications to connect to Oracle databases by matching a connection name with all of the relevant details.
It’s written using a specific syntax, which I’ll cover later in this article. The good news is that it can be edited with any text editor.
The file and this article refer to a few different terms, such as service names and connect descriptors, which I’ll cover later in this article.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Where Is the TNSNAMES.ORA File Located?
The location of the TNSNAMES.ORA file is:
$ORACLE_HOME\network\admin\
What does this mean?
Well, $ORACLE_HOME is an environment variable. It works in the same way as a variable in a programming language, but it’s sits in your operating system.
In this case, $ORACLE_HOME is the location that the Oracle database is installed in. This environment variable, or path, works the same on Unix and Windows operating systems.
So, how do you find your $ORACLE_HOME value? I’ll show you how to do that in the next section.
There are some other locations that the TNSNAMES.ORA file can be stored in:
Client Machine
The ORACLE_HOME\network\admin folder on your client machine. There is a file on both the server and the client.
TNS_ADMIN Environment Variable
There is another environment variable called TNS_ADMIN. The location of this folder could also have a TNSNAMES file.
To find the location of TNS_ADMIN, follow the same steps below to find ORACLE_HOME, but substitute the TNS_ADMIN value.
What Order Does Oracle Check These Locations In?
Because there are several locations for the TNSNAMES.ORA file, they are checked in a certain order:
- If TNS_ADMIN is set, then this location is checked first. If the file is not found in this directory, it is assumed the file does not exist. You may need to create one.
- In Windows, if the TNS_ADMIN environment variable is not set, then the registry is checked for the TNS_ADMIN parameter and checks that directory.
- If the TNS_ADMIN variable is not set, then the ORACLE_HOME\network\admin directory is checked.
How To Find ORACLE_HOME and the TNSNAMES.ORA Location in Unix
To find the location of ORACLE_HOME in Unix, you can run these commands:
env | grep ORACLE_HOME
Or, you can run the echo command
echo $ORACLE_HOME
How To Find ORACLE_HOME and the TNSNAMES.ORA Location in Windows
To find the ORACLE_HOME location in Windows, we can check a few places.
First, we’ll check the Environment Variables in the control panel. If it’s not there, we’ll check the registry.
To start, open the Control Panel.
Then, open System.
Click on Advanced System Settings, on the left.
Click on the Advanced tab (if it is not already selected) and click Environment Variables down the bottom.
Check the User Variables section and the System Variables section for a variable called ORACLE_HOME. If it is shown, then the Value will be your ORACLE_HOME location.
If it does not exist, it means you’ll need to check the registry. It isn’t showing in my Environment Variables, so I’ll check the registry.
Open the Run command box (on older versions of Windows), or if you’re on Windows 10, just open the Start menu.
Type regedit and press Enter.
On the left panel, navigate to this location by expanding the folders:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Now, you’ll need to click on the item below Oracle on the left. This may be called KEY_XE (if you’re running Oracle Express like I am) or KEY_OraDb11g or something similar.
There will be an entry in this list on the left called ORACLE_HOME.
This is your ORACLE_HOME location. For example:
C:\oraclexe\app\oracle\product\11.2.0\server
To navigate to it, double-click on the line labelled ORACLE_HOME.
Copy the Value here, and paste it into Windows Explorer.
What Is The Syntax of the TNSNAMES.ORA File?
This file contains a series of entries, where each of them represents a connection string to the database.
An entry will look like this:
net_service_name =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=service_name)
)
)
What does this mean?
- net_service_name: This is the name that you use for a connection string later. You can choose what this is. It’s like a name you give to this set of connection details.
- host: The IP address or server name where the database lives or that you want to connect to.
- port: The port that is required for the connection. In most cases the default port of 1521 will be fine.
- service_name: This is the name of the database you want to connect to.
What about the SID? The SID parameter was used in older versions of Oracle in this file (Oracle 8 and earlier). The service_name parameter should be used instead.
How Can I Modify the TNSNAMES.ORA File?
You can modify the file in a simple text editor. You can change an existing entry or create a new one.
To add an entry into the file, you can either copy the format from above, or copy and paste an existing entry from the file.
Then, make changes to it as needed.
Change the net_service_name, or the name you want to give to the connection. Change the host to the server name or IP address you want to connect to. Finally, change the service_name to the name of the database you want to connect to.
Save the file, and your changes will be saved.
How Can I Create a TNSNAMES.ORA File?
If you don’t have a TNSNAMES.ORA file in your ORACLE_HOME directory, you can create one. Or you can create one for any other reason.
To create the file, open a new text file in the editor of your choice (I use Notepad++).
Save the file with the name TNSNAMES.ORA (not a .txt file) and save it into your ORACLE_HOME location.
Now, add in a template for the entry you want to create:
net_service_name =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=service_name)
)
)
Then, change the parameters to what you need to store for your database connection:
- net_service_name: the name you give to this connection, which will be used when you connect to it later.
- host: the server or IP address that the database runs on
- service_name: the name of the database you’re connecting to.
See below for an example of this.
TNSNAMES.ORA Entry Example
Here’s an example of an entry in this file:
ora_test =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver.yourcompany.com)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=oratst)
)
)
This means that the database runs on the server that’s called “oracle server.yourcompany.com”. The database name is orates, and when you connect to it, you’ll refer to this as ora_test.
Does SQL Developer Use TNSNAMES.ORA?
Yes, it does. In SQL Developer, you can set the location of your TNSNAMES.ORA file, which will give you additional options when creating connections to a database.
In SQL Developer, open Tools > Preferences.
Expand the Database section and click on Advanced.
In the Tnsnames Directory option at the bottom of the screen, add the location of the TNSNAMES.ORA file. This will be ORACLE_HOME\network\admin as mentioned earlier.
Then, click OK.
Now, when you create a new connection, you can use this TNSNAMES data.
Click Create New Connection (the green + sign on the top left of SQL Developer).
In the Connection Type drop-down, select TNS.
Selecting TNS will allow you to select your connection details from the TNSNAMES file. This makes it easier to manage.
Jeff Smith has written more about how SQL Developer finds these files in this article.
Conclusion
The TNSNAMES.ORA file is used by Oracle to store and configure the connection details to different databases. It can be hard to find, but using this guide will make it easier. Making changes is easy, as it’s a simple text file with a specific format. It might not be something a database developer would need to use that often, but it’s still good to know.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Most Oracle Client installs use a TNSNames.ora file. This allows users to enter a database alias in client programs
to connect to an Oracle database. A common problem is when the Oracle Client either can’t find the TNSNames.ora
file or is using a different one than was intended.
Default location
The default location of the TNSNames.ora file is «ORACLE_HOME\network\admin\tnsnames.ora», where ORACLE_HOME is the
directory which contains your primary Oracle Home installation. This could be something like
«C:\Oracle12\network\admin\tnsnames.ora».
Forcing the Oracle Client to use a particular TNSNames.ora file
If you use TNSNames.ora it is recommended to use either an environment variable or a registry setting to force the
location of the file. This setting is called TNS_ADMIN and is set to the directory/path of the tnsnames.ora
file (but should not contain the filename, just the path to it.)
TNS_ADMIN can be set in a number of different ways, but Oracle will use the following precedence:
- Local session environment variable.
- Global environment variable.
- User registry setting under HKey_Current_User\Software\Oracle (or individual Oracle home registry entry.)
- System registry setting under HKey_Local_Machine\Software\Oracle (or individual Oracle Home registry entry.)
Possible Problems
One possible problem (and this comes up surprisingly often!) is that the Oracle Client will first check if the current
application directory contains tnsnames.ora. The current application directory can be initially set in the startup icon
options for a program, or programs often keep track of the last used document directory. It is a good idea to not keep
copies of tnsnames.ora in default program directories or document directories so as not to have the Oracle Client
accidentally use it instead of the intended tnsnames.ora file.
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.
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.
- Open the PL/SQL Developer and go to Tools > Preferences;
- 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;
- Restart the SQL Developer;
- Now in SQL Developer select Connections and click New Connection;
- Select the TNS connection type from the drop-down list;
- 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.
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
Brian Jackson
Brian Jackson started this blog in 2011. Brian has a huge passion for WordPress and technology for over a decade. Brian enjoys blogging, movies, and hiking.
Пройдите тест, узнайте какой профессии подходите
Работать самостоятельно и не зависеть от других
Работать в команде и рассчитывать на помощь коллег
Организовывать и контролировать процесс работы
Быстрый ответ
Для привязки файла tnsnames.ora к Oracle SQL Developer указывается путь к Oracle Client через настройку Preferences. Действуйте следующим образом: перейдите в Инструменты > Настройки > База данных > Дополнительно и укажите путь к файлу tnsnames.ora. При верно заданном пути SQL Developer начнет использовать TNS имя для соединения с базой данных:
tnsnames.oraдолжен находиться в$ORACLE_HOME/network/adminили в каталоге, определённом в переменной$TNS_ADMIN.- В SQL Developer откройте Инструменты > Настройки > База данных > Дополнительно.
- В поле «Использовать Oracle Client» вставьте соответствующий путь.
- Во время создания нового соединения с базой данных выберите нужное TNS имя.
Изменения вступают в силу немедленно, для их применения не требуется перезапускать SQL Developer.
Настройка переменных среды
Указание переменной среды TNS_ADMIN позволяет SQL Developer определить расположение файла tnsnames.ora. Прежде чем начинать работу с SQL Developer, примените следующие настройки:
Для пользователей Windows
- Откройте Система > Дополнительные параметры системы > Переменные среды.
- Создайте новую Системную переменную с именем
TNS_ADMIN, указав путь к каталогу с файломtnsnames.ora.
Для пользователей Linux и Unix-подобных систем
В Linux внесите следующую строку в файл ~/.profile или в соответствующий профиль:
После этого SQL Developer и другие утилиты, работающие с Oracle, будут знать, где расположен файл с TNS именами. Не забудьте перезапустить SQL Developer или выйти и повторно войти в систему, чтобы переменные среды обновились.
Устранение проблем с подключением
Если возникли проблемы, проверьте следующее:
- Доступность файла
tnsnames.ora: файл должен быть открыт для чтения. - Синтаксис файла: наличие синтаксических ошибок в
tnsnames.oraможет вызвать проблемы. - Использование
tnsping: убедитесь, что сеть может определить TNS имя.
Если проблемы сохранились, не волнуйтесь. Сообщество Oracle на community.oracle.com и другие форумы всегда готовы помочь.
Настройка для Instant Client
При работе с Oracle Instant Client укажите путь к каталогу в параметре -Djava.library.path файла sqldeveloper.conf:
Это поможет обеспечить корректное взаимодействие Instant Client и SQL Developer при использовании TNS-имен.
Визуализация
Подключение к базе данных Oracle с помощью SQL Developer, который правильно настроен на использование файла tnsnames.ora, можно изобразить так:
Файл tnsnames.ora сообщает Oracle SQL Developer-у, к какой базой данных и каким образом ему следует подключаться для выполнения запросов.
Лучшие практики
Резервирование и организация
Сохраняйте резервные копии tnsnames.ora, но помните о их адекватной организации, чтобы не возникло путаницы в SQL Developer. Для устаревших и архивных версий используйте отдельные папки.
Стандартное наименование файла
Проверьте, что имя файла tnsnames.ora соответствует стандарту, иначе SQL Developer может его не опознать.
Изменения в сетевой конфигурации
При вносе изменений в сетевую конфигурацию важно вовремя перепроверить записи TNS.
Эффективная навигация
Пользовательские псевдонимы и централизованный доступ к сетевым настройкам через tnsnames.ora помогают оптимизировать взаимодействие с базами данных и упрощают рабочий процесс в команде.
Полезные материалы
- Обсуждение на Stackoverflow о SQL Developer и tnsnames.ora — много полезной информации по теме.
- Скачивание Oracle Client и сетевых компонент — официально сайт для загрузки Oracle Instant Client.
- Официальная документация Oracle по использованию tnsnames.ora с SQL Developer — инструкция от самих разработчиков.
- Как настроить подключения к базе данных через tnsnames.ora — практическое руководство по настройке.
Oracle InstantClient: TNSNAMES.ORA location
, by Simon Krenger
When you install the InstantClient binaries, you often want to use your existing TNSNAMES.ORA file containing all your databases. So how do you specify the location of your TNSNAMES.ORA file?
The Oracle website has the answer:
Always set the TNS_ADMIN environment variable or registry to the location of the tnsnames.ora file (full directory path only, do not include the file name). This practice will ensure that you are using the appropriate tnsnames.ora for your application when running with Instant Client.
So, for UNIX and Linux systems set the variable TNS_ADMIN like so in your .profile or .bash_profile:
export TNS_ADMIN=/opt/oracle/instantclient_11_2/On Windows systems, set the environment variables via the Advanced System properties:
- Open CMD, enter
sysdm.cpl - In the Advanced tab, select Environment Variables
- Under “System Variables”, click on New… and enter “TNS_ADMIN” as the name and the path where your TNSNAMES.ORA resides as your value.
Now, you can use your TNS names for your InstantClient, for example for SQL*Plus:
$ sqlplus simon@KDB01For more information on SQL*Plus configuration, please refer to the Oracle documentation for SQL*Plus.