shmall参数设置不当引起数据库启动时报outofmemory报错-mysql教程

shmall是全部允许使用的共享内存大小，单位是页,可以通过getconfPAGESIZE来获取每页的大小，一般为4096字节shmmax是单个段允许使用的最大共享内存大小可以使用ip

shmall是全部允许使用的共享内存大小，单位是页,可以通过getconf PAGESIZE来获取每页的大小，一般为4096字节
shmmax是单个段允许使用的最大共享内存大小
可以使用 ipcs -l 看到shmall,shmmax设置的值。ipcs -u可以看到实际使用的情况。

oracle@fly007:~> ipcs -l ------ Shared Memory Limits -------- max number of segments = 4096 max seg size (kbytes) = 4194304 //shmmax为4G max total shared memory (kbytes) = 8388608 //shmall为8G min seg size (bytes) = 1 ------ Semaphore Limits -------- max number of arrays = 128 max semaphores per array = 250 max semaphores system wide = 32000 max ops per semop call = 100 semaphore max value = 32767 ------ Messages: Limits -------- max queues system wide = 16 max size of message (bytes) = 65536 default max size of queue (bytes) = 65536 oracle@fly007:~> ipcs -u ------ Shared Memory Status -------- segments allocated 3 pages allocated 3 pages resident 3 pages swapped 0 Swap performance: 0 attempts 0 successes ------ Semaphore Status -------- used arrays = 4 allocated semaphores = 488 ------ Messages: Status -------- allocated queues = 0 used headers = 0

下面为shmall和shmmax参数相关的案例

环境介绍：

操作系统：suse 11 sp1 64bit 数据库版本：oracle 11g R1 64bit 主机内存：94G

1、主机内存94G，更改数据库的sga_target为40G，重启数据库生效该参数，在启动过程报ORA-27102: out of memory错误：

SQL> alter system set sga_target=40G scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

2、当前的shmall和shmmax参数设置如下：

fly007:~ # cat /etc/sysctl.conf | grep -E 'shmall|shmmax' kernel.shmall = 4194304 kernel.shmmax = 50708928512

3、shmmax是单个段允许使用的最大共享内存大小，单位是字节，一般shmmax>sga_target的大小，这样整个sga就在一个共享内存段，这是推荐的做法，我们这设置的shmmax为物理内存的一半，也就是47G，大于sga_target=40G，该参数设置正确

fly007:~ # cat /etc/sysctl.conf | grep shmmax kernel.shmmax = 50708928512 fly007:~ # echo "50708928512/1024/1024/1024" | bc 47

4、shmall是全部允许使用的共享内存大小，注意单位是页,通过getconf PAGESIZE命令得到每页的大小为4096字节，也是就4194304*4096/1024/1024/1024=16G

fly007:~ # getconf PAGESIZE 4096 fly007:~ # cat /etc/sysctl.conf | grep shmall kernel.shmall = 4194304 fly007:~ # echo "4194304*4096/1024/1024/1024" | bc 16

5、全部允许使用的共享内存大小为16G<40G,所以启动数据库报错了，shmall参数可以这样设置，shmall=shmmax/4096=50708928512/4096=12380109
6、设置shmall参数为12380109，，生效该设置，启动数据库，正常启动

fly007:~ # cat /etc/sysctl.conf | grep shmall kernel.shmall = 12380109 fly007:~ # sysctl -p fly007:~ # su - oracle oracle@fly007:~> sqlplus /nolog SQL*Plus: Release 11.1.0.7.0 - Production on Wed Dec 25 23:45:04 2013 Copyright (c) 1982, 2008, Oracle. All rights reserved. SQL> conn /as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 4.8103E+10 bytes Fixed Size 2170704 bytes Variable Size 1.9998E+10 bytes Database Buffers 2.8052E+10 bytes Redo Buffers 50548736 bytes Database mounted. Database opened. SQL>

以下关于shmall和shmmax内容来自：+and+SHMALL

Configuring SHMMAX and SHMALL for Oracle in Linux

======
SHMMAX and SHMALL are two key shared memory parameters that directly impact’s the way by which Oracle creates an SGA. Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel where multiple processes share a single chunk of memory to communicate with each other.
While trying to create an SGA during a database startup, Oracle chooses from one of the 3 memory management models a) one-segment or b) contiguous-multi segment or c) non-contiguous multi segment. Adoption of any of these models is dependent on the size of SGA and values defined for the shared memory parameters in the linux kernel, most importantly SHMMAX.

SHMMAX and SHMALL -

SHMMAX is the maximum size of a single shared memory segment set in “bytes”.