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

I'm using JDeveloper when I try to create a database connection with the hr/hr user I get this message:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
localhost:1521:ORCL

then I go to the command line and I put tnsping ORCL

C:\Users\Administrator>tnsping ORCL

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 20-FEB-2
018 15:55:23

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

Used parameter files:
C:\app\oracle\virtual\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=213.130.77.281)(PORT=1521)))
OK (90 msec)

C:\Users\Administrator>é[email protected]


and checking If the Oracle listener is running:

C:\Users\Administrator>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 22-FEB-2018 12:34
:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TESTINFRA)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Produ
ction
Start Date                20-FEB-2018 15:02:35
Uptime                    1 days 21 hr. 31 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\oracle\virtual\product\12.2.0\dbhome_1\network\
admin\listener.ora
Listener Log File         C:\app\oracle\virtual\diag\tnslsnr\TESTINFRA\listener\
alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TESTINFRA)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Answers

Your listener service doesn't clearly state that it has any ORCL database SID here, on this host. When you used TNSPING command to check connectivity, you walked around this by using Oracle EZCONNECT adapter that resolves all needed connect info itself, but JDBC driver needs either clear declaration of SID in listener or clear command to use EZCONNECT adapter.

So are two ways to solve this:

  1. Open listener.ora and find these lines:

     (SID_LIST =
      (SID_DESC =
       (SID_NAME = CLRExtProc)
       (ORACLE_HOME = C:\app\oracle\virtual\product\12.2.0\dbhome_1)
       (PROGRAM = extproc)
       (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\virtual\product\12.2.0\dbhome_1\bin\oraclr11.dll")
      )
     )
    
    

    Replace them with these:

     (SID_LIST =
      (SID_DESC =
       (SID_NAME = CLRExtProc)
       (ORACLE_HOME = C:\app\oracle\virtual\product\12.2.0\dbhome_1)
       (PROGRAM = extproc)
       (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\virtual\product\12.2.0\dbhome_1\bin\oraclr11.dll")
      )
      (SID_DESC =
       (SID_NAME = ORCL)
       (ORACLE_HOME = C:\app\oracle\virtual\product\12.2.0\dbhome_1)
       (GLOBAL_DBNAME = ORCL)
      )	
     )
    
    

    Then use "lsnrctl stop" and "lsnrctl start" to restart the listener. This change will register new SID_DESC block with description of your database SID in listener.

  2. Change JDBC string to connect through EZCONNECT adapter and not through SID. Please, post your JDBC connection string if you want exact answers, but in overall you shouldn't use:

     jdbc:oracle:thin:@213.130.77.281:1521:orcl
    
    

    And use instead:

     jdbc:oracle:thin:@//213.130.77.281:1521/orcl
    
    

    This change will tell JDBC to use EZCONNECT adapter.

Posted on by Dmitriy Yankin

In you jdbc error you are using : localhost:1521:ORCL

In the TNSPING, it’s 213.130.77.281

Use the ip address in jdev also.

Posted on by Kris Rice