11 Replies Latest reply: Jan 10, 2011 2:30 PM by EdStevens RSS

    TNS:listener does not currently know of service requested in connect

    72640
      [Oracle 10g 10.1.0.2] TNS:listener does not currently know of service requested in connect

      hi,
      My system is Windows XP.
      Oracle 10g : 10.1.0.2.0

      I can connect through sqlplus (and toad) with the following command:

      sqlplus scott/tiger /

      but I cannot connect with this: sqlplus scott/tiger@localhost:1521:cambridg

      I am trying to connect to my database cambridg on my computer (named eclipse).
      Where is the problem ? Can anyone help me here ?

      This is my tnsnames.ora

      # tnsnames.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora
      # Generated by Oracle configuration tools.

      CAMBRIDG =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ECLIPSE)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cambridg)
      )
      )

      EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
      )
      )



      And my listener.ora :

      # listener.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
      # Generated by Oracle configuration tools.

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ECLIPSE)(PORT = 1521))
      )
      )
      )

      A tnsping works perfect :

      C:\bob>tnsping cambridg

      TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 30-JUIL.
      -2005 14:46:11

      Copyright (c) 1997, 2003, Oracle. All rights reserved.

      Used parameter files:

      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ECLIPSE)
      (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cambridg)))
      OK (20 msec)

      I get the following message:

      C:\>sqlplus scott/tiger@localhost:1521:cambridg

      SQL*Plus: Release 10.1.0.2.0 - Production on Sam. Juil. 30 15:10:58 2005

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

      ERROR:
      ORA-12514: TNS:listener does not currently know of service requested in connect
      descriptor

      Also, in the services running, I can see that the listener is started and running:
      OracleOraDb10g_home1TNSListener

      Maybe it has something to do with the other listener, that I cannot start:
      OracleOraDb10g_home1TNSListenerSID_LIST_LISTENER

      Any help would be much appreciated as I am currently stucked with that.

      Thanks a lot
        • 1. Re: TNS:listener does not currently know of service requested in connect
          Ivan Kartik
          1.
          $ oerr ora 12514
          12514, 00000, "TNS:listener could not resolve SERVICE_NAME given in connect descriptor"
          // *Cause:  The SERVICE_NAME in the CONNECT_DATA was not found in
          // the listener's tables.
          // *Action: Check to make sure that the SERVICE_NAME specified is correct.
          // *Comment: This error will be returned if the (database) service has not been
          // registered with the listener; a database instance that is part of this
          // service may need to be started or configured properly.
          2.
          http://forums.oracle.com/forums/search.jspa?q=12514&searchID=50974&rankBy=10001&start=30

          3.
          Strange ORA-12514 Problem
          • 2. Re: TNS:listener does not currently know of service requested in connect
            Jens Petersen
            The syntax is wrong. You don't need to specify host and port.

            Just try:
            sqlplus scott/tiger@cambridg
            • 3. Re: TNS:listener does not currently know of service requested in connect
              62618
              <
              C:\>sqlplus scott/tiger@localhost:1521:cambridg
              >

              this is sqlplus 10G feature, cambridg here is the SID, there are several concepts need to be made clear

              tnsname: alias of the database
              SID: oracle instance ID
              service name: name pointing to instance(s), or other resources

              different tnsnames can point to a single oracle instance, it's just alias used by oracle NET to locate the resource. The mapping is stored in tnsnames.ora file if using local name resolution.
              • 5. Re: TNS:listener does not currently know of service requested in connect
                444898
                For this kind of problems:
                http://www.syntheticbytes.com/oracle/WinSID.zip

                Please provide feedack...
                • 6. Re: TNS:listener does not currently know of service requested in connect
                  Mverheij-Oracle
                  Hi,

                  Either you use the TNSNAMES.ORA file and connect using:
                  scott/tiger@cambridg
                  or you use the new functionality without any TNSNAMES.ORA file, like in:
                  CONNECT scott/tiger@ECLIPSE:1521/cambridg
                  CONNECT scott/tiger@//ECLIPSE/cambridg
                  where these connect strings convert into the following connect descriptor:

                  (DESCRIPTION=
                  (ADDRESS=(PROTOCOL=tcp)(HOST=ECLIPSE)(PORT=1521))
                  (CONNECT_DATA=
                  (SERVICE_NAME=cambridg)))

                  Please read Chapter 8 of the Oracle® Database Net Services Administrator's Guide
                  10g Release 2 (10.2) manual for more information on using this new feature.

                  The syntax you present is the old Oracle Net V1 syntax, which has died with Oracle7.

                  Regards,
                  Michael Verheij
                  • 7. Re: TNS:listener does not currently know of service requested in connect
                    Mverheij-Oracle
                    Paul,

                    Feedback on this:
                    1) no need for an utility when it's only about reading documentation
                    2) the utility is shielded by a password, but you cannot register on the website

                    Please refrain from providing these kinds of links.

                    Michael
                    • 8. Re: TNS:listener does not currently know of service requested in connect
                      686143
                      I had the same issue ....
                      but when I changed Connection URL for THIN Driver to above ( last ":" in URL change to "/" )...then it worked without any errors from listener:

                      from:
                      ====
                      jdbc:oracle:thin:@cmt-ora-aa:1525:CMT_ORA_AA.swisslife.ch


                      to:
                      ===
                      jdbc:oracle:thin:@cmt-ora-aa:1525/CMT_ORA_AA.swisslife.ch

                      Is this related to SID -> SERVICE_NAME entries in config of oracle listener ...

                      Edited by: user6437014 on Mar 15, 2010 9:27 AM
                      • 9. Re: TNS:listener does not currently know of service requested in connect
                        muhammad nadeem
                        hi all,

                        I have the following error when try to connect the database thru form developer:

                        TNS:listener does not currently know of service requested in connect descriptor

                        any one have the solution?


                        Regards:

                        mn
                        • 11. Re: TNS:listener does not currently know of service requested in connect
                          EdStevens
                          Muhammad Nadeem wrote:
                          hi all,

                          I have the following error when try to connect the database thru form developer:

                          TNS:listener does not currently know of service requested in connect descriptor

                          any one have the solution?


                          Regards:

                          mn
                          =================================

                          A couple of important points.

                          First, the listener is a server side only process. It's entire purpose in life is to receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn't sustain the connection. One listener, with the default name of LISTENER, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners or to name the listener as if it belongs to a particular database. That would be like the telephone company building a separate switchboard for each customer.

                          Additional notes on the listener: One listener is capable of listening on multiple ports. But please notice that it is the listener using these ports, not the database instance. You can't bind a specific listener port to a specific db instance. Similarly, one listener is capable of listnening on multiple IP addresses (in the case of a server with multiple NICs) But just like the port, you can't bind a specific ip address to a specific db instance.

                          Second, the tnsnames.ora file is a client side issue. It's purpose is for address resolution - the tns equivalent of the 'hosts' file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.

                          Assume you have the following in your tnsnames.ora:
                          larry =
                            (DESCRIPTION =
                              (ADDRESS_LIST =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
                              )
                              (CONNECT_DATA =
                                (SERVICE_NAME = curley)
                              )
                            )
                          Now, when you issue a connect, say like this:
                          $> sqlplus scott/tiger@larry
                          tns will look in your tnsnames.ora for an entry called 'larry'. Finding it, tns sends a request through the normal network stack to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).

                          Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name 'myhost' will get resolved to an IP address, either via a local 'hosts' file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.

                          Next, the standard networking process delivers the message to port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, the listener will spawn a server process to act as the intermediary between your client and the database instance. Communication to the server process will be on a randomly selected available port. At that point the listener is out of the process and continues to user port 1521 to await other connection requests.



                          What can go wrong?

                          First, there may not be an entry for 'larry' in your tnsnames. In that case you get "ORA-12154: TNS:could not resolve the connect identifier specified" No need to go looking for a problem on the host, with the listener, etc. If you can't place a telephone call because you don't know the number (can't find your telephone directory (tnsnames.ora) or can't find the party you are looking for listed in it (no entry for larry)) you don't look for problems at the telephone switchboard.

                          Maybe the entry for larry was found, but myhost couldn't be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in "ORA-12545: Connect failed because target host or object does not exist"

                          Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in "ORA-12545: Connect failed because target host or object does not exist"

                          Maybe the IP was good, but there is no listener running: "ORA-12541: TNS:no listener"

                          Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. "ORA-12560: TNS:protocol adapter error"

                          Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn't know about SERVICE_NAME = curley. "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"

                          Third: If the client is on the same machine as the db instance, it is possible to connect without referencing tnsnames and without going through the listener.

                          Now, when you issue a connect, say like this:
                          $> sqlplus scott/tiger
                          tns will attempt to establish an IPC connection to the db instance. How does it know the name of the instance? It uses the current value of the enviornment variable ORACLE_SID. So...
                          $> export ORACLE_SID=fred
                          $> sqlplus scott/tiger
                          It will attempt to connect to the instance known as "fred". If there is no such instance, it will, of course, fail. Also, if there is no value set for ORACLE_SID, the connect will fail.

                          check executing instances to get the SID
                          [oracle@vmlnx01 ~]$ ps -ef|grep pmon|grep -v grep
                          oracle    4236     1  0 10:30 ?        00:00:00 ora_pmon_vlnxora1
                          set ORACLE_SID appropriately, and connect
                          [oracle@vmlnx01 ~]$ export ORACLE_SID='vlnxora1
                          [oracle@vmlnx01 ~]$ sqlplus scott/tiger
                          
                          SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:42:37 2010
                          
                          Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                          
                          
                          Connected to:
                          Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          Now set ORACLE_SID to a bogus value, and try to connect
                          SQL> exit
                          [oracle@vmlnx01 ~]$ export ORACLE_SID=FUBAR
                          [oracle@vmlnx01 ~]$ sqlplus scott/tiger
                          
                          SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:42:57 2010
                          
                          Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                          
                          ERROR:
                          ORA-01034: ORACLE not available
                          ORA-27101: shared memory realm does not exist
                          Linux Error: 2: No such file or directory
                          
                          
                          Enter user-name: 
                          Now set ORACLE_SID to null, and try to connect
                          [oracle@vmlnx01 ~]$ export ORACLE_SID=
                          [oracle@vmlnx01 ~]$ sqlplus /scott/tiger
                          
                          SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:43:24 2010
                          
                          Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                          
                          ERROR:
                          ORA-12162: TNS:net service name is incorrectly specified
                          Ok, that is how we get from the client connection request to the listener. What about the listener's part of all this?

                          The listener is very simple. It's job is to listen for connection requests and make the connection (server process) between the client and the database instance. Once that connection is made, the listener is out of the picture. If you were to kill the listener, all existing connections would continue. The listener is configured with the listener.ora file, but if that file doesn't exist, the listener is quite capable of starting up with all default values. One common mistake with the listner configuration is to specify "HOST=localhost" or "HOST=127.0.01". This is a NONROUTABLE ip address. LOCALHOST and ip address 127.0.0.1 always mean "this machine on which I am sitting". So, all computers are known as "localhost" or "127.0.0.1". If you specify this address, the listener will only be capable of receiving requests from the machine on which it is running. If you specified that address in your tnsnames file - on a remote client machine - the request would be routed to the machine on which the requesting client resides. Probably not what you want.

                          =====================================