During a recent troubleshooting, we came across a requirement where we wanted to know what all locations does Oracle searches tnsnames.ora ? So writing this quick post for the analysis that we did and the results we reached during this exercise.

You might already aware of tnsnames.ora in path defined by TNS_ADMIN variable and default $ORACLE_HOME/network/admin path but are these the only 2 locations (Spoiler Alert, there are 4..!!) and what’s the order in which Oracle searches for tnsnames.ora in case both these locations had a different versions of tnsnames.ora file present.

For our exercise, we set the TNS_ADMIN variable as /home/oracle/nitish but did not created any tnsnames.ora file and similarly ensured that there is no tnsnames.ora in $ORACLE_HOME/network/admin path too. And then ran a strace on TNSPING:

strace tnsping TEST

I am just using an excerpt of the output below in our discussion but you can download the complete output to see what all is happening in the background.

Excerpt from strace output:

1. access(“/home/oracle/nitish/sqlnet.ora”, F_OK) = -1 ENOENT (No such file or directory)
2. access(“/apps/oracle/server/18.3.0.0/dbhome_1/network/admin/sqlnet.ora”, F_OK) = -1 ENOENT (No such file or directory)
3. access(“/home/oracle/.tnsnames.ora”, F_OK) = -1 ENOENT (No such file or directory)
4. access(“/home/oracle/nitish/tnsnames.ora”, F_OK) = -1 ENOENT (No such file or directory)
5. access(“/etc/tnsnames.ora”, F_OK)       = -1 ENOENT (No such file or directory)
6. access(“/apps/oracle/server/18.3.0.0/dbhome_1/network/admin/tnsnames.ora”, F_OK) = -1 ENOENT (No such file or directory)

Observations from strace excerpt:

1. In Line 1, Server Process first checks for sqlnet.ora in the path set by TNS_ADMIN variable but did not find the file.
2. In Line 2, it checks for sqlnet.ora in $ORACLE_HOME but did not find the file.
3. In Line 3, it then looks for tnsnames.ora in /home/oracle/.tnsnames.ora (Software Owner’s home directory) still doesn’t find the file.
4. In Line 4, it then looks for tnsnames.ora in the path specified by TNS_ADMIN variable and as expected still doesn’t find the file.
5. In Line 5, It then looks for tnsnames.ora in /etc/tnsnames.ora and again didn’t find the file.
6. In Line 6, it then finally looks for tnsnames.ora in $ORACLE_HOME/network/admin.

Result:

So, the sequence in which Server Process looks for tnsnames.ora is:

1. Oracle Software Owner’s home directory. (but the filename is .tnsnames.ora (hidden file) )
2. Path specified by TNS_ADMIN variable.
3. /etc/tnsnames.ora
4. $ORACLE_HOME/network/admin/tnsnames.ora

Hope you find this post helpful, please let us know in comments if you have any further questions on this post or tnsnames.ora.