Oracle XE stopped working. TNS listener refused connection
database-connection, oracle11g
Solution
Your Oracle DB is down. It may have some problem starting up.
Go to command prompt and type:
SQL>sqlplus / as sysdba
If you get a message "connected to an idle instance" then type
SQL>startup
and let me know the message you get. If you don't get the "connected to an idle instance" message on SqlPlus startup, the type:
SQL>set lines 80 pages 50
SQL>select * from v$database;
and post the output here.
Problem
I'm working on an application which uses Oracle XE 11g as its RDBMS. It was working for several weeks but now I started to get an error message about the connection being refused. I restarted my system but it did not help. I uninstalled XE, deleted all remaining files (even from registry) and reinstalled it. Now I get another error message: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor I've read about this error and others suggested adding a new SID to the listener.ora file: ``` (SID_DESC = (SID_NAME = XE) (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) ) ``` If I do this I get another error message: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist My configuration files are: listener.ora ``` SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = XE) (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) ) (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 = AAROLD.in.mycompany.com)(PORT = 1521)) ) ) DEFAULT_SERVICE_LISTENER = (XE) ``` tnsnames.ora ``` XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = AAROLD.in.mycompany.com)(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) ) ) ``` lsnrctl status returns with: ``` LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 19-M┴RC. -2013 15:12:24 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production Start Date 19-M┴RC. -2013 15:01:12 Uptime 0 days 0 hr. 11 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\AAROLD\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AAROLD.in.mycompany.com)(PORT=1521))) 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 ``` Do you have any idea what can be the problem? I'm not an Oracle expert so I'm completely in the dark here.