Enhance Skills Daily

October 03, 2026

Oracle DBA Lesson 36 Part 1 — Local Naming With tnsnames.ora

A local alias gives scripts and users a reusable connection name. The Oracle client resolves that name to a connect descriptor stored in its selected tnsnames.ora file. The descriptor supplies the listener address and the database service to request. The alias is a client-side name. The service is a database access identity.

Oracle DBA Lesson 36 Part 1 — Local Naming With tnsnames.ora

Build the mapping

In the tnsnames.ora selected by this client:

APP_CONN =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = dbhost)
      (PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = app_service))
  )

APP_CONN is the local alias. DESCRIPTION groups the saved connection instructions. ADDRESS locates the listener: TCP on dbhost, port 1521. CONNECT_DATA describes the requested database connection. SERVICE_NAME requests the registered app_service service. In this example that service belongs to the pluggable database APPDB. See tnsnames.ora parameters and file locations.

The alias can have a different name from the service or container. An alias label alone does not tell you which database it will reach. Inspect the descriptor, and then the established session. See identifying and accessing the database.

The relevant naming-order setting in the selected sqlnet.ora is:

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

This example tries local naming first and Easy Connect second. Preserve other settings required by your environment. Use the effective files for the process making the connection. TNS_ADMIN, when set, selects the configuration directory. Otherwise the applicable Oracle client home supplies its default network configuration location. Read-only Oracle homes and other client layouts have additional rules in the Oracle reference. See configuring naming methods.

Inspect resolution and reachability

From the Linux shell on that client:

tnsping APP_CONN

Inspect the actual output's naming adapter and resolved descriptor. Confirm the expected host, port, and requested service.

Used TNSNAMES adapter to resolve the alias
HOST = dbhost       PORT = 1521
SERVICE_NAME = app_service
OK

A successful response establishes listener reachability at the resolved address. Database authentication and the resulting session are separate checks. If resolution is unexpected, inspect the effective client configuration, naming order, and entry contents before changing the database server. See testing connections with TNSPING and SQL*Plus.

Connect and verify the session

sqlplus -L APP_USER@APP_CONN

Enter the account's password at the prompt. -L prevents another username/password prompt when the initial connection fails. Local naming supplies the descriptor for APP_CONN. The listener uses registered service information to direct connection setup to a suitable handler. After authentication, inspect the session:

SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') AS connected_service
FROM dual;

The expected container is APPDB, and the connected service is app_service. SHOW CON_NAME is a SQL*Plus command. See SQL*Plus SHOW. The SQL query asks the session environment for its service name. See SYS_CONTEXT. Read the results together and compare them with your intended destination. Actual service values can be domain-qualified.

If the results identify an unexpected destination, exit that session and investigate the selected mapping before doing database work.

Each client owns its local configuration

Changing a file on one machine does not update files on every machine that runs the script. Distribute the intended mapping through the client configuration process used in your environment. Test from each relevant client, including the environment in which a scheduled script actually runs.

Multiple listener addresses in a descriptor can support connect-time policies, such as trying another address while establishing a connection. These settings concern connection setup. Continuing database work after an established session fails, or replaying a transaction, requires the applicable client and database recovery features and configuration. Adding addresses alone does not supply transaction replay.

Recap

A local alias stores the listener address and the requested service in a connect descriptor. Confirm resolution and listener reachability with tnsping, then authenticate and compare the session container and connected service with the intended destination. Each client uses its own configuration. Two aliases can request the same service when their descriptors match.

Quiz

1. What does APP_CONN identify in this example?

2. Which fields locate the listener endpoint?

3. A successful tnsping APP_CONN response most directly establishes what?

4. Can two different local aliases refer to the same PDB service?

5. One client's alias was updated. Another client still resolves the previous descriptor. What should you inspect first?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...