dbi Blog

This blog post is a follow-up of TNS_ADMIN – which *.ora files Oracle will use. There, the main focus was to find out if a running database instance will use a TNS_ADMIN environment or the binary defaults.

In this blog post we will analyze much more the internal mechanisms to find the *.ora files, which offered a few surprises. You will see many tracing output. If you are only interested in the summary, scroll down to the end 😉

Local and remote connections

To connect to an Oracle database, you can do that locally

sqlplus appuser/password
sqlplus appuser    #asks for password
sqlplus / as sysdba

In this case, it connects locally to the current environment of ORACLE_HOME and ORACLE_SID. No listener is involved for that.

For a remote connection, you have to connect to a listener (defined by hostname and port), and specifying the desired SID or SERVICE_NAME for which the listener should start a process for you. The easiest form is EZCONNECT: host:port/service, but usually you will use an alias that will be resolved to the destination. These aliases are defined in the tnsnames.ora file or an ldap server specified in ldap.ora. For how to setup an ldap server, see my blog post Setup an LDAP server for Oracle naming on Enterprise Linux 9

MYAPP.DBI-SERVICES.COM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = PDBMYAPP.dbi-services.com)
    )
  )

To connect to that database remotely, you can use

sqlplus appuser/password@//host01:1521/pdbmyapp.dbi-services.com
sqlplus appuser/[email protected]
sqlplus appuser/password@myapp  #if names.default_domain=dbi-services.com

Location of the Oracle network configuration (*.ora files)

Without TNS_ADMIN

And where is sqlplus or tnsping looking for the configuration files? As an old, experienced administrator, I would expect it in the directory of the $TNS_ADMIN variable, or if unset, in $ORACLE_HOME/network/admin. Or if you are using the nice feature of the Read-Only ORACLE_HOME (ROOH) where configuration and logs are relocated to ORACLE_BASE (roohctl -enable), it will be in $ORACLE_BASE/homes/<home_name>/network/admin. I will call the directory as $DEFAULT_TNS and it points, depending if ROOH or non-ROOH, to the corresponding network/admin directory.

Hint: My home for the tests is such a ROOH home. If you have a non-ROOH home, simply set DEFAULT_TNS to $ORACLE_HOME/network admin.

Tracing with strace

But is my expectation of name resolution really correct? On Linux, we can see with the strace binary what the tool will look for:

strace sqlplus -S -L a/b@xyz 2>&1 |grep -E '\.ora'

I will use an alias that does not exist (@xyz). strace prints the output on STDERR, so I will redirect it to STDOUT (2>&1) and filter the whole output for “.ora”. I use the ORACLE_HOME=/u00/app/oracle/product/19.32.0 where no Oracle newtork configuration is present:

lstat("/u00/app/oracle/product/19.32.0/env.ora", {st_mode=S_IFREG|0644, st_size=852, ...}) = 0
openat(AT_FDCWD, "/u00/app/oracle/product/19.32.0/env.ora", O_RDONLY) = 3
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/.sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/intchg.ora", F_OK)         = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnav.ora", F_OK)         = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnames.ora", F_OK)       = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
openat(AT_FDCWD, "/u00/app/oracle/product/19.32.0/ldap/admin/fips.ora", O_RDONLY) = -1 ENOENT (No such file or directory)
openat(AT_FDCWD, "/u00/app/oracle/product/19.32.0/ldap/admin/fips.ora", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora", 0x7fff8d79e530) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.32.0/network/admin/ldap.ora", 0x7fff8d79e530) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/homes/OraDB19Home2/network/admin/ldap.ora", 0x7fff8d79e620) = -1 ENOENT (No such file or directory)

$ORACLE_HOME/env.ora : First surprise, a file that was not known to me. In this file you can set environment variables. Here you can set a TNS_ADMIN variable as a fallback if it is not yet set in the environment.
/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora: The next one is the $DEFAULT_TNS location which is expected for me
/home/oracle/.sqlnet.ora : Did you know that it looks for a “.sqlnet.ora” (mind the dot!) in your home-directory?
intchg.ora / tnsnav.ora : seems to be related to network “communities” and routing, not naming resolution.
/home/oracle/.tnsnames.ora : It also looks for a .tnsnames.ora (mind the dot!) in the user’s home.
/etc/tnsnames.ora : I have never seen that before …
/u00/app/oracle/homes/OraDB19Home2/network/admin/tnsnames.ora : expected $DEFAULT_TNS location
fips.ora : Related to cryptography (US government Federal Information Processing Standards)
/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora : It is a home configured for ROOH. Why it looks here? Probably a bug…
/u00/app/oracle/homes/OraDB19Home2/network/admin/ldap.ora : That is what I expected.

Summary: 1st, it looks for an sqlnet.ora (to determine name resolution order and default domain); Then, it looks for a tnsnames.ora, then for an ldap server

Next, I will create a /home/oracle/.sqlnet.ora and trace what happens:

$ strace sqlplus -S -L a/b@xyz 2>&1 |grep -E '\.ora'
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/.sqlnet.ora", F_OK) = 0
openat(AT_FDCWD, "/home/oracle/.sqlnet.ora", O_RDONLY) = 6
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)

It opens my .sqlnet.ora, but it also tries to open the sqlnet.ora of the $DEFAULT_TNS. I assume it will use it as fallback for parameters not present in $HOME/.sqlnet.ora; let’s try:

Create a test configuration

I create a configuration with

# /u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora
names.default_domain = dbi-services.com
SQLNET.EXPIRE_TIME = 10

# /home/oracle/.sqlnet.ora
names.default_domain = dbi.internal
SQLNET.EXPIRE_TIME = 10

#  /u00/app/oracle/homes/OraDB19Home2/network/admin/
DBI.dbi-services.com = ...

# /home/oracle/.tnsnames.ora
EXT.dbi-services.com = ...
INT.dbi.internal = ...

Then, I try to connect:

sqlplus a/b@EXT  # ORA-12154: TNS:could not resolve the connect identifier specified
sqlplus a/b@INT   # it can resolve it and connects (default_domain of $HOME/.sqlnet.ora)

The domain of $HOME/.sqlnet.ora is used. If I remove the default domain from the $HOME/.sqlnet.ora:

sqlplus a/b@EXT  # it can resolve it and connects (using default_domain of $DEFAULT_TNS/sqlnet.ora)
sqlplus a/b@INT   # ORA-12154: TNS:could not resolve the connect identifier specified

That means, values in $HOME/.sqlnet.ora will override values of the default $DEFAULT_TNS/sqlnet.ora. The same applies to $HOME/.tnsnames.ora, it overrides entries in the default tnsnames.ora

And what about the /etc/tnsnames.ora? Create it:

# /etc/tnsnames.ora
ETC.dbi-services.com = ...

sqlplus a/[email protected]
sqlplus a/[email protected] 
sqlplus a/[email protected]   # ORA-12154: TNS:could not resolve the connect identifier specified
sqlplus a/b@ETC

If it is present, the default tnsnames.ora in the $DEFAULT_TNS is ignored (it will not find the EXT.dbi-services.com), but the $HOME/.tnsnames.ora has precedence (it can resolve INT.dbi.internal). And we can also see that the default-domain for “@ETC” is taken from the $DEFAULT_TNS/sqlnet.ora (in $HOME/.sqlnet.ora it is not defined).

With TNS_ADMIN

TNS_ADMIN is the variable to explicitly specify the location of the *.ora configuration files. If it is not set in your shell, Oracle looks for it in $ORACLE_HOME/env.ora;
It is usually used to centralize the configuration for all ORACLE_HOME’s in one directory. We recommend ${ORACLE_BASE}/network/admin.

First, I will use an empty TNS_ADMIN directory with no *.ora files

mkdir /tmp/empty
export TNS_ADMIN=/tmp/empty
strace sqlplus -S -L a/b@abc 2>&1 |grep -E "\.ora"|grep -vE 'tnsnav|intchg|fips'

lstat("/u00/app/oracle/product/19.32.0/env.ora", {st_mode=S_IFREG|0644, st_size=852, ...}) = 0
openat(AT_FDCWD, "/u00/app/oracle/product/19.32.0/env.ora", O_RDONLY) = 3
access("/tmp/empty/sqlnet.ora", F_OK)   = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora", F_OK) = 0
openat(AT_FDCWD, "/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora", O_RDONLY) = 6
access("/home/oracle/.sqlnet.ora", F_OK) = 0
openat(AT_FDCWD, "/home/oracle/.sqlnet.ora", O_RDONLY) = 6
access("/tmp/empty/sqlnet.ora", F_OK)   = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora", F_OK) = 0
openat(AT_FDCWD, "/u00/app/oracle/homes/OraDB19Home2/network/admin/sqlnet.ora", O_RDONLY) = 8
access("/home/oracle/.tnsnames.ora", F_OK) = 0
access("/tmp/empty/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnames.ora", F_OK)       = 0
stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=716, ...}) = 0
openat(AT_FDCWD, "/home/oracle/.tnsnames.ora", O_RDONLY) = 9
stat("/etc/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=579, ...}) = 0
openat(AT_FDCWD, "/etc/tnsnames.ora", O_RDONLY) = 9
stat("/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora", 0x7ffde1f5b730) = -1 ENOENT (No such file or directory)
stat("/tmp/empty/ldap.ora", 0x7ffde1f5b730) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.32.0/network/admin/ldap.ora", 0x7ffde1f5b730) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/homes/OraDB19Home2/network/admin/ldap.ora", 0x7ffde1f5b820) = -1 ENOENT (No such file or directory)
stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=716, ...}) = 0
stat("/etc/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=579, ...}) = 0
...

If TNS_ADMIN is set, but the directory is empty, it will fallback to the order as above.
Now I will set it to a directory with an sqlnet.ora file and no tnsnames.ora, and I delete the /etc/tnsnames.ora:

mkdir /tmp/admin; cp $ORACLE_BASE/network/admin/sqlnet.ora /tmp/admin/
export TNS_ADMIN=/tmp/admin
strace sqlplus -S -L a/b@abc 2>&1 |grep -E 'tnsnames|sqlnet'

access("/tmp/admin/sqlnet.ora", F_OK)   = 0
openat(AT_FDCWD, "/tmp/admin/sqlnet.ora", O_RDONLY) = 6
access("/home/oracle/.sqlnet.ora", F_OK) = 0
openat(AT_FDCWD, "/home/oracle/.sqlnet.ora", O_RDONLY) = 6
access("/tmp/admin/sqlnet.ora", F_OK)   = 0
openat(AT_FDCWD, "/tmp/admin/sqlnet.ora", O_RDONLY) = 8
access("/home/oracle/.tnsnames.ora", F_OK) = 0
access("/tmp/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnames.ora", F_OK)       = -1 ENOENT (No such file or directory)
access("/u00/app/oracle/homes/OraDB19Home2/network/admin/tnsnames.ora", F_OK) = 0
stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=716, ...}) = 0
openat(AT_FDCWD, "/home/oracle/.tnsnames.ora", O_RDONLY) = 12
stat("/u00/app/oracle/homes/OraDB19Home2/network/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=432, ...}) = 0
openat(AT_FDCWD, "/u00/app/oracle/homes/OraDB19Home2/network/admin/tnsnames.ora", O_RDONLY) = 12
stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=716, ...}) = 0
stat("/u00/app/oracle/homes/OraDB19Home2/network/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=432, ...}) = 0
...

It takes the $TNS_ADMIN/sqlnet.ora and ignores the $DEFAULT_TNS/sqlnet.ora. It will not find the $TNS_ADMIN/tnsnames.ora (does not exist) and not the /etc/tnsnames.ora (I removed it before), so it uses the default in $DEFAULT_TNS.

For the next try I will add the tnsnames.ora to the TNS_ADMIN.

cp $ORACLE_BASE/network/admin/tnsnames.ora /tmp/admin
export TNS_ADMIN=/tmp/admin
strace sqlplus -S -L a/b@abc 2>&1 |grep -E 'tnsnames|sqlnet'

access("/tmp/admin/sqlnet.ora", F_OK)   = 0
openat(AT_FDCWD, "/tmp/admin/sqlnet.ora", O_RDONLY) = 6
access("/home/oracle/.sqlnet.ora", F_OK) = 0
openat(AT_FDCWD, "/home/oracle/.sqlnet.ora", O_RDONLY) = 6
access("/tmp/admin/sqlnet.ora", F_OK)   = 0
openat(AT_FDCWD, "/tmp/admin/sqlnet.ora", O_RDONLY) = 8
access("/home/oracle/.tnsnames.ora", F_OK) = 0
access("/tmp/admin/tnsnames.ora", F_OK) = 0
stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=716, ...}) = 0
openat(AT_FDCWD, "/home/oracle/.tnsnames.ora", O_RDONLY) = 12
stat("/tmp/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=11319, ...}) = 0
openat(AT_FDCWD, "/tmp/admin/tnsnames.ora", O_RDONLY) = 12
read(12, "# tnsnames.ora Network Configura"..., 4096) = 4096
stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=716, ...}) = 0
stat("/tmp/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=11319, ...}) = 0
...

As expected, it finds the $TNS_ADMIN/tnsnames.ora and it no longer looks for it in the $DEFAULT_TNS/tnsnames.ora. And this was not quite clear to me: it will no longer look for the /etc/tnsnames.ora file. It is only used if TNS_ADMIN is NOT set!

ldap.ora

And what about the LDAP connections (defined in ldap.ora)? Currently there is no ldap.ora present. Without TNS_ADMIN:

stat("/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora", 0x7ffdb464fd30) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.32.0/network/admin/ldap.ora", 0x7ffdb464fd30) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/homes/OraDB19Home2/network/admin/ldap.ora", 0x7ffdb464fe20) = -1 ENOENT (No such file or directory)

“Works as designed” or a bug: it first looks in $ORACLE_HOME, instead of the ROOH admin direcory.

And with TNS_ADMIN set, but without ldap.ora

stat("/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora", 0x7ffcb23acdb0) = -1 ENOENT (No such file or directory)
stat("/tmp/admin/ldap.ora", 0x7ffcb23acdb0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.32.0/network/admin/ldap.ora", 0x7ffcb23acdb0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/homes/OraDB19Home2/network/admin/ldap.ora", 0x7ffcb23acea0) = -1 ENOENT (No such file or directory)

Now I set in TNS_ADMIN the names.directory_path=(LDAP) and add the ldap.ora file

stat("/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora", 0x7ffdb55680b0) = -1 ENOENT (No such file or directory)
stat("/tmp/admin/ldap.ora", {st_mode=S_IFREG|0644, st_size=500, ...}) = 0
stat("/tmp/admin/ldap.ora", {st_mode=S_IFREG|0644, st_size=500, ...}) = 0
openat(AT_FDCWD, "/tmp/admin/ldap.ora", O_RDONLY) = 12
...

And it works! But what if I create an ldap.ora in the ORACLE_HOME? I create it with an invalid ldap host.

stat("/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora", {st_mode=S_IFREG|0644, st_size=501, ...}) = 0
stat("/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora", {st_mode=S_IFREG|0644, st_size=501, ...}) = 0
openat(AT_FDCWD, "/u00/app/oracle/product/19.32.0/ldap/admin/ldap.ora", O_RDONLY) = 12

We can see that it only uses the $ORACLE_HOME/ldap/admin/ldap.ora, and not the $TNS_ADMIN/ldap.ora! For me, that is a bug. For all other *.ora files the TNS_ADMIN variable is respected, but not for ldap.ora!

Summary

To resolve an sql*net alias, which configuration files sqlplus will use?

My stone-age knowledge that the *.ora files in $TNS_ADMIN are used, and if TNS_ADMIN is not set, then it uses the files in the $DEFAULT_TNS. That’s mostly correct (or at least not completely wrong), but there are some differences. My most important findings:

If a file in TNS_ADMIN does not exist, it looks in other locations. If a file .sqlnet.ora or .tnsnames.ora (with leading dot) in $HOME exists, the normal *.ora files will be used, but entries in the dot files has precedence. And if there is a file /etc/tnsnames.ora and TNS_ADMIN is not set, it will use it. And it ignores a $TNS_ADMIN/ldap.ora if there is one in $ORACLE_HOME/ldap/admin/.

sqlnet.ora (Network defaults)

The relevant parameters for name resolution are NAMES.DEFAULT_DOMAIN and NAMES.DIRECTORY_PATH.
It uses the sqlnet.ora in:

$HOME/.sqlnet.ora : (mind the dot!): if it exists, the following files will be used, but values in this file has precedence.
$TNS_ADMIN/sqlnet.ora : It uses this file and stops. If it does not exist or TNS_ADMIN is not defined, then continue with the next point:
$default_tns/sqlnet.ora

tnsnames.ora

$HOME/.tnsnames.ora : (mind the dot!): if it exists, the following files will be used, but aliases in this file has precedence.
/etc/tnsnames.ora : if TNS_ADMIN is NOT set! It uses this file and stops
$TNS_ADMIN/tnsnames.ora : if TNS_ADMIN is set and tnsnames.ora is present, it uses it and stops; if the file is not present, it continues with the next point.
$default_tns/tnsnames.ora

ldap.ora

$ORACLE_HOME/ldap/admin/ldap.ora : if it exists, It uses this file and stops (TNS_ADMIN is ignored!)
$TNS_ADMIN/ldap.ora : if TNS_ADMIN is set and ldap.ora is present, it uses it and stops; if the file is not present, it continues with the next point.
$default_tns/ldap.ora

These are the findings with an sqlplus of version 19.32.0; For other releases/versions it is probably the same, but you can simply verify it yourself with the strace utility.

For Oracle 26ai (23.26) it is a litte bit easier to see what tnsnames.ora is used

SQL*Plus: Release 23.26.1.0.0 - Production on Wed Oct 28 14:10:19 2026
Version 23.26.1.0.0

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

ERROR:
ORA-12154: Cannot connect to database. Cannot find alias db1970 in
/home/oracle/.tnsnames.ora,
/u00/app/oracle/product/23/23.26/network/admin/tnsnames.ora.
Help: https://docs.oracle.com/error-help/db/ora-12154/


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
Help: https://docs.oracle.com/error-help/db/sp2-0751/

or with an ldap configuration, you will see that it tried it, but not which ldap.ora was used (so strace may be still needed).

ORA-12154: Cannot connect to database. Cannot find alias db1970 in
/home/oracle/.tnsnames.ora, /tmp/admin/tnsnames.ora and LDAP.

Name resolution of Oracle

Local and remote connections

Location of the Oracle network configuration (*.ora files)

Without TNS_ADMIN

Tracing with strace

Create a test configuration

With TNS_ADMIN

ldap.ora

Summary

sqlnet.ora (Network defaults)

tnsnames.ora

ldap.ora

Martin Bracher

Leave a Reply:

Related blog articles

Local and remote connections

Location of the Oracle network configuration (*.ora files)

Without TNS_ADMIN

Tracing with strace

Create a test configuration

With TNS_ADMIN

ldap.ora

Summary

sqlnet.ora (Network defaults)

tnsnames.ora

ldap.ora

Martin Bracher

Leave a Reply:

Related blog articles

tnsping with Oracle instant-client?

Oracle and btrfs snapshots

Retrieving GoldenGate logs, reports, and diagnostics with the REST API

Anatomy of an Enterprise Manager metric extension