TNS_ADMIN – which *.ora files Oracle will use

Introduction

Oracle uses some configuration files (*.ora) related to the network.

• tnsnames.ora: A mapping of an alias name to a connect string for Oracle. A kind of “/etc/hosts” for Oracle databases
• ldap.ora: Configuration of ldap servers to use. A kind of “/etc/resolv.conf” for Oracle products.
• sqlnet.ora: General network settings like domain, timeout-parameters, wallet-location and more.
• listener.ora: Configuration of the listeners

Location of the *.ora files used

Per default, Oracle will look for these files in $ORACLE_HOME/network/admin; If you have more than one ORACLE_HOME, then you have to maintain it for each home, or you can create symbolic links to the “master” file.

And if you use Readonly-Oracle-Home (“rooh”, roohctl -enable). That is a great feature to relocate all database- and configuration specific files from the software directory to the ORACLE_BASE. The dbs-directories of all homes will be centralized in $ORACLE_BASE/dbs, and the home-specific configuration- and log-files will be moved to the ORACLE_BASE/homes/homename. With rooh enabled, Oracle looks for the TNS configuration in $ORACLE_BASE/homes/homename/network/admin; homename is the name in the oraInventory. To show the current top-level directory, use

oracle>  orabasehome
/u00/app/oracle/homes/OraDB23Home4

Instead of the default, depending on the ORACLE_HOME, you can set the variable TNS_ADMIN in your environment to a central directory containing above *.ora files.

oracle>  export TNS_ADMIN=/u01/app/oracle/network/admin

If this variable is set, then Oracle uses this directory instead of the default $ORACLE_HOME/network/admin or orabasehome/network/admin.

In this context, “Oracle” means the binary you use. If $TNS_ADMIN is set in the shell where the binary is started, it takes the value of it to look for Oracle’s network files.

If you use sqlplus, then it looks for the *.ora files in $TNS_ADMIN to resolve the given connect alias of your connect string.

If you start the Oracle database-instance, then it uses the *.ora files in $TNS_ADMIN to resolve connect aliases for database links and data guard connects. If unset, it uses the default location as fallback.

In summary the priority of the TNS locations:

1. $TNS_ADMIN, if it is set
2. $ORACLE_BASE/homes/<home_name>/network/admin, if readonly-oracle_home is activated
3. $ORACLE_HOME/network/admin

Find out the TNS location that is used

tnsping

For sqlplus it is easy to find out what it will use. You can check it before start in your shell with tnsping:

oracle>  tnsping DB01

TNS Ping Utility for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 12-DEC-2025 09:16:10

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

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

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=...)

“Used parameter files” shows the directory it looks for the *.ora files.

If we start an instance interactively with sqlplus, we also know the location, as described above.

But caution: If you use Grid Infrastructrue (clusterware) and you start the instance with “srvctl”, then a TNS_ADMIN in your current shell is irrelevant. The instance is started by clusterware in background and it uses a minimal shell. It does not source the shell-profile of Oracle (.profile, .bash_profile), so probably your TNS_ADMIN is unknown to clusterware.

That means, if a database is started interactively or via clusterware, it may behave differently. Maybe with one method db-links are working, with the other not.

Best practice: Alwas start database instances with the same mechanism. If using clusterware, always use srvctl to start it.

But if we have issuses with connects of db-links or Data Guard, how we can find out what directory the instance uses to look for the network files?

OS-tools

On the database server, we can find it out by OS tools:

Get the process-ID of an instance-process, e.g. pmon (ps -ef |grep pmon_$ORACLE_SID)

Then, for this process-ID we can show the environment-variables of this process (ps eww ID) and look for TNS_ADMIN. If we do not find it, TNS_ADMIN is the default $ORACLE_HOME/network/admin or $ORACLE_BASE/homes/homename/network/admin

ps eww `ps -ef |grep -v grep|grep pmon_$ORACLE_SID|awk '{print $2}'`  \
   | grep TNS_|sed -e 's/.*TNS_ADMIN=//' |sed -e 's/ .*$//g'

Caution! On Linux, often it does not work for the process owner oracle, even if /proc/<pid>/environment shows readable for Oracle. If $ORACLE_HOME/bin/oracle has the suid-bit (-rws-s–x), the kernel blocks the access. In this case, you need root privileges to run above command. If the 1st line (without \) does not return any variables, then you are affected by this security limitation.

Inside the database

But, can we find it out by connecting to the database by running the following code?

set serveroutput on;
DECLARE
  V_ENVIROMENT VARCHAR2(500);
BEGIN
  SYS.DBMS_SYSTEM.GET_ENV('TNS_ADMIN', V_ENVIROMENT);
  DBMS_OUTPUT.PUT_LINE('TNS_ADMIN='||V_ENVIROMENT);
END;
/

Unfortunately not. The Oracle process connecting the database inherits the environment of our environment. For a direct connect (/ as sysdba) it is our shell-environment, for a remote-connect (@sqlnet-alias) it is the environment of the listener. This way we get the TNS_ADMIN environment of our process, and not that of the server processes.

But how can we get the environment of the running server processes? For this we need a little trick: We make the query via a DBMS_SCHEDULER job. This means that it is executed via a server process.

 DBMS_SCHEDULER.create_job (
      job_name   => 'TNS_ADMIN',
      job_type   => 'PLSQL_BLOCK',
      job_action => q'[
        DECLARE
          v_tns_admin VARCHAR2(4000);
        BEGIN
          DBMS_SYSTEM.GET_ENV('TNS_ADMIN', v_tns_admin);
          DBMS_OUTPUT.PUT_LINE(v_tns_admin);
        END;
      ]',
      start_date => SYSTIMESTAMP,
      enabled    => TRUE,
      auto_drop  => TRUE
    );

The result can be found in ALL_SCHEDULER_JOB_RUN_DETAILS. Relevant are only records after startup time, because the environment can only be changed at startup. That’s why we should get exactly 1 result with “distinct”.

select distinct output from ALL_SCHEDULER_JOB_RUN_DETAILS 
where job_name='TNS_ADMIN' 
and log_date > (select startup_time from v$instance);

An empty result means that the TNS_ADMIN variable was not set and the database is using the default (see above).

Debugging name resolution problems

Next time, if you get errors using a database link in a scheduled job (using the server processes environment):

ORA-12154: TNS:could not resolve the connect identifier specified

Check what the database link will connect to (e.g. DB01.domain) and what TNS_ADMIN (or default-value) your database is using.

For Data Gurad debugging it is not so easy. For the primary site above trick works, but not on the standby site. It is mounted or read-only. If you have root-access, you can check the environment file of the process (see above). Otherwise I recommend to restart the standby database, then you know what TNS_ADMIN was set.