This was first published on https://blog.dbi-services.com/12cr2-tns_admin-in-env-ora (2017-03-10)
Republishing here for new followers. The content is related to the the versions available at the publication date
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://blog.dbi-services.com/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@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
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
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.
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.