A correct alias entry helps only when the client making the connection reads that entry. A laptop and a database server have separate configuration, and two Oracle homes on one machine can also use different files. Begin with the actual client process, then locate the file that owns the setting you want to change.
Oracle DBA Lesson 34 Part 1 — Edit the Configuration the Client Actually Uses
Distinguish the three files
| File | Purpose | Relevant owner/context |
|---|---|---|
sqlnet.ora |
Network profile, including naming-method order and other network settings. | The client or server process using that profile. |
tnsnames.ora |
Local aliases mapped to connection descriptors containing addresses and requested services. | The client resolving that alias. A database host making an outgoing connection can also act as a client. |
listener.ora |
Listener addresses and settings, including static service entries when configured. | The listener process on its host. |
The naming-method order is configured through NAMES.DIRECTORY_PATH in the relevant profile. For example, local naming uses a tnsnames.ora alias. Easy Connect supplies connection details through the connect string. Establish which method the client is using before investigating an alias file. See sqlnet.ora profile parameters.
Editing the server's tnsnames.ora changes its local client configuration. It does not distribute that change to a laptop. Similarly, listener configuration does not replace the client's alias entry. See listener.ora parameters.
Identify the actual client and launch environment
From the terminal that will launch the full client:
command -v sqlplus
sqlplus -V
printenv TNS_ADMIN
printenv ORACLE_HOME
command -v identifies the executable selected by the shell. sqlplus -V reports the client version. Compare the executable with the intended Oracle home and the libraries loaded for that client. An ORACLE_HOME value alone does not establish that the shell launched the matching executable. A shell function, wrapper, or executable from another installation needs its own inspection.
TNS_ADMIN is a directory, not the full path of tnsnames.ora. An exported value is inherited by a newly started client. A different terminal, background service, or application can have another environment. Changing your current shell does not retroactively change an already running process.
A launcher might use:
export TNS_ADMIN=/work/client-net
/opt/oracle/homeA/bin/sqlplus -V
/opt/oracle/homeA/bin/tnsping APP_CONNECT
Use the actual full-client executable and matching library configuration. Do not mix tools from different homes merely because both are available on the shell path.
Follow the applicable alias-file lookup
For the documented Oracle 19c local naming path:
- Look for
tnsnames.orain the directory selected byTNS_ADMIN. - If the variable is unset, or the file is absent there, follow the installation's default lookup.
- A regular read-write Oracle home uses
ORACLE_HOME/network/admin. - A read-only Oracle home uses
ORACLE_BASE_HOME/network/admin. If the alias file is absent there, it falls back toORACLE_HOME/network/admin.
See local naming and tnsnames.ora lookup.
An alias missing inside an existing file is a different condition from the file being absent. Do not assume the client automatically combines every home's alias file. Also inspect any IFILE includes. An entry can be supplied by an included file rather than the main file alone.
For a read-only home, the actual home's bin/orabasehome utility reports its Oracle base home. Oracle's read-only-home installation mode is more specific than a directory being mounted without write permission. See Oracle base home.
Instant Client has its own default network/admin subdirectory below the client library directory. TNS_ADMIN can select another directory. Do not assume its default is a regular full Oracle home's path, or that it ships the same diagnostic executables. See Instant Client configuration.
Java and GUI applications can use driver-specific properties, URLs, and connection settings. For JDBC Thin alias use, inspect the oracle.net.tns_admin property as applicable. See JDBC connection URLs and properties. Windows installations have additional documented environment and registry behavior.
The selected file
Suppose the client launches from full home A, and its environment contains:
TNS_ADMIN=/work/client-net
The directory contains tnsnames.ora, including APP_CONNECT, with no includes in this example. The client resolves that alias from this file. Copies under home A's default directory, home B's default directory, or the database server are outside this lookup.
A complete alias entry is:
APP_CONNECT =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = dbhost-old)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = app_service)))
The alias is the local name before =. ADDRESS supplies the listener endpoint. SERVICE_NAME is the requested database service. It is not automatically the PDB name or SID. SERVER=DEDICATED requests a dedicated server. Host-name resolution is an operating-system and network step after the alias has supplied the host name.
Interpret the probe evidence
Use TNSPING from the same full client home and launch environment:
/opt/oracle/homeA/bin/tnsping APP_CONNECT
Inspect these observations separately:
| Observation | Interpretation |
|---|---|
Used parameter files |
Profile configuration information, such as the selected sqlnet.ora when displayed. This is not conclusive proof of the exact alias-source path. |
TNSNAMES adapter |
The identifier was resolved through local naming. |
| Attempted connection descriptor | The resolved host, port, and service the probe is using. |
OK response |
The corresponding listener was reachable for this probe. |
Combine the observations with the applicable lookup rules and file inspection. A TNSPING response does not establish a successful database login, credentials, intended container, or application operation. See TNSPING and database connection testing.
Verify an isolated change
Change only HOST=dbhost-old to HOST=dbhost. Hold the executable, libraries, environment, alias, port, and service constant. Rerun the same probe.
The expected comparison is:
| Held constant | Before | After the isolated edit |
|---|---|---|
| Alias, client, and environment | APP_CONNECT, home A, private directory |
Same |
| Port and service | 1521, app_service |
Same |
| Resolved host | dbhost-old |
dbhost |
A changed resolved host supports that the selected alias edit affected resolution. If it remains unchanged, recheck the actual launch environment, naming method, selected file, alias, and includes. A listener failure can still show a useful resolved descriptor. Resolution and reachability are different observations.
Attempt the real session separately:
/opt/oracle/homeA/bin/sqlplus -L APP_USER@APP_CONNECT
SQL*Plus prompts for the password. The -L option prevents repeated prompting after an initial login failure. It is not a network timeout setting. After a successful login, the query checks the destination:
SELECT SYS_CONTEXT('USERENV','CON_NAME') container_name,
SYS_CONTEXT('USERENV','SERVICE_NAME') service_name,
SYS_CONTEXT('USERENV','SESSION_USER') session_user
FROM dual;
Compare the returned container, service, and user with the expected values. Then test the required operation under that account's grants. Record actual observations separately from expected results.
Does each PDB need a listener configuration file?
No. A listener can advertise services for many PDBs through dynamic service registration. A default listener can start without listener.ora, using documented default settings. It initially has no services until registration supplies them. An absent file alone does not establish failure. Custom addresses and other listener settings require their appropriate configuration and administration. See listener administration and dynamic registration.
Recap
Start with the client that makes the connection, then locate the file that owns the setting you want to change. Alias entries, naming-method order, and listener addresses belong to different files and different processes. Explain what the probe and the session test each establish.
No comments:
Post a Comment