All green... but still can't connect

Toad for Oracle & DevOps Toolkit
1

Was using Toad for Oracle 11.5 but company upgraded. That version was uninstalled and I just received Toad for Oracle 12.12.0.39

My saved sessions were wiped. I had to re-edit the tnsnames.ora and re-add my servers. I did this via copy 'n paste from a tnsnames.ora on the servers... they're identical contents.

I can connect to the DB using SQL*Plus from server1

% sqlplus myname/password@server1
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 1 19:02:58 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connect to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL>

However, when I try to connect to the database using Toad for Oracle 12.12.0.39 it gives me this error:

ORA-12154: TNS:could not resolve the connect identifier specified

The error dialog is quite large and interestingly, it has a number of bulleted items that are all green. It says that TNS_ADMIN was found, tnsname.ora was found, Oracle home was found, it displays an accurate connect descriptor, sqlnet.ora, and that the NAMES>DIRECTORY_PATH specifies TNSNAMES in the sqlnet.ora file. Everything has a pretty green checkmark. So it is an error dialog, but everything it display is good/green/accurate.

It then suggests I take the following steps:

Try to ping <hostname> using the Ping button on the toolbar
Try to TNSPing SERVER1 using the TNS Ping button on the toolbar

the ping is successful, but the TNSPing says failed to resolve name.

Any idea why it is failing to resolve the name when it has found the tnsnames.ora and even shows me an accurate connect descriptor?

[edit] I just tried a direct connect and that worked. So it appears to just be failing when trying to do TNS.

Thank you in advance,
Darren

2

Do you have a TNS_ADMIN environement variable set? If so, is it pointing to the folder of your tnsnames.ora file?
Judging by what you said in [edit], it sounds like it is pointing somewhere else.

Also -

Do you have more than one Oracle client installed? If so, and you only need one, remove the ones that you don't need.

Do you have an ORACLE_HOME environment variable set? If so, you probably don't need that either. Remove it.

3

Make sure you don't have a duplicate connect descriptor in your tnsnames.ora file for the db. When I duplicate an entry in my tnsnames.ora file where the first one is correct and the latter has an incorrect service name I receive an error. Toad's connection error checks dialog shows all green and the connect descriptor shown is the first, correct one, however under the hood OCI must be picking up on the second, incorrect one. If I reverse my duplicate entries so that the bad one appears first then Toad will connect.

4

JohnDorlon, yes TNS_NAMES is set and pointing at the right folder. It even shows up with a green checkmark in the error dialog. The green checkmark indicating it is supposedly "good"

mstaszew, I do not have a duplicate entry in the tnsnames.ora file. I've checked that several times and it's fine. In fact, the contents are just a copy 'n paste from another server and it works fine over there.

5

In the past I have had a few problems with tns_names:

  • duplicate entrirs;
  • copy and paste, via Word, causing "smart" quotes;
  • hidden invalid characters;
  • typo in host names;
  • host name not known;
  • opening '(' in first column used to be a bug, best avoided.

HTH

Cheers,
Norm.

1 Like
6

Moderators: Feel free to delete this post if considered spam, etc. Thanks.

There's a rather good Tnsnames checker at https://github.com/NormanDunbar/Tnsnames_checker/releases/tag/Release_0v05 if anyone wants one. (Executables in the zip file.)

Brief details at https://github.com/NormanDunbar/Tnsnames_checker too. Covers building the app, but you don't need to.

Cheers,
Norm. [TeamT]