Oracle - What TNS Names file am I using?
Solution 1
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.
Solution 2
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.
Solution 3
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.
Solution 4
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.
Solution 5
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.
Mark Harrison
I'm a Software Engineer at Google where I work on machine learning planning systems. From 2001-2015 I was the Pixar Tech Lead of the Data Management Group. My 50-year charter was to store and catalog all data and metadata related to the Studio's feature films. This system ("Templar") is in use to this day. From 1997 to 2001 I lived in Beijing, China and was the Chief Software Architect at AsiaInfo, the company that built China's Internet. While there my software was used to grow the China Internet from 200K to 65M users. The last I heard they were at 350M+ users. I studied computer science and worked in Texas for many years. I wrote a couple of computer books... the best one was in print for 20 years. Feel free to drop me a line! [email protected]
Updated on July 08, 2022Comments
-
Mark Harrison almost 2 years
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.