| Lesson 4 | The tnsnames.ora file |
| Objective | Describe the components and functions of the tnsnames.ora file. |
The tnsnames.ora file implements Oracle Net local naming: it maps a convenient net service name, or alias, to a connect descriptor. The descriptor tells an Oracle client how to reach a listener and which database service to request. It is connection configuration, not a catalog containing every detail about a remote database.
For example, an application can use REMOTE_SVC instead of embedding a host, port, and service name in every connection command. Oracle Net resolves that alias using the applicable local naming configuration. Changing an entry can change the destination of subsequent connections that resolve it, so maintaining these aliases is part of managing application connectivity.
In Oracle AI Database 26ai, distinguish four names throughout this lesson: a client-side alias, a host name, a database service name, and a database-link name. They serve different purposes. They can look similar, but making their spelling identical does not make them the same object.
This example matches the first diagram below. It describes a TCP endpoint and a database service. The host and service are illustrative; replace them with values supplied for your environment before attempting a connection.
REMOTE_SVC =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = remote-db.example.com)
(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = salespdb.example.com)))
Read the entry from the outside inward. REMOTE_SVC names the complete descriptor. DESCRIPTION contains connection information. Its ADDRESS identifies a listener endpoint, while CONNECT_DATA identifies the service requested through that endpoint. Indentation makes these nested groups easier to inspect, but the parentheses establish their structure.
| Component | Meaning in this example |
|---|---|
REMOTE_SVC | The local net service name used by the client. |
DESCRIPTION | The enclosing connect descriptor. |
ADDRESS | A protocol address for reaching the listener. |
PROTOCOL = TCP | The transport used for this endpoint. |
HOST | The host name or address of the endpoint. |
PORT = 1521 | The listener port used here, not a mandatory value for every installation. |
CONNECT_DATA | The group containing database service identification information. |
SERVICE_NAME | The database service requested by the connection. |
This is Oracle Net configuration syntax, not SQL. Do not append SQL statement terminators to its entries or execute the descriptor as a query. Keep the nested parentheses balanced, and use plain text rather than typographic quotation marks copied from a formatted document.
The alias REMOTE_SVC is the name the client looks up. The value salespdb.example.com is the service requested from the listener. A database service represents a way to connect to a database workload and may be associated with a pluggable database. The alias can be chosen independently of the service name.
A SID identifies an Oracle instance. Older examples often use (SID=customer), but that is not interchangeable with (SERVICE_NAME=salespdb.example.com). Use the service appropriate to the target database or PDB in a modern service-based example. Do not replace a service with a guessed SID merely because a connection failed.
Conversely, SID syntax has not vanished from every supported connection scenario. The correction is to use the identifier required by the actual deployment, rather than teaching SID as a universal synonym for database name. Ask for the registered service and listener endpoint instead of deriving them from a server's folder names or a schema name.
A single file can contain multiple entries. In the following example, two sites support order-processing services. Distinct aliases let an operator select the intended destination explicitly:
# Raleigh order-processing service
ORDERS_RAL =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = raleigh-db.example.com)
(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orders-ral.example.com)))
# Colorado Springs order-processing service
ORDERS_COS =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = colorado-db.example.com)
(PORT = 1527))
(CONNECT_DATA =
(SERVICE_NAME = orders-cos.example.com)))
Port 1527 illustrates a deliberately configured alternative endpoint. It does not imply that the second database on a network must use that port. Different machines can use the same listener port, and one listener can support multiple services. The endpoint and requested service together determine where the request is directed.
Descriptive aliases reduce mistakes, especially when development, test, and production environments contain similarly named schemas. A naming policy might include application, location, and environment. What matters is that the policy is predictable and the mapping is documented. An alias named PROD does not itself prove that the descriptor points to production.
With local naming configured, run this command from an operating-system terminal that has the intended SQL*Plus installation available:
sqlplus report_user@REMOTE_SVC
SQL*Plus prompts for the password. The alias resolves to the descriptor, the client attempts the connection, and the database authenticates the account. A successful login requires more than a well-formed file: the endpoint must be reachable, the requested service available, and the credentials acceptable.
Local naming is not required for every Oracle client connection. For the simple TCP example above, an Easy Connect string can identify the same destination without the alias:
sqlplus report_user@//remote-db.example.com:1521/salespdb.example.com
This comparison separates naming from connectivity. If the explicit endpoint works but REMOTE_SVC does not, compare the alias and the effective local naming configuration. Do not conclude immediately that the database is unavailable. Different drivers also have their own configuration capabilities; verify what the application actually uses rather than assuming it follows a separate SQL*Plus installation.
Multiple Oracle installations can leave several files named tnsnames.ora on one computer. Editing a file is useful only if the connection process reads it. Record the executable or driver, its environment, and its configuration directory before making a change.
Oracle's documented lookup considers TNS_ADMIN, followed by applicable Oracle-home configuration locations. Read-only Oracle homes use an Oracle base home configuration location, with documented fallback behavior. Windows services may obtain configuration from a different environment or registry setting than an interactive terminal. Instant Client also has its own installation layout.
For this reason, do not treat a single hard-coded directory as correct for every platform. Consult the installed client's documentation and inspect its effective environment. TNS_ADMIN identifies a configuration directory, not the alias being resolved. A copied file in an unrelated Oracle home does not automatically become active.
The sqlnet.ora profile can specify naming-method preferences through NAMES.DIRECTORY_PATH. The naming file and the profile have different jobs: one supplies alias mappings, while the other can govern how those mappings are sought. Neither file replaces the listener process or creates the requested service.
The HOST value can be a host name or an IP address. With a name such as remote-db.example.com, resolution follows the operating system's configured mechanisms, commonly DNS and possibly a hosts file. It is inaccurate to describe every connection as a lookup in a UNIX hosts file.
A stable host name can reduce client-file changes when an endpoint's IP address changes, provided the naming infrastructure is updated correctly. It does not automatically relocate the database, register a service, or open a firewall port. An IP address avoids host-name lookup for that address, but it also embeds that endpoint directly in the descriptor.
Keep host resolution separate from service resolution. DNS can find the machine without knowing whether its listener recognizes salespdb.example.com. Similarly, a correct service name cannot compensate for a hostname that resolves to the wrong machine. Testing these stages independently makes connection errors easier to interpret.
The following two diagrams show a database-link connection using local naming. An application is already connected to an originating database and issues SQL that references another database:
SELECT * FROM customer@rdb;
Here, rdb is a database-link name, not the alias from tnsnames.ora. Assume an existing accessible link whose connection identifier is REMOTE_SVC, with appropriate remote authentication and object privileges. The wildcard keeps the illustration compact; application queries should normally request the columns they need.
The originating database becomes an Oracle Net client for this outgoing connection. Therefore, its naming environment must resolve REMOTE_SVC. Putting that alias only on an application workstation does not make it available to the database server. This distinction explains why a direct workstation login can succeed while a database-link query fails.
Database-link definitions and local naming entries are maintained separately. Views such as USER_DB_LINKS, ALL_DB_LINKS, and DBA_DB_LINKS expose link metadata according to privileges. They should not be presented as a table returning a readable password. Authentication depends on the link type; a fixed-user example is not a description of every possible database link.
The listener arranges the connection to an appropriate service handler; it does not execute the query. Avoid imagining a single packet that contains an IP address, SID, username, and plaintext password. Connection establishment, authentication, and SQL execution involve distinct mechanisms. The picture simplifies those stages rather than documenting network packet fields.
Actual link naming must also comply with database settings, including GLOBAL_NAMES when enabled. This illustration assumes the link already exists and is valid. It does not require changing that setting, creating a public link, or publishing credentials. Ordinary direct client connections do not need a database link merely because the database is remote.
From the relevant client environment, a useful initial diagnostic is:
tnsping REMOTE_SVC
TNSPING helps test naming resolution and listener reachability. It does not establish that the database is open for the intended account, that a password is valid, or that a user can query customer. Treat its result as evidence about one stage, then perform an authorized login test and a suitable query separately.
For a database-link problem, apply this reasoning to the originating database environment. Preserve the full error text and identify where the test ran. Two tests with the same alias spelling can resolve differently when they read different files.
An organization may distribute a controlled master copy of local naming entries, but that is an operational choice, not a requirement that every Oracle machine contain the same file. Directory naming and other supported centralized methods can reduce the need to distribute individual local files. Choose an approach that matches the clients and the administrative environment.
When maintaining local files, keep a record of each alias's intended application, environment, endpoint, and service. Review changes, retain the previous version, and test from the clients that will use the update. A working test on an administrator's laptop does not verify a scheduled job running under a different service account.
Use comments to explain a mapping's purpose, not to store passwords. Avoid duplicate or ambiguous aliases within a managed configuration. If a service moves, coordinate the destination change with application owners and test new connections. Existing sessions are not automatically moved merely because someone edits an alias.
Oracle Net supports descriptors with multiple addresses and additional connection behavior. These features can support appropriate connection-time failover or address selection, but adding extra addresses does not replicate a database or guarantee application-session recovery. Learn the simple address/service distinction before applying availability options.
Security is also separate from naming convenience. The TCP examples here do not establish that traffic is encrypted. TLS configurations use the relevant transport and trust settings; other Oracle network security mechanisms have their own policies. Use the deployment's approved connection details rather than treating a teaching example as a complete security configuration.
Case 1: The alias fails, but Easy Connect works. First confirm that both tests use the same application installation, account, host, port, and service. If they do, examine the local alias and configuration search location. An alias could be missing, misspelled, or mapped to an older destination. The successful explicit connection is useful evidence that the tested endpoint can accept a session; it does not prove that every alias on the machine is correct.
Case 2: A workstation connects, but the database link fails. Identify the database that executes the link query. Its outgoing connection can use a different configuration directory and network path. Compare the link's connection identifier with the alias available to that environment. Then distinguish a naming error from an authentication or privilege error. Copying an entry to the workstation again cannot repair a missing server-side mapping.
Case 3: The host is reachable, but the service is unknown. Host reachability establishes only that part of the path. Compare the requested SERVICE_NAME with what the listener currently knows, and check the database or PDB service state. A correctly spelled alias can still contain the wrong service value. Changing the alias's display name without changing its descriptor would leave that problem unresolved.
Case 4: Two applications use the same alias but connect differently. Compare their effective configuration rather than assuming one shared file. Different client homes, service environments, or driver settings can explain the discrepancy. Record the resolved endpoint and service for each application. Once you identify the intended source of configuration, update it through the established distribution process and verify the applications independently.
These examples reinforce the same method: identify the connection origin, resolve the naming information, then test the destination and database access. Keep each successful test within its limits. A naming-file correction is complete when the intended client resolves the intended descriptor and an authorized connection test confirms the required service, not merely when the edited text looks plausible.
A tnsnames.ora entry maps a client alias to a connect descriptor. Read ADDRESS for the endpoint and CONNECT_DATA for the requested service. Keep alias, host, service, SID, and database-link names distinct. Verify which environment performs the lookup, especially when a database initiates a connection through a link.
The next lesson covers TCP settings and access control in sqlnet.ora, including how these settings relate to legacy protocol.ora guidance.