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

Author

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

mike henkins

2005-07-30, 9:23 am

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_D
ATA =
(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_home1
TNSListener

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

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

Thanks a lot

IANAL_VISTA

2005-07-30, 9:23 am

"mike henkins" <dd@nospam.com> wrote in
news:42eb7d2b$0$906$
8fcfb975@news.wanadoo.fr:

> 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_D
ATA =
> (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_home1
TNSListener
>
> Maybe it has something to do with the other listener, that I cannot
> start: OracleOraDb10g_home1
TNSListenerSID_LIST_
LISTENER
>
> Any help would be much appreciated as I am currently stucked with
> that.
>
> Thanks a lot
>
>
>
>

please do NOT cross/multipost.

Sybrand Bakker

2005-07-30, 9:23 am

On Sat, 30 Jul 2005 15:14:20 +0200, "mike henkins" <dd@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@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_D
ATA =
> (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_home1
TNSListener
>
>Maybe it has something to do with the other listener, that I cannot start:
> OracleOraDb10g_home1
TNSListenerSID_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

Sponsored Links