Re: ORA-12505: TNS:listener

From: nilesh kumar <nileshkum_at_gmail.com>
Date: Wed, 11 Jun 2008 01:10:35 +0530
Message-ID: <7877ab4b0806101240i4312f73erd9a3c0fc9661c5fc@mail.gmail.com>

Hello Shanmugam,

Hope this helps

*C:>tnsping ora920

TNS Ping Utility for 32-bit Windows: Version 9.2.0.7.0 - Production

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
c:\oracle\ora920\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DEV01)(PORT =
 2491))) (CONNECT_DATA = (SID = UNKNOWN) (SERVER = DEDICATED))) OK (20 msec)*

As one can see, this is the connection information stored in a tnsnames.ora file: **

*ORA920.EU.DBMOTIVE.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DEV01)(PORT = 2491))     )
    (CONNECT_DATA =

      (SID = UNKNOWN)
      (SERVER = DEDICATED)

    )
  )*

However, the SID *UNKNOWN* is not known by the listener at the database server side.
In order to test the known services by a listener, we can issue following command at the database server side: **

*C:>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DEV01)(PORT=1521))) Services Summary...
Service "ORA10G.eu.dbmotive.com" has 1 instance(s).   Instance "ORA10G", status UNKNOWN, has 1 handler(s) for this service...     Handler(s):

      "DEDICATED" established:0 refused:0
         LOCAL SERVER

Service "ORA920.eu.dbmotive.com" has 2 instance(s).   Instance "ORA920", status UNKNOWN, has 1 handler(s) for this service...     Handler(s):

      "DEDICATED" established:0 refused:0
         LOCAL SERVER

  Instance "ORA920", status READY, has 1 handler(s) for this service...     Handler(s):

      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER

The command completed successfully*

Know services are ORA10G and ORA920.

Changing the SID in our tnsnames.ora to a known service by the listener (* ORA920*) solved the problem.
Thanks
Nilesh

On Wed, Jun 11, 2008 at 1:04 AM, Roman Podshivalov < roman.podshivalov_at_gmail.com> wrote:

> Hi,

>

> If your listener is up but database is down or not registered with the
> listener running on 1243 port and automatic instance registration is used
> i.e. no static SID_LIST in listener.ora file, that is exactly what you
> get. You are running listener on non-standard port, so you need to verify
> local_listener parameter is set in the CDER database. If you provide the
> following output it'll be much easier to pinpoint the problem

>

> - lsnrctl status <listener_name>
> - show parameter local_listener from CDER database

>

> --romas

>
>

> On 6/10/08, Shanmugam, Dhandapani <dhandapani.shanmugam_at_eds.com> wrote:

>>
>> Hi,
>>
>> Could some one help on the below error
>>
>> F:\>tnsping CDER
>>
>> TNS Ping Utility for 32-bit Windows: Version 9.2.0.8.0 - Production on
>> 10-JUN-20
>> 08 14:00:10
>>
>> Copyright (c) 1997, 2006, Oracle Corporation.  All rights reserved.
>>
>> Used parameter files:
>> c:\Oracle\network\admin\sqlnet.ora
>>
>> Used LDAP adapter to resolve the alias
>> Attempting to contact
>> (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cder.guru.sree.com)(PORT=1243)
>> )(CONNECT_DATA=(SID=CDER)))
>> OK (60 msec)
>>
>> F:\>sqlplus test_at_CDER
>>
>> SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 10 14:00:25 2008
>>
>> Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
>>
>> Enter password:
>> ERROR:
>> ORA-12505: TNS:listener could not resolve SID given in connect
>> descriptor
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

-- 
Soni
Temples & Softwares are more or less the same, first we build them and then
we pray ;)

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 10 2008 - 14:40:35 CDT