ORA-12514: No TNS Listener

Resolving the “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!


No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...