oracle 数据库服务器 内存,Oracle数据库服务器升级内存需要考虑的问题

数据库内存分配现状：总内存32G

SGA：8G

PGA：5.6G

计划调整之后的内存分配数值情况：总内存32G

SGA：16G

PGA：5.6G

2.2 调整操作步骤

备份spfile文件：

cd /eb_db/Oracle/product/10.2/db/dbs/

cp spfileebai.ora spfileebai.ora.bak20130330

备份内核参数文件：

cp /etc/sysctl.conf /etc/sysctl.conf.bak20130330

修改内核文件：

vi /etc/sysctl.conf

kernel.shmall = 5767168

解释一下内核中这俩参数的设置规定：

shmmax<=物理内存数(G)*1024*1024*1024(bytes)

shmall>=sga(G)*1024*1024*1024/page_size

这里推荐大家直接使用SGA和PGA的和来计算比较好。

page_size可以通过如下命令查询：

getconf PAGE_SIZE

更加具体的shmall和shmmax的设置细节标准请参看后边的内容.

sqlplus / as sysdba

create pfile from spfile;

alter system set sga_target=16384m scope=spfile;

alter system set sga_max_size=16384m scope=spfile;

alter system set processes=1600 scope=spfile;

alter system set sessions=1765 scope=spfile;

alter system checkpoint;

shutdown immediate;

startup;

show parameter processes

show parameter sessions

3 shmall和shmmax的设置方法

如何设置shmall：

SQL> startup nomount

ORA-27102: out of memory

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

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.

Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Install/Upgrade.

Changes

shmall is too small, most likely is set to the default setting of 2097152

$ cat /proc/sys/kernel/shmall

2097152

Cause

shmall is the total amount of shared memory, in pages, that the system can use at one time.

Solution

Set shmall equal to the sum of all the SGAs on the system, divided by the page size.

The page size can be determined using the following command:

$ getconf PAGE_SIZE

4096For example, if the sum of all the SGAs on the system is 16Gb and the result of '$ getconf PAGE_SIZE' is 4096 (4Kb) then set shmall to 4194304 pages

As the root user set the shmall to 4194304 in the /etc/sysctl.conf file:

kernel.shmall = 4194304

then run the following command:

$ sysctl -p

$ cat /proc/sys/kernel/shmall

4194304NOTE:

The above command loads the new value and a reboot is not necessary.

如何设置shmmax：

Goal

QUESTION 1

===========

What is the maximum value of SHMMAX for a 32-bit (x86) Linux system?

QUESTION 2

===========

What is the maximum value of SHMMAX for a 64-bit (x86-64) Linux system?

Fix

ANSWER 1

============

Oracle Global Customer Support officially recommends a " maximum" for SHMMAX of just less than 4Gb, or 4294967295.

The maximum size of a shared memory segment is limited by the size of the available user address space. On 32-bit systems, this is a theoretical 4GB. The maximum possible value for SHMMAX is just less than 4Gb, or 4294967295. Setting SHMMAX to 4GB exactly will give you 0 bytes as max, as this value is interpreted as a 32-bit number and it wraps around.

ANSWER 2

===========

Oracle Global Customer Support officially recommends a " maximum" for SHMMAX of "1/2 of physical RAM".

The maximum size of a shared memory segment is limited by the size of the available user address space. On 64-bit systems, this is a theoretical 2^64bytes. So the "theoretical limit" for SHMMAX is the amount of physical RAM that you have. However, to actually attempt to use such a value could potentially lead to a situation where no system memory is available for anything else. Therefore a more realistic "physical limit" for SHMMAX would probably be "physical RAM - 2Gb".

In an Oracle RDBMS application, this "physical limit" still leaves inadequate system memory for other necessary functions. Therefore, the common "Oracle maximum" for SHMMAX that you will often see is "1/2 of physical RAM". Many Oracle customers chose a higher fraction, at their discretion.

Occasionally, Customers may erroneously think that that setting the SHMMAX as recommended in this NOTE limits the total SGA. That is not true. Setting the SHMMAX as recommended only causes a few more "shared memory segments" to be used for whatever total SGA that you subsequently configure in Oracle.For additional detail, please see

Document 15566.1, "SGA, SHMMAX, Semaphores and Shared Memory Explained"

Also to be taken into consideration for memory configuration is the kernel parameter for kernel.shmall which is the total amount of shared memory, in pages, that the system can use at one time. Review:

Document 301830.1 Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device

常见错误：

ORA-27102: out of memory

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

这个情况，一般都是由于设置内核参数错误导致。