Oracle What TNS name file do I use?

Question

Sometimes I get problems with the Oracle connection because I cannot figure out which tnsnames.ora file my database client is using.

What is the best way to figure this out? ++ satisfied with the various solutions of the platform.

Answer 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 which file you are using. The utility is located in the Oracle bin .

Answer 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 

Go to

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

will display all file paths that do not work.

Answer 3

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

Answer 4

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

Remember to set filters so that you are not overloaded with the chatty file system stream.

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

Answer 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 wrote the tnsnames.ora file:

 ifile=path_to_tnsnames.ora_file/tnsnames.ora 

Thus, if for some reason the wrong tnsnames.ora file is used by the client, it will always be on the latest version.

Answer 6

Codeslave asks, “Is it not always $ ORACLE_ HOME / network / admin / tnsnames.ora? The answer is no, it is not. Consider these two tnsp calls on the same computer:

 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> 

Pay attention to two different locations of the parameter file, which depend on the executable tnsping executable file (and, possibly, on its launch). For oracle tnsnames-based networks, using the TNS_ADMIN variable is the only way to ensure that you get a consistent tnsnames.ora file. (NOTE: Windows-based answer)

Answer 7

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

@Pete Holberton You are absolutely right. Which reminds me, there is another monkey key in the works called TWO_ TASK

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

Answer 8

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

Answer 9

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 saved in the following places:

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

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

If you have multiple ORACLE_HOMES, keep in mind which one you are using, as the location of the tnsnames.ora file can vary from one ORACLE_HOME to another.

For the person who mentions the TWO_TASK environment variable, which is used to set the default database service name for the connection (which may be a database on another server). The name of the service that you installed TWO_TASK is then viewed in the tnsnames.ora file when it connects.

Answer 10

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

Using filemon, as suggested by others, will also do the trick.

Answer 11

This is not a direct answer to your question, but I was very upset trying to find and update all tnsnames files, since I had several oracle installations: 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 tnsnames in all oracle houses. It is licensed under MIT, you can use it 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.