I’ve been very heavily relying on Toad for a large-scale project of a merge of two companies. There’s a potential for more time savings with Toad bug fixes and new functionality, so I thought all of my overdue tasks can wait another 30 mins while I troubleshoot…
Here’s my setup and what I did to resolve Toad 12.5GA/12.6b not being able to use my TNSNAMES.ORA:
Toad 12.6.0.7 64bit (no 128-bit version yet!)
Win7-64
Two ORACLE_HOMEs, in the order installed.
11.2.0.3 client-only
11.2.0.3 database
Win PATH: C:/database-path/bin;C:/client-path/bin;… <— NOTE the DB home is first, as it was last installed
All Toad connections use client OH
In Toad Connection screen, OH selector ellipsis shows the two OHs, both “correct”
Desired TNSNAMES.ORA in client OH – database OH/network/admin has no connection in its TNSNAMES.ORA
No TNS_ADMIN environment variable
All connections using TNSNAMES.ORA were working in Toad 12.1GA
No connections using TNSNAMES.ORA working in 12.5GA or 12.6b, failing with ORA-12154 (can’t resolve TNS entry).
To fix, I just modified my Win PATH variable to swap the client OH – and its TNSNAMES.ORA – with the database OH, putting the client OH first. Now all connections using client TNSNAMES.ORA work!
So, I wanted to verify this and I switched 1 connection’s OH to the database OH. The connection failed with ORA-12154, lending credence to my theory that the OH is ignored in the connection and the Win PATH is used instead. However, now the OH dropdown in the connection screen is greyed out and I can’t switch anything back to use the client’s OH. Hmm…restart Toad and I’m able to make the OH swap and all’s well.
From this mess I’ve made and tried to document here, it seems to me that Toad 12.5GA and 12.6b rely on the PATH variable to resolve TNS aliases, instead of the OH selected in the connection screen dropdown. I’m sure I’m missing something here, but this is what I’ve got so far.
Thoughts?
TIA!
Rich
p.s No, no, no…don’t…have…time…to…check out…new…ASH…interface…must…work…on…project…
Simple - TNS_ADMIn (as per Oracle metalink docs) has precedence. So to use multiple Oracle homes and sharing/haing one tnsnsmes.ora file - create the TNS_ADMIN env var for where the tnsnames.ora file is and that’s it. You can place the file in either home - or in my case I have the tnsnames.ora file on c:\ so it’s quick and easy to get to for quick edits.
yeah it seems like dumb luck that it worked in 12.1, with a tnsnames.ora in one Oracle home, but worked from multiple Oracle clients without a TNSADMIN variable.
Our 3rd party Oracle access component set made some internal changes with regards to Oracle clients, so I guess your luck ran out on this one, sorry, Rich. But you know how to get around it, so there’s no real problem here, is that right?
@Bert, it would be simple if the database would work with that TNSNAMES.ORA – it cannot, as some of the entries there conflict with what the database ORACLE_HOME requires. The database is a test, where names need to be the same as a Production environment, but pointing to different servers/DBs/etc, where almost all of my Toad connections need to point to the actual Production ones. (terrible explanation, but there’s pizza waiting for me)
@John, I did get it to work, but I hope you can see my confusion where Toad’s not using the OH selected. Technically, it is using it for pulling in values from TNSNAMES.ORA to select in the dropdown, but not for the actual connection. I’m surprised that no one else seems to have this issue.
I’m Good To Go™, knowing now how to workaround the issue. If anything, this is here for posterity, because I have to believe that thumbody else will hit this and be as confused as I am. Although, to be fair, I think Oracle’s implementation of multiple OHs in Windows is frankly terrible, so I may be missing something else. As far back as Oracle 7.4, I’ve never had good luck with multiple OHs in Windows.
Yes, I do see the cause of confusion. When I first start up Toad, the connections dropdown gets loaded with the tns info, I guess from the last home used, which is where my tnsnames.ora is. But then if I change it to a home that does not have a tnsnames.ora file, the dropdown becomes blank. I’ll see if I can do something about it.
By the way, I have had the best luck with multiple Oracle homes if I install them from oldest to newest. If that means deleting all of them, making sure the Oracle node in my registry is clear, and then starting over, that’s what I do. But I always use a TNS_ADMIN variable.
