Oracle XE 11g – Resolving ORA-12514 TNS Listener Error

javaoracle

I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue.

issue is: when I wanted to integrate OWASP WebGoat application with oracle XE 11g , I receive the above mentioned error.

connection URL in web.xml is: 

jdbc:oracle:thin:@//localhost:1521/XE/${USER}

=================================================================
listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER = 
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

=============================================================
tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)(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) 
    ) 
  )

===================================================================
java version:

java version "1.7.0_55"
Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
Java HotSpot(TM) Client VM (build 24.55-b03, mixed mode, sharing)

===================================================================
output of the lsnrctl services:

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 09-MAY-2014 16:34
:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: AMIR-PC, pid: 5740>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Amir-PC)(PORT=2403))
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

=====================================================================
output of the tnsping xe :

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 09-MAY-2
014 16:35:19

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

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

could some one please help me resolve this issue? I have been struggling more than 3 days and not found any useful answer yet.

Best Answer

In a comment you said your web.xml contains:

<init-param>
 <param-name>DatabaseConnectionString</param-name>
 <!-- The string "${USER}" in the connection string will be replaced
      by the active username when making a connection. -->
 <param-value>
  <!--jdbc:hsqldb:mem:${USER}-->
  <!--jdbc:oracle:thin:@127.0.0.1:1521:xe/${USER}-->
  jdbc:oracle:thin:@localhost:1521
 </param-value>
</init-param>

The connection string you showed at the top of the question is commented out, so you're actually connecting with jdbc:oracle:thin:@localhost:1521. That isn't supplying a service name; but your listener.ora does have DEFAULT_SERVICE_LISTENER = (XE), so this should be the equivalent of jdbc:oracle:thin:@localhost:1521/XE. (I've tested this with 11gR2 EE and the parentheses around the XE in the listener.ora don't seem to matter).

But that doesn't quite seem to work as expected. When you changed that and set the user name explicitly:

jdbc:oracle:thin:system/system@//localhost:1521/XE

... the TNS-12514 went away and was replaced with an ORA-01017, invalid username/password. That means the connection string is now working and your system account's password is something other than system. With a valid username and password it looks like it should now work.

If the ${USER} substitution works as the comment in the web.xml suggests, and contains both the username and password, then making your connection string look like this ought to work:

jdbc:oracle:thin:${USER}@//localhost:1521/XE

But only if ${USER} is actually a valid username/password string.

Related Question