3

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>é2@

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
MT0
  • 143,790
  • 11
  • 59
  • 117
Nuñito Calzada
  • 4,394
  • 47
  • 174
  • 301
  • Check what your listener is actually listening to: "lsnrctl status" – access_granted Feb 20 '18 at 22:30
  • 1
    You should be using service names not SID for the connection, but the listener isn't showing any; either your database isn't up, or it can't register. Has this ever worked? Can you connect any other way (e.g. locally with SQL\*Plus)? [This answer](https://stackoverflow.com/a/22769396/266304) *might* be useful. – Alex Poole Feb 26 '18 at 13:19
  • Did you check the database was up, and its `local_listener` setting, and try modifying that and/or manually trying to register? Or have you already resolved the issue some other way? – Alex Poole Mar 01 '18 at 12:42

2 Answers2

1

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.

Kris Rice
  • 3,300
  • 15
  • 33
1

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.

Dima Yankin
  • 365
  • 1
  • 12