Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Unanswered: Connect with SQLPlus but cant with Toad?

    Hi Guys

    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..

    Any ideas?????

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Quote 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.

  3. #3
    Join Date
    Jun 2007
    Posts
    3
    Ive only installed one home, which is the Oracle Home 10g and I think I have only installed one product, straight off the Oracle installation CD....

    Ive searched for any other copies of the tnsnames file but there is only one and I have the 3 database connections needed configured in one file...

    I was wondering whether it was some sort of registry problem since its only 3rd party tools that cannot connect to the db?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Check what SQLNET.ORA says about this parameter:

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    What happens if you comment this line (put a hash # sign in front of the line) or change NTS to NONE so that it looks like

    SQLNET.AUTHENTICATION_SERVICES = (NONE)

    Or, post both SQLNET.ORA and TNSNAMES.ORA here. Perhaps we'll see something strange.

  5. #5
    Join Date
    Jun 2007
    Posts
    3
    I had hashed out both the lines not hashed out in the SQLNET.ORA file already......

    However I think I managed to fix the problem, there seemed to be some sort of ODBC issue that needed correcting, also I managed to get a newer version of toad and that fixed that issue !?!

    so far so good!

    Many thanks for your time mate....

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ODBC? I'd never suspect it was the problem here.

    OK, never mind; I'm glad you managed to solve the problem!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •