ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
bomahony
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:
beatrix:1522:cm101
This is happening on oracle102 and oracle101
For this ill just use the oracle 10.1.0.5 install.
i have the listener called listener101, and the port is 1522 (oracle
10.2 install is listener102 and port 1523 - only one is ever running
at a time)
Here is the result from a tnsping:
bash-3.00$ tnsping curam101
TNS Ping Utility for Solaris: Version 10.1.0.5.0 - Production on 19-
JUL-2007 16:27:54
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
/oracle101/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST
= beatrix)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = curam101)))
here is the output from lsnrctl status:
bash-3.00$ lsnrctl status listener101
LSNRCTL for Solaris: Version 10.1.0.5.0 - Production on 19-JUL-2007
16:28:48
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias listener101
Version TNSLSNR for Solaris: Version 10.1.0.5.0 -
Production
Start Date 19-JUL-2007 10:43:53
Uptime 0 days 5 hr. 44 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle101/network/admin/listener.ora
Listener Log File /oracle101/network/log/listener101.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beatrix)(PORT=1522)))
The listener supports no services
The command completed successfully
Netstat:
bash-3.00$ netstat -a | grep 1522
*.1522 *.* 0 0 49152 0
LISTEN
So its listening and seems to be hooked up, but when i ever i run an
app locally, that connects to the db, or when i do a remote connection
using squirell, it fails with the error.
And here is the last line from the listener101.log file:
19-JUL-2007 16:28:48 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=beatrix)
(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=listener101)(
VERSION=168822016)) * status * 0
I dont have a clue where to go from here.
Any ideas?
OK (10 msec)
Frank van Bortel
Hash: SHA1
First of all, you do not have consent from the Ministry of Interior
to use Our Queens name for servers.
Because of this, FBI's working with flaws.
Secondly, use the database parameter local_listener wisely,
and perform alter system register. Then query your listener
again - it should be supporting services now.
You local connection may go through bequeath - no listener
required, just the SID as environment setting
- --
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD8DBQFGn4xRLw8L4IAs830RAtnKAJ4tG60gHeN6KyVkQDsAQqbBjA4EFACfUiSE
zbOXZgS7K9wuzguF+AWoceA=
=Ua5v
-----END PGP SIGNATURE-----
joel garry
Your database, if up, should be registering as a service. This should
be happening automatically. Do you see it with other listeners?
http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/listenercfg.htm#sthref947
It might have something to do with how you configured service
registration in the init.ora, or you might need to be explicit about
oracle_home, or you might need to bring up the db.
> The command completed successfully
>
> Netstat:
>
> bash-3.00$ netstat -a | grep 1522
> *.1522 *.* 0 0 49152 0
> LISTEN
>
> So its listening and seems to be hooked up, but when i ever i run an
> app locally, that connects to the db, or when i do a remote connection
> using squirell, it fails with the error.
Please copy and paste the exact commands you are using and the
results. Syntax makes a difference as to whether you are really local
or just pretending to be non-local.
>
> And here is the last line from the listener101.log file:
> 19-JUL-2007 16:28:48 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=beatrix)
> (USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=listener101)(
> VERSION=168822016)) * status * 0
>
> I dont have a clue where to go from here.
>
> Any ideas?
> OK (10 msec)
jg
--
@home.com is bogus.
"Can the following paragraph be removed? It seems to be relevant only
to Oracle8" - Oracle® Database Net Services Administrator's Guide 10g
Release 2 (10.2)
bomahony
machine.
The listeners are set up on port 1522 and 1523. They are called
listener101 and listener102 respectively. Only one instance runs at a
time. However when i try to connect remotely, i get the above error.
Here is the output of some some commands: (using the 10.1.0.5
installation)
bash-3.00$ tnsping curam101
TNS Ping Utility for Solaris: Version 10.1.0.5.0 - Production on 19-
JUL-2007 16:27:54
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
/oracle101/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST
= beatrix)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = curam101)))
OK (10 msec)
bash-3.00$ lsnrctl status listener101
LSNRCTL for Solaris: Version 10.1.0.5.0 - Production on 19-JUL-2007
16:28:48
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias listener101
Version TNSLSNR for Solaris: Version 10.1.0.5.0 -
Production
Start Date 19-JUL-2007 10:43:53
Uptime 0 days 5 hr. 44 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle101/network/admin/listener.ora
Listener Log File /oracle101/network/log/listener101.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beatrix)(PORT=1522)))
The listener supports no services
The command completed successfully
last line of listener.log:
24-JUL-2007 10:03:23 * (CONNECT_DATA=(SID=curam101)(CID=(PROGRAM=)
(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.161.123)
(PORT=4152)) * establish * curam101 * 12505
TNS-12505: TNS:listener does not currently know of SID given in
connect descriptor
bash-3.00$ netstat -a | grep 1522
*.1522 *.* 0 0 49152 0
LISTEN
I am using squirrel to connect, using the oracle thin driver (which i
use to connect to other oracle instances on other servers).
any help would be much appreciated.
Cristian Cudizio
As Frank as already suggested to you, you must set database parameter
local_listener,
in fact, you see in the output of lsnrctl status that there are no
services registered. If listener
is configured on a non-default address-port you must specify explicity
parameter local_listener
to say database where to register itself (you can use an alias defined
in tnsnames.ora of the
dbserver)
Bye
Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.worpress.com
Niall Litchfield
ORA-12514. I have setup a little demo of the difference. I have a
database nl102 sitting on a linux box here, but not running. I have
created two listeners LISTENER_SERVICES which runs on 1521 and will be
the listener with which my services register and LISTENER_SID running
not very imaginatively on 1522. It has no static list of either SIDs or
SERVICES. The status of the two looks like this.
LSNRCTL> STATUS LISTENER_SERVICE
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=demo.domain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_SERVICE
Version TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date 24-JUL-2007 20:16:07
Uptime 0 days 0 hr. 10 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File
/u01/app/oracle/product/10.2.0/db_1/network/log/listener_service.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=demo.domain.com)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> STATUS LISTENER_SID
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=demo.domain.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER_SID
Version TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date 24-JUL-2007 20:17:08
Uptime 0 days 0 hr. 9 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File
/u01/app/oracle/product/10.2.0/db_1/network/log/listener_sid.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=demo.domain.com)(PORT=1522)))
The listener supports no services
The command completed successfully
LSNRCTL>
Now I have two tns aliases setup nl102 which connects to the service and
still unimaginatively nl102_sid which uses the sid. When I try to
connect the following occurs.
oracle@demo:~> sqlplus niall@nl102
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 24 20:23:14 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in
connect
descriptor
Enter user-name: niall@nl102_sid
Enter password:
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor
In my case the first message means that the database is down and I have
no static service registration, in the second case the database is still
down but I have no static SID listed. The corrective actions that you
describe to make a database register with a non-default listener apply
as far as I know only to services. The corrective action for the users
actual error would I think be to list the SID in the listener.ora for
the non-default listener.
It seems to me however that there may be something else going on here
and that is the naming methods in use for the client. To start with we
see that tnsping uses the service name to test the listener. The remote
JDBC connection however uses the SID. I suspect that the problem is
therefore a configuration issue on the remote client (probably a
mid-tier box if it's jdbc) and that configuring the mid-tier to use the
service would also work.
--
Niall Litchfield
Oracle DBA
http://www.orawin.info/services
Cristian Cudizio
wrote:
i think it is not that the problem, it depends on how you try to
connect, you can use
in your connect descriptor service_name or sid , the preferred way is
use service_name.
In my simplest case, of single instance database with empty
db_domain,
service_name (that is global_name) equals sid so i can use
this two descriptors and both works:
METHONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = xyz)
)
)
METHONE_SID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1.13)(PORT = 1521))
)
(CONNECT_DATA =
(SID = xyz)
)
)
In the case of the OP, he (or she) use simplest JDBC thin client
connection descriptor in the form
hostname:port:SID that is equivalent to my connect descriptor
"METHONE_SID".
so message TNS-12514 appens when you use "service_name" in your
connect descriptor and database is not registered with
listener and tns-12505 appens when you use "sid" in your connect
descriptor (or jdbc thin client configuration above)
When database instance register itself with known listeners, listeners
knows about sid and service_name.
So, if you force datatabase instance to register to a listener with
parameter local_listener both connect descriptor works.
it is not necessary to statically register database configuring
sid_listener in listener.ora. You must always have no
"The listener supports no services "
line in lsnrctl status output but you must have a line like this:
Service "xyz" has 1 instance(s).
Instance "xyz", status READY, has 1 handler(s) for this service...
Don't bother with tnsping that verifies only that there is a listener
on that address and not controls service_name or sid.
You can test using wrong sid and wrong service_name to make tests both
with tnsping that always succeeds. On the
other hand with sqlplus you receive tns-12505 with wrong sid and
tns-12514 with wrong service_name
Excuse me for my poor english, but i hope to have clarified the
problem