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

Question

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

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.