Network Topology   «Prev  Next»

Lesson 4 The tnsnames.ora file
Objective Describe the components and functions of the tnsnames.ora file.

Oracle 26ai tnsnames.ora: Components and Connection Examples

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.

A Basic tnsnames.ora Entry

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.

ComponentMeaning in this example
REMOTE_SVCThe local net service name used by the client.
DESCRIPTIONThe enclosing connect descriptor.
ADDRESSA protocol address for reaching the listener.
PROTOCOL = TCPThe transport used for this endpoint.
HOSTThe host name or address of the endpoint.
PORT = 1521The listener port used here, not a mandatory value for every installation.
CONNECT_DATAThe group containing database service identification information.
SERVICE_NAMEThe 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.

Alias, Service Name, and SID Are Different

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.

Using More Than One Net Service 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.

Connecting Directly from a Client

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.

Finding the Configuration File That Is Actually Used

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.

How Host Names Become Network Addresses

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.

Database Links: A Different Connection Origin

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.

Oracle 26ai database link RDB resolves to REMOTE_SVC, then to protocol, host, port, and service in tnsnames.ora.
Steps 1–3: The originating database resolves the database link and its connection identifier. In this local-naming example, REMOTE_SVC identifies the remote endpoint and service through tnsnames.ora. Link metadata views do not expose plaintext passwords.

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.

Host resolution leads to a TCP listener connection, service-handler selection, and authentication in the remote Oracle database.
Steps 4–7: The originating host resolves the destination using its configured resolver. Oracle Net reaches the listener and requests the database service. The remote database session authenticates and checks privileges before accessing data. This conceptual sequence does not represent one network packet.

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.

Testing the Mapping Without Overstating the Result

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.

  1. Cannot resolve the identifier: check the spelling, effective configuration location, naming-method settings, and descriptor syntax.
  2. Cannot reach the endpoint: check host resolution, network access, port number, and listener availability.
  3. Listener does not know the service: compare SERVICE_NAME with the service configuration and registration. Do not substitute a guessed SID.
  4. Login rejected: investigate authentication and account state rather than repeatedly editing the host address.
  5. Login succeeds but a query fails: check object names, schema resolution, and privileges in the connected database.

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.

Maintaining Aliases Across Sites

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.

Beyond the Basic Descriptor

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.

Worked Checks: Which Component Needs Attention?

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.

Lesson Summary

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.

Oracle 26ai References


SEMrush Software 4 SEMrush Banner 4