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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike henkins - 30 Jul 2005 14:13 GMT

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

Reply to this Message

IANAL_VISTA - 30 Jul 2005 14:22 GMT

> hi,
> My system is Windows XP.
[quoted text clipped - 108 lines]
>
> Thanks a lot

It appears that no entry for the actual database exists withn LISTENER.ORA

Reply to this Message

mike henkins - 30 Jul 2005 14:36 GMT

Well,

I tried to modify it like this, and i get the same error:

# 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))
(SID_DESC =
(SID_NAME = cambridg)
(ORACLE_HOME = c:\oracle\product\10.1.0\Db_1))
)

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

Any clue ? thanks

> > hi,
> > My system is Windows XP.
[quoted text clipped - 110 lines]
>
> It appears that no entry for the actual database exists withn LISTENER.ORA

Reply to this Message

IANAL_VISTA - 30 Jul 2005 14:46 GMT

> Well,
>
> I tried to modify it like this, and i get the same error:

Plz do NOT top post.

Below is from a working production DB system.
listener =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cdb1.example.com)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = cdb1))
)
)
)

sid_list_listener =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cdb1)
(ORACLE_HOME = /b/oracle/product/9.2.0)
(SID_NAME = cdb1)
)
)

Do you notice any differences between this & yours?

Reply to this Message

mike henkins - 30 Jul 2005 15:18 GMT

Thanks. I modified it like your example:

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

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

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cambridg)
(ORACLE_HOME = c:\oracle\product\10.1.0\Db_1)
(SID_NAME = cambridg)
)
)

I still get the same boring and annoying error :
C:\Documents and Settings\bob>sqlplus scott/tiger@localhost:1521:cambridg

SQL*Plus: Release 10.1.0.2.0 - Production on Sam. Juil. 30 16:18:22 2005

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

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

Any suggestion please ?

> > Well,
> >
[quoted text clipped - 25 lines]
>
> Do you notice any differences between this & yours?

Reply to this Message

Randy Harris - 30 Jul 2005 15:40 GMT

> Thanks. I modified it like your example:

[snip]

> I still get the same boring and annoying error :
> C:\Documents and Settings\bob>sqlplus scott/tiger@localhost:1521:cambridg
[quoted text clipped - 8 lines]
>
> Any suggestion please ?

Do you still get the same error if you use scott/tiger@cambridg as the
connect descriptor?

Reply to this Message

mike henkins - 30 Jul 2005 15:50 GMT

Thanks, it works if I use sqlplus scott/tiger@cambridg it works but if you
check
http://forums.oracle.com/forums/thread.jspa?forumID=220&messageID=960520&thr
eadID=303267
you will see that it seems to be a problem with oracle 10g.

However i am connecting through jdbc:

conn = DriverManager.getConnection ("jdbc:oracle:thin:@" + server + ":" +
port + ":" + base, user, pwd);

that is why i want to get the sqlplus connection under DOS working.

I need the localhost + port as well.

Unless there is another getConnection method that only takes user, pwd and
base ?

> > Thanks. I modified it like your example:
> >
[quoted text clipped - 15 lines]
> Do you still get the same error if you use scott/tiger@cambridg as the
> connect descriptor?

Reply to this Message

IANAL_VISTA - 30 Jul 2005 16:22 GMT

What part of "Plz do NOT top post." do you NOT understand?

Why do you think syntax for one utility is valid with a different utility?

You've proved the listener is functioning.

Now go solve your own java problem.

Reply to this Message

HansF - 30 Jul 2005 17:25 GMT

> Thanks, it works if I use sqlplus scott/tiger@cambridg it works but if you
> check
[quoted text clipped - 13 lines]
> Unless there is another getConnection method that only takes user, pwd and
> base ?

1) Please, PLEASE do not top post.

2) Realize that the JDBC Thin driver does NOT use the TNSNAMES entry at
all, and therefore will not respond the same way as sqlplus.

sqlplus MUST use the {sqlnet, net8, Oracle networking} substrate and
therefore refers to TNSNAMES.

The JDBC thin driver is self-contained, and uses the server/port
combination to literally download the appropriate portion of the Oracle
Networking (TTC) at run time.

You might want to spend some additional time with the "Oracle® Database
JDBC Developer's Guide and Reference" manual to get this a little clearer,
from which I quote:

------------
The JDBC Thin driver allows a direct connection to the database by
providing an implementation of SQL*Net and TTC (the wire protocol used by
OCI) on top of Java sockets. Both of these protocols are lightweight
implementation versions of their counterparts on the server. The Thin
driver runs over TCP/IP only.
------------

Your attempt to use JDBC Thin protocol access with SQLPlus is
totally incorrect.

Signature

Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

Reply to this Message

Sybrand Bakker - 30 Jul 2005 17:28 GMT

>conn = DriverManager.getConnection ("jdbc:oracle:thin:@" + server + ":" +
>port + ":" + base, user, pwd);
>
>that is why i want to get the sqlplus connection under DOS working.
>
>I need the localhost + port as well.

As explained to you in comp.databases.oracle.server to you before,
this is not the correct syntax. In fact the jdbc *thin* driver is the
exception.
So, no, you don't need the localhost + port, and you would have known
that, provided you would have spent some time to look up the
troubleshooting section of tne net administrators manual.
However spending some minimal effort is quite clearly asked too much,
as you by now have demonstrated to you are too lazy to hit ctrl-end
and to refrain from top-posting.
You might consider switching to a mickeysoft product.

--
Sybrand Bakker, Senior Oracle DBA

Reply to this Message

Maxim Demenko - 30 Jul 2005 17:47 GMT

mike henkins schrieb:

> hi,
> My system is Windows XP.
[quoted text clipped - 105 lines]
>
> Thanks a lot

Maybe you are trying to use the easy connection naming method
http://download-west.oracle.com/docs/cd/B14117_01/network.101/b10775/naming.htm#
sthref804
In that case, you syntax is slightly wrong - at last component, service
name, you should put a "/" instead of ":".

So it should look like
C:\>sqlplus scott/tiger@localhost:1521/cambridg

Also, notice, that service must be registered by listener ( to force it
you could issue "ALTER SYSTEM REGISTER;" ).

Best regards

Maxim

Reply to this Message