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)
ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #531068] Sat, 12 November 2011 21:06 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
lsnrctl status
lsnrctl service

COPY commands & results then PASTE all back here
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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!
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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.





Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #583404 is a reply to message #559142] Tue, 30 April 2013 08:35 Go to previous messageGo to next message
ishimdar
Messages: 2
Registered: April 2013
Location: Delhi
Junior Member
hye Brother U got your solution.If got please tell me
Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor [message #583434 is a reply to message #583404] Tue, 30 April 2013 14:27 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Who do you call "brother"? Kishan_nag posted 2 options; did you try these?
Previous Topic: How do I see the information before commit?
Next Topic: Generating tkprof from SqlDeveloper tool.
Goto Forum:
  


Current Time: Fri Mar 29 07:37:26 CDT 2024