Oracle 使用大記憶體出現:ORA-27102

Oracle 使用大記憶體出現:ORA-27102

問題現象：

發現其sga太小：

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 2G

sga_target                           big integer 2G

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora

SQL> create pfile='/u01/app/oracle/initorcl20100705.ora' from spfile;

Done

SQL> alter system set sga_max_size=14G scope=spfile;

System altered

SQL> alter system set sga_target=14G scope=spfile;

System altered

SQL> alter system set optimizer_index_cost_adj=40;

SQL> alter system set optimizer_dynamic_sampling=4;

SQL>

重啟資料庫報錯：

SQL> startup

ORA-27102: out of memory

Linux-x86_64 Error: 28: No space left on device

解決方法：

檢查作業系統引數：

[root@cwncdb ~]# 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

kernel.shmall = 268435456

kernel.shmmax = 17179869180

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 1048576

net.core.rmem_max = 1048576

net.core.wmem_default = 1048576

net.core.wmem_max = 262144

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

[root@cwncdb kernel]# more /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux

#

# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and

# sysctl.conf(5) for more details.

# Controls IP packet forwarding

net.ipv4.ip_forward = 0

# Controls source route verification

net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing

net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel

kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.

# Useful for debugging multi-threaded applications.

kernel.core_uses_pid = 1

#use for oracle

kernel.shmall = 268435456

kernel.shmmax = 17179869180

kernel.shmmni = 4096

# semaphores: semmsl, semmns, semopm, semmni

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=1048576

net.core.rmem_max=1048576

net.core.wmem_default=1048576

net.core.wmem_max=262144

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni =4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

[root@cwncdb kernel]# vi /etc/sysctl.conf 

vi /etc/sysctl.conf編輯將上面藍色字型部分註釋掉或刪除（因為這部分是多餘的導致設定kernel.shmmax = 17179869180 沒有生效 ，17179869180/1024/1024/1024=15.9G）

[root@cwncdb kernel]# 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

kernel.shmall = 268435456

kernel.shmmax = 17179869180

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 1048576

net.core.rmem_max = 1048576

net.core.wmem_default = 1048576

net.core.wmem_max = 262144

接著啟動可以正常啟動資料庫