Oracle Express intermittently stops responding, requiring a OS reboot to fix

Question

I've been having an issue with Oracle XE the past 2 days and it's really been making me pull my hair out.

There seems to be no rhyme nor reason why this happens, or when it does, but it happened 6 times yesterday and so far twice this morning. I'm developing a .NET app, and sometimes when I start the app, refresh the page, or do whatever, I get an exception that a connection could not be made to the database. I then try to connect with sqlplus I just get:

C:\>sqlplus user/password@localhost

SQL*Plus: Release 11.2.0.2.0 Beta on Wed May 1 11:08:53 2013

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

It then just sits there forever. No timeout, no error, no nothing. I can't connect with any other database program either, the whole server is just unresponsive.

If I go into the Services manager, I see that the services are indeed running so nothing has crashed:

I've tried restarting OracleServiceXE and OracleXETNSListener. Both will shut down and restart, however I still cannot make a connection to the server.

The only thing that fixes this problem is a complete Windows reboot. However, this of course takes 5-10 minutes and is really starting to get old.

There does not appear to be anything related to Oracle around this time in the Windows Event Viewer. I'm happy to add any other information that would help debug. Any ideas?

Update:

I did notice a few strange errors in listener.log. These errors start the exact same time the crashes started happening:

30-APR-2013 08:50:42 * 12531
TNS-12531: TNS:cannot allocate memory
Tue Apr 30 08:52:28 2013
30-APR-2013 08:52:28 * 12531
TNS-12531: TNS:cannot allocate memory
Tue Apr 30 08:52:58 2013
30-APR-2013 08:52:58 * 12531
TNS-12531: TNS:cannot allocate memory

Last 1,000 lines of alert_XE.log are here.

Answer 1

You are encountering these problems because you are running a 32-bit version of Oracle XE on 64-bit Windows. This is not a supported configuration, and is known to have many documented problems.

Due to there not being a 64-bit version of XE, you can instead install the Standard Edition (free for development) from here. If you're also using APEX, you can get it here.

Answer 2

Issue 1

This error can occur approximately after 2048 connections have been made via the listener when running on a non-English Windows installation.

Fix for Issue 1

Create a Windows User Group named Administrators on the computer where the listener.exe resides. This can fix the issue of the listener dying.

Reference: I'll post the link for the first issue as soon as I find it again

---

Issue 2

This error can also occur on Windows 64-Bit systems where the Desktop Application Heap is too small.

Fix for Issue 2

Try to Increase the Desktop Application Heap Registry in windows its located in

HKLM\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows

Just as note don't add this Value by yourself, you have to depend on document.

Basically search for the registry entry and alter the third value for the key SharedSection=1024,20480,1024. This is a trial and error approach, but seems to improve listener's stability and memory issues.

Reference: TNS:cannot allocate memory - is there limit to the num databases on one box (Oracle Developer Community)