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

User · Answer

For linux     strace sqlplus -L scott tiger orcl 2 gt  amp 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 gt  amp 1  grep -i  tnsnames ora    will show all the file paths that are failing

User · Answer

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

User · Answer

Oracle provides a utility called tnsping   R   gt 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   gt    R   gt 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   gt    This should show what file you re using  The utility sits in the Oracle bin directory

User · Answer

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 gt 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 gt 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 gt    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

User · 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] Oracle - What TNS Names file am I using?

Examples related to oracle

Examples related to connection

Examples related to tnsnames

Examples related to tns