15

With a new Oracle Installation when I go to Configuration Management Tools > Microsoft ODBC Administrator > System DSN > Add > [new installation] it does not have the TNSNAMES.ora connection name in the drop down. With the old installation, this had worked.

Create New Data Source

I've tried updating Environment Paths and Registry keys but still can't find a way to get ODBC to see it in the drop down.

enter image description here

There was a previous installation of Oracle Client on the Windows Server. I tried to remove most of the files but our Oracle DBA said we could just do another install and update the Environment Paths with the new location.

I understand there are two other posts on SO here and here regarding this issue. However, I'm not sure TNS PING will help yet because it seems to be an issue regarding Environment Paths and ODBC being able to locate the .ora file.

TNSNAMES.ORA Location

TNS_Admin = E:\app\[username]\product\11.2.0\client_1\network\admin\TNSNAMES.ora

Environment Variables 

TNS_Admin = E:\app\[username]\product\11.2.0\client_1  
Path = E:\app\[username]\product\11.2.0\client_1

Registry Entries
home3 is the new installation. Should I delete the old registry key (home2)? HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home2 and \..._home3

Also, if I select "OraClient11g_home2" in the Create New Data Source screen here is what is displayed in the drop down:

enter image description here

Community
  • 1
  • 1
pixelbobby
  • 4,368
  • 5
  • 29
  • 49
  • 1
    I've voted to move this to dba.se, where I believe that it would be a better fit. If you agree then you might want to flag this for a moderators attention and ask to have it moved. – Ben Jul 11 '12 at 17:19
  • Good idea, @Ben. You got it. – pixelbobby Jul 11 '12 at 17:51
  • I flagged it... just waiting now... – pixelbobby Jul 11 '12 at 18:33
  • 1
    Every Oracle install I've worked with has been installed to a public location, not a user-specific location like yours. Also, if you're on a 64 bit OS, you should try both the 32 bit and 64 bit ODBCAD32.EXE (one is in SysWow64, the other is in System32) – Nick.Mc Dec 17 '13 at 23:29

8 Answers8

7

If service name doesn't show in the dropdown, directly type in the service name and see if you are able to test the connection.

enter image description here

Geovani Martinez
  • 2,053
  • 2
  • 27
  • 32
Srinivas
  • 71
  • 1
  • 1
  • Wonderful answer. This solved my problem without needing to add Environment variables. Not sure if that step is still needed along my way, but this is also a great answer – Ak777 Jun 10 '22 at 04:41
4

It appears the tumbleweed rolled by on this one but just for everyone who may be experiencing the same issue; I was able to fix this by using the Net Configuration Assistant wizard and providing the right information. It generated tnsnames.ora in proper format AND it should be noted that the filename given to me was tsnames.ora!

pixelbobby
  • 4,368
  • 5
  • 29
  • 49
3

For the stupid souls like me: if you're writing out tnsnames.ora via notepad or so, make sure it doesn't have the .txt extension which is by (our) default not visible in windows explorer

dr jerry
  • 9,768
  • 24
  • 79
  • 122
2

Sorry to revive an ancient thread, but I had the same problem, and Net Configuration Assistant wouldn't run.

(Windows XP SP3, Oracle 11gR2 client)

SOLUTION:
1. Uninstall all other Oracle_Home instances using the Oracle (De-)Installer (don't just go deleting registry keys!), so that you only have the one Oracle_Home that you want to keep:
%oracle-home-that-you-want-to-keep%\client\oui\bin\setup.exe
2. Create or set Windows environment variables:
TNS_Admin = %oracle_home%\client_1\network\admin
Path = %oracle_home%\client_1; ...
3. Reboot. (sigh...)

Anyway, that's what worked for me.

Robert
  • 21
  • 1
1

Or just use a connection string that you enter in the textbox (the one that is supposed to get populated using the dropdownbox), e.g. 127.0.0.1/XE see here.

BSharp
  • 43
  • 1
  • 9
0

For some reason windows 2012R2 did not have an ORACLE_HOME environment variables set. This can case issues so either add oracle_home or tns_admin. Windows 2012 installs had a oracle_home variable.

tuomastik
  • 4,559
  • 5
  • 36
  • 48
Chrisvb
  • 11
0

DSN file set the tnsnames.ora and generate a tnsnames.ora.dsn define that like file

enter image description here

Kai-Sheng Yang
  • 1,535
  • 4
  • 15
  • 21
gabis_melody
  • 1
-1

Make sure that:

  • Your tnsnames.ora is encoded in ANSI and not UTF8.
  • You do not have special characters as üäö, not even in comments.
xaizek
  • 5,098
  • 1
  • 34
  • 60
Becks TibiaFun
  • 1
  • 2