ORA-27102: out of memory (调整SGA时)-优快云博客

本文详细介绍了如何在Oracle数据库中调整SGA配置参数，并通过修改Linux内核参数以支持更大的SGA大小。此外，还提供了具体的步骤和命令示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--oracle user and login in database with as sysdba
SQL> show parameter log_buff

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 14234624
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 6000M
sga_target big integer 6000M
SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 2000M

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

--root user
localhost:/ # uname -a
Linux localhost 2.6.32.12-0.7-default #1 SMP 2010-05-20 11:14:20 +0200 x86_64 x86_64 x86_64 GNU/Linux

--oracle user
oracle@localhost:/> cd oracle/
oracle@localhost:/oracle> l
total 44
drwxr-xr-x 8 oracle oinstall 4096 2011-10-16 19:10 ./
drwxr-xr-x 25 root root 4096 2013-03-29 13:58 ../
drwxr-x--- 3 oracle oinstall 4096 2011-10-17 10:10 admin/
drwxr-x--- 3 oracle oinstall 4096 2011-10-16 19:12 flash_recovery_area/
drwxr-xr-x 2 oracle oinstall 16384 2011-10-16 17:33 lost+found/
drwxr-x--- 3 oracle oinstall 4096 2012-12-24 19:43 oradata/
drwxrwx--- 6 oracle oinstall 4096 2011-10-16 19:02 oraInventory/
drwxr-xr-x 3 oracle oinstall 4096 2011-10-16 18:33 product/
oracle@localhost:/oracle> mkdir backup
oracle@localhost:/oracle> ll
total 40
drwxr-x--- 3 oracle oinstall 4096 2011-10-17 10:10 admin
drwxr-xr-x 2 oracle oinstall 4096 2013-04-02 18:41 backup
drwxr-x--- 3 oracle oinstall 4096 2011-10-16 19:12 flash_recovery_area
drwxr-xr-x 2 oracle oinstall 16384 2011-10-16 17:33 lost+found
drwxr-x--- 3 oracle oinstall 4096 2012-12-24 19:43 oradata
drwxrwx--- 6 oracle oinstall 4096 2011-10-16 19:02 oraInventory
drwxr-xr-x 3 oracle oinstall 4096 2011-10-16 18:33 product

--oracle user and login in database with as sysdba
create pfile='/oracle/backup/pfile1.ora' from spfile;

alter system set sga_max_size=16384M scope=spfile;
alter system set sga_target=16384M scope=spfile;

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

SQL>startup pfile='/oracle/backup/pfile1.ora'
ORACLE instance started.

Total System Global Area 6291456000 bytes
Fixed Size 2093256 bytes
Variable Size 1392512824 bytes
Database Buffers 4882169856 bytes
Redo Buffers 14680064 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

SQL> create spfile from pfile='/oracle/backup/pfile1.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/102_64/dbs/spf
ilezmmcdev.ora
SQL>

--root user login in
--查看linux内核
localhost:~ # more /etc/sysctl.conf
# Disable response to broadcasts.
# You don't want yourself becoming a Smurf amplifier.
net.ipv4.icmp_echo_ignore_broadcasts = 1
# enable route verification on all interfaces
net.ipv4.conf.all.rp_filter = 1
# enable ipV6 forwarding
#net.ipv6.conf.all.forwarding = 1
# increase the number of possible inotify(7) watches
fs.inotify.max_user_watches = 65536
# avoid deleting secondary IPs on deleting the primary IP
net.ipv4.conf.default.promote_secondaries = 1
net.ipv4.conf.all.promote_secondaries = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax =8200000000
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
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304

其中shmall=2097152*4096/1024/1024/1024=8G

这里，对每个参数值做个简要的解释和说明。
（1）shmmax：该参数定义了共享内存段的最大尺寸（以字节为单位）。缺省为32M，对于oracle来说，该缺省值太低了，通常将其设置为2G。
（2）shmmni：这个内核参数用于设置系统范围内共享内存段的最大数量。该参数的默认值是 4096 。通常不需要更改。
（3）shmall：该参数表示系统一次可以使用的共享内存总量（以页为单位）。缺省值就是2097152，通常不需要修改。
（4）sem：该参数表示设置的信号量。
（5）file-max：该参数表示文件句柄的最大数量。文件句柄设置表示在linux系统中可以打开的文件数量。

说明linux限制了oracle 使用的最大内存是8G，所以修改该内核；
localhost:~ # vi /etc/sysctl.conf
# Disable response to broadcasts.
# You don't want yourself becoming a Smurf amplifier.
net.ipv4.icmp_echo_ignore_broadcasts = 1
# enable route verification on all interfaces
net.ipv4.conf.all.rp_filter = 1
# enable ipV6 forwarding
#net.ipv6.conf.all.forwarding = 1
# increase the number of possible inotify(7) watches
fs.inotify.max_user_watches = 65536
# avoid deleting secondary IPs on deleting the primary IP
net.ipv4.conf.default.promote_secondaries = 1
net.ipv4.conf.all.promote_secondaries = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 6291456
kernel.shmmax = 25769803776
kernel.shmmni = 4096

其中shmall=6291456*4096/1024/1024/1024=24G
shmmax=25769803776/1024/1024/1024=24G
使之生效
localhost:~ # sysctl -p
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.conf.all.rp_filter = 1
fs.inotify.max_user_watches = 65536
net.ipv4.conf.default.promote_secondaries = 1
net.ipv4.conf.all.promote_secondaries = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 6291456
kernel.shmmax = 25769803776
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
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304

Ok，说明已经修改成功

--oracle user and login in database with as sysdba
SQL> alter system set sga_max_size=16384M scope=spfile;

System altered.

SQL> alter system set sga_target=16384M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.7180E+10 bytes
Fixed Size 2114144 bytes
Variable Size 2147487136 bytes
Database Buffers 1.5016E+10 bytes
Redo Buffers 14659584 bytes
Database mounted.
Database opened.

OK ,调整成功