Oracle - What TNS Names file am I using?

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.

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.

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.

There is another place where the TNS location is stored: If you're using Windows, open regedit and navigate to My HKEY Local Machine/Software/ORACLE/KEY_OraClient10_home1 where KEY_OraClient10_home1 is your Oracle home. If there is a string entry called TNS_ADMIN, then the value of that entry will point to the TNS file that Oracle is using on your computer.

On my development machine I have three different versions of Oracle client software. I manage the tnsnames.ora file in one of them. In the other two, I have entered in the tnsnames.ora file:

ifile=path_to_tnsnames.ora_file/tnsnames.ora

This way, if for some reason the wrong tnsnames.ora file is used by a client, it will always end up at the up-to-date version.

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.

Codeslave asks "Shouldn't it always be "$ORACLE_ HOME/network/admin/tnsnames.ora"? The answer is no, it isn't. Consider these two invocations of tnsping on the same machine:

C:\Documents and Settings\me>D:\Oracle\10.2.0_DB\BIN\tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 09-OCT-2
008 14:30:12

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
D:\Oracle\10.2.0_DB\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

OK (40 msec)

C:\Documents and Settings\me>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 09-OCT-2
008 14:30:21

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\10.2.0_Client\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = XXXX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (20 msec)

C:\Documents and Settings\me>

Note the two different parameter file locations, that are dependent on which tnsping executable you're running (and perhaps where it's being run from). For tnsnames-based oracle networking, using the TNS_ADMIN variable is the only way to ensure you're getting a consistent tnsnames.ora file. (NOTE: Windows-centric answer)

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.

Shouldn't it always be "$ORACLE_ HOME/network/admin/tnsnames.ora"? Then you can just do "echo $oracle_ home" or the *nix equivalent.

@Pete Holberton You are entirely correct. Which reminds me, there's another monkey wrench in the works called TWO_ TASK

According http://www.orafaq.com/wiki/TNS_ADMIN
TNS_ADMIN is an environment variable that points to the directory where the SQL*Net configuration files (like sqlnet.ora and tnsnames.ora) are located.

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.

Not direct answer to your question, but I've been quite frustrated myself trying find and update all of the tnsnames files, as I had several oracle installs: Client, BI tools, OWB, etc, each of which had its own oracle home. I ended up creating a utility called TNSNamesSync that will update all of the tnsnames in all of the oracle homes. It's under the MIT license, free to use here https://github.com/artybug/TNSNamesSync/releases

The docs are here: https://github.com/artchik/TNSNamesSync/blob/master/README.md

This is for Windows only, though.

The easiest way is probably to check the PATH environment variable of the process that is connecting to the database. Most likely the tnsnames.ora file is in first Oracle bin directory in path..\network\admin. TNS_ADMIN environment variable or value in registry (for the current Oracle home) may override this.

Using filemon like suggested by others will also do the trick.