9 Replies Latest reply on May 28, 2013 10:10 AM by Jim Smith

    SQLDEVELOER =>ORA-12505 / SQLPLUS=>ORA-12560: TNS:protocol adapter error

    user1048437
      Hi Guys, I recently installed Oracle11g on my windows 7 laptop. I get ORA-12560 error. I have installed and uninstalled Oracle couple times, I guess it may have messed-up windows services or some other settings.

      Oracle Universal installed created following listener.ora and tnsnames.ora.
      The tnsnames.ora looks incomplete to me.

      Any help in fixing this problem is appreciated.

      ************listener.ora****************************************

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\orains\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\orains\product\11.2.0\dbhome_1\bin\oraclr11.dll")
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = abc-xyz-PC.gateway.2wire.net)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER = C:\orains

      *******tnsnames.ora************************

      ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
      )
      )

      **************************************************************************

      C:\Users\abc-xyz>LSNRCTL STATUS

      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 25-MAY-2013 02:21
      :34

      Copyright (c) 1991, 2010, Oracle. All rights reserved.

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
      ction
      Start Date 23-MAY-2013 23:43:57
      Uptime 1 days 2 hr. 37 min. 39 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File C:\orains\product\11.2.0\dbhome_1\network\admin\listen
      er.ora
      Listener Log File c:\orains\diag\tnslsnr\abc-xyz-PC\listener\alert\
      log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=abc-xyz-PC)(PORT=1521)))
      Services Summary...
      Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
        • 1. Re: ORA-12560: TNS:protocol adapter error
          Jim Smith
          What are you trying to do when you get this error?

          What are your SQLDeveloper connection settings?

          The tnsnames won't contain any entries until you run Net Configuration Assistant and add entries for databases you want to access.
          • 2. Re: ORA-12560: TNS:protocol adapter error
            thatJeffSmith-Oracle
            You installed Oracle, but did you create a database?
            1 person found this helpful
            • 3. SQLDEVELOER =>ORA-12505 / SQLPLUS=>ORA-12560: TNS:protocol adapter error
              user1048437
              Jim,

              when I try to connect from SQLDEVELOPER
              Hostname => Localhost
              Port=>1521
              SID=XE

              I get
              ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

              when I try to connect from SQLPLUS I get
              C:\Users\abc-xyz>sqlplus scott/tiger

              SQL*Plus: Release 11.2.0.1.0 Production on Sat May 25 12:05:08 2013

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

              ERROR:
              ORA-12560: TNS:protocol adapter error

              Enter user-name:

              Both are related I would guess.

              Is Net Configuration Assitant not for remote connections only? In this case my laptop is everything.
              I have successfully connected to the database by doing just what I have done now, nothing more.
              • 4. SQLDEVELOER =>ORA-12505 / SQLPLUS=>ORA-12560: TNS:protocol adapter error
                user1048437
                Jeff,

                Does it not create the SCOTT schema by default?
                I think, I selected the option to install server when I was running the universal installer on my laptop.
                How do I do check; if it created a database or not?
                • 5. Re: SQLDEVELOER =>ORA-12505 / SQLPLUS=>ORA-12560: TNS:protocol adapter error
                  1010561
                  Hi,

                  Go into the SQL plus consle and type the following

                  / as sysdba
                  shutdown abort
                  startup

                  Regards,
                  Geeta
                  • 6. Re: SQLDEVELOER =>ORA-12505 / SQLPLUS=>ORA-12560: TNS:protocol adapter error
                    Jim Smith
                    Looks like you don't have a database.
                    The sqlplus error means you don't have a default ORACLE_SID set. The sqldeveloper error means the connection identifier you are using Xe? Orcl? Isnt running.

                    Go into control panel services and look for something called OracleServicexxxx. If it isn't there you don't have a database.
                    If it is there, xxxx is what you should specify in your sqldeveloper connection.
                    • 7. Re: SQLDEVELOER =>ORA-12505 / SQLPLUS=>ORA-12560: TNS:protocol adapter error
                      user1048437
                      Hi,

                      Go into the SQL plus consle and type the following

                      +/ as sysdba+
                      shutdown abort
                      startup

                      Regards,
                      Geeta

                      SQLPLUS / as sysdba did not work
                      it gave me ORA-12560: TNS:protocol adapter error
                      • 8. Re: SQLDEVELOER =>ORA-12505 / SQLPLUS=>ORA-12560: TNS:protocol adapter error
                        user1048437
                        Looks like you don't have a database.
                        The sqlplus error means you don't have a default ORACLE_SID set. The sqldeveloper error means the connection identifier you are using Xe? Orcl? Isnt running.

                        Go into control panel services and look for something called OracleServicexxxx. If it isn't there you don't have a database.
                        If it is there, xxxx is what you should specify in your sqldeveloper connection.

                        Jeff,

                        I don't have anything with OracleServiceXXXX in my windows services.
                        Why is it missing ? Is that something I did or (did not do) during the installation ?
                        I could use Database Configuration Assistant to create a database. I am wondering why did the universal installer not create a database with SCOTT schema, as it normally did.

                        I have following starting with Oracle

                        OracleOraDb11g_home1TNSListener
                        OracleOraDb11g_home2TNSListener
                        OracleOraDb11g_home3TNSListener
                        OracleOraDb11g_home3ClrAgent
                        OracleRemExecService
                        OracleMTSRecoveryService
                        Oracle_app_abc~product_112~1.0_dbhome_1ConfigurationManager

                        I guess OracleOraDb11g_home3TNSListener came with the most recent install. How do I get rid of all the previous listeners? I believe I don't need them.
                        • 9. Re: SQLDEVELOER =>ORA-12505 / SQLPLUS=>ORA-12560: TNS:protocol adapter error
                          Jim Smith
                          user1048437 wrote:
                          Looks like you don't have a database.
                          The sqlplus error means you don't have a default ORACLE_SID set. The sqldeveloper error means the connection identifier you are using Xe? Orcl? Isnt running.

                          Go into control panel services and look for something called OracleServicexxxx. If it isn't there you don't have a database.
                          If it is there, xxxx is what you should specify in your sqldeveloper connection.

                          Jeff,

                          I don't have anything with OracleServiceXXXX in my windows services.
                          Why is it missing ? Is that something I did or (did not do) during the installation ?
                          I guess you didn't select the option to create a database.
                          I could use Database Configuration Assistant to create a database.
                          That is the best option at this stage.
                          I have following starting with Oracle

                          OracleOraDb11g_home1TNSListener
                          OracleOraDb11g_home2TNSListener
                          OracleOraDb11g_home3TNSListener
                          OracleOraDb11g_home3ClrAgent
                          OracleRemExecService
                          OracleMTSRecoveryService
                          Oracle_app_abc~product_112~1.0_dbhome_1ConfigurationManager

                          I guess OracleOraDb11g_home3TNSListener came with the most recent install. How do I get rid of all the previous listeners? I believe I don't need them.
                          Are the previous installations still there? You need to run the net configuration assistant in each installation and delete the listener. If you can't do that set them to disabled. You can also look at the SC utility which can be used to delete services.