【ERROR】ORA-27102: out of memory 解决方法

千家信息网最后更新 2024年09月27日【ERROR】ORA-27102: out of memory 解决方法一、问题描述 oracle数据库在修改SGA后，启动时报错ORA-27102: out of memory，这很显然表示超出了内存范围，但是机器内存是够的，这是why? 通过一系统search，最终发现是操作系统参数shmall设置过小影响了，shmall 参数是全部允许使用的共享内存大小。下面实验一下，看看小小参数的魅力。

二、实验

1.检查环境

[root@sam ~]# free -m             total       used       free     shared    buffers     cachedMem:          1993       1590        403          0         47        752-/+ buffers/cache:        790       1203Swap:         4095          0       4095[root@sam ~]# uname -aLinux sam 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux[oracle@sam ~]$ cat /etc/sysctl.conf |grep shmall# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 1073741824 on x86_64# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 2097152 on i386kernel.shmall = 1073741824

2.将该值调小至107374

[root@sam ~]# sed -i 's/kernel.shmall = 1073741824/kernel.shmall = 107374/g' /etc/sysctl.conf[root@sam ~]# cat /etc/sysctl.conf |grep shmall# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 1073741824 on x86_64# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 2097152 on i386kernel.shmall = 107374

3.检查SGA参数后，重新启动数据库

SYS@orcl >show parameter sgaNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean     FALSEpre_page_sga                         boolean     FALSEsga_max_size                         big integer 800Msga_target                           big integer 0SYS@orcl >shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

4.使修改后的参数生效

[root@sam ~]# sysctl -pnet.ipv4.ip_forward = 0net.ipv4.conf.default.rp_filter = 1net.ipv4.conf.default.accept_source_route = 0kernel.sysrq = 0kernel.core_uses_pid = 1net.ipv4.tcp_syncookies = 1net.bridge.bridge-nf-call-ip6tables = 0net.bridge.bridge-nf-call-iptables = 0net.bridge.bridge-nf-call-arptables = 0kernel.msgmnb = 65536kernel.msgmax = 65536fs.file-max = 6815744kernel.sem = 250 32000 100 128kernel.shmmni = 4096kernel.shmall = 107374kernel.shmmax = 2147483648net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576fs.aio-max-nr = 1048576net.ipv4.ip_local_port_range = 9000 65500

5.修改SGA参数为1G后启动数据

SYS@orcl >startupORA-27102: out of memoryLinux-x86_64 Error: 28: No space left on deviceAdditional information: 1061158912Additional information: 1

6.将系统参数shmall修改回源值并生效

[root@sam ~]# sed -i 's/kernel.shmall = 107374/kernel.shmall = 1073741824/g' /etc/sysctl.conf[root@sam ~]# cat /etc/sysctl.conf |grep shmall# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 1073741824 on x86_64# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 2097152 on i386kernel.shmall = 1073741824[root@sam ~]# sysctl -pnet.ipv4.ip_forward = 0net.ipv4.conf.default.rp_filter = 1net.ipv4.conf.default.accept_source_route = 0kernel.sysrq = 0kernel.core_uses_pid = 1net.ipv4.tcp_syncookies = 1net.bridge.bridge-nf-call-ip6tables = 0net.bridge.bridge-nf-call-iptables = 0net.bridge.bridge-nf-call-arptables = 0kernel.msgmnb = 65536kernel.msgmax = 65536fs.file-max = 6815744kernel.sem = 250 32000 100 128kernel.shmmni = 4096kernel.shmall = 1073741824kernel.shmmax = 2147483648net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576fs.aio-max-nr = 1048576net.ipv4.ip_local_port_range = 9000 65500

7.启动数据

SYS@orcl >startupORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2260088 bytesVariable Size            1010828168 bytesDatabase Buffers           50331648 bytesRedo Buffers                5517312 bytesDatabase mounted.Database opened.

8.检查SGA参数

SYS@orcl >show parameter sgaNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean     FALSEpre_page_sga                         boolean     FALSEsga_max_size                         big integer 1Gsga_target                           big integer 0

三、总结

此次报错原因主要是对操作系统参数不了解导致，作为一名DBA人员，时时刻刻都与操作紧密结合，有很多优化方案，最终也都与操作系统参数有关，总的来说就是未来操作系统学习也是有必要的。下面就再补充学习一下该参数。

四、补充shmmax,shmmni,shmall参数学习

shmmax 该参数定义了共享内存段的最大尺寸，这里要注意的是参数值的单位是(bytes)，所以需要大家算好再填写。该参数不小于物理内存的一半，可能设置为内存的90%-100%，例如16G 内存，16*1024*1024*1024*90% = 15461882265，16*1024*1024*1024*100%=17179869184

shmmni 这个内核参数用于设置系统范围内共享内存段的最大数量。该参数的默认值是 4096，此值也是在系统中可以启动的Oracle实例的最大数量,通常不需要更改,因为显然在生产环境下,一个服务器上同时启动的实例个数达不到这个数量。

shmall 该参数表示系统任意时刻可以分配的所有共享内存段的总和的最大值(以页为单位)，其值应不小于shmmax/page_size。例如上面的16G，可以为15461882265/4096 (getconf PAGESIZE可得到页大小) = 3774873，或17179869184/4096=4194304，根据内存100%来推算内存8G为 2097152, 内存16G为4194304, 内存32G为8388608,内存64G为16777216。