Sample tnsnames.ora Dedicated Server Entry (with ASM)

calendar May 1, 2026 / · 5 min read · Oracle Database SQL*Net tnsnames.ora Oracle Net Oracle DBA Database Connectivity ASM TNS  ·
Share on: linkedin copy

Sample tnsnames.ora Dedicated Server Entry (with ASM)

Purpose

The tnsnames.ora file is the client-side address book for Oracle Net. Each entry maps a short alias (SCR9) to a full network address — protocol, host, port, and target service. Without a working tnsnames.ora entry, sqlplus, rman, and any application using OCI cannot reach the database.

The two entries below — taken straight from the shutdownabort.com DBA Quick Guides (Andrew Barry, 2007–2013, preserved via the Wayback Machine as the anchor source for this post) — are the smallest possible reference: one dedicated-server connection to a database service, and one connection to a non-default listener used by ASM.

Code

 1SCR9 =
 2  (DESCRIPTION =
 3    (ADDRESS_LIST =
 4      (ADDRESS = (PROTOCOL = TCP)(HOST = bloo)(PORT = 1521))
 5    )
 6    (CONNECT_DATA =
 7      (SERVER = DEDICATED)
 8      (SERVICE_NAME = scr9)
 9    )
10  )
11
12LISTENER_+ASM =
13  (ADDRESS = (PROTOCOL = TCP)(HOST = bloo)(PORT = 1522))

Breakdown of Code

The first entry (SCR9) is a full DESCRIPTION block. It is what most clients use.

  • SCR9 = — the alias. Use this name on the command line: sqlplus scott/tiger@SCR9.
  • (DESCRIPTION = ...) — wraps the whole connection definition. Required.
  • (ADDRESS_LIST = (ADDRESS = ...)) — one or more network endpoints. The ADDRESS_LIST wrapper is needed when you list more than one address (for failover or load balancing); for a single address, the ADDRESS line on its own is also accepted by current clients.
  • (PROTOCOL = TCP) — the network protocol. Almost always TCP today; TCPS for TLS, IPC for same-host inter-process.
  • (HOST = bloo) — the listener host. Use a hostname or IP. For RAC, use the SCAN name.
  • (PORT = 1521) — the listener port. 1521 is the historic default.
  • (CONNECT_DATA = ...) — what to ask the listener for once connected.
  • (SERVER = DEDICATED) — request a dedicated server process. The other option is SHARED, which uses the shared-server (formerly multi-threaded server) infrastructure if it is configured on the target.
  • (SERVICE_NAME = scr9) — the database service to connect to. This is the modern alternative to SID; a single instance can register many service names.

The second entry (LISTENER_+ASM) is an address-only form. It has no DESCRIPTION wrapper, no CONNECT_DATA, just an ADDRESS. This shape is used when a server-side process needs to know where a listener lives, not which database to ask for. Oracle uses it for LOCAL_LISTENER registration: an ASM instance reads this entry to find the listener it should register with on port 1522.

How It Works

When a client runs sqlplus user/pwd@SCR9, the Oracle Net layer:

  1. Searches for tnsnames.ora in $TNS_ADMIN, then $ORACLE_HOME/network/admin, then a few historic locations.
  2. Resolves the alias SCR9 to the full DESCRIPTION block.
  3. Opens a TCP socket to bloo:1521 and asks the listener on that port for the service named scr9.
  4. The listener spawns (or hands off to) a dedicated server process and returns it to the client.
  5. The client's TCP session is now bound to that dedicated server process for the life of the session.

SERVICE_NAME versus SID:

  • SERVICE_NAME is what the database registers with the listener at startup (via the SERVICE_NAMES init parameter and PMON's dynamic registration). Multiple services can map to one instance, and one service can be served by many instances (RAC). Always prefer SERVICE_NAME on new entries.
  • SID is the older form: it names a specific instance (the value of ORACLE_SID). It still works for single-instance databases but does not support service-based load balancing or RAC.

Key Points

  • tnsnames.ora lives in $TNS_ADMIN — set this environment variable on every client. Without it, Oracle falls back to $ORACLE_HOME/network/admin, which is fine on the database server but rarely correct on a separate client host.
  • Aliases are case-insensitive — SCR9, scr9, and Scr9 all resolve to the same entry.
  • Indentation does not matter to the parser — but it does matter to the next DBA who reads the file. Use consistent indentation.
  • Service names are case-sensitive in some clients — the safe rule: match the exact case the database registered, which lsnrctl services will show.

Smoke Test with tnsping

Before handing a tnsnames.ora entry to an application team, confirm it resolves and the listener answers:

1$ tnsping SCR9
2
3TNS Ping Utility for Linux: Version 21.0.0.0.0
4Used parameter files:
5Used TNSNAMES adapter to resolve the alias
6Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = bloo)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = scr9)))
7OK (10 msec)

tnsping only confirms the listener is reachable and that the alias parses. It does not confirm the service is available, the database is open, or your credentials work. Use sqlplus user/pwd@SCR9 for end-to-end validation.

Common Variations

Add a second address for listener failover:

 1SCR9 =
 2  (DESCRIPTION =
 3    (ADDRESS_LIST =
 4      (ADDRESS = (PROTOCOL = TCP)(HOST = bloo1)(PORT = 1521))
 5      (ADDRESS = (PROTOCOL = TCP)(HOST = bloo2)(PORT = 1521))
 6      (LOAD_BALANCE = yes)
 7      (FAILOVER = yes)
 8    )
 9    (CONNECT_DATA =
10      (SERVER = DEDICATED)
11      (SERVICE_NAME = scr9)
12    )
13  )

Use SID instead of SERVICE_NAME on a single-instance database with no service registration:

1SCR9 =
2  (DESCRIPTION =
3    (ADDRESS = (PROTOCOL = TCP)(HOST = bloo)(PORT = 1521))
4    (CONNECT_DATA = (SID = scr9))
5  )

Important Considerations

tnsnames.ora is just the resolution layer. Even a perfect entry will fail if the listener is down (lsnrctl status), if the database has not registered the service (SHOW PARAMETER service_names), or if a firewall blocks port 1521 between client and server. Diagnose in that order: alias parses (tnsping), listener answers (lsnrctl status), service is up (lsnrctl services), end-to-end auth works (sqlplus).

The original entries above target Oracle 10gR2-era hostnames and ports; the syntax has not changed in 21c. Drop the entries into a current-version tnsnames.ora unchanged.

References

Posts in this series