Forum Stats

  • 3,874,263 Users
  • 2,266,716 Discussions
  • 7,911,794 Comments

Discussions

ORA - 12560 with ODBC

DBelt
DBelt Member Posts: 209 Green Ribbon
edited Jun 7, 2016 3:12PM in General Database Discussions

Now, I know this error is common and has been discussed a lot -- after all, I have been trying to figure it out for over a week, almost full time.  Please help.

Here is my working environment -- and I know it works because I have set it up many times.  I am trying to rebuild my system because we had to rebuild the workstation.

OS is Windows 7 - 64 bit.

Database (back end) is 11.2.0.1.0 - 64 bit

Database (front end) is Access 2010 - 32 bit (Access exists only as a 32 bit application)

Dedicated server - FE and BE on same workstation

No network connections (stand-alone development system).

Here is my process for installing on a newly created Windows workstation.

1. Install Oracle database software, not creating a database.

2. Create Listener with netca.

3. Create database with dbca. (I do it this way because I want to increase the maximum number of processes -- it's the easiest way).

4. Import production database via impdp (Import Dump utility).

5.  Test login via SQLPlus in command prompt window.

          - Connection works

          - Database imported

5.  Run Net Manager from Start->OraDB11g_home1->Net Manager

6.  Install 32 bit Oracle Client Administrator.

7.  Run c:\Windows\SysWOW64\odbcad32.exe.

     - Create User DSN and System DSN

8.  In Windows create TNS_ADMIN system environment variable

     - Yes I know you don't need to set these environment variables (at least that is what I have seen said many times) but if I don't create them I get error:

               ORA-12154: TNS: Could not resolve the connect identifies specified

8.  Open Access front-end and we get successful connection to the backend database.

9.  Open database using SQLPlus in command prompt window.

     - Get ORA-12560:  TNS:protocol adapter error.

After attempt to log on via SQLPlus the following entry is placed in the sqlnet.log file (I do not show all because there is no way to transfer information except by keyboard):

     Fatal NI connect error 12528, connection to:

     <listener string>

  

     blah .. blah

     Tns error struct:

          ns main err code: 12564

          TNS-12564: TNS:connection refused

          ns secondary err code:  0

          nt main err code:  0

          nt secondary err code:  0

          nt OS err code:  0

There is a lot of history (i.e. many failed attempts) in coming to this process.  I think it would be inappropriate to post a dissertation on this discussion board.

If you need more information on that history I will be glad to provide some answers where I am able.

John Thorton

Best Answer

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Jun 7, 2016 1:48AM Answer ✓

    I see where the confusion came from, apologies. I meant that SqlDeveloper must have been told where to connect to in order for it to make the connection, without seeing a screen shot of the connection set up there's little to say but I'm confident it would should the connection going to ORCL or DRSDB.

    My guess is that in setting up the DSN, some of the environment parameters that SQLPlus uses to connect to default were changed.

    Check what "echo %LOCAL%" and "echo %ORACLE_SID%" return from cmd. Check what other Oracle environment variables are set, do "set Oracle".

    There should also be valuable information in your sqlnet.log file, this should be located in a directory like:

    ORACLE_BASE\diag\clients\user_%\%\trace

    For me that is

    C:\app\OraUser\diag\clients\user_Andy\host_938889026_82\trace\sqlnet.log

    Post the last few entries after you have tried using sqlplus with no connection string.

«13456

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 31, 2016 6:41PM

    It is not clear how you attempted to connect in step 5, did you use a connection identifier?

    Where are you trying to connect from? Local/remote?

    Is db online? Is listener started?

    On DB server do

    lsnrctl status

    What connection identifier are you using? What entry exists in tnsnames for it?

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited May 31, 2016 7:14PM

    In spite of your assertion that there is no network connection, your connection between the Access front end and the Oracle db is a network connection.  It is using network protocols and the full network stack.  For that, it is irrelevant that the client and the server processes just happen to be on the same machine.

    As for your protocol adapter error, in general it is pointless to post an error message without the full, complete, and exact command that created the error.  (Remember that in future postings).  But in this case I can make a very educated guess. You tried to start a bequeth (not network) connection with a command like this:  'sqlplus scott/tiger'.  And you did not have ORACLE_SID set to specify the name of a local database whose background windows service had been started.

    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 11.2.0.1.0 Production on Tue May 31 18:10:23 2016
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12560: TNS:protocol adapter error
    
    
    Enter user-name:
    C:\>set ORACLE_SID=lawton
    
    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 11.2.0.1.0 Production on Tue May 31 18:10:37 2016
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL>
    

    As for your comment about knowing you don't need TNS_ADMIN but not being able to make it work any other way, all I can say is "what a shame".  By not digging in and figuring it out, you deprived yourself of a very valuable learning experience.

  • Dude!
    Dude! Member Posts: 22,830 Black Diamond
    edited Jun 1, 2016 3:53PM

    Looks to me like you may have an incorrect TNS_ADMIN variable pointing to the wrong configuration files. ORA-12154 means that no listener exists for the given connect string (tnsnames.ora) and ORA-12560 usually means that the listener configuration does not point to any existing network or valid oracle home (listener.ora). If you have multiple oracle homes, make sure to update and run the correct listener. Since you ran netca prior to configuring TNS_ADMIN, you may have configured the wrong configuration files.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jun 1, 2016 3:56PM
    Dude! wrote:
    
    ORA-12154 means that no listener exists for the given connect string (tnsnames.ora)
    

    WRONG! (TNS-12514 is the error you think about)

    ora-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
    ora-12154 NEVER involves the listener, the database itself or anything on the DB Server.
    ora-12154 occurs when client requests a connection to some DB server system using some connection string.
    TNS-03505 is thrown by tnsping & is same error as ora-12154 thrown by sqlplus or others.
    The lookup operation fails because the name provided can NOT be resolved to any remote DB.
    The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
    The most frequent cause for the ora-12154 error is when the connection alias can not be found in tnsnames.ora.

    The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.

  • Dude!
    Dude! Member Posts: 22,830 Black Diamond
    edited Jun 1, 2016 4:29PM
    WRONG! (TNS-12514 is the error you think about)
    

    Did I outline anything different? If tnsnames.ora is configured improperly using a wrong address or service name, then obviously the listener cannot be contact, since the appropriate listener does not exist. I did not say it is a problem with the listener.

  • DBelt
    DBelt Member Posts: 209 Green Ribbon
    edited Jun 1, 2016 5:25PM

    Here are various keys from the registry:

    ORACLE_HOME = D:\app_orcl\product\11.2.0\dbhome_2

    ORACLE_HOME_NAME = OraDb11g_home1 (or home_2)  I did a reinstall this value did not get changed so I changed it manually to try both ways, still broken)

    ORACLE_SID = orcl

    TNS_ADMIN is not defined in the registry.

    Define TNS_ADMIN environment variable = D:\app_orcl1\product\11.2.0\dbhome_2\NETWORK\ADMIN

    PATH env variable starts with D:\app_client32\product\11.2.0\client_1\bin;D:\app_orcl\product\11.2\dbhome\bin (there may be typos here because I have to fat-finger)

    Contents of files is RNS_ADMIN config files:

    listener.ora

    LISTENER =

         (DESCRIPTION_LIST =

              (DESCRIPTION =

                   (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))

                   (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))

              )

         )

       ADDR_BASE_LISTENER = D:\app_orcl

    sql.ora

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAME.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    tnsnames.ora

    ORCL =

         (DESCRIPTION =

              (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))

              (CONNECT_DATA =

                   (SERVER = DEDICATED)

                   (SERVICE_NAME = orcl)

              )

    results of tnsping orcl:

    tnsping orcl

    Used TNSNAMES adapter to resolve the alias

    Attempting to contace <CONTENTS OF TNSNAMES.ORA>

    OK (10 msec)

    When I first create the database sqlplus opens the instance without any problems

    Installing the 32 bit client breaks the sqlplus login with the TNS porotocol adapter error.

    I don't even try to set up DNS parameters at all and it breaks.

    I have tried so many different things as suggested by so many different sites, threads, and so forth and none of them have helped.

    I hope this helps to understand the results I get.

    I am sure I am missing something simple but I just can't find it.

  • Dude!
    Dude! Member Posts: 22,830 Black Diamond
    edited Jun 1, 2016 6:24PM
    When I first create the database sqlplus opens the instance without any problems
    Installing the 32 bit client breaks the sqlplus login with the TNS porotocol adapter error.
    

    Have you verified that your database instance is actually started?

  • Dude!
    Dude! Member Posts: 22,830 Black Diamond
    edited Jun 1, 2016 6:27PM

    Did you install the 32-bit client in a different oracle home than the database, which is required?

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Jun 1, 2016 6:29PM

    What happens when you use tnsping now? What is the FULL error message?

    You say you've tried so many things but you haven't said what any of them were.

    EdStevens has blogged extensively on the subject , here's what I found for ora-12154 Troubleshooting ora-12154 :: edstevensdba Your issue is that the connection identifier you are using cannot be found in a tnsnames.ora file .

    If you want help fixing this you'll have to tell us exactly what you have done to receive the error. What does tnsping <connection string> return? Include your tnsnames.ora file.

  • DBelt
    DBelt Member Posts: 209 Green Ribbon
    edited Jun 1, 2016 7:02PM
    Dude! wrote:
    
    
    When I first create the database sqlplus opens the instance without any problems
    Installing the 32 bit client breaks the sqlplus login with the TNS porotocol adapter error.
    
    
    Have you verified that your database instance is actually started?
    

    When I attempt to log in via SQLPlus I am prompted for username and password.  Immediately upon login I get the TNS Adapter protocol error and not SQL prompt.

This discussion has been closed.