Home » RDBMS Server » Networking and Gateways » ORA-12505, TNS:listener does not currently know of SID given (Oracle 10g Express 10.2.0.1.0 on Windows XP Pro SP2)
icon8.gif  ORA-12505, TNS:listener does not currently know of SID given [message #425820] Mon, 12 October 2009 06:54 Go to next message
rizviks
Messages: 7
Registered: October 2009
Junior Member
When trying to connect from SQL Developer, I get this error message:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was: localhost:1521:xe

Upon trying to resolve it, c:\>tnsping xe produces this message:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = solarpc)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (40 msec)

Having used MSSQL Server and MySQL for the past 10 years, I have just recently started using Oracle. Any help will be appreciated.

Thanks!

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425828 is a reply to message #425820] Mon, 12 October 2009 07:21 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
copy and paste your listener.ora file...

or output for the following...
lsnrctl status

Lsnrctl services

Sriram

[Updated on: Mon, 12 October 2009 07:24]

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425832 is a reply to message #425828] Mon, 12 October 2009 07:29 Go to previous messageGo to next message
rizviks
Messages: 7
Registered: October 2009
Junior Member
ramoradba wrote on Mon, 12 October 2009 07:21
copy and paste your listener.ora file...

or output for the following...
lsnrctl status

Lsnrctl services

Sriram


C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-OCT-2009 07:27
:41

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date 11-OCT-2009 05:57:02
Uptime 1 days 1 hr. 30 min. 41 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\10.2.0\server\network\a
dmin\listener.ora
Listener Log File C:\oraclexe\app\oracle\product\10.2.0\server\network\l
og\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solarpc)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

============================
C:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-OCT-2009 07:28
:59

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425833 is a reply to message #425828] Mon, 12 October 2009 07:32 Go to previous messageGo to next message
rizviks
Messages: 7
Registered: October 2009
Junior Member
And here are the contents of listener.org

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = solarpc)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425838 is a reply to message #425833] Mon, 12 October 2009 07:47 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
The Connection descriptor used by the client was: localhost:1521:xe


Is that localhost same with "solarpc" ?
I mean is that server running on your node(solarpc)?

post out put from your tnsnames.ora file

sriram

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425849 is a reply to message #425838] Mon, 12 October 2009 08:28 Go to previous messageGo to next message
rizviks
Messages: 7
Registered: October 2009
Junior Member
Yes, the localhost is solarpc. Below is contents of tnsnames.org:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = solarpc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

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

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425854 is a reply to message #425849] Mon, 12 October 2009 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How many systems are involved?
If more than 1 PC exists, what s/w is installed on each PC involved?

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425856 is a reply to message #425854] Mon, 12 October 2009 10:22 Go to previous messageGo to next message
rizviks
Messages: 7
Registered: October 2009
Junior Member
BlackSwan wrote on Mon, 12 October 2009 09:31
How many systems are involved?
If more than 1 PC exists, what s/w is installed on each PC involved?


Just one PC - stand-alone installation. 10g Express on Windows XP Pro.

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425858 is a reply to message #425856] Mon, 12 October 2009 11:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Reproduce error again the post tail end (last 30 - 40 lines ) from
C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log

back here

post results from following command

lsnrctl service

[Updated on: Mon, 12 October 2009 11:07]

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425868 is a reply to message #425858] Mon, 12 October 2009 13:09 Go to previous messageGo to next message
rizviks
Messages: 7
Registered: October 2009
Junior Member
BlackSwan wrote on Mon, 12 October 2009 11:06
Reproduce error again the post tail end (last 30 - 40 lines ) from
C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log

back here


Copyright (c) 1991, 2005, Oracle. All rights reserved.

System parameter file is C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
Log messages written to C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Trace information written to C:\oraclexe\app\oracle\product\10.2.0\server\network\trace\listener.trc
Trace level is currently 0

Started with pid=3496
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solarpc)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
12-OCT-2009 11:35:00 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
12-OCT-2009 11:35:07 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1206)) * establish * xe * 0
12-OCT-2009 11:36:52 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\oraclexe\app\oracle\product\10.2.0\server\bin\sqlplus.exe)(HOST=SOL ARPC)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.16)(PORT=1223)) * establish * XE * 0
12-OCT-2009 11:37:03 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\oraclexe\app\oracle\product\10.2.0\server\bin\sqlplus.exe)(HOST=SOL ARPC)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.16)(PORT=1224)) * establish * XE * 0

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-OCT-2009 12:29:24

Copyright (c) 1991, 2005, Oracle. All rights reserved.

System parameter file is C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
Log messages written to C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Trace information written to C:\oraclexe\app\oracle\product\10.2.0\server\network\trace\listener.trc
Trace level is currently 0

Started with pid=3948
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solarpc)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
12-OCT-2009 13:04:48 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1503)) * establish * xe * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 2: No such file or directory
12-OCT-2009 13:04:58 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1504)) * establish * xe * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 2: No such file or directory
12-OCT-2009 13:05:25 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1506)) * establish * xe * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 2: No such file or directory

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425869 is a reply to message #425858] Mon, 12 October 2009 13:10 Go to previous messageGo to next message
rizviks
Messages: 7
Registered: October 2009
Junior Member
BlackSwan wrote on Mon, 12 October 2009 11:06

post results from following command

lsnrctl service



LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-OCT-2009 13:09
:58

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "XE" has 1 instance(s).
Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:3
LOCAL SERVER
The command completed successfully

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #425872 is a reply to message #425868] Mon, 12 October 2009 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Below indicates a serious malconfigured environment.

>TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
>12-OCT-2009 13:04:48 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1503)) * establish * xe * 12518
>TNS-12518: TNS:listener could not hand off client connection
>TNS-12560: TNS:protocol adapter error
>TNS-00530: Protocol adapter error
>32-bit Windows Error: 2: No such file or directory

Has SQL*NET ever worked?
If so, what changed.

Try the following:

lsnrctl stop
Now rename listener.ora to a new name so listener won't find it.
lsnrctl start
lsnrctl status
lsnrctl service

Try to make a new connection.
Try to make new connection using actual HOSTNAME; not localhost

Post all resultsback here

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #429394 is a reply to message #425872] Tue, 03 November 2009 08:36 Go to previous messageGo to next message
Maori
Messages: 2
Registered: November 2009
Junior Member
Hello,

thanks a lot for your suggestion. I had the problem before, and it was solved after a listener restart, but not today - today nothing seemed to be helping.
After I followed the steps in your post though, the service is working properly again. In between I renamed the listener.ora and here are the results for listener status and service calls (nbeva is my localhost):

C:\Users\eva>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2009 15:11:09

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)KEY=EXTPROC_FOR_XE)))
The command completed successfully

C:\Users\eva>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2009 15:11:30

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Log messages written to C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nbeva)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 03-NOV-2009 15:11:34
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nbeva)(PORT=1521)))
The listener supports no services
The command completed successfully

C:\Users\eva>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2009 15:11:56

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 03-NOV-2009 15:11:34
Uptime 0 days 0 hr. 0 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nbeva)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=7777))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\eva>lsnrctl service

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2009 15:12:33

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: NBEVA, pid: 4268>
(ADDRESS=(PROTOCOL=tcp)(HOST=nbeva)(PORT=53872))
Service "XE_XPT" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #429396 is a reply to message #425872] Tue, 03 November 2009 08:39 Go to previous messageGo to next message
Maori
Messages: 2
Registered: November 2009
Junior Member
I'm sorry. I posted the logs, but forgot to ask for an explanation. How come the listener can function properly without the "listener.ora" file? I thought that a new one would somehow be generated, but it has not been...

Thanks

Report message to a moderator

Re: ORA-12505, TNS:listener does not currently know of SID given [message #429404 is a reply to message #429396] Tue, 03 November 2009 09:40 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>How come the listener can function properly without the "listener.ora" file?
Without any listener.ora file, listener just wakes up & starts to listen on port 1521; just default behavior.

Report message to a moderator

Previous Topic: oracle local net service name have default username password
Next Topic: TOAD:TNS listner not found error.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jan 17 18:28:24 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.