ORA-12505, TNS:listener does not currently know of SID given in connect des

Question

Please kindly help me as I just installed Oracle 11g but cannot connnect using SQL Devloper using the settings below:

Username: system

Password: mypassword

Hostname: localhost

Port: 1521

SID: XE

Error message:

Status : Failure -Test failed: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

I checked on Windows Services:

OracleServiceXE: Started

OracleXETNSListener: Started

I also unable to connect to admin page: **http://localhost:8080/apex/f?p=4950**

It says "This page can't be displayed"

I also tested to connect using SqlPlus:

Test 1: Result is ORA-12170: TNS:Connect timeout occurred

C:\oraclexe\app\oracle\product\11.2.0\server\bin>sqlplus.exe system/mypassword@XE

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 22 11:22:31 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved.

ERROR: ORA-12170: TNS:Connect timeout occurred

Enter user-name:

Test 2: Result is Successful

C:\oraclexe\app\oracle\product\11.2.0\server\bin>sqlplus.exe system/mypassword

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 22 11:33:22 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL>

Test 3: Result is Successful

C:\oraclexe\app\oracle\product\11.2.0\server\bin>sqlplus.exe / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 22 11:34:29 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL>

Listener.log

22-APR-2014 11:53:51 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=admin))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=50010)) * establish * XE * 12505
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = NASAYAO2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date                22-APR-2014 14:29:22
Uptime                    0 days 0 hr. 56 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\a
dmin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\NASAYAO2\listener\
alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NASAYAO2)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Please kindly help me.

Answer 1

Are you sure the database name is XE ?

Do you know what name did you give to database/instance while installing 11g ?

Usually the default name is ORCL.

Try changing Sid name to ORCL and test it.

Answer 2

Happened to me when I abruptly shutdown the system while being connected to database. Restarting the system does the trick.

Answer 3

If you Still getting this Error message:

Status : Failure -Test failed: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Do following steps: 1. open cmd prompt. 2. c:/ services.msc 3. its navigate to system services console there it self once you check it the OracleServiceXE status. 4.then starts the service status(rightclick>start) wait till status showing the running.

Answer 4

I have same problem, but I run on oracle linux. After searching and testing, i can connect sqldeveloper to my oracle11g without setting my listener.ora or else

here is the syntax that i run on terminal sql

alter system set LOCAL_LISTENER='(DESCRIPTION_LIST =  (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))))';

then

alter system register;

i get the tutorial here

Answer 5

1. Open Net Manager. In Oracle Net Configuration, open Local, and then, open Listeners.
2. Select your Listener.
3. In the upper right ComboBox, select Database Services.
4. Clic Add Database.
5. Fill the 3 fields: Global Database Name (i.e. ORCL.NET. In SQL Plus type SELECT * FROM GLOBAL_NAME), Oracle Home Directory (i.e. C:\app\User\product\11.2.0\dbhome_1) and SID (i.e. orcl)
6. File > Save Network Configuration.
7. Restart the OracleOraDb11g_home1TNSListener service.

Answer 6

For SQL Developer maybe you need to add these parameters

AddVMOption -Duser.language=en

AddVMOption -Duser.region=US

in the file

$SQLDEVELOPER_HOME\sqldeveloper\bin\sqldeveloper.conf

Answer 7

if u r using oracle 11g enterprise edition, then do one thing, u just uninstall it, and again reinstall oracle 11g. do all the steps carefully. when installation will finish, then go to start menu-oracle 11g-configuration & migration tool-database configuration assistance. open this & again do configuration here u will give a sid name . that sid will be used when u will create a connection in sql developer insteade of xe. execept this above dont change other. i hope it will be definitely work.