一、问题描述
oracle数据库在修改SGA后，启动时报错ORA-27102: out of memory，这很显然表示超出了内存范围，但是机器内存是够的，这是why? 通过一系统search，最终发现是操作系统参数shmall设置过小影响了，shmall 参数是全部允许使用的共享内存大小。下面实验一下，看看小小参数的魅力。
二、实验
1.检查环境
[root@sam ~]# free -m total used free shared buffers cached Mem: 1993 1590 403 0 47 752 -/+ buffers/cache: 790 1203 Swap: 4095 0 4095 [root@sam ~]# uname -a Linux 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 i386 kernel.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 i386 kernel.shmall = 107374
3.检查SGA参数后，重新启动数据库
SYS@orcl >show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 800M sga_target big integer 0 SYS@orcl >shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
4.使修改后的参数生效
[root@sam ~]# sysctl -p net.ipv4.ip_forward = 0 net.ipv4.conf.default.rp_filter = 1 net.ipv4.conf.default.accept_source_route = 0 kernel.sysrq = 0 kernel.core_uses_pid = 1 net.ipv4.tcp_syncookies = 1 net.bridge.bridge-nf-call-ip6tables = 0 net.bridge.bridge-nf-call-iptables = 0 net.bridge.bridge-nf-call-arptables = 0 kernel.msgmnb = 65536 kernel.msgmax = 65536 fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel.shmmni = 4096 kernel.shmall = 107374 kernel.shmmax = 2147483648 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500
5.修改SGA参数为1G后启动数据
SYS@orcl >startup ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device Additional information: 1061158912 Additional 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 i386 kernel.shmall = 1073741824 [root@sam ~]# sysctl -p net.ipv4.ip_forward = 0 net.ipv4.conf.default.rp_filter = 1 net.ipv4.conf.default.accept_source_route = 0 kernel.sysrq = 0 kernel.core_uses_pid = 1 net.ipv4.tcp_syncookies = 1 net.bridge.bridge-nf-call-ip6tables = 0 net.bridge.bridge-nf-call-iptables = 0 net.bridge.bridge-nf-call-arptables = 0 kernel.msgmnb = 65536 kernel.msgmax = 65536 fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel.shmmni = 4096 kernel.shmall = 1073741824 kernel.shmmax = 2147483648 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500
7.启动数据
SYS@orcl >startup ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 1010828168 bytes Database Buffers 50331648 bytes Redo Buffers 5517312 bytes Database mounted. Database opened.
8.检查SGA参数
SYS@orcl >show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 1G sga_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。
