Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

On Jul 24, 9:42 pm, Niall Litchfield <niall.litchfi..._at_dial.pipex.com> wrote:
> Cristian Cudizio wrote:
> > On Jul 24, 11:08 am, bomahony <bomah..._at_gmail.com> wrote:
> >> I have oracle 10.1.0.5.0 and 10.2.0.1 installed on a Solaris 10
> >> 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.
>
> > 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)
>
> It looks to me as though you and Frank have confused ORA-12505 with
> 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_at_demo:~> sqlplus niall_at_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_at_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 DBAhttp://www.orawin.info/services

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

Bye

Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.worpress.com Received on Wed Jul 25 2007 - 02:49:26 CDT