Where is TNSNAMES.ORA?

Question

I am working on a Windows server and access Oracle databases using the Oracle ODBC driver. I have Oracle ODBC connections that work fine on this machine.

However I want to add a new connection, and when testing it I get the error

Unable to connect
SQL State=S1000
[Oracle][ODBC][Ora] ORA-12170: TNS: Connect timeout occurred

I presume, though I am no DBA, that this has something to do with my TNS Service Name not being in TNSNAMES.ORA which points oracle toward the right server, port, etc.

However I cannot find TNSNAMES.ORA

I have an ORACLE_HOME environment variable, but it is empty.

Where can I find this file or otherwise configure my TNS names?

Did you check this? https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF259 — shree.pat18, Nov 25 '15 at 03:08
Yes but it refers either to the TNS_ADMIN environment variable, which I don't have, or to ORACLE_HOME, which is empty — Alexis Eggermont, Nov 25 '15 at 03:14
both of these get me 'file not found', and yet I definitely have working processes which use the ODBC driver configured with a TNS Service Name. — Alexis Eggermont, Nov 25 '15 at 03:34
Solved. I was able to use IP:port/service name instead of the TNSNAME. I think there is simply no TNSNAMES.ORA on that computer. — Alexis Eggermont, Nov 25 '15 at 03:40
`TNS: Connect timeout occurred` sounds more like a network problem or firewall issue, not a missing tnsnames.ora file. — Wernfried Domscheit, Nov 25 '15 at 08:05
t as @WernfriedDomscheit says, it is a network/firewall problem. All these speculaions about the tnsname.ora file are wrong — miracle173, Jun 10 '22 at 08:30

score 11 · Answer 1 · edited Apr 26 '19 at 06:55

11

tnsnames.ora is located in the ORACLE_HOME\network\admin directory on Windows. Oracle home path is usually C:\app\oracle_account\product\12.1.0\dbhome_1 in windows.

edited Apr 26 '19 at 06:55

LuckyLikey

3,504
1
31
54

answered Apr 26 '19 at 06:01

spoorthi vaidya

411
5
9

maneesh · Answer 2 · 2021-02-17T16:49:30.907

3

How to find 'TNSNAMES.ORA' on Windows

Step 1) Please check if Oracle has been installed on your system. On Cmd, Type 'sql plus'

Then Control Panel -> Advanced System Settings ->

3)On the 'Advanced' tab, select 'Environment Variables' Then check 'Oracle ' or 'TNS_ADMIN' on System Variables.

Here, on 'TNS_ADMIN' will get the path for TNS file

edited Feb 17 '21 at 16:49

answered Feb 17 '21 at 15:31

maneesh

919
8
6

score 0 · Answer 3 · answered Mar 23 '22 at 20:44

0

The path to the tns file can be listed in machine.config too.

<oracle.manageddataaccess.client>
 <version number="4.121.2.0">
  <settings>
    <setting name="TNS_ADMIN" value="C:\app\client\...\product\12.1.0\client\network\admin"/>
  </settings>
 </version>
</oracle.manageddataaccess.client>

answered Mar 23 '22 at 20:44

jaycer

2,941
2
26
36

That's a good general solution. The interesting thing about the actual value in your answer is that it appears to be a default location that the Oracle libraries will use anyway. I.e you don't need to set TNS_ADMIN at all for ODBC in this case. – Christopher Jones Mar 24 '22 at 09:30

Where is TNSNAMES.ORA?

3 Answers3