How to Write and Configure Oracle tnsnames.ora [A Beginner’s Guide]

Are you struggling with how to write or configure the tnsnames.ora file, which is essential for connecting to an Oracle Database? This article provides a beginner-friendly explanation of everything from the basic mechanism of tnsnames.ora, a file that defines connection identifiers for Oracle, to specific configuration steps and solutions for common connection errors (like ORA-12154), using a 19c environment on Linux as an example.

By reading this article, you will be able to connect smoothly to your Oracle database from a client PC or another server.

🇯🇵日本語版はこちら (Japanese version) >

Conclusion: The Quickest Steps to Configure tnsnames.ora
Background and Basics: What is “tnsnames.ora”?
Procedure & Implementation: Location and Syntax of tnsnames.ora
1. 1. Location of the tnsnames.ora File
2. 2. Basic Syntax of tnsnames.ora
Execution Example: Configuring and Connecting
Troubleshooting
Operational and Security Notes
FAQ
Summary

Conclusion: The Quickest Steps to Configure tnsnames.ora

For those short on time, here is a summary of the process for configuring tnsnames.ora and connecting to Oracle.

Confirm the File Location: Locate the tnsnames.ora file in the $ORACLE_HOME/network/admin/ directory on the connecting side (the client).
Open the File: Open tnsnames.ora with a text editor. (If it doesn’t exist, create a new one).
Add Connection Information: Append the information for the database you want to connect to, following the specified format.
Save the File: Save your changes and close the file.
Test Connectivity: Use the tnsping <net_service_name> command to verify that the configuration is correct.
Connect: Connect to the database using sqlplus <user>/<password>@<net_service_name>.

Background and Basics: What is “tnsnames.ora”?

tnsnames.ora is a configuration file that acts like a “destination list” for connecting to an Oracle database. Its role is to map connection details—such as an IP address, port number, and service name—to an easy-to-understand alias called a “Net Service Name.”

For example, a net service name like DB_DEV_01 allows a human to immediately recognize it as “Development Environment DB #1.” Thanks to this file, applications and tools don’t need to remember complex connection strings; they can connect to the target database simply by specifying this “Net Service Name.”

Term	Description	Example
Net Service Name	An easy-to-understand alias for the target database, defined within `tnsnames.ora`.	`ORCL_TNS`
Connection Descriptor	The section that describes the destination’s hostname, port number, service name, etc.	`(DESCRIPTION=...)`
Listener	A process on the database server that listens for incoming connection requests.	`LISTENER`

Quick Tip TNS stands for “Transparent Network Substrate.” It provides a mechanism that allows developers to access a database by name without needing to be aware of the physical network configuration.

Procedure & Implementation: Location and Syntax of tnsnames.ora

This section explains where to find the tnsnames.ora file and how to write it.

1. Location of the tnsnames.ora File

This file is placed on the machine that is connecting to the database (the client). It is typically located at the following path:

Linux/UNIX: $ORACLE_HOME/network/admin/
Windows: %ORACLE_HOME%\network\admin\

If you don’t know the location of $ORACLE_HOME, you can find it by running the following command as the oracle user.

echo $ORACLE_HOME

Note that if the TNS_ADMIN environment variable is set, that path will take precedence.

2. Basic Syntax of tnsnames.ora

tnsnames.ora is written using the following simple syntax.

<net_service_name> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname_or_ip>)(PORT = <port_number>))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <service_name>)
    )
  )

net_service_name: Give it any easy-to-understand name. This is the name you specify after the @ symbol in SQL*Plus and other tools.
HOST: Specify the hostname or IP address of the database server.
PORT: The port number that the server-side listener is listening on. The default is 1521.
SERVICE_NAME: Specify the service name of the destination. This can be confirmed on the server-side listener.

Execution Example: Configuring and Connecting

Let’s walk through the specific steps of creating a tnsnames.ora entry based on the provided lsnrctl status information and connecting with SQL*Plus.

Prerequisites

Schema: The scott user exists and is able to connect.
Client: A Linux machine with the Oracle Client installed.
Connection Info (from lsnrctl status):
- Hostname: orcl19c
- Port: 1521
- Service Name: orcl

1. Editing tnsnames.ora

First, open $ORACLE_HOME/network/admin/tnsnames.ora with an editor like vi.

vi $ORACLE_HOME/network/admin/tnsnames.ora

Next, add the following content (or create the file if it’s new). Here, we will use ORCL_TNS as the net service name.

# Connection setting for Development DB (orcl)
ORCL_TNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

SQL Explanation This configuration means: “If a connection is requested using the name ORCL_TNS, use the TCP protocol to connect to port 1521 on the host orcl19c, and request the database service named orcl.”

2. Verifying Connectivity with tnsping

To check if the configuration can be read correctly, run the tnsping command.

# Execute tnsping <net_service_name>
tnsping ORCL_TNS

If you see an OK message like the one below, the tnsnames.ora entry is correct, and you are ready to communicate with the listener.

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-OCT-2025 14:00:00

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl19c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

3. Connecting with SQL*Plus

Finally, connect to the database using the sqlplus command.

# Connect with sqlplus <username>/<password>@<net_service_name>
sqlplus scott/tiger@ORCL_TNS

Upon a successful connection, the SQL prompt will be displayed.

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 13 14:01:00 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

That completes the database connection using tnsnames.ora.

Troubleshooting

Here is a summary of common errors encountered with tnsnames.ora configuration and their solutions.

ORA Error Code	Primary Cause	Checkpoints	Solution (Example)
ORA-12154	Net service name cannot be found.	・Is the path to `tnsnames.ora` correct? ・Spelling mistake in the name within the file. ・Syntax error (e.g., missing closing parenthesis).	Check the `TNS_ADMIN` environment variable, verify with `tnsping`, and review syntax.
ORA-12541	The listener is not running, or the host/port is incorrect.	・Run `lsnrctl status` on the server. ・Check `HOST`/`PORT` in `tnsnames.ora`.	Start the listener on the server (`lsnrctl start`), and correct the `HOST`/`PORT` in `tnsnames.ora`.
ORA-12514	The listener is running but does not know the specified service name.	・Check the `Services Summary` in `lsnrctl status`. ・Check `SERVICE_NAME` in `tnsnames.ora`.	Correct the `SERVICE_NAME` in `tnsnames.ora` to match the value shown in `lsnrctl status`.

Quick Tip The standard procedure for troubleshooting connection errors is to first check if name resolution works on the client side with tnsping, and then check the listener’s status on the server side with lsnrctl status.

Operational and Security Notes

Backup: Always create a backup of the tnsnames.ora file before editing it. A syntax error could render all connection definitions unusable.
Comparison with Easy Connect Naming: There is an alternative method (Easy Connect) that allows direct connection without tnsnames.ora, using the format user/password@host:port/service_name. While convenient, it results in long connection strings and cannot be used for advanced features like load balancing in a RAC configuration. Choose the method that suits your needs.
TNS_ADMIN Environment Variable: If you need to switch between different tnsnames.ora files for development, testing, and production environments, the TNS_ADMIN environment variable is a convenient way to specify the file’s location.
Reverting Changes: If a problem occurs, you can quickly revert to the previous state by overwriting the file with the backup you created.

FAQ

Q1: Where is the default location of the tnsnames.ora file?
A1: The default location is $ORACLE_HOME/network/admin/ in Linux/UNIX environments and %ORACLE_HOME%\network\admin\ in Windows environments. However, if the TNS_ADMIN environment variable is set, that directory takes precedence.

Q2: What is the difference between tnsnames.ora and sqlnet.ora?
A2: While tnsnames.ora acts as a destination list (address book) that answers “where to connect,” sqlnet.ora is a file that defines connection rules (communication methods and security) that answer “how to connect.”

Q3: Is there a difference in the syntax of tnsnames.ora between the Linux and Windows versions?
A3: No, there is no difference in the file’s syntax or how it’s written. The content can be used on either OS. The only difference is the file path (directory separators are / vs. \).

Q4: Can I connect to Oracle without this file?
A4: Yes, you can. By using the “Easy Connect Naming Method,” you can connect by directly specifying the host, port, and service name in the connection string, like sqlplus scott/tiger@orcl19c:1521/orcl.

Q5: Can I define multiple destinations in a single tnsnames.ora file?
A5: Yes, you can. By listing multiple net service name definitions consecutively in the file, you can manage connection information for various databases—such as development and production—all in one place.

Summary

This article provided a guide to Oracle’s tnsnames.ora file.

tnsnames.ora is a “destination list” for managing DB connection information.
The default location is typically $ORACLE_HOME/network/admin.
The key components are “Net Service Name,” “HOST,” “PORT,” and “SERVICE_NAME.”
It is standard practice to verify the configuration with tnsping after making changes.
An ORA-12154 error usually points to a name resolution failure.

By correctly understanding and configuring this file, you can make your daily Oracle operations much smoother.

This article is based on Oracle Database 19c. The user interface and default values may differ in other versions.

[reference]
Oracle Database Database Net Services Reference, 19c