9 Replies Latest reply: Mar 13, 2012 7:47 AM by karthiksingh_india RSS

    ORA-12560 TNS protocol adapter error

    user11017933
      Hi, all,
      Thanks in advance.
      I have 2 oracle homes(9i, 11g) sitting on windows server 2003 now, 11g is new installed successfully,listener.ora and tnsnames.ora are configured. I can connect to the 11g database from SQL developer remotely. The problem is I can not log in using sqlplus either on server side nor on client side.

      From server side, the error is:
      ORA-12560 TNS protocol adapter error
      From client side, the error is ORA-12170,TNS connect timeout occured.

      Another problem: after 11g is installed, the command window displays 11.2.0.1 version on the server(like this: SQL*PLUS Release 11.2.0.1.0 Production on Mon Feb 5 09:20:30 2012)
      and after I move 9i home before 11g for the env PATH, it changed to 9.2( wanted to test if 9i sqlplus works or not, actually it did not work). Now I changed env PATH as before, but the command window still show 9.2 version, not the 11 one. any idea about this?

      Any help is appreciated.

      Edited by: user11017933 on Feb 6, 2012 12:37 PM
        • 1. Re: ORA-12560 TNS protocol adapter error
          sb92075
          user11017933 wrote:
          Hi, all,
          Thanks in advance.
          I have 2 oracle homes(9i, 11g) sitting on windows server 2003 now, 11g is new installed successfully,listener.ora and tnsnames.ora are configured. I can connect to the 11g database from SQL developer remotely. The problem is I can not log in using sqlplus either on server side nor on client side.

          From server side, the error is:
          ORA-12560 TNS protocol adapter error
          From client side, the error is ORA-12170,TNS connect timeout occured.

          Any help is appreciated.
          what is ORACLE_SID for each database?

          post results from following OS commands

          set
          lsnrctl status
          lsnrctl service

          COPY commands & results then PASTE all back here
          • 2. Re: ORA-12560 TNS protocol adapter error
            user11017933
            thanks so much for your quick response!
            oracle_sid: one is orcl, the other is test
            I don't know why I can not see the listener now (it was there and works fine, looks more problems come)

            LSNRCTL> status listenerehms
            TNS-01101 Could not find service name
            NNC-00405 network failure

            Edited by: user11017933 on Feb 6, 2012 1:02 PM
            • 3. Re: ORA-12560 TNS protocol adapter error
              sb92075
              user11017933 wrote:
              thanks so much for your quick response!
              oracle_sid: one is orcl, the other is ehms

              I don't know why I can not see the listener now (it was there and works fine, looks more problems come)

              LSNRCTL> status listenerehms
              TNS-01101 Could not find service name
              NNC-00405 network failure
              are you unwilling or incapable of following simple directions previously provided?
              • 4. Re: ORA-12560 TNS protocol adapter error
                user11017933
                Sorry, just realize how to copy the text from command line.

                C:\Documents and Settings\Administrator>lsnrctl services

                LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 06-FEB-2012 14:03:
                47

                Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Data-server)(PORT=1521)
                ))
                Services Summary...
                Service "ORA.DATA" has 2 instance(s).
                Instance "ORA", status UNKNOWN, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0
                LOCAL SERVER
                Instance "ORA", status READY, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0 state:ready
                LOCAL SERVER
                Service "ORAXDB.DATA" has 1 instance(s).
                Instance "ORA", status READY, has 1 handler(s) for this service...
                Handler(s):
                "D000" established:0 refused:0 current:0 max:1002 state:ready
                DISPATCHER <machine: DATA-SERVER, pid: 4960>
                (ADDRESS=(PROTOCOL=tcp)(HOST=data.local)(PORT=1106))
                Service "PLSExtProc" has 1 instance(s).
                Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0
                LOCAL SERVER
                Service "TEST.DATA" has 1 instance(s).
                Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
                Handler(s):
                "DEDICATED" established:0 refused:0
                LOCAL SERVER
                The command completed successfully

                C:\Documents and Settings\Administrator>lsnrctl status

                LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 06-FEB-2012 14:04:
                44

                Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=data-server)(PORT=1521)
                ))
                STATUS of the LISTENER
                ------------------------
                Alias LISTENER
                Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc
                tion
                Start Date 06-FEB-2012 08:07:10
                Uptime 0 days 5 hr. 57 min. 34 sec
                Trace Level off
                Security OFF
                SNMP OFF
                Listener Parameter File F:\oracle\ora92\network\admin\listener.ora
                Listener Log File F:\oracle\ora92\network\log\listener.log
                Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=data-server.Data.local)(PORT=1
                521)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=data-server.Data.local)(PORT=8
                083))(Presentation=HTTP)(Session=RAW))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=data-server.Data.local)(PORT=2111)(Presentation=FTP)(Session=RAW))
                Services Summary...
                Service "ORA.DATATRAKS" has 2 instance(s).
                Instance "ORA", status UNKNOWN, has 1 handler(s) for this service...
                Instance "ORA", status READY, has 1 handler(s) for this service...
                Service "ORAXDB.DATATRAKS" has 1 instance(s).
                Instance "ORA", status READY, 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 "TEST.DATATRAKS" has 1 instance(s).
                Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
                The command completed successfully
                • 5. Re: ORA-12560 TNS protocol adapter error
                  sb92075
                  user11017933 wrote:
                  Sorry, just realize how to copy the text from command line.
                  Should I be satisfied with you doing 2 out of 3 requested commands?

                  the running listener is the V9 listener.

                  which SID is the V9 DB?
                  • 6. Re: ORA-12560 TNS protocol adapter error
                    user11017933
                    which SID is the V9 DB? -- ORA ( sorry, checked again, thanks for your patience)
                    • 7. Re: ORA-12560 TNS protocol adapter error
                      user11017933
                      any ideas? thanks
                      • 8. Re: ORA-12560 TNS protocol adapter error
                        user11017933
                        What runs before Env PATH for oracle?
                        • 9. Re: ORA-12560 TNS protocol adapter error
                          karthiksingh_india
                          Hi,

                          me too got the same problem and

                          MY LISTENER SERVICE SHOWS*

                          C:\Documents and Settings\SKS>lsnrctl service

                          LSNRCTL for 32-bit Windows: Version 11.1.0.7.0 - Production on 13-MAR-2012 18:12:40

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

                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
                          Services Summary...
                          Service "VESS" has 1 instance(s).
                          Instance "VESS", status UNKNOWN, has 1 handler(s) for this service...
                          Handler(s):
                          *"DEDICATED" established:0 refused:0*
                          LOCAL SERVER
                          The command completed successfully


                          MY LISTENER STATUS SHOWS*

                          C:\Documents and Settings\SKS>lsnrctl status

                          LSNRCTL for 32-bit Windows: Version 11.1.0.7.0 - Production on 13-MAR-2012 17:49:37

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

                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=_127.0.0.1_)(PORT=1521))) -- -- -- previous host name is SKS
                          STATUS of the LISTENER
                          ------------------------
                          Alias LISTENER
                          Version TNSLSNR for 32-bit Windows: Version 11.1.0.7.0 - Production
                          Start Date 13-MAR-2012 17:45:31
                          Uptime 0 days 0 hr. 4 min. 7 sec
                          Trace Level off
                          Security ON: Local OS Authentication
                          SNMP OFF
                          Listener Parameter File C:\app\SKS\product\11.1.0\db_1\network\admin\listener.ora
                          Listener Log File c:\app\sks\diag\tnslsnr\computer\listener\alert\log.xml
                          Listening Endpoints Summary...
                          *(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))*
                          Services Summary...
                          Service "VESS" has 1 instance(s).
                          Instance "VESS", status UNKNOWN, has 1 handler(s) for this service...
                          The command completed successfully


                          MY SET SHOWS*

                          C:\Documents and Settings\SKS>set
                          ALLUSERSPROFILE=C:\Documents and Settings\All Users
                          APPDATA=C:\Documents and Settings\SKS\Application Data
                          CommonProgramFiles=C:\Program Files\Common Files
                          COMPUTERNAME=COMPUTER
                          ComSpec=C:\WINDOWS\system32\cmd.exe
                          FP_NO_HOST_CHECK=NO
                          HOMEDRIVE=C:
                          HOMEPATH=\Documents and Settings\SKS
                          LOGONSERVER=\\COMPUTER
                          NUMBER_OF_PROCESSORS=2
                          ORACLE_HOME=C:\app\SKS\product\11.1.0\db_1\
                          ORACLE_HOSTNAME=SKS
                          OS=Windows_NT
                          Path=C:\app\SKS\product\11.1.0\db_1\bin;C:\app1\SKS\product\11.1.0\db_2\bin;C:\WINDOWS\system32;C:\WINDOW
                          S;C:\WINDOWS\System32\Wbem
                          PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
                          PROCESSOR_ARCHITECTURE=x86
                          PROCESSOR_IDENTIFIER=x86 Family 6 Model 15 Stepping 13, GenuineIntel
                          PROCESSOR_LEVEL=6
                          PROCESSOR_REVISION=0f0d
                          ProgramFiles=C:\Program Files
                          PROMPT=$P$G
                          SESSIONNAME=Console
                          SystemDrive=C:
                          SystemRoot=C:\WINDOWS
                          TEMP=C:\DOCUME~1\SKS\LOCALS~1\Temp
                          TMP=C:\DOCUME~1\SKS\LOCALS~1\Temp
                          USERDOMAIN=COMPUTER
                          USERNAME=SKS
                          USERPROFILE=C:\Documents and Settings\SKS
                          VBOX_INSTALL_PATH=C:\Program Files\Oracle\VirtualBox\
                          windir=C:\WINDOWS

                          Please Help

                          Thanks in Advance
                          Karthik...