ORA-27301错误分析和处理方法

ORA-27301错误分析和处理方法

发生时现象

应用不能连接数据库.alert日志告警ORA-27301.
网络通信有问题,内存耗用高.
发生时,有的系统连接数较高,有的不高.所以跟连接数没有必然联系.

错误严重程度

不能连接数据库.影响业务.

预防/解决方法

如果alert日志已经有ORA-27301错误,需要尽快修改.即使alert日志没有报错,也建议部署.

部署步骤有2步.部署不需要重启数据库或者集群(如果是RAC)

1) 修改sysctl.conf

vm.min_free_kbytes = 1073742
这个1073742 kbytes, 就是1G内存.,是对于256G内存以及大于256G内存的.
较小的内存可以调小.比如524288(512M内存)
然后sysclt -p 即刻生效. 永久有效.

如果只是一次性使用,重启失效,可以不修改sysctl.conf,用:
sysctl -w vm.min_free_kbytes=1073742

2) 修改lo的MTU.

查看当前MTU的值: netstat -in
不需要重启network 服务.以root执行
ifconfig lo mtu 16436
这样就可以了.

要重启服务器时也生效,需要修改lo的配置文件
/etc/sysconfig/network-scripts/ifcfg-lo
加入如下一行.
MTU=16436

说明

所以对于RAC,不需要重启数据库和集群. 文档2041723.1的描述,好像一定要重启集群.分析如下:
ifconfig lo mtu 16436 , 即刻生效.
修改ifcfg-lo是为了让修改重启服务器时也生效, 重启服务器时, network自然重启了. 所以不需要手动重启集群.

参考文档

1. Oracle Linux: ORA-27301:OS Failure Message: No Buffer Space Available (Doc ID 2041723.1)
2. ORA-27301: OS Failure Message: No Buffer Space Available / ORA-27302: failure occurred at: sskgxpsnd2 Source Script (Doc ID 2322410.1)
3. https://access.redhat.com/solutions/2945981
4. Recommended Value for the Kernel Parameter vm.min_free_kbytes If Memory is Added to a BDA Server (Doc ID 2740145.1)
5. Oracle Linux: Recommended Value For vm.min_free_kbytes Kernel Tuning Parameter (Doc ID 2501269.1)
6. https://www.ibm.com/docs/en/gpfs/4.1.0.4?topic=considerations-memory
7. https://www.kernel.org/doc/Documentation/sysctl/vm.txt
8. https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8/html/monitoring_and_managing_system_status_and_performance/configuring-an-operating-system-to-optimize-memory-access_monitoring-and-managing-system-status-and-performance

其中第一个文档,是在执行rman备份脚本时失败报错.也说明了只要修改 ifconfig lo mtu 16436 即可立即生效.

进一步分析

1) min_free_bytes的linux 官方解释

https://www.kernel.org/doc/Documentation/sysctl/vm.txt

This is used to force the Linux VM to keep a minimum number of
kilobytes free. The VM uses this number to compute a
watermark[WMARK_MIN] value for each lowmem zone in the system. Each
lowmem zone gets a number of reserved free pages based proportionally
on its size.

Some minimal amount of memory is needed to satisfy PF_MEMALLOC
allocations; if you set this to lower than 1024KB, your system will
become subtly broken, and prone to deadlock under high loads.

Setting this too high will OOM your machine instantly.

2) redhat 官方文档

redhat文档

Sets the size of the reserved free pages pool. It is also responsible
for setting the min_page, low_page, and high_page thresholds that
govern the behavior of the Linux kernel’s page reclaim algorithms. It
also specifies the minimum number of kilobytes to keep free across the
system. This calculates a specific value for each low memory zone,
each of which is assigned a number of reserved free pages in
proportion to their size.

Setting the vm.min_free_kbytes parameter’s value:

Increasing the parameter value effectively reduces the application
working set usable memory. Therefore, you might want to use it for
only kernel-driven workloads, where driver buffers need to be
allocated in atomic contexts.

Decreasing the parameter value might render the kernel unable to
service system requests, if memory becomes heavily contended in the
system.

Warning

Extreme values can be detrimental to the system’s performance. Setting
the vm.min_free_kbytes to an extremely low value prevents the system
from reclaiming memory effectively, which can result in system crashes
and failure to service interrupts or other kernel services. However,
setting vm.min_free_kbytes too high considerably increases system
reclaim activity, causing allocation latency due to a false direct
reclaim state. This might cause the system to enter an out-of-memory
state immediately.

The vm.min_free_kbytes parameter also sets a page reclaim watermark,
called min_pages. This watermark is used as a factor when determining
the two other memory watermarks, low_pages, and high_pages, that
govern page reclaim algorithms.

3) 设置多大 min_free_kbyes

在Oracle Linux: Recommended Value For vm.min_free_kbytes Kernel Tuning Parameter (Doc ID 2501269.1)
提到:

Setting vm.min_free_kbytes too low prevents the system from reclaiming
memory. This can result in system hangs and OOM-killing multiple
processes. vm.min_free_kbytes设置过低,会使得linux 不去reclaim (回收)内存.

Setting vm.min_free_kbytes too low prevents the system from reclaiming
memory. This can result in system hangs and OOM-killing multiple
processes.

但是设置过高,会导致系统频繁回收内存.也导致OOM错误(Out of Memory)

However, setting this parameter to a value that is too high (5-10% of
total system memory) will cause your system to become out-of-memory
immediately. Linux is designed to use all available RAM to cache file
system data. Setting a high min_free_kbytes value results in the
system spending too much time reclaiming memory.

如何计算的

在Redhat https://access.redhat.com/solutions/336033文档提到,这个min_free_kbytes的缺省值在mm/page_alloc.c 计算.

关于设置多大: 内存的0.5%

oralce的MOS(2041723) 文档有的提到0.4%, MOS(2501269.1) 提到0.5%, IBM的文档提到5%.

修改参数对系统的负面影响

建议在系统不繁忙时设置,

对于RAC. 逐个节点设置. 不需要重启数据库或者RAC的集群.

注意,如下内容做参考, 因为这里指的是Oracle 的DBA服务器. 它安装的是Oracle Linux.

在Recommended Value for the Kernel Parameter vm.min_free_kbytes If Memory is Added to a BDA Server (Doc ID 2740145.1)中提到,

1. No outage or impact on existing services is expected when making this change. And no cluster restart is needed when updating the
   parameter. 修改参数对当前服务不会造成终端或者影响,不需要重启cluster.
   (这里提到的cluster不是RAC的集群,不过概念类似).

2. There is a small chance of an out-of-memory condition on the node where this is being performed. This could require a reboot. For this
   reason it is better to make the change to vm.min_free_kbyes one node
   at a time and during off-peak hours. 有很小的概率会导致时节点重启,所以建议滚动修改.
   而且在业务不忙时实施.