6 Replies Latest reply on Aug 27, 2011 9:52 PM by EdStevens

    ORA-12541: TNS:no listener

    greddy
        Hi,

        I have a problem connecting to oracle database
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
      PL/SQL Release 10.2.0.3.0 - Production
      CORE    10.2.0.3.0      Production
      TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
      NLSRTL Version 10.2.0.3.0 - Production
      
      SQL> conn scott/tiger@ORCL
      ERROR:
      ORA-12541: TNS:no listener
      
      however, I am able to connect if i omit the @ORCL
      
      SQL> conn scott/tiger
      Connected.
      
      This is my TNS entry.
      
      ORCL =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          )
          (CONNECT_DATA =
            (SERVICE_NAME = ORCL)
          )
        )
      
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
        but, i should be able to connect using "conn scott/tiger@ORCL" format instead of just ""conn scott/tiger", because i need to connect to database   using JDBC connection.


        Please help

        -RG
        • 1. Re: ORA-12541: TNS:no listener
          sb92075
          greddy wrote:
            Hi,

            I have a problem connecting to oracle database
          Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
          PL/SQL Release 10.2.0.3.0 - Production
          CORE    10.2.0.3.0      Production
          TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
          NLSRTL Version 10.2.0.3.0 - Production
          
          SQL> conn scott/tiger@ORCL
          ERROR:
          ORA-12541: TNS:no listener
          
          however, I am able to connect if i omit the @ORCL
          
          SQL> conn scott/tiger
          Connected.
          
          This is my TNS entry.
          
          ORCL =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          )
          (CONNECT_DATA =
          (SERVICE_NAME = ORCL)
          )
          )
          
          EXTPROC_CONNECTION_DATA =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
          )
          )
            but, i should be able to connect using "conn scott/tiger@ORCL" format instead of just ""conn scott/tiger", because i need to connect to database   using JDBC connection.


            Please help

            -RG
          no listener is required to connect to LOCAL DB, via BEQUEATH protocol.

          just start the listener by doing as below while logged onto DB Server system

          lsnrctl start
          • 2. Re: ORA-12541: TNS:no listener
            greddy
              I started the listener service.
            after the listener is started, iam getting this error.
            SQL> conn scott/tiger@ORCL
            ERROR:
            ORA-12514: TNS:listener does not currently know of service requested in connect
            descriptor
            
            
            C:\>lsnrctl status LISTENER
            
            LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 27-AUG-2011 23:50
            :11
            
            Copyright (c) 1991, 2006, Oracle.  All rights reserved.
            
            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
            STATUS of the LISTENER
            ------------------------
            Alias                     LISTENER
            Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Produ
            ction
            Start Date                27-AUG-2011 23:49:37
            Uptime                    0 days 0 hr. 0 min. 33 sec
            Trace Level               off
            Security                  ON: Local OS Authentication
            SNMP                      OFF
            Listener Parameter File   D:\oracle\product\10.2.0\db_2\network\admin\listener.o
            ra
            Listener Log File         D:\oracle\product\10.2.0\db_2\network\log\listener.log
            
            Listening Endpoints Summary...
              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
            Services Summary...
            Service "PLSExtProc" has 1 instance(s).
              Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
            The command completed successfully
            
            C:\>
               I do not know how to connect to DB using JDBC driver in java using BEQUETH protocol.

              Please help.
            • 3. Re: ORA-12541: TNS:no listener
              sb92075
              greddy wrote:
                I started the listener service.
              after the listener is started, iam getting this error.
              SQL> conn scott/tiger@ORCL
              ERROR:
              ORA-12514: TNS:listener does not currently know of service requested in connect
              descriptor
              
              
              C:\>lsnrctl status LISTENER
              
              LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 27-AUG-2011 23:50
              :11
              
              Copyright (c) 1991, 2006, Oracle.  All rights reserved.
              
              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
              STATUS of the LISTENER
              ------------------------
              Alias                     LISTENER
              Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Produ
              ction
              Start Date                27-AUG-2011 23:49:37
              Uptime                    0 days 0 hr. 0 min. 33 sec
              at this point in time the listener had been up less than 1 minute
              Oracle DB's try once every 60 seconds to register themselves with listener.

              Is any DB running on this system now?

              Trace Level off
              Security ON: Local OS Authentication
              SNMP OFF
              Listener Parameter File D:\oracle\product\10.2.0\db_2\network\admin\listener.o
              ra
              Listener Log File D:\oracle\product\10.2.0\db_2\network\log\listener.log

              Listening Endpoints Summary...
              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
              no Remote client can communicate with this listener or any DB on this system
              Services Summary...
              open Command Window & type into it each line EXACTLY as below

              lsnrctl service
              set
              sqlplus
              / as sysdba
              startup
              exit


              COPY commands & results then PASTE all back here
              • 4. Re: ORA-12541: TNS:no listener
                greddy
                   The problem is resolved now.

                   I just modified the listener.ora file

                   From
                LISTENER_ORCL =
                  (DESCRIPTION_LIST =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = GOPU)(PORT = 1521))
                      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                    )
                  )
                
                SID_LIST_LISTENER_ORCL =
                  (SID_LIST =
                    (SID_DESC =
                      (SID_NAME = PLSExtProc)
                      (ORACLE_HOME = D:\oracle\product\10.2.0\db_2)
                      (PROGRAM = extproc)
                    )
                  )
                   To
                LISTENER_ORCL =
                  (DESCRIPTION_LIST =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = GOPU)(PORT = 1521))
                      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                    )
                  )
                
                SID_LIST_LISTENER_ORCL =
                  (SID_LIST =
                    (SID_DESC =
                      (SID_NAME = ORCL)
                      (ORACLE_HOME = D:\oracle\product\10.2.0\db_2)
                      (PROGRAM = extproc)
                    )
                  )
                    Thank you all for your help.
                • 5. Re: ORA-12541: TNS:no listener
                  sb92075
                  Ready, FIRE, Aim.

                  too bad for you that you got lucky & don't understand how SQL*Net actually works.
                  • 6. Re: ORA-12541: TNS:no listener
                    EdStevens
                    greddy wrote:
                       The problem is resolved now.

                       I just modified the listener.ora file

                       From
                    LISTENER_ORCL =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = GOPU)(PORT = 1521))
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                    )
                    )
                    
                    SID_LIST_LISTENER_ORCL =
                    (SID_LIST =
                    (SID_DESC =
                    (SID_NAME = PLSExtProc)
                    (ORACLE_HOME = D:\oracle\product\10.2.0\db_2)
                    (PROGRAM = extproc)
                    )
                    )
                       To
                    LISTENER_ORCL =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = GOPU)(PORT = 1521))
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                    )
                    )
                    
                    SID_LIST_LISTENER_ORCL =
                    (SID_LIST =
                    (SID_DESC =
                    (SID_NAME = ORCL)
                    (ORACLE_HOME = D:\oracle\product\10.2.0\db_2)
                    (PROGRAM = extproc)
                    )
                    )
                        Thank you all for your help.
                    Glad you stumbled into a solution that worked. To gain some understanding, you might want to read my blog on this very subject at edstevensdba.wordpress.com