Oracle TNS_ADMIN: Proper Usage and Configuration Steps [Linux/19c]

English

Introduction (Summary)
When you want to centrally manage Oracle connection settings, use the TNS_ADMIN environment variable. This article explains, for oracle clients/servers, how to set TNS_ADMIN to switch the lookup location of tnsnames.ora, sqlnet.ora, and, if necessary, listener.ora. The guidance targets Linux and Oracle Database 19c, with clear steps for beginners to intermediate users.

OracleNetを使ったリモート接続の手順：初心者向け解説
Oracleデータベースを使い始めると、「データベースにどうやって接続するか」という課題に直面します。その中で「OracleNet」という仕組みは、データベースと外部から通信するために重要な役割を果たします。この記事では、初心者向けにOra...
oracle-master00000.com

Conclusion & Quick Steps (To-Do List)

  1. Decide a shared directory (e.g., /u01/app/oracle/network/admin_global).
  2. Set TNS_ADMIN in the Oracle OS user’s environment (recommend .bash_profile).
  3. Place tnsnames.ora / sqlnet.ora under TNS_ADMIN (and listener.ora if needed).
  4. Verify with tnsping <service_name> → sqlplus user/pass@<service_name>.
  5. If using a listener, check lsnrctl status / lsnrctl services for the parameter path and registered services.
  6. For issues, follow the read-only checks (below) to isolate causes → if changes are required, review impact and rollback.

Background & Basics: What is TNS_ADMIN?

What is TNS_ADMIN?
TNS_ADMIN is an environment variable that lets Oracle Net look for configuration files (tnsnames.ora, sqlnet.ora, and optionally listener.ora) in a directory of your choice, instead of the default $ORACLE_HOME/network/admin.

Lookup order (key points)

  • If TNS_ADMIN is set, that directory is used.
  • If not set, Oracle generally uses $ORACLE_HOME/network/admin (with some platform-specific exceptions).

Tip: Since 19c, Easy Connect Plus enables configurations where TNS_ADMIN may not be necessary. Evaluate whether a TNS-less connection style is sufficient before introducing file-based naming.

Text Diagram: Lookup Path at Connection Time

┌──────────────────────────────────┐
│  Config file lookup flow at connect time            │
├───────────────┬───────────────────────┤
│ ① TNS_ADMIN   │ /u01/app/oracle/network/admin_global  │ ← Priority
├───────────────┼───────────────────────┤
│ ② ORACLE_HOME │ $ORACLE_HOME/network/admin             │
└───────────────┴───────────────────────┘
※ If ① is unset/unavailable, ② is used.

Terminology notes:

  • tnsnames.ora: Local naming that maps service names to connect descriptors.
  • sqlnet.ora: Client-side profile settings for naming resolution, timeouts, etc.
  • listener.ora: Server-side listener configuration. lsnrctl also resolves via TNS_ADMIN.

Procedure & Implementation (Linux / 19c / non-CDB)

Prerequisites

  • OS: Oracle Linux 7/8 equivalent (generic Linux)
  • DB: Oracle Database 19c (non-CDB)
  • Oracle OS user: oracle (manual DB/Listener management)
  • Impact: Changes the lookup path for connection resolution / listener settings
  • Rollback: Comment out TNS_ADMIN in .bash_profile and re-login (or unset TNS_ADMIN)

1）Prepare the directory

  • Description: Create the shared location and set proper permissions.
# Shared lookup location
sudo mkdir -p /u01/app/oracle/network/admin_global
sudo chown -R oracle:oinstall /u01/app/oracle/network

2）Set TNS_ADMIN (oracle OS user)

  • Description: Append to .bash_profile so it is applied at login.
# Append to oracle's .bash_profile (example)
echo 'export TNS_ADMIN=/u01/app/oracle/network/admin_global' >> /home/oracle/.bash_profile
# Apply immediately
source /home/oracle/.bash_profile

3）Place sqlnet.ora

  • Description: Minimal example using TNSNAMES for naming resolution.
# /u01/app/oracle/network/admin_global/sqlnet.ora
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 60

4）Place tnsnames.ora

  • Description: Non-CDB example (assumes service name orcl).
# /u01/app/oracle/network/admin_global/tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

5）Place listener.ora (only if needed)

  • Description: Not required when dynamic registration suffices; needed for static listeners, custom logging, etc.
# /u01/app/oracle/network/admin_global/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.local)(PORT=1521)))
  )

# Optional log/trace controls
DIAG_ADR_ENABLED = ON

6）Connection test

  • Description: Verify naming with tnsping, then connect with sqlplus.
# Check TNS_ADMIN
echo "$TNS_ADMIN"
ls -l $TNS_ADMIN

# Name resolution
tnsping ORCL

# Connect (use a precreated test user)
sqlplus tns_test/"StrongPwd1"@ORCL

7）Check the listener (if in use)

  • Description: Confirm the parameter file and registered services used by lsnrctl.
lsnrctl status
lsnrctl services

In lsnrctl status, check Used parameter file:. If it points to the TNS_ADMIN path, the setting is in effect.

Command execution example

[oracle@orcl19c ~]$ echo "$TNS_ADMIN"  ★TNS_ADMIN not set

[oracle@orcl19c ~]$ ls -l $ORACLE_HOME/network/admin  ★tnsnames.ora does not exist
total 4
drwxr-xr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r--r--. 1 oracle oinstall 1536 Feb 13  2018 shrept.lst
[oracle@orcl19c ~]$ tnsping ORCL  ★error output

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 27-AUG-2025 10:31:55

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

TNS-03505: Failed to resolve name
[oracle@orcl19c ~]$ echo 'export TNS_ADMIN=/u01/app/oracle/network/admin_global' >> /home/oracle/.bash_profile  ★Set TNS_ADMIN
[oracle@orcl19c ~]$ source /home/oracle/.bash_profile
[oracle@orcl19c ~]$ vi /u01/app/oracle/network/admin_global/sqlnet.ora
[oracle@orcl19c ~]$ cat /u01/app/oracle/network/admin_global/sqlnet.ora
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 60
[oracle@orcl19c ~]$ vi /u01/app/oracle/network/admin_global/tnsnames.ora  ★Create tnsnames.ora
[oracle@orcl19c ~]$ cat /u01/app/oracle/network/admin_global/tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
[oracle@orcl19c ~]$ echo "$TNS_ADMIN"
/u01/app/oracle/network/admin_global  ★TNS_ADMIN is set
[oracle@orcl19c ~]$ tnsping ORCL  ★The result is returned

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 27-AUG-2025 10:30:29

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin_global/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl19c)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@orcl19c ~]$ sqlplus system/oracle@orcl  ★Connected

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 27 10:33:07 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN

Execution Example (Copy/Paste-Ready for Lab Use)

Goal: Create a minimal test user and connect to ORCL defined under TNS_ADMIN.
Assumptions: Logged in as a DB administrator (SYSDBA). In production, follow your privilege policy and drop unneeded users afterward.

-- ① Test user for lab use only
CREATE USER tns_test IDENTIFIED BY "StrongPwd1";
GRANT CREATE SESSION TO tns_test;

-- ② OS-level connection check
-- sqlplus tns_test/"StrongPwd1"@ORCL

Explanation: CREATE USER adds a user to the data dictionary. CREATE SESSION is the minimum privilege required for login. The connect string uses the ORCL entry in tnsnames.ora. (This example concerns network connectivity; indexes are unrelated.)

Impact / Risks / Rollback

  • Impact: All TNS-based clients/utilities may start reading configs from the new location.
  • Rollback: Comment out the line in .bash_profile and re-login (or unset TNS_ADMIN). Confirm the default path with lsnrctl status.
  • Note: In environments with a Grid Infrastructure-managed listener, environment variable management may differ. Avoid mixing TNS_ADMIN arbitrarily; define it per your operational design.

Troubleshooting (Read-Only Checks in Order)

Symptom / ErrorLikely CauseRead-Only Checks (Safe)
ORA-12154: TNS:could not resolveWrong service name / tnsnames.ora not loadedecho $TNS_ADMIN → ls -l $TNS_ADMIN/tnsnames.ora → tnsping <name>
ORA-12514: listener does not know...Not registered / service name mismatchCheck lsnrctl services for registration status
ORA-12541: TNS:no listenerListener down / port mismatchlsnrctl status / `netstat -lnp
Connects but slowDNS reverse lookup delay, etc.Review NAMES.DIRECTORY_PATH and ADR settings in sqlnet.ora; adjust name resolution
lsnrctl reads an unexpected fileTNS_ADMIN not set / started under different envIn lsnrctl status, check Used parameter file (does it point to TNS_ADMIN?)

Note: lsnrctl resolves listener.ora under TNS_ADMIN if set.

Operations, Monitoring, and Security Notes

  • Pros: Centralized management of config files across multiple ORACLE_HOME and tools; easy migration by copying during upgrades.
  • Cons: Change blast radius is wide since all components may reference the same configs. Enforce test → approval → production rollout.
  • Pitfalls: Processes started under different environment scopes (GI-managed listeners, OS services, etc.) may see a different TNS_ADMIN. Verify per startup owner.
  • Monitoring: Rotate listener.log / sqlnet.log (ADR). Prefer an actual connection health check over only tnsping.
  • Alternative (when applicable): Consider Easy Connect Plus to eliminate tnsnames.ora / sqlnet.ora where feasible.

Frequently Asked Questions (FAQ)

Q1. Should I set TNS_ADMIN per user?

A. Start with the Oracle OS user (oracle), validate behavior, then roll out minimally to application users. This limits impact.

Q2. Must listener.ora always reside under TNS_ADMIN?

A. Not required. If dynamic registration suffices, you may not need listener.ora at all. Place it only when needed (e.g., static registration, logging). Check Used parameter file in lsnrctl status to confirm the path.

Q3. What is the default lookup order?

A. Generally, TNS_ADMIN if set; otherwise $ORACLE_HOME/network/admin.

Q4. Is TNS_ADMIN obsolete now?

A. In 19c, Easy Connect Plus enables “no-file” operation for some scenarios. However, many environments still benefit from tnsnames.ora-based operations. Choose based on requirements.

Summary (Key Points)

  • TNS_ADMIN switches the lookup directory for Oracle Net configuration files.
  • First set it for the oracle OS user to limit impact, then expand after validation.
  • If dynamic registration suffices, listener.ora is unnecessary; verify with lsnrctl status/services.
  • Easy Connect Plus can remove the need for TNS_ADMIN in certain scenarios.

This article targets Oracle Database 19c (other versions may differ in screens and defaults).


[reference]
Oracle Database Net Services Administrator’s Guide, 19c

OracleNetを使ったリモート接続の手順：初心者向け解説
Oracleデータベースを使い始めると、「データベースにどうやって接続するか」という課題に直面します。その中で「OracleNet」という仕組みは、データベースと外部から通信するために重要な役割を果たします。この記事では、初心者向けにOra...
oracle-master00000.com

コメント

Copied title and URL