Unanswered: Connect with SQLPlus but cant with Toad?
New to here and new to Oracle to be honest (not exactly a DBA!) but Ive asked around my company and done some searching on the web but still no joy....
Im running Oracle 10g on a Windows server and have created 2 databases to work with our software without any problems, these are either created through GUI's built into our software or by using a template and creating through Oracle's Database configuration assistant.
Now I went to import a new database and created it with a template and importing a .dmp file, then I needed to import a few new tablespaces and users so I imported those afterwards. Now, I needed to check the tables but could not connect with Toad which was installed locally on the server, however I could connect through a local Sqlplus connection and I can also connect to the database from my laptop using Toad..... I have checked and double checked the tnsnames file and used both localhost and the servers name in the connect string.... I have also switched off the Windows firewall....
I dont understand why I can connect to this database through Toad on my laptop (using the same tnsnames configuration) but not through Toad on the server itself. In addition to this our software cannot connect from both my laptop or the local installation on the server!!!
The error I get is ORA-12151 TNS:Could not resolve the connect identifier specified
and if i test the service in the Oracle Net Configuration Assistant it succeeds..
How many Oracle products did you install on the server? Every one of them contains the \network\admin directory which contains the TNSNAMES.ORA and SQLNET.ORA files.
TOAD uses one of them (which one? You can see it on the right side of TOAD's Database Login window - "Connect Using" dropdown list). If there are, for example, Oracle 10g XE database, Developer Suite 10g and Client 9i installed there, you'll have 3 different TNSNAMES.ORA files.
Now, OUI (Oracle Universal Installer) adds relevant directories into the PATH system variable. Using SQL*Plus will *probably* find the right TNSNAMES.ORA and connect to the database.
However, if TOAD uses the "wrong" one, you might have problems.
First, try to change Oracle Home (from the dropdown list). If you select the right one, maybe you'll connect successfully.
Also, perhaps you might want to consider maintaining only one copy of the TNSNAMES.ORA file (instead of 3 of them, as mentioned in a previous example). To do that, choose a default location for the file - it doesn't have to be one of the \network\admin ones, you can create a new directory. Then set the TNS_ADMIN system variable to point to this directory and from now onwards, all database aliases should be put into this file.
As I can't guarantee that this approach will help, do return and tell us what happened (if anything); we'll see what to do next.
BTW, Oracle's error description isn't descriptive at all:
Originally Posted by Oracle
ORA-12151: TNS:received bad packet type from network layer
Cause: Internal error.
Action: Not normally visible to the user. For further details, turn on tracing and reexecute the operation. If error persists, contact Worldwide Customer Support.