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

databaseoracleoracle-sqldeveloperoracle11gsqlplus

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.

Best Answer

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

Related Topic