<p>Sometimes I get Oracle connection problems because I can't figure out which tnsnames.ora file my database client is using.</p>

<p>What's the best way to figure this out?  ++happy for various platform solutions.   </p>

[{'answer_id': 10501, 'author': 'Mark Harrison', 'author_id': 116, 'author_profile': 'https://Stackoverflow.com/users/116', 'pm_score': 5, 'selected': False, 'text': '<p>For linux:</p>\n\n<pre><code>$ strace sqlplus -L scott/tiger@orcl 2&gt;&amp;1| grep -i \'open.*tnsnames.ora\'\n</code></pre>\n\n<p>shows something like this:</p>\n\n<pre><code>open("/opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora",O_RDONLY)=7\n</code></pre>\n\n<p>Changing to </p>\n\n<pre><code>$ strace sqlplus -L scott/tiger@orcl 2&gt;&amp;1| grep -i \'tnsnames.ora\'\n</code></pre>\n\n<p>will show all the file paths that are failing.</p>\n'}, {'answer_id': 11373, 'author': 'BIBD', 'author_id': 685, 'author_profile': 'https://Stackoverflow.com/users/685', 'pm_score': 1, 'selected': False, 'text': '<p>Shouldn\'t it always be "$ORACLE_ HOME/network/admin/tnsnames.ora"?\nThen you can just do "echo $oracle_ home" or the *nix equivalent.</p>\n\n<p>@Pete Holberton\nYou are entirely correct. Which reminds me, there\'s another monkey wrench in the works called TWO_ TASK</p>\n\n<p>According <a href="http://www.orafaq.com/wiki/TNS_ADMIN" rel="nofollow noreferrer">http://www.orafaq.com/wiki/TNS_ADMIN</a><br>\nTNS_ADMIN is an environment variable that points to the directory where the SQL*Net configuration files (like sqlnet.ora and tnsnames.ora) are located. </p>\n'}, {'answer_id': 29767, 'author': 'Ishmaeel', 'author_id': 227, 'author_profile': 'https://Stackoverflow.com/users/227', 'pm_score': 2, 'selected': False, 'text': '<p>For Windows: <a href="http://technet.microsoft.com/en-us/sysinternals/bb896642.aspx" rel="nofollow noreferrer">Filemon</a> from SysInternals will show you what files are being accessed.</p>\n\n<p>Remember to set your filters so you are not overwhelmed by the chatty file system traffic.</p>\n\n<p><img src="https://i.stack.imgur.com/tY0Ov.jpg" alt="Filter Dialog"></p>\n\n<p><strong>Added:</strong> Filemon does not work with newer Windows versions, so you might have to use <a href="http://technet.microsoft.com/en-us/sysinternals/bb896645" rel="nofollow noreferrer">Process Monitor</a>.</p>\n'}, {'answer_id': 29807, 'author': 'stjohnroe', 'author_id': 2985, 'author_profile': 'https://Stackoverflow.com/users/2985', 'pm_score': 7, 'selected': True, 'text': "<p>Oracle provides a utility called <code>tnsping</code>:</p>\n\n<pre><code>R:\\&gt;tnsping someconnection\n\nTNS Ping Utility for 32-bit Windows: Version 9.0.1.3.1 - Production on 27-AUG-20\n08 10:38:07\n\nCopyright (c) 1997 Oracle Corporation.  All rights reserved.\n\nUsed parameter files:\nC:\\Oracle92\\network\\ADMIN\\sqlnet.ora\nC:\\Oracle92\\network\\ADMIN\\tnsnames.ora\n\nTNS-03505: Failed to resolve name\n\nR:\\&gt;\n\n\nR:\\&gt;tnsping entpr01\n\nTNS Ping Utility for 32-bit Windows: Version 9.0.1.3.1 - Production on 27-AUG-20\n08 10:39:22\n\nCopyright (c) 1997 Oracle Corporation.  All rights reserved.\n\nUsed parameter files:\nC:\\Oracle92\\network\\ADMIN\\sqlnet.ora\nC:\\Oracle92\\network\\ADMIN\\tnsnames.ora\n\nUsed TNSNAMES adapter to resolve the alias\nAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = **)\n (PROTOCOL = TCP) (Host = ****) (Port = 1521))) (CONNECT_DATA = (SID = ENTPR0\n1)))\nOK (40 msec)\n\nR:\\&gt;\n</code></pre>\n\n<p>This should show what file you're using. The utility sits in the Oracle <code>bin</code> directory.</p>\n"}, {'answer_id': 35804, 'author': 'Sir Rippov the Maple', 'author_id': 2822, 'author_profile': 'https://Stackoverflow.com/users/2822', 'pm_score': 3, 'selected': False, 'text': "<p>There is another place where the TNS location is stored: If you're using Windows, open <code>regedit</code> and navigate to <code>My HKEY Local Machine/Software/ORACLE/KEY_OraClient10_home1</code> where <code>KEY_OraClient10_home1</code> is your Oracle home. If there is a string entry called <code>TNS_ADMIN</code>, then the value of that entry will point to the TNS file that Oracle is using on your computer.</p>\n"}, {'answer_id': 37235, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p>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:</p>\n\n<p>The directory specified by the TNS_ADMIN environment variable (or registry value)</p>\n\n<p>On UNIX operating systems, the global configuration directory. For example, on the Solaris Operating System, this directory is /var/opt/oracle</p>\n\n<p>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.</p>\n\n<p>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.</p>\n'}, {'answer_id': 189142, 'author': 'DCookie', 'author_id': 8670, 'author_profile': 'https://Stackoverflow.com/users/8670', 'pm_score': 2, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>C:\\Documents and Settings\\me&gt;D:\\Oracle\\10.2.0_DB\\BIN\\tnsping orcl\n\nTNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 09-OCT-2\n008 14:30:12\n\nCopyright (c) 1997,  2007, Oracle.  All rights reserved.\n\nUsed parameter files:\nD:\\Oracle\\10.2.0_DB\\network\\admin\\sqlnet.ora\n\n\nUsed TNSNAMES adapter to resolve the alias\nAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx\n)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))\n\nOK (40 msec)\n\nC:\\Documents and Settings\\me&gt;tnsping orcl\n\nTNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 09-OCT-2\n008 14:30:21\n\nCopyright (c) 1997, 2005, Oracle.  All rights reserved.\n\nUsed parameter files:\nD:\\oracle\\10.2.0_Client\\network\\admin\\sqlnet.ora\n\n\nUsed TNSNAMES adapter to resolve the alias\nAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)\n(HOST = XXXX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))\nOK (20 msec)\n\nC:\\Documents and Settings\\me&gt;\n</code></pre>\n\n<p>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)</p>\n'}, {'answer_id': 200344, 'author': 'Rene', 'author_id': 17323, 'author_profile': 'https://Stackoverflow.com/users/17323', 'pm_score': 3, 'selected': False, 'text': '<p>On my development machine I have three different versions of Oracle client software. I manage the <code>tnsnames.ora</code> file in one of them. In the other two, I have entered in the <code>tnsnames.ora</code> file:</p>\n\n<pre><code>ifile=path_to_tnsnames.ora_file/tnsnames.ora\n</code></pre>\n\n<p>This way, if for some reason the wrong <code>tnsnames.ora</code> file is used by a client, it will always end up at the up-to-date version.</p>\n'}, {'answer_id': 341948, 'author': 'stili', 'author_id': 20763, 'author_profile': 'https://Stackoverflow.com/users/20763', 'pm_score': 0, 'selected': False, 'text': '<p>The easiest way is probably to check the <strong>PATH</strong> environment variable of the process that is connecting to the database. Most likely the tnsnames.ora file is in <em>first Oracle bin directory in path</em>..\\network\\admin. TNS_ADMIN environment variable or value in registry (for the current Oracle home) may override this.</p>\n\n<p>Using filemon like suggested by others will also do the trick.</p>\n'}, {'answer_id': 20523156, 'author': 'sri', 'author_id': 3091747, 'author_profile': 'https://Stackoverflow.com/users/3091747', 'pm_score': 1, 'selected': False, 'text': '<p><code>strace sqlplus -L scott/tiger@orcl</code> helps to find <code>.tnsnames.ora</code> file on <code>/home/oracle</code> to find the file it takes instead of <code>$ORACLE_HOME/network/admin/tnsnames.ora</code> file.   Thanks for the posting.</p>\n'}, {'answer_id': 36229027, 'author': 'artybug', 'author_id': 6080126, 'author_profile': 'https://Stackoverflow.com/users/6080126', 'pm_score': 1, 'selected': False, 'text': '<p>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 <a href="https://github.com/artybug/TNSNamesSync/releases" rel="nofollow">https://github.com/artybug/TNSNamesSync/releases</a></p>\n\n<p>The docs are here:\n<a href="https://github.com/artchik/TNSNamesSync/blob/master/README.md" rel="nofollow">https://github.com/artchik/TNSNamesSync/blob/master/README.md</a></p>\n\n<p>This is for Windows only, though.</p>\n'}]
['https://Stackoverflow.com/questions/10499', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/116/']