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

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.

Answer 3

@CodeSlave

That's not always the case. You can use the TNS_ADMIN environment variable to point to a different tnsnames.ora. I've read, though I don't know whether this applies to all Oracle versions, that on some *nix operating systems Oracle will look in /etc or /var/opt/oracle before checking ORACLE_HOME.

@Mark Sorry that hasn't helped answer your question...

Answer 4

Hi,

Oracle provide 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 5

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.

Answer 6

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.

Answer 7

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)

Answer 8

On my development machine I have three different versions of Oracle client software. I manage the tnsnames.ora file in 1 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.

Answer 9

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.