Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am a newbie to oracle database. I am trying to make a connection but I receive the following error:

Listener refused the connection with following error: ORA-12505, TNS: listener does not currently know of SID given in connect descriptor. 

I have done my research online and after executing the lsnrctl status and lsnrctl service I get the following. Does anyone what I need to do? All kind of help is highly appreciated.

Thanks.

C:\Users\myComputer>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:33
:29

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

TNS-01106: Listener using listener name LISTENER has already been started

C:\Users\myComputer>lsnrctl service

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:33
:46

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "oracle.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:166 refused:0 state:ready
         LOCAL SERVER
Service "oracleXDB.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: PATO-PC, pid: 6232>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Pato-PC)(PORT=49537))
The command completed successfully

C:\Users\myComputer>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-SEP-2012 23:34
:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                24-SEP-2012 21:08:10
Uptime                    0 days 2 hr. 26 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\myComputer\product\11.2.0\dbhome_2\network\admin\list
ener.ora
Listener Log File         c:\app\myComputer\diag\tnslsnr\Pato-PC\listener\alert\log.xm
l
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "oracle.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
Service "oracleXDB.220.8.191" has 1 instance(s).
  Instance "oracle", status READY, has 1 handler(s) for this service...
The command completed successfully
share|improve this question

migrated from stackoverflow.com Sep 25 '12 at 9:51

This question came from our site for professional and enthusiast programmers.

    
please mention , how your connecting to the system –  user12899 Sep 25 '12 at 11:40
    
@shiva Please do not add comments as answers. We have a comment functionality for a reason, even if you don't have the reputation to access it yet. –  JNK Sep 25 '12 at 12:04
    
Thanks for your response. I have been able to fix the problem. –  user12909 Sep 25 '12 at 16:24
    
If you would kindly use the same login information on both Stack Overflow and DBA, then the accounts will become "linked" and you can take ownership of this question, and give it an accept checkmark. –  jcolebrand Sep 25 '12 at 16:41

1 Answer 1

How are you trying to make a connection? If it is SQLplus here's what happens:

c:>sqlplus user/password@database

The entry "database" is looked up in the tnsnames.ora file. Here is how this may look:

database=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = databas)
    )
  )

This says that database "database" runs on server "server" with SID "databas". A connection is made with the listener on server "server" on port 1521 with a request to connect to database with SID "databas". The listener responds with the message that it does not know a database with SID "databas".

One way of getting some more information about this is to use TNSPING.

C:\>tnsping database

TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 25-SEP-2
012 08:21:21

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
D:\app\rwe\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = SERVER)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = databas)))

This will tell you the location of sqlnet.ora. Your tnsnames.ora file is in the same location. Also you get to see what the entry for database is in your tnsnames.ora file.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.