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

On Sat, 30 Jul 2005 15:14:20 +0200, "mike henkins" <dd_at_nospam.com> wrote:

>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_at_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_at_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
>
>

Several remarks
- The correct syntax is
sqlplus username/password@<tns service name> Try removing the redundant localhost:1521: -
If you are still getting ora-12514, this means the *database* service name you provide in the connect_data section of your tnsnames.ora doesn't correspond with the service_name parameter of your database lsnrctl services will tell you what services are supported. Show parameter service_name will tell that too. Btw: there is a special troubleshooting section in the net administrators manual (the doc no one reads), you could easily verify this in that section.

--
Sybrand Bakker, Senior Oracle DBA