Search code examples
oracledatabase-administration

Periodic tns-12531: TNS: Cannot allocate memory

I have a problem that's been plaguing me about a year now. I have Oracle 12.1.x.x installed on my machine. After a day or two the listener stops responding and the listener.log contains a bunch of TNS-12531 messages. If I reboot, the problem goes away and I'm fine for another day or two. I'm lazy and I hate rebooting, so I decided to finally track this down, but I'm having no luck. Since the alternative is to do work that I really don't want to do, I'm going to spend all my time researching this.

Some notes:

  • Windows 10 Pro
  • 64-Bit
  • 32 GB RAM
  • Generally, about 20GB free when the error occurs
  • I have several databases and it doesn't matter which DB is running
  • Restarting the DB doesn't help
  • Restarting the listener doesn't help
  • Only rebooting clears the problem
  • When I set TRACE_LEVEL_LISTENER = 16, I don't get much more info. Trace files are not written to
  • I can connect to the DB if I bypass the listener (ie, set ORACLE_SID=xxx and connect without a DB identifier)
  • All other network interactions seem to work fine after the listener stops
  • lsnrctl status hangs and adds another TNS-12531 to the listener.log
  • I have roughly the same config at home and this does not happen

Below is an example of a listener.log file:

Fri Jul 28 14:21:47 2017
System parameter file is D:\app\user\product\12.1.0\dbhome_1\network\admin\listener.ora
Log messages written to D:\app\user\diag\tnslsnr\LJ-Quad\listener\alert\log.xml
Trace information written to D:\app\user\diag\tnslsnr\LJ-Quad\listener\trace\ora_24288_14976.trc
Trace level is currently 16

Started with pid=24288
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LJ-Quad)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
28-JUL-2017 14:22:06 * 12531
TNS-12531: TNS:cannot allocate memory
28-JUL-2017 14:22:47 * 12531
TNS-12531: TNS:cannot allocate memory
28-JUL-2017 14:26:24 * 12531
TNS-12531: TNS:cannot allocate memory

Thanks a bunch for any help you can provide!

Solution

  • 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)