oracle 11g 耗尽分配内存，连接中断问题

Yong Huang

I see "desktop heap allocation failed" in your Event Viewer. Can you confirm that the time of this error corresponds to the starting time of your connection failure and also TNS-12531? If so, the solution is in Oracle note

Windows: Connections Via the Listener Fail with TNS-12531 On Windows 64-bit Platform (Doc ID 1384337.1)

--- begin quote ---
You need to check the third value for the registry entry \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems\:
Eg:
SharedSection=1024,20480,1024
The third argument is the maximum amount of heap memory allocated to non interactive desktops. Increase this third value and check when the TNS error disappears. There is no optimum value, it depends from one system to another.
A change in registry setting will require a restart of the machine.
--- end quote ---

Note that the description of the registry key in this document is not quite right. It should be ... Session Manager\SubSystems\Windows, and the value is like
%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,20480,768 Windows=...
In this case, you can increase the 3rd value 768 by at least 256 for example.

That very likely will solve your problem. If not, also check to see if there's any program or task that runs at the time you start to have the problem. Disable antivirus if it's enabled. Disable any hot backup agent if it's scheduled.

amirons

Yong Huang 发表于 2017-2-28 01:40
I see "desktop heap allocation failed" in your Event Viewer. Can you confirm that the time of this e ...

thankyou verymuch.I will check again

unix007

你看看数据库alert日志吧，应该是processes参数不够引起的。
进入sqlplus查看一下：
show parameter processes;
修改：
alter system set processes=1000 scope=spfile;
然后重启数据库

kasabulanka888

amirons 发表于 2017-2-28 07:57
thankyou verymuch.I will check again

1.IP地址要固定，确定你的IP是固定的，是否为双网卡
2.如unix007所说要看一下processes和session，设定3000也可以
3.我们一般是client 端连接AP Server，通过Server net provide连接DB，如果直接势必造成
DB LOADING，这时要考虑其它方案缓解

amirons

kasabulanka888 发表于 2017-2-28 09:01
1.IP地址要固定，确定你的IP是固定的，是否为双网卡
2.如unix007所说要看一下processes和session，设定3 ...

扛不住，重新安装了

Yong Huang

You lost a very important opportunity to learn by simply reinstalling the software. A possible solution is permanently lost. But if you were under high pressure and pressed for a project deadline, I understand what you were feeling.

If the processes parameter was set too low, (1) you would see ORA-00020 in alert.log, and (2) your database would reject connection whether it's local or through Oracle Net (SQL*Net). Neither applied to your case so I don't think your processes was set too low.

Whether the cause of the problem is due to dual network cards is hard to say. I don't see any evidence for that. But I'm pretty sure the server load was not the cause based on the resource monitor images you showed earlier.

Being an IT professional requires more than pure technical skills. General troubleshooting skills are just as important. Be very keen on finding minor differences between different scenarios. Pay extra attention to what happens at the time of the error. Research on all relevant error messages. Unfortunately, you don't seem to be interested in doing this sort of things.

amirons

Yong Huang 发表于 2017-2-28 22:55
You lost a very important opportunity to learn by simply reinstalling the software. A possible solut ...

thankyou verymuch.but I must fix it as soon as possible.Miss the chance of learning.what a pity

kasabulanka888

amirons 发表于 2017-2-28 23:08
thankyou verymuch.but I must fix it as soon as possible.Miss the chance of learning.what a pity

1.监听是静态注册的还是动态注册的？这个也是有一个原因吧，只是目前的排除项太多，从你提供的选项来看很难找到根本原因。

2.如果有STB的话，可以切换一下，把备库做为主库看一下情况

3.针对你的重装来说相对来看也是一个方法，只是如上所说，你失去一个学习的机会，一个找根本原因的机会，不确定你重装后能否解决，但上述的几个选项一定要提前规划好
如：内核参数，hostname,监听注册（静，动态注册选定），IP规划，ILO确定,performance监控，DB软体监控等等，以上设定好后出现问题才可以快速查找问题和排除问题，谢谢

amirons

最终问题还是监听问题，但是没有找到解决办法。后来是卸掉ORACLE 11g后重新安装ORACLE 12c，问题没有现再出现。推测原因是oracle 11.2.0.1.0这样的低版本与windows server 2012这样的操作系统兼容性不好所致，也不知道这种推测对不对。感谢各位的大力帮忙。

小颜Kevin

我判断是监听的问题，因为你sqlplus / as sysdba 可以正常连接，而sqlplus someuser/password@xxx 等待时间长。 我的建议是监听删了重建，并且使用静态监听看看效果。另外方便的话把sqlplus / as sysdba 执行show parameter target 的结果贴一下。