1 2 Previous Next 23 Replies Latest reply: Oct 14, 2010 6:46 PM by intgUser RSS

    ORA-12560: TNS:protocol adapter error

    244142
      Microsoft Windows XP [Version 5.1.2600]
      (C) Copyright 1985-2001 Microsoft Corp.

      C:\Documents and Settings\landon kelsey>cd\

      C:\>sqlplus / as sysdba >>>>>> I tried all sorts of combinations (works everytime under Fedora 9)

      SQL*Plus: Release 11.1.0.6.0 - Production on Wed Dec 31 21:19:57 2008

      Copyright (c) 1982, 2007, Oracle. All rights reserved.

      ERROR:
      ORA-12560: TNS:protocol adapter error

      XP Prof SP3 UNEVENTFUL SUCCESSFUL INSTALL...NO ERRORS..yes I installed a windows Loopback Adaptor

      Yes I created a database!
      ALL THESE 3 ARE SET
      ORACLE_HOME
      ORACLE_SID
      ORACLE_GDN
      FIREWALLS INTRUSION PREVENTION OFF
      LISTENER ON
      ORACLE DATABASE SERVER IS STARTED
      REBOOTED MANY TIMES

      I DO THE SAME THING UNDER FEDORA 9 AND EVERYTHING IS OK!

      I've done a search on 12560 in this forum and I seen nothing I've forgotten.

      I also did a google search and NOTHING

      I could understand if it had been a week or two and I had messed with all sorts of internal.

      It must therefore be something simple!

      The problem with most Oracle docs is that they jump immediately

      into the deep details where neophytes can't swim. This doc problem has only gotten worse.

      Marketing content is also in the way.

      files but NOT!

      SQLDEVELOPER shows tabs for MySQL and MS SQL Server! Great! I don't remember seeing that under Fedora 9!
        • 1. Re: ORA-12560: TNS:protocol adapter error
          OrionNet
          Hello,

          What's your tnsnames.ora entry looks like and listener.ora? Also make sure listener is runnign and post output of

          C:> lsnrctl status

          Your tnsnames.ora entry should looks like this, hope this helps.
          Where DSS should be replaced with your database instance name.
          
          DSS =
            (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = <ipaddress or hostname>)(PORT = 1521))
              (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = DSS)
                (INSTANCE_NAME=DSS)
              )
            )
          C:>sqlplus "/as sysdba"

          Regards

          Edited by: OrionNet on Jan 1, 2009 11:18 AM
          • 2. Re: ORA-12560: TNS:protocol adapter error
            244142
            working on getting what has been requested

            problem is that the windows 11g1 remove does not remove everything and

            used disk space accumulates

            using regedit I see many many erroneous fragments left behind

            the environment variables are not set so I set them and that conflicts with the

            other stored values

            there is also some mystery concerning specification of gdn and sid

            not sure yet but in the future I would specify both having 8 characters or less

            or else the installed may HELP YOU OUT!

            another tip....make copious notes about entries you make...better still screenshots

            In the first install screen, the installer will give you a suggested path.>>>replace spaces in the path with a "_".

            If not you'll get another chance.

            An inherent problem for newbies:
            there are many names some created mysteriously by the installer..etc

            sid
            gdn
            database name
            service name

            I've seen the installer change LMKGDN to LMKGD and use it in various files.

            The the documentation (must have been written by _____s in a big hurry)

            For years I've noticed that a step that works is (after install) is to remove any database created and make a fresh one!
            This has been working for 10 years! Problem is that disk space for the first database is NOT removed even with an install.

            I just wiped my HD XP Prof SP 3 clean of Oracle stuff to start over (Oracle remove doesn't do this)

            I may have broken something removing items from the registry. If so I'll reinstall XP Prof SP3.

            Just in : after removing every trace of Oracle from this computer XP Prof SP3, I reinstalled.

            A GDN and SID from days ago have appeared in the environment


            C:\>set ORACLE_GDN
            ORACLE_GDN=LMKIIIGDN

            C:\>set ORACLE_SID
            ORACLE_SID=LMKIIIGDNSID

            Conclusion: Oracle needs to pull this 11g1 for Windows!

            Edited by: landonmkelsey on Jan 2, 2009 12:09 PM
            • 3. Re: ORA-12560: TNS:protocol adapter error
              OrionNet
              You should set env variables pointing to your right oracle home location. ALso verify window services if none of old oracle and listener related service lingering around. Were you able to successfully run following command and if yes/no, post its output here?

              Regards
              • 4. Re: ORA-12560: TNS:protocol adapter error
                244142
                tonight
                • 5. Re: ORA-12560: TNS:protocol adapter error
                  581825
                  The advice you have been given elsewhere in this thread to check the listener is running and your tnsnames.ora contents is completely pointless: you're doing a local connection on the server itself, and therefore tns isn't even used. You can shut the listener down and delete your tnsnames.ora, and you will still be able to make local connections!

                  You assert that ORACLE_SID is set, but we don't actually have any evidence of that. If it is not set, then Oracle doesn't know which service it should be attaching to -and will throw a 12560 error as a consequence.

                  Just repeat your test, but preface it with an explicit SET ORACLE_SID=XXXX first. The "XXXX" bit there has to match, exactly, with the last bit of the name of the relevant OracleServiceXXXX that needs to be running before trying anything at all, of course. If you've got OracleServiceFRED, then set ORACLE_SID=FRED. If it's OracleServiceWILMA, then set ORACLE_SID=WILMA, and so on. If that works, then you'll need to review how you go about setting it in configuration files so that it doesn't need to be set manually each time -but we can cross that bridge when you get to it.
                  • 6. Re: ORA-12560: TNS:protocol adapter error
                    244142
                    Everything in previous posts I said is true, still true!

                    For example, 11g1 for windows has a faulty uninstaller.

                    The whole system needs rethinking if any thinking in the first place occurred!

                    I worked around the ORA-12560: TNS:protocol adapter error

                    and got the 11g1 database to work on Windows XP Prof SP3.

                    here is how!

                    After removing every trace of 11g1 I could (even from the registry), I reinstalled and got the same problem.

                    So...I went into the Windows Start->Programs menu and selected "Database Control"

                    This brought up https://landon:1158/em replace "landon" with your host/computer name...localhost probably works.

                    Look in the hosts file deep in C:\windows

                    Before install you should have installed the Windows Loopback Protocol Adaptor.

                    I am now in Oracle Enterprise Manager (OEM) in IE7 (or Firefox) after logging in as
                    sys nothing else would work
                    password
                    log in as sysdba

                    I gave sys every privilege known to the free world and tried to use sqlplus (that comes with OEM but that didn't work!)

                    to create my own user lmkiii.

                    Still under sys I created my own user lmkiii and gave myself every privilege known to the free world.

                    Not a good idea but I am in a learning stage and tired of Oracle baloney!

                    Then I opened sql developer and made a connection to user lmkiii using the lmkgdn I supplied just before install.

                    I was able to create a complex table I use for sanity checks.

                    Now I can
                    C:\>sqlplus lmkiii/rumprocella@landon/lmkgdn

                    without error and run a select statement on my sanity table.

                    ________________________new subject

                    HOWEVER: I still get


                    C:\>sqlplus / as sysdba@landon/lmkgdn

                    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 2 19:16:52 2009

                    Copyright (c) 1982, 2007, Oracle. All rights reserved.

                    ERROR:
                    ORA-12560: TNS:protocol adapter error


                    Enter user-name:

                    ........................or...................................
                    C:\>sqlplus / as sysdba

                    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 2 19:17:55 2009

                    Copyright (c) 1982, 2007, Oracle. All rights reserved.

                    ERROR:
                    ORA-12560: TNS:protocol adapter error


                    Enter user-name:

                    Finally:

                    I wasted plenty of time on this I didn't waste on 11g1 on Fedora 9!

                    Under Fedora 9 11g1, I never saw ORA-12560: TNS:protocol adapter error...not once!

                    I am far from being a Windows registry guru but I don't know how any oracle references got

                    by my own removal routine.

                    The installer doesn't ask for the SID on the first create...it assumes the GDN and the SID are the same.

                    GDN and SIDs several days old are in the registry...I fixed them and prob screwed up something else.

                    Under the OEM, sysdba doesn't show up as a user!
                    • 7. Re: ORA-12560: TNS:protocol adapter error
                      OrionNet
                      Hello,

                      Did you try seting ORACLE_SID, here is small test if we have incorrect or sid not correctely.
                      C:\>set ORACLE_SID=DSP
                      C:a>sqlplus "/as sysdba"
                      SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 2 20:56:30 2009
                      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                      
                      ERROR:
                      ORA-12560: TNS:protocol adapter error
                      
                      Enter user-name:
                      ERROR:
                      ORA-12560: TNS:protocol adapter error
                      
                      Enter user-name:
                      ERROR:
                      ORA-12560: TNS:protocol adapter error
                      SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
                      Setting up a valid SID or SERVICE NAME_
                      C:\>set ORACLE_SID=DSS
                      C:\>sqlplus "/as sysdba"
                      
                      SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 2 20:59:04 2009
                      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                      Connected to:
                      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                      With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
                      Also, to access db cotnrol check if your database control is running or not using following command post its output.
                      C:> emctl status dbconsole

                      Regards
                      • 8. Re: ORA-12560: TNS:protocol adapter error
                        244142
                        You need to read my posts carefully.

                        Nevertheless:


                        C:\>set ORACLE_SID=lmkgdn

                        C:\>sqlplus "/as sysdba"

                        SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 2 20:59:00 2009

                        Copyright (c) 1982, 2007, Oracle. All rights reserved.

                        ERROR:
                        ORA-01031: insufficient privileges


                        Enter user-name:

                        Never presume Oracle is "absolute" and "right"
                        • 9. Re: ORA-12560: TNS:protocol adapter error
                          Paul M.
                          C:\>set ORACLE_SID=lmkgdn

                          C:\>sqlplus "/as sysdba"

                          SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 2 20:59:00 2009

                          Copyright (c) 1982, 2007, Oracle. All rights reserved.

                          ERROR:
                          ORA-01031: insufficient privileges
                          Assuming your OS user is member of ORA_DBA group (check that out).

                          Also, you should have a SQLNET.ORA file, in ORACLE_HOME\network\admin, which should contain a line like this

                          SQLNET.AUTHENTICATION_SERVICES = (NTS)
                          • 10. Re: ORA-12560: TNS:protocol adapter error
                            244142
                            everything is fine now due to my tenacity in working around obvious Oracle problems.

                            I stand on my previous posts! The problem is obviously that Oracle pushed this product

                            out the door without real testing.

                            from the black DOS Windows window, I can access my user tables

                            and sql developer works great!

                            IT HAD NO PROBLEM making the connection!

                            My PHP 5.2.8 is connecting/accessing FINE!

                            I never touched this file:

                            # sqlnet.ora Network Configuration File: C:\app\landon_kelsey\product\11.1.0\db_1\network\admin\sqlnet.ora
                            # Generated by Oracle configuration tools.

                            # This file is actually generated by netca. But if customers choose to
                            # install "Software Only", this file wont exist and without the native
                            # authentication, they will not be able to connect to the database on NT.

                            SQLNET.AUTHENTICATION_SERVICES= (NTS)

                            NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

                            You have helped me a lot in the past but I must caution all to

                            never believe that Oracle is flawless.

                            Luckily I didn't have to reinstall XP Prof SP3 to get rid of old GDN SID artifacts.

                            The Oracle uninstall is severely flawed! See previous posts.

                            It is a wonder that I didn't mess up the registry repairing artifact registry data.

                            I encountered many brick walls but made it anyway!

                            A hint for those following:

                            I have found in the last 10 years that after initial install, it sometimes helps to delete

                            the database automatically created and create a new one. Problem with this 11g1

                            for windows is that the disk space is not deleted and I my scsi drive has only 38 gig!
                            • 11. Re: ORA-12560: TNS:protocol adapter error
                              244142
                              I never had problems with my user!

                              Almost everything works now and I can use em to

                              do any admin stuff I need done.

                              One stupid thing I ran into is that one of the sqlplus windows (not the DOS window)

                              didn't like the ";" at the end of the statement.

                              The MySQL command line is great in that using the up arrow, one

                              can go back and edit/view previous SQL.
                              • 12. Re: ORA-12560: TNS:protocol adapter error
                                Paul M.
                                never believe that Oracle is flawless.
                                Is there someone who think so ? :-)
                                The Oracle uninstall is severely flawed!
                                Did you have the same problems with Unix/Linux ?
                                • 13. Re: ORA-12560: TNS:protocol adapter error
                                  244142
                                  previous post states I had NO problem with 11g1 under Fedora 9

                                  I did have problems with php 5.2.6 oci_connect and the forums are filled with

                                  people having that problem!

                                  BTW: under windows XP Prof SP3 ,

                                  php 5.2.8 connected up to 11g1 with only a few changes to the .php script from Fedora 9

                                  minutes not days
                                  • 14. Re: ORA-12560: TNS:protocol adapter error
                                    581825
                                    I asked you, simply, to post the results of you doing a SET ORACLE_SID=somethingorother, and you failed to do so, instead posting a great screed about how wonderful Linux is and it never has these problems. It is true that Oracle on Linux will rarely raise an ORA-12560 error, because that is a consequence of Windows needing a service to allocate the memory and process space architecture within which threads can be created. Specify the wrong memory-process name when attempting to create a thread, and you will get a TNS error. But in an entirely process architecture such as Unix or Linux, you don't get that particular issue. Try and connect to an instance when the wrong ORACLE_SID is set on those platforms, and you'll simply get a 'connected to an idle instance' message -which is fine until you try to issue the startup command, at which point the wrongness of your ORACLE_SID will become apparent. Either way, on either platform, not setting your ORACLE_SID environment variable properly DOES cause equally-bad outcomes.

                                    Finally, you DO manage to post the results of doing what I asked you to do, prefaced with a completely unnecessary "you need to read my posts carefully". And what do we see when you finally get around to doing what you were asked to do? Goodness me: not a single TNS 12560 error in sight. Instead, you get a trivial error about not having the appropriate privileges. What does that tell someone who thinks about it before grandstanding about it? It tells them that your ORIGINAL problem, the one causing the ORA-12560, was indeed due to the fact that your ORACLE_SID was NOT set correctly. Set it correctly, and explicitly, and you don't get TNS protocol adapter errors, period.

                                    Now, OK: you finally set it and got a completely different error about SYS not having the right privileges. I'd call that progress, myself -and is the reason why these things are best taken one step at a time before making generalised judgements about things.

                                    That error, that is, your new one about having insufficient privileges, simply means that your OS user is not a member of the ORA_DBA local group on that server OR that your sqlnet.ora is not setup correctly to do OS user authentication.. It's got nothing at all to do with Oracle being right or always right, other people not reading your speeches carefully enough, or anything else. It simply has everything to do with you not setting up your server correctly. In this case, you need to spend two seconds adding your OS user to the DBA group, and you'll be fine; or two seconds setting sqlnet.ora to the correct NTS settings. The first is the exact equivalent to you creating the oracle user as a member of the dba group; the second has no Linux equivalent because OS authentication is native to Oracle on Linux and doesn't require a configuration file to switch it on.

                                    In future, try not to proselytize about systems (OSes) that you're not fully qualified to comment upon. The error you started this thread with is a simple mis-configuration of your ORACLE_SID. The second error is a simple configuration issue with your local groups or your sqlnet.ora file. Neither error warrants any hyperbole from you about the inadequacies of Oracle in general or Oracle-on-Windows specifically. Both errors are down to your configuration actions, not Oracle's presumptions, assumptions or peculiarities. These configuration issues are also extremely well-documented.

                                    In [http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#ADMIN00102|http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#ADMIN00102] we read: "+Before you attempt to use SQL*Plus to connect locally to an Oracle instance, you must ensure that environment variables are set properly. ... On Windows, you must set only the ORACLE_SID environment variable to select an instance before starting SQL*Plus.+"

                                    In [http://download.oracle.com/docs/cd/B19306_01/win.102/b14304/authen.htm#sthref471|http://download.oracle.com/docs/cd/B19306_01/win.102/b14304/authen.htm#sthref471] we read "+When you install Oracle Database, a special Windows local group called ORA_DBA is created (if it does not already exist from an earlier Oracle Database installation), and your Windows username is automatically added to it. Members of local group ORA_DBA automatically receive the SYSDBA privilege. Membership in ORA_DBA enables you to connect to local Oracle Database servers without a password with the command CONNECT / AS SYSDBA+"

                                    And in [http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm#sthref371|http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm#sthref371] we have this: "+Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. ... Authentication Methods Available with Oracle Net Services (include) nts for Windows NT native authentication+"

                                    I will say, too, that you will only ever stumble over these sorts of errors on Windows when you've done the one thing you absolutely should never do: install, deinstall, reinstall. Because NTS is set as an authentication service by default. And you, the local PC user, are set as a member of the ORA_DBA group by default. And the ORACLE_SID is set, correctly, by default when you get a starter database created as part of the install. So for you to not have these things set correctly can only happen if you do repeated installations. I've been writing and teaching about the inadvisability of doing that since 1999: once Oracle is allowed to touch a Windows system, it screws it, royally. Going back is not an option (not without a lot of fiddling and registry editing by hand, anyway). And it's precisely because of that I've been advising people who want to learn Oracle on Windows boxes which cannot just be wiped and reinstalled from scratch (starting with the OS, that is) to use virtual machines to learn with, because those can be wiped at will.

                                    No-one is, therefore, claiming Oracle on Windows is perfect. It's installation and de-installation procedures have always been something of a train wreck, and lots of people have posted lots of words on that subject for many years past. But in the end, think about it: if you were rolling out a new production database server, you would almost certainly build it fresh and do a clean installation on top of a clean OS. You should always (in my view) approach Oracle in that way, and do clean installs on clean OSes even in dev and learning environments.

                                    But either way, it comes down to understanding the platform you're working with. Windows has its quirks like these. Linux has its fair share of them, too, if one is not especially tuned to the way Linux servers work (you don't ever have to edit sysctl.conf on a Windows box, for example!). It's just different horses for different courses, and making sure you are as clued up as to how any particular horse or course is supposed to work. In this specific case, it happens to have been your configuration that was iffy. Chalk it up to experience and move on... there's really no point in drawing supposedly deep-and-meaningful conclusions about things you can't configure correctly.
                                    1 2 Previous Next