12cR2: TNS_ADMIN in env.ora

By Franck Pachot

.
The network files (sqlnet.ora, tnsnames.ora, lsnrctl.ora) are read by default from ORACLE_HOME/network/admin but you may have several Oracle installations, and want only one place for those files. Then you can use TNS_ADMIN environment variable. But are you sure that it is always set when starting the database? the listener? the client? They must be consistent (see https://www.dbi-services.com/blog/oracle-12cr2-plsql-new-feature-tnsping-from-the-database/). Then what we do for the cases where TNS_ADMIN is not set: define symbolic links from the ORACLE_HOME to the common location. It would be better to just change the default location and this is what can be done in 12.2 with env.ora

By default, the $ORACLE_HOME/env.ora is empty. There are only comments.

If you run any oracle 12.2 client the $ORACLE_HOME/ora.env will be read. If nothing is set here, then the default $ORACLE_HOME/network/admin location is read.

$ORACLE_HOME/network/admin

[oracle@VM104 tmp]$ strace -e trace=file tnsping localhost 2>&1 | grep -E "[.]ora"
lstat("/u01/app/oracle/product/12.2.0/dbhome_1/env.ora", {st_mode=S_IFREG|0644, st_size=852, ...}) = 0
open("/u01/app/oracle/product/12.2.0/dbhome_1/env.ora", O_RDONLY) = 3
access("/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora", F_OK) = 0
open("/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora", O_RDONLY) = 3
access("/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora", F_OK) = 0
open("/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora", O_RDONLY) = 3
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("/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)

Here I have a sqlnet.ora but no tnsnames.ora so the next locations that are searched are ~/.tnsnames.ora and /etc/tnsnames.ora

TNS_ADMIN environment variable

If I set the environment variable TNS_ADMIN to /tmp then

[oracle@VM104 tmp]$ TNS_ADMIN=/tmp strace -e trace=file tnsping localhost 2>&1 | grep -E "[.]ora"
lstat("/u01/app/oracle/product/12.2.0/dbhome_1/env.ora", {st_mode=S_IFREG|0644, st_size=852, ...}) = 0
open("/u01/app/oracle/product/12.2.0/dbhome_1/env.ora", O_RDONLY) = 3
access("/tmp/sqlnet.ora", F_OK)         = 0
open("/tmp/sqlnet.ora", O_RDONLY)       = 3
access("/tmp/sqlnet.ora", F_OK)         = 0
open("/tmp/sqlnet.ora", O_RDONLY)       = 3
access("/home/oracle/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/tmp/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("/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)

The directory defined in TNS_ADMIN is searched first

TNS_ADMIN in env.ora

I have added the TNS_ADMIN=/tmp in the env.ora:

[oracle@VM104 tmp]$ tail -3 $ORACLE_HOME/env.ora
#   Default: $ORACLE_HOME/network/admin
#
TNS_ADMIN=/tmp

When I run tnsping without setting any environment variable, I have exactly the same as before:

[oracle@VM104 tmp]$ strace -e trace=file tnsping localhost 2>&1 | grep -E "[.]ora"
lstat("/u01/app/oracle/product/12.2.0/dbhome_1/env.ora", {st_mode=S_IFREG|0644, st_size=867, ...}) = 0
open("/u01/app/oracle/product/12.2.0/dbhome_1/env.ora", O_RDONLY) = 3
access("/tmp/sqlnet.ora", F_OK)         = 0
open("/tmp/sqlnet.ora", O_RDONLY)       = 3
access("/tmp/sqlnet.ora", F_OK)         = 0
open("/tmp/sqlnet.ora", O_RDONLY)       = 3
access("/home/oracle/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/tmp/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("/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)

The good thing about it is that the setting is centralized for all binaries running from this ORACLE_HOME set.

Both

However the setting in environment has priority over the env.ora one:

[oracle@VM104 tmp]$ TNS_ADMIN=/var/tmp strace -e trace=file tnsping localhost 2>&1 | grep -E "[.]ora"
lstat("/u01/app/oracle/product/12.2.0/dbhome_1/env.ora", {st_mode=S_IFREG|0644, st_size=867, ...}) = 0
open("/u01/app/oracle/product/12.2.0/dbhome_1/env.ora", O_RDONLY) = 3
access("/var/tmp/sqlnet.ora", F_OK)     = 0
open("/var/tmp/sqlnet.ora", O_RDONLY)   = 3
access("/var/tmp/sqlnet.ora", F_OK)     = 0
open("/var/tmp/sqlnet.ora", O_RDONLY)   = 3
access("/home/oracle/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/var/tmp/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("/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)

So the recommandation if you want to use the env.ora is not to set TNS_ADMIN, especially when starting the listener or the database, to be sure that the same environment is always used. Final note: I’ve not seen it in the documentation so if you rely on it for critical environment, better to validate with support.