[SOLVED] ORA-12505, TNS:listener does not currently know of SID given in connect descriptor. Eclipse and Fedora 20 via JDBC

Issue

I've installed Oracle 11g XE on a Fedora 20 Virtual Machine, configured it and set the enviroment variables (running the oracle_env.sh). I've got this error when trying to connect Eclipse with the database via jdbc, using this string "jdbc.databaseurl=jdbc:oracle:thin:@192.168.88.134:1521:XE"

I can connect to the database in the Virtual Machine via SQL*Plus (sqlplus / as sysdba).

The $ORACLE_SID variable is ok (XE). I've already tried to stop and start the listener, then startup the database, and use the alter system register; command.

Also statically registering the database without success (I cannot asure I did not mistake doing this, so if somebody thinks this could solve my problem I would try again).

Here is the listener.ora file:

# listener.ora Network Configuration File: 

SID_LIST_LISTENER = 
  (SID_LIST = 
    (SID_DESC = 
      (SID_NAME = PLSExtProc) 
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe) 
      (PROGRAM = extproc) 
    ) 
  ) 



LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) 
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.134)(PORT = 1521)) 
    ) 
  ) 

DEFAULT_SERVICE_LISTENER = (XE) 

And the tnsnames.ora file:

# tnsnames.ora Network Configuration File: 

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

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

And lsnrctl status:

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 31-MAR-2014 01:22:35 

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) 
STATUS of the LISTENER 
------------------------ 
Alias                     LISTENER 
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production 
Start Date                30-MAR-2014 22:41:35 
Uptime                    0 days 2 hr. 41 min. 1 sec 
Trace Level               off 
Security                  ON: Local OS Authentication 
SNMP                      OFF 
Default Service           XE 
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora 
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/192/listener/alert/log.xml 
Listening Endpoints Summary... 
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE))) 
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.88.134)(PORT=1521))) 
Services Summary... 
Service "PLSExtProc" has 1 instance(s). 
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... 
The command completed successfully

I'm almost sure that the problem is that the listener doesn't know about the database. The previous command should show, apart from what it already shows, something like

Service XE has 1 instance.
Instance "XE", status READY, has 1 handler for this service

... but it doesn't, and I don't know how to solve this.

I'm new at this, mainly at Linux, so I will appreciate every detail in the solutions you suggest.

Solution

This sounds like your database is trying to register using the wrong IP address to contact the listener. Your listener is configured to listen on 192.168.88.134, but perhaps the DB is assuming localhost (127.0.0.1), or an old IP value if the configuration has ever changed.

By default the database will attempt to register against the server's external host name (the default when local_listener is blank), but you may be getting an unexpected value from that - so what is in /etc/hosts for the machine name matters. Whatever has caused that, registration seems to be failing.

You can explicitly tell the DB to register using the actual listener address:

alter system set local_listener = '192.168.88.134:1521' scope=memory;
alter system register;

If that works and lsnrctl services now shows XE, then repeat the set command with scope=both to make it stick on the next DB restart.

Answered By - Alex Poole