Login/Register
Oracle - What TNS Names file am I using?
Asked Answered
Solved oracleconnectiontnsnamestns
E

11

61

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.

Extrasensory answered 13/8, 2008 at 23:49 Comment(0)
D
88

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.

Decentralize answered 27/8, 2008 at 9:41 Comment(0)
E
22

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.

Extrasensory answered 13/8, 2008 at 23:52 Comment(2)
Thanks, based on this, it looks like /etc/tnsnames.ora is a reliable, global location for tns names. – Chichi
@AvindraGoolcharan, that is my conclusion as well. That's where we put it and have had no problems. – Extrasensory
S
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.

Scornik answered 30/8, 2008 at 9:12 Comment(0)
S
5

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.

Strasser answered 14/10, 2008 at 8:9 Comment(0)
M
4

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.

Filter Dialog

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

Mandle answered 27/8, 2008 at 9:8 Comment(0)
I
4

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)

Intra answered 9/10, 2008 at 20:45 Comment(2)
Don't you just love multiple oracle homes? – Decentralize
Soooooo many +1's for this answer. It's been a few years since I've had to deal with this issue. The TNS_ADMIN environmental variable is the key. For PowerShell, it is $env:TNS_Admin. C# is Environment.GetEnvironmentVariable("TNS_Admin"). – Matherne
I
2

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.

Inflight answered 31/8, 2008 at 23:5 Comment(0)
D
1

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.

Dickens answered 14/8, 2008 at 17:23 Comment(1)
Actually, no, you cannot assume this location. There's the agent home and client home, just to name 2, that have their own network/admin directory. The answer you get depends on which executable you're running. – Intra
C
1

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.

Candis answered 11/12, 2013 at 15:30 Comment(0)
P
1

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.

Phraseologist answered 25/3, 2016 at 22:24 Comment(0)
J
0

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.

Jerrelljerri answered 4/12, 2008 at 20:31 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.