ORA-12514

“ORA-12514: No TNS Listener” error with Oracle XE...

I faced this particularly nagging error with a personal install of Oracle XE (Oracle Database 10g Express Edition) on my Windows XP laptop:

ORA-12514:No TNS Listener

The SQL Command prompt that came with Oracle XE would work but none of the other tools like Toad, SQL Plus or even the Oracle XE Database Homepage which would open in a browser window would work.

The final resolution after a lot of research seemed to be painfully simple:

1. Run the LSNRCTL.EXE program from the BIN directory of your Oracle XE install (default: C:\oraclexe\app\oracle\product\10.2.0\server\BIN). You should even be able to run it from any command prompt since the BIN directory is generally added to your PATH environment variable by default.
2. At the LSNRCTL> command prompt, issue the STOP command. It should say: Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
   The command completed successfully
3. Now, at the LSNRCTL> prompt, issue the START command. It should return: Starting tnslsnr: please wait…
   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Production
   System parameter file is C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\AD
   MIN\listener.ora
   Log messages written to C:\oraclexe\app\oracle\product\10.2.0\server\network\log
   \listener.log
   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR
   _XEipc)))
   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=port)))
   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
   STATUS of the LISTENER
   ————————
   Alias                     LISTENER
   Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Produ
   ction
   Start Date                14-MAY-2009 01:24:49
   Uptime                    0 days 0 hr. 0 min. 5 sec
   Trace Level               off
   Security                  ON: Local OS Authentication
   SNMP                      OFF
   Default Service           XE
   Listener Parameter File   C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\listener.ora
   Listener Log File         C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
   Listening Endpoints Summary…
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=port)))
   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

That’s it! That should resolve the problem. Verify by running “tnsping XE” or “tnsping dbname” at the command prompt. It should return OK.

All your tools like Toad, SQL Plus or Oracle SQL Developer should be able to connect to the database now!