OraFAQ Forum: Client Tools » ORA-12505 TNS:listener does not currently know of SID given in connect descriptor

Home » Other » Client Tools » ORA-12505 TNS:listener does not currently know of SID given in connect descriptor (sql developer version(3.0.04) WINDOW XP sp3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531068]

Sat, 12 November 2011 21:06

9390512774
Messages: 103
Registered: January 2011
Location: hyd

Senior Member

Hi Experts,
I installed oracle11gR2 on OEL5.5 which is in VMWare machine.
Now i tried to connect to oracle(which is in VMWare machine) using sql developer from windows machine. i am able to use putty to connect to OEL5.5. but when i tried to connect oracle from sql developer tool its giving me an error "ORA-12505 TNS:listener does not currently know of SID given in connect descriptor"

Please help me on finding solution.

I am providing you the following information.
My "tnsnames.ora" file entry is:

DB11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.157.128)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB11G.global)
    )
  )

"sqlnet.ora" file is:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

listener.ora file is:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.157.128)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

and finally lsnrctl start message:

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-NOV-2011 08:32:00

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oel5/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.157.128)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                13-NOV-2011 08:32:01
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oel5/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.157.128)(PORT=1521)))
The listener supports no services
The command completed successfully

Thanks in advance. Your help is appreciated great.

[Updated on: Sat, 12 November 2011 21:07]

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531069 is a reply to message #531068]

Sat, 12 November 2011 21:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

lsnrctl status
lsnrctl service

COPY commands & results then PASTE all back here

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531070 is a reply to message #531069]

Sat, 12 November 2011 21:14

9390512774
Messages: 103
Registered: January 2011
Location: hyd

Senior Member

the result of "lsnrctl status" is:


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-NOV-2011 08:41:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.157.128)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     DB11G
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                13-NOV-2011 08:38:18
Uptime                    0 days 0 hr. 3 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oel5/db11g/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.157.128)(PORT=1521)))
The listener supports no services
The command completed successfully

the result of "lsnrctl service" is:


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-NOV-2011 08:43:00

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.157.128)(PORT=1521)))
The listener supports no services
The command completed successfully

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531071 is a reply to message #531070]

Sat, 12 November 2011 21:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

within /u01/app/oracle/diag/tnslsnr/oel5/listener/alert/log.xml find "12505"

post excerpt from 20 lines above to 20 lines below

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531072 is a reply to message #531071]

Sat, 12 November 2011 21:55

9390512774
Messages: 103
Registered: January 2011
Location: hyd

Senior Member

The below is the info i given in sql developer to connect to oracle

connection name:db1
user:scott
password:........
connection type:Basic
Role:default
hostname:192.168.157.128
Port:1521
SID:DB11G

the entry in "log.xml" before trying the connection for database from "sql developer" is:

/* Some more code*/
<msg time='2011-11-13T09:29:12.145+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.157.128)(PORT=1521)))
 </txt>
</msg>
<msg time='2011-11-13T09:29:12.150+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>Listener completed notification to CRS on start
 </txt>
</msg>
<msg time='2011-11-13T09:29:12.159+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
 </txt>
</msg>
<msg time='2011-11-13T09:29:12.163+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>WARNING: Subscription for node down event still pending
 </txt>
</msg>
<msg time='2011-11-13T09:29:12.168+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>13-NOV-2011 09:29:12 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=oel5.localhost)(USER=oracle))
(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
 </txt>
</msg>

the entry in "log.xml" after trying the connection for database from "sql developer" is:

<msg time='2011-11-13T09:30:28.045+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>13-NOV-2011 09:30:28 * (CONNECT_DATA=(SID=DB11G)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)
(USER=VENKAT))) * 
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.157.1)(PORT=1893)) * establish * DB11G * 12505
 </txt>
</msg>
<msg time='2011-11-13T09:30:28.045+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
 </txt>
</msg>

the entry in log.xml after issuing "lsnrctl status":

<msg time='2011-11-13T09:34:37.503+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>WARNING: Subscription for node down event still pending
 </txt>
</msg>
<msg time='2011-11-13T09:34:37.503+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>13-NOV-2011 09:34:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=oel5.localhost)(USER=oracle))
(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
 </txt>
</msg>

the entry in log.xml after issuing "lsnrctl service":

<msg time='2011-11-13T09:35:26.790+05:30' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oel5.localhost'
 host_addr='127.0.0.1'>
 <txt>13-NOV-2011 09:35:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=oel5.localhost)(USER=oracle))
(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * services * 0
 </txt>
</msg>

[Updated on: Sun, 13 November 2011 01:05] by Moderator

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531073 is a reply to message #531072]

Sat, 12 November 2011 22:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.157.1)(PORT=1872)) * establish * DB111G * 12505

from where did 192.168.157.1 originate?

"localhost" means that no remote client can talk to the DB on this server.

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531074 is a reply to message #531073]

Sat, 12 November 2011 22:16

9390512774
Messages: 103
Registered: January 2011
Location: hyd

Senior Member

Sorry, I wrongly copied the log message i modified the log information. Please recheck the post one more time for me and let me know my mistakes.
Solution for issue.

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531075 is a reply to message #531074]

Sat, 12 November 2011 22:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I refuse to waste more time attempting to discern fact from fiction.

Enjoy your mystery & errors!

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531101 is a reply to message #531068]

Sun, 13 November 2011 06:59

OracGPS
Messages: 17
Registered: April 2011

Junior Member

there are two methods.

the first is :
modify tnsnames.ora
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.157.128)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G.global)--> (sid = $ORACLE_SID)
)
)

cmd--> tnsping db11g

the second is :
modify listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = xxx)
(ORACLE_HOME = xxxxx)
(global_dbname = xxx)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.157.128)(PORT = 1521))
)
)

cmd--> tnsping db11g

when you want to connect with db using PlSqlDev tool , you must first tnsping db11g is OK.

Report message to a moderator

Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #559142 is a reply to message #531101]

Fri, 29 June 2012 02:41

kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai

Junior Member

I also got same error ORA- 12505 when I am trying to connect Database from SQL Developer. I have resolved this in two different ways.

1st Method: I queried the DB name from below query. Used same name as SID while connecting to DB from SQL developer.

SELECT name from V$DATABASE;

2nd Method: I have used service name which is available in TNSNAMES.ORA (Path : H:\app\pcadmin\product\11.2.0\dbhome_1\NETWORK\ADMIN) while connecting to DB from SQL developer.

Report message to a moderator