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 DBA
http://www.orawin.info/services
Received on Tue Jul 24 2007 - 14:42:52 CDT
