Oracle - What TNS Names file am I using?

Question

Sometimes I get Oracle connection problems because I can't figure out which tnsnames.ora file my database client is using.

What's the best way to figure this out? ++happy for various platform solutions.

Answer 1

For linux:

$ strace sqlplus -L scott/tiger@orcl 2>&1| grep -i 'open.*tnsnames.ora'

shows something like this:

open("/opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora",O_RDONLY)=7

Changing to

$ strace sqlplus -L scott/tiger@orcl 2>&1| grep -i 'tnsnames.ora'

will show all the file paths that are failing.

Answer 2

For Windows: Filemon from SysInternals will show you what files are being accessed.

Remember to set your filters so you are not overwhelmed by the chatty file system traffic.

Added: Filemon does not work with newer Windows versions, so you might have to use Process Monitor.

Answer 3

Oracle provides a utility called tnsping:

R:\>tnsping someconnection

TNS Ping Utility for 32-bit Windows: Version 9.0.1.3.1 - Production on 27-AUG-20
08 10:38:07

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\Oracle92\network\ADMIN\sqlnet.ora
C:\Oracle92\network\ADMIN\tnsnames.ora

TNS-03505: Failed to resolve name

R:\>


R:\>tnsping entpr01

TNS Ping Utility for 32-bit Windows: Version 9.0.1.3.1 - Production on 27-AUG-20
08 10:39:22

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\Oracle92\network\ADMIN\sqlnet.ora
C:\Oracle92\network\ADMIN\tnsnames.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = **)
 (PROTOCOL = TCP) (Host = ****) (Port = 1521))) (CONNECT_DATA = (SID = ENTPR0
1)))
OK (40 msec)

R:\>

This should show what file you're using. The utility sits in the Oracle bin directory.

Answer 4

strace sqlplus -L scott/tiger@orcl helps to find .tnsnames.ora file on /home/oracle to find the file it takes instead of $ORACLE_HOME/network/admin/tnsnames.ora file. Thanks for the posting.

Answer 5

By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory on UNIX operating systems and in the ORACLE_HOME\network\admin directory on Windows operating systems. tnsnames.ora can also be stored the following locations:

The directory specified by the TNS_ADMIN environment variable (or registry value)

On UNIX operating systems, the global configuration directory. For example, on the Solaris Operating System, this directory is /var/opt/oracle

If you have multiple ORACLE_HOMES, be aware of which one you are using, as the location of the tnsnames.ora file can vary from one ORACLE_HOME to the next.

For the person who mentioned the TWO_TASK environment variable, that is used to set a default database service name to connect to (which could be a database on another server). The service name you set TWO_TASK to is then looked up in the tnsnames.ora file when you connect.