Toad World® Forums

Toad can't find DB after unsetting TNS_ADMIN

#1

Hey all,

In b.69 (64-bit; also in .58), I experimented with a centralized TNSNAMES.ORA file on a network drive. That worked great, but I needed extra DBA connections that I don't want to expose to the general populous, so I unset the TNS_ADMIN variable from Winders 7 64-bit Pro. Toad didn't recognize it as being unset, which I discovered was a limitation of the launcher I used (Object Dock) having a local copy of env vars at the time of its launch. No biggie, I'll just launch Toad the old-fashioned way, via menu shortcut.

Toad no longer sees the TNS_ADMIN env variable, but it can't connect to the DB using the local copy of TNSNAMES.ORA. When I try to connect, I get a popup of: "ORA-12154: TNS:could not resolve the connect identifier specified" In that popup, I tried the Copy button, but it didn't put anything on the clipboard. The tnsping button pops up another window with a successful connection to my DB. Likewise the server ping comes back correctly (and fast). Clicking on the TNS button brings up the correct local TNSNAMES.ORA in the correct ORACLE_HOME with the DB entry intact. The SQL button brings up the SQLNET.ORA file, which should also be correct (it attempts to put many items not currently existing in my SQLNET.ORA). The rest of the window is in the attached expurgated screenshot.

My PATH is a bit hosed, possibly. It contains the path to the expected ORACLE_HOME's bin directory, but is preceded by another, which is in turn preceded by an Instant Client path. However, this hasn't been an issue in the past.

Also, the "Additional home properties" ellipsis button in the connection dialog shows two correctly configured ORACLE_HOMEs, with the "Advice" button greyed out.

Phew! Thoughts anyone?

Thanks!
Rich

2014-04-08 15_37_49-Error_ ORA-12154.png
2014-04-08 15_37_49-Error_ ORA-12154.png576×511 38.2 KB

#2

p.s. sqlplus.exe from the same ORACLE_HOME connects to the DB just fine…

Rich

#3

OK, wierd: Like b.29, Toad 11.6.1.6 also does not connect to the DB, but 12.1.0.22 does!

Rich

#4

Hey Rich,

I’ve got 4 clients installed (Oracle 9, 10, 11 and 12). I’ve been using a TNS_ADMIN variable. As a test, I deleted my TNS_ADMIN variable and copied my TNSNames.ora file to the network\admin folder of one of my clients. As expected, I can still connect with that client but none of the others.

Is that where you are placing your local tnsnames.ora file?

-John

#5

Hey John,

Yes, in %ORACLE_HOME%/network/admin. That ORACLE_HOME matches what I’ve selected on the dropdown in the connection dialogue. I suppose I should mention the client version is 11.2.0.3…

Thanks!

Rich

#6

Coincidentally, that’s the same client version where I decided to put my tnsnames.ora.

I actually have an instant in my path before the 11g client too, but it’s my 12c client, which looks a lot more like a regular client than an instant (it has all the folders of a regular client, not just a handful of files)

hmmm…

#7

32 or 64 bit? I’ve been testing in 32.

I just have an instant client in 64 bit land, so I’ll have to download a full 64 bit client to test that one.

#8

duh. nevermind. that was the first thing you said.

#9

I got around to trying it in 64-bit-land today, for me it works fine there too…sorry, Rich.

#10

Hi Rich,
On 08/04/14 21:41, Rich J. wrote:

In b.69 (64-bit; also in .58), I experimented with a centralized
TNSNAMES.ORA file on a network drive. That worked great, but I needed
extra DBA connections that I don’t want to expose to the general
populous, so I unset the TNS_ADMIN variable from Winders 7 64-bit Pro.
Try this:
Reset your TNS_ADMIN to wherever you want it to be, leaving your centralised [global] tnsnames.ora on the network drive where it belongs.
Create a small tnsnames.ora in your local $TNS_ADMIN location, which looks like this:
IFILE="/path/to/central/tnsnames.ora"
IFILE="/path/to/local/$TNS_ADMIN/tnsnames.ora"
Or, you can have your private aliases embedded in the tnsnames.ora that lines in $TNS_ADMIN and just IFILE="" the central one.
That is what I have on my work laptop. I have a company wide, global tnsnames.ora on our shared (S:) drive and I have my privates on a local C:\ drive in my local 11g client’s network\admin which is where TNS_ADMIN points. The local one IFILEs the global one.
If I remember correctly, the earlier an alias is defined in the grand scheme of things, then that is the one that will be used, so, IFILE carefully if you have duplicate aliases.
IFILE is documented for other stuff, but doesn’t mention tnsnames.ora but I have been using it for years and it works. I think the only docs refer to the pfile init$ORACLE_SID.ora type files.
HTH
Cheers,
Norm. [TeamT]
– Cheers,
Norm. [TeamT]

#11

Hey Norm,

I like that idea, but it’s weird that only the beta has this problem for me. 12.1 GA works just fine.

Thanks!

Rich