A-A+

关于 ORA-27102: out of memory 解决一例

2013年11月11日 TroubleShooting 暂无评论 阅读 1,499 次

问题描述:对内存为24GB的linux 64位之上的oracle11g调整sga大小导致错误ORA-27102,并进一步阐述参数kernel.shmmax和参数kernel.shmall设置问题。

基本信息
OS:linux x86-64 DB:11.2.0.3

[oracle@tongjidb ~]$ more /etc/fstab 

#
# /etc/fstab
# Created by anaconda on Fri Nov  1 22:06:11 2013
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=9cf955b0-f18f-4848-90d0-0605663fbab1 /                       ext4    defaults        1 1
UUID=7d64dc55-51e1-4f9d-89a4-42cbaf6f229a /boot                   ext4    defaults        1 2
UUID=427C-4136          /boot/efi               vfat    umask=0077,shortname=winnt 0 0
UUID=66b835cf-0b77-4a2d-bba9-55c821d3215c swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults,size=20G        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
[oracle@tongjidb ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda4             786G   57G  690G   8% /
tmpfs                  20G     0   20G   0% /dev/shm
/dev/sda2             3.9G   98M  3.6G   3% /boot
/dev/sda1             3.9G  260K  3.9G   1% /boot/efi
tmpfs                  20G     0   20G   0% /dev/shm
[oracle@tongjidb ~]$ free -g
             total       used       free     shared    buffers     cached
Mem:            23          2         20          0          0          2
-/+ buffers/cache:          0         22
Swap:           29          0         29

当前sga设置如下

[root@tongjidb ~]# su - oracle
[oracle@tongjidb ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 11 10:30:58 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 7920M
sga_target                           big integer 7920M
SQL> sho parameter spfile

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
spfile                               string
/u01/app/oracle/product/11.2.0
/db_1/dbs/spfiletongji.ora

调大sga参数设置,重启数据库报错

SQL> alter system set sga_target=16g scope=spfile;

System altered.

SQL> alter system set sga_max_size=16g scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

修改操作系统参数[/etc/sysctl.conf]

#oracle set
fs.aio-max-nr = 1048576
fs.file-max = 6815744
#kernel.shmall = 2097152
kernel.shmall = 5767168
#kernel.shmmax = 4294967295
kernel.shmmax = 23622320128
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
#oracle set
[root@tongjidb ~]# sysctl -p

参数生效后尝试启动数据库,正常。

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.9241E+10 bytes
Fixed Size                  2236488 bytes
Variable Size            7247761336 bytes
Database Buffers         1.1945E+10 bytes
Redo Buffers               45682688 bytes
SQL> sho parameter sga

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga                             boolean
FALSE
pre_page_sga                         boolean
FALSE
sga_max_size                         big integer
18G
sga_target                           big integer
18G

参数说明与设定(64位OS):
kernel.shmmax:
Controls the maximum shared segment size, in bytes

kernel.shmall:
Controls the maximum number of shared memory segments, in pages

[root@tongjidb ~]# getconf PAGE_SIZE
4096

kernel.shmmax 一般为物理内存的一半,最大为物理内存-2GB

我们设置的参数
kernel.shmmax 值为:24GB-2GB=22GB=22*1024*1024*1024=23622320128
kernel.shmall 值为:kernel.shmmax/PAGE_SIZE=23622320128/4096=5767168

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: