Shulou(Shulou.com)06/01 Report--

1. Problem description: after modifying the SGA, the oracle database launches the error ORA-27102: out of memory, which obviously indicates that it is out of the memory range, but the machine memory is enough, is this why? Through a system search, it is finally found that the operating system parameter shmall setting is too small, and the shmall parameter is all allowed shared memory size. Let's experiment to see the charm of small parameters.

2. Experiment

1. Check the environment [root@sam] # free-m total used free shared buffers cachedMem: 1993 1590 403 047 752 buffers/cache + 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 GNU/Linux [oracle@sam ~] $cat / etc/sysctl.conf | grep shmall# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 1073741824 on x86 six months oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 2097152 on i386kernel.shmall = 1073741824

two。 Adjust the value down to 107374

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

3. Restart the database after checking the SGA parameters

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. Make the modified parameters effective

[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. Start the data after changing the SGA parameter to 1G

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

6. Modify the system parameter shmall back to the source value and take effect

[root@sam ~] # sed-I 's/kernel.shmall = 107374/kernel.shmall = 1073741824 etc/sysctl.conf g' / etc/sysctl.conf [root@sam ~] # cat / etc/sysctl.conf | grep shmall# oracle-rdbms-server-11gR2-preinstall setting for kernel.shmall is 1073741824 on x86pm 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 = 6815744kernel.sem = 250 32000 100 128kernel.shmmni = 4096kernel.shmall = 1073741824kernel.shmmax = 2147483648net.core.rmem_default = 262144net.core.rmem2144net.rmemmeme _ Max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576fs.aio-max-nr = 1048576net.ipv4.ip_local_port_range = 9000 65500

7. Startup data

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. Check SGA parameters

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

III. Summary

The reason for this error is mainly due to the lack of understanding of the operating system parameters. As a DBA staff, it is closely integrated with the operation all the time. There are many optimization programs, which are ultimately related to the operating system parameters. Generally speaking, it is necessary to learn the operating system in the future. Here is a supplementary study of this parameter.

IV. Supplementary learning of shmmax,shmmni,shmall parameters

Shmmax this parameter defines the maximum size of the shared memory segment. Note here that the unit of the parameter value is (bytes), so you need to calculate it before entering it. This parameter is not less than half of the physical memory, and may be set to 90% of the memory, for example, 16g memory, 16" 1024" 1024" 1024" 90% = 15461882265meme 16" 1024" 1024" 1024" 100% "17179869184

The kernel parameter shmmni is used to set the maximum number of system-wide shared memory segments. The default value for this parameter is 4096, which is also the maximum number of Oracle instances that can be started on the system, and usually does not need to be changed, because it is obvious that in a production environment, the number of instances started at the same time on a server cannot reach this number.

Shmall this parameter represents the maximum value (in pages) of the sum of all shared memory segments that the system can allocate at any time, and its value should not be less than shmmax/page_size. For example, the 16G above can be 15461882265ax 4096 (page size available for getconf PAGESIZE) = 3774873, or 17179869184max 4096mm 4194304. Based on 100% of the memory, it is estimated that 8G of memory is 2097152, 16G of memory is 4194304, 32G of memory is 8388608, and 64G of memory is 16777216.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.