1 2 Previous Next 23 Replies Latest reply: May 16, 2013 9:20 AM by SShubhangi RSS

    ORA-12560 : TNS Protocol adapter error

    SShubhangi
      When trying to connect to database from C:\app\user\product\11.2.0\client_1 using sqlplus getting error,

      ORA-12560 : TNS Protocol adapter error

      Previously i was just having server 10.2.0.3.0. installed on my vista machine , above 11g client is the newly installed one
      and with server 10.2.0.3.0. it was working properly

      when trying to connect using the below query,

      sqlplus user/pwd@XE then getting below error ,

      ORA-12154 : TNS : could not resolve the connect identifier

      Server Path,
      C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN

      listener.ora
      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
            (PROGRAM = extproc)
          )
          (SID_DESC =
            (SID_NAME = CLRExtProc)
            (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
            (PROGRAM = extproc)
          )
        )
      
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
            (ADDRESS = (PROTOCOL = TCP)(HOST = My-PC)(PORT = 1521))
          )
        )
      
      DEFAULT_SERVICE_LISTENER = (XE)
      sqlnet.ora
      # 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)
      tnsnames.ora
      XE =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = My-PC)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
          )
        )
      
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
      
      ORACLR_CONNECTION_DATA = 
        (DESCRIPTION = 
          (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) 
          ) 
          (CONNECT_DATA = 
            (SID = CLRExtProc) 
            (PRESENTATION = RO) 
          ) 
        ) 
      Did search too much on the net / Forum

      But still not sure what is the solution for this error

      Please help
        • 1. Re: ORA-12560 : TNS Protocol adapter error
          dariyoosh
          As it seems to me in your listener.ora there is no information about the new oracle installation (11g). In particular within the SID_LIST_LISTENER there are only parameters about the old installation but nothing about the new ORACLE_HOME and the new SID (XE)


          Regards,
          Dariyoosh
          • 2. Re: ORA-12560 : TNS Protocol adapter error
            SShubhangi
            Thanks Dariyoosh...

            what can be done for this ?

            I haven't removed 10 Server just installed 11g client

            Edited by: SShubhangi on May 12, 2013 1:08 PM
            • 3. Re: ORA-12560 : TNS Protocol adapter error
              dariyoosh
              SShubhangi wrote:
              I haven't removed 10 Server just installed 11g client
              Oh, sorry I made a mistake, because I thought that you also upgraded the server. Yet, as I said there is no information in listener.ora for SID=XE (assuming that there is actually an instance referred to by SID=XE)

              Because in your tnsnames.ora you have
              . . .
              XE =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = My-PC)(PORT = 1521))
                  (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = XE)
                  )
                )
              . . . 
              SERVICE_NAME = XE means that there has to be somewhere a SID=XE.
              Did you add this new entry after the installation of the new client?

              If so, then for me, you have also change listener.ora which should look similar to this
              SID_LIST_LISTENER =
                (SID_LIST =
                  (SID_DESC =
                    (SID_NAME = PLSExtProc)
                    (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
                    (PROGRAM = extproc)
                  )
                  (SID_DESC =
                    (SID_NAME = CLRExtProc)
                    (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
                    (PROGRAM = extproc)
                  )
                  (SID_DESC =
                    (SID_NAME = XE)
                    (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
                    (PROGRAM = extproc)
                  )
                )
              Once listener.ora has been changed, you should restart the listener.

              Yet, there is something that still is not clear for me. You said that before, you had 10g server which worked pretty well with the 10g client. Now you have installed a 11g client.

              - Are the server and the client are on the same physical machine?
              - What was the content of the tnsnames.ora before you install the new client?

              Regards,
              Dariyoosh
              • 4. Re: ORA-12560 : TNS Protocol adapter error
                SShubhangi
                I haven't change any setting...

                Server 10 and client 11g are on the same machine

                i donnot rember exactly but i think thr was only server 10 before i installed 11g client
                • 5. Re: ORA-12560 : TNS Protocol adapter error
                  dariyoosh
                  You mean that at the time when the client 10 was on the machine, you used the following to connect to the server?
                  sqlplus user/pwd@XE
                  Also why in listener.ora you write
                  DEFAULT_SERVICE_LISTENER=(XE)
                  instead of writing simply
                  DEFAULT_SERVICE_LISTENER=XE
                  Regards,
                  Dariyoosh
                  • 6. Re: ORA-12560 : TNS Protocol adapter error
                    yoonas
                    Hi,

                    It seems newly installed client home is nor properly configured.


                    Do you have tnsnames in your new 11g client home ? If its not there copy from xe_home/create tnsnames and add an entry for your database.

                    can you start the 11g client sqlplus and try connecting <username>/<password>@My-PC:1521/XE

                    post the output of

                    lsnrctl status

                    Regards
                    Yoonas

                    Edited by: yoonus on May 12, 2013 2:54 AM
                    • 7. Re: ORA-12560 : TNS Protocol adapter error
                      SShubhangi
                      hi yoonus

                      here r the details

                      C:\app\user\product\11.2.0\client_1\network\admin\sample

                      tnsnames.oRA details,
                      # This file contains the syntax information for 
                      # the entries to be put in any tnsnames.ora file
                      # The entries in this file are need based. 
                      # There are no defaults for entries in this file
                      # that Sqlnet/Net3 use that need to be overridden 
                      #
                      # Typically you could have two tnsnames.ora files
                      # in the system, one that is set for the entire system
                      # and is called the system tnsnames.ora file, and a
                      # second file that is used by each user locally so that
                      # he can override the definitions dictated by the system
                      # tnsnames.ora file.
                      
                      # The entries in tnsnames.ora are an alternative to using
                      # the names server with the onames adapter.
                      # They are a collection of aliases for the addresses that 
                      # the listener(s) is(are) listening for a database or 
                      # several databases.
                      
                      # The following is the general syntax for any entry in 
                      # a tnsnames.ora file. There could be several such entries 
                      # tailored to the user's needs.
                      
                      <alias>= [ (DESCRIPTION_LIST =  # Optional depending on whether u have 
                                          # one or more descriptions
                                          # If there is just one description, unnecessary ]
                             (DESCRIPTION=
                               [ (SDU=2048) ]     # Optional, defaults to 2048
                                          # Can take values between 512 and 32K
                               [ (ADDRESS_LIST=    # Optional depending on whether u have
                                          # one or more addresses
                                          # If there is just one address, unnecessary ]
                                 (ADDRESS=
                                [ (COMMUNITY=<community_name>) ] 
                                (PROTOCOL=tcp)
                                (HOST=<hostname>)
                                (PORT=<portnumber (1521 is a standard port used)>)
                                 )
                                 [ (ADDRESS=
                                  (PROTOCOL=ipc)
                                  (KEY=<ipckey (PNPKEY is a standard key used)>)     
                                )
                                 ]
                                 [ (ADDRESS=
                                  [ (COMMUNITY=<community_name>) ]
                                  (PROTOCOL=decnet)
                                  (NODE=<nodename>)
                                  (OBJECT=<objectname>)
                                )
                                 ]
                                    ... # More addresses
                               [ ) ] # Optional depending on whether ADDRESS_LIST is used or not 
                               [ (CONNECT_DATA=
                                (SID=<XE>)
                                [ (GLOBAL_NAME=<global_database_name>) ]
                                 )
                               ]
                               [ (SOURCE_ROUTE=yes) ]  
                             )
                             (DESCRIPTION=     
                               [ (SDU=2048) ]     # Optional, defaults to 2048
                                          # Can take values between 512 and 32K
                               [ (ADDRESS_LIST= ]     # Optional depending on whether u have more
                                          # than one address or not
                                          # If there is just one address, unnecessary
                                 (ADDRESS
                                [ (COMMUNITY=<community_name>) ]
                                (PROTOCOL=tcp)
                                (HOST=<hostname>)
                                (PORT=<portnumber (1521 is a standard port used)>)
                                 )
                                 [ (ADDRESS=
                                  (PROTOCOL=ipc)
                                  (KEY=<ipckey (PNPKEY is a standard key used)>)
                                    )
                                 ]
                                 ...           # More addresses
                               [ ) ]           # Optional depending on whether ADDRESS_LIST  
                                          # is being used
                               [ (CONNECT_DATA=
                                (SID=<XE>)
                                [ (GLOBAL_NAME=<global_database_name>) ]
                                 )
                               ]
                               [ (SOURCE_ROUTE=yes) ]
                             )
                             [ (CONNECT_DATA=
                                 (SID=<XE>)
                                 [ (GLOBAL_NAME=<global_database_name>) ]
                               )
                             ]
                             ...   # More descriptions 
                           [ ) ]     # Optional depending on whether DESCRIPTION_LIST is used or not
                      and when running sqlplus from "C:\app\user\product\11.2.0\client_1\network\admin\sample "
                      it is directly asking user name to connect ...
                      • 8. Re: ORA-12560 : TNS Protocol adapter error
                        dariyoosh
                        As Yoonas said, post also the output of the following to see the list of recognized services and SIDs by the listener.
                        lsnrctl status
                        Regards,
                        Dariyoosh
                        • 9. Re: ORA-12560 : TNS Protocol adapter error
                          SShubhangi
                          from where we need to run this command ?
                          • 10. Re: ORA-12560 : TNS Protocol adapter error
                            yoonas
                            start>run>cmd
                            • 11. Re: ORA-12560 : TNS Protocol adapter error
                              SShubhangi
                              Here is the output,
                              LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-MAY-2013 15:45
                              :40
                              
                              Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                              
                              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
                              STATUS of the LISTENER
                              ------------------------
                              Alias                     LISTENER
                              Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
                              ction
                              Start Date                12-MAY-2013 11:57:39
                              Uptime                    0 days 3 hr. 48 min. 1 sec
                              Trace Level               off
                              Security                  ON: Local OS Authentication
                              SNMP                      OFF
                              Default Service           XE
                              Listener Parameter File   C:\oraclexe\app\oracle\product\10.2.0\server\network\a
                              dmin\listener.ora
                              Listener Log File         C:\oraclexe\app\oracle\product\10.2.0\server\network\l
                              og\listener.log
                              Listening Endpoints Summary...
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=My-PC)(PORT=1521)))
                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=My-PC)(PORT=8080))(Presentation=
                              HTTP)(Session=RAW))
                              Services Summary...
                              Service "CLRExtProc" has 1 instance(s).
                                Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                              Service "PLSExtProc" has 1 instance(s).
                                Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                              Service "XEXDB" has 1 instance(s).
                                Instance "xe", status READY, has 1 handler(s) for this service...
                              Service "XE_XPT" has 1 instance(s).
                                Instance "xe", status READY, has 1 handler(s) for this service...
                              Service "xe" has 1 instance(s).
                                Instance "xe", status READY, has 1 handler(s) for this service...
                              The command completed successfully
                              • 12. Re: ORA-12560 : TNS Protocol adapter error
                                yoonas
                                you should have a tnsnames under C:\app\user\product\11.2.0\client_1\network\admin not the sample one.

                                copy tnsnames from C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\ to C:\app\user\product\11.2.0\client_1\network\admin
                                • 13. Re: ORA-12560 : TNS Protocol adapter error
                                  dariyoosh
                                  In addition, in the case where he has already defined the environment variable TNS_ADMIN based on the old client installation, I think this one is also to be changed accordingly in order to reflect the changes after the new client installation.

                                  Regards,
                                  Dariyoosh
                                  • 14. Re: ORA-12560 : TNS Protocol adapter error
                                    EdStevens
                                    dariyoosh wrote:
                                    In addition, in the case where he has already defined the environment variable TNS_ADMIN based on the old client installation, I think this one is also to be changed accordingly in order to reflect the changes after the new client installation.

                                    Regards,
                                    Dariyoosh
                                    Which is why I recommend that when using TNS_ADMIN, one should create a 'neutral' tns admin directory ... one that is not tied to any particular ORACLE_HOME. I've done this before and located TNS_ADMIN=$ORACLE_BASE/tns_admin. Then put my tnsnames.ora and sqlnet.ora and listener.ora files in $ORACLE_BASE/tns_admin.
                                    1 2 Previous Next