数据库|数据库服务器升级内存需要考虑的问题 . 数据库|操作系统

项目背景：
原来的数据库服务器运行在HP DL388G7服务器上面，内存32G，由于业务增长，内存吃紧，加上时不时出现服务器硬件故障，由于是单实例单服务器，存在单点发现，于是打算采取一些措施改善一下：
1）升级服务器内存
2）并搭建服务器操作系统级别的双机
3）迁移数据库数据到新服务器

前面已经写过数据迁移相关的文章，题目为“EXP/IMP迁移数据”，链接如下：http://blog.csdn.NET/laven54/article/details/8877940
前面已经写过数据迁移相关的文章，题目为“SUSE Linux HA双机搭建”，链接如下：http://blog.csdn.Net/laven54/article/details/8878048

————————————————————————————————————————————

如果你的系统业务量加大，数据库服务器的压力加大，需要物理主机加内存，应该加多少，需要如何调整参数？本文教你一步一步做。

1 理论方法

需要调整的参数包括SGA, PGA, process，session数值，还有内核参数中的shmall，shmmax。

1.1 SGA和PGA的计算方法

SGA=物理内存总和*50%
PGA=物理内存总和*20%
剩下的30%留给操作系统使用。如果内存资源比较紧张，需要考虑成本的系统，如果数据库压力也并不大的话，其实可以将sga和pga的大小都设置得小一些，一点一点做调整。比如将物理内存的20%分配给SGA，将5%分配给PGA，然后根据实际情况做调整即可。

1.2 内核参数设置计算方法

解释一下内核中shmall，shmmax这俩参数的设置方法：
shmmax<=物理内存数（G）*1024*1024*1024（bytes）
【数据库|数据库服务器升级内存需要考虑的问题 .】shmall>=sga(G)*1024*1024*1024/page_size
这里推荐大家直接使用SGA和PGA的和来计算比较好。
page_size可以通过如下命令查询：
getconf PAGE_SIZE

shmmax<=物理内存数（G）*1024*1024*1024（bytes）
是指单个共享内存段的最大值，单位为bytes，就是俗称的B. 一般推荐为物理内存的一半，可以稍微大点，我喜欢设置为sga和pga的和的大小。
shmall=SGA（G）/page_size（bytes）=sga(G)*1024*1024*1024/page_size,比如sga大小为22G，page_size=4kb=4096bytes,那么shmall=22*1024*1024*1024bytes/4096bytes=5767168
shmall是指共享内存页面的总数目，共享内存你可以连接为SGA，因为对Oracle来说，PGA是不共享的，好吧就当我说的废话。
页面的大小一般情况下是4KB，单位是bytes，通过命令get page_size查到的数值一般都是4096bytes

总结：要注意单位，shmmax指的是内存值，有单位，单位是bytes，page_size的单位为bytes，shmall无单位，它只是一个数目，表示页面数量。

单位换算表：
1 byte (B) = 8 bits (b) 字节=8个二进制位
1 Kilobyte(K/KB)=2^10 bytes=1,024 bytes 千字节
1 Megabyte(M/MB)=2^20 bytes=1,048,576 bytes 兆字节
1 Gigabyte(G/GB)=2^30 bytes=1,073,741,824 bytes 千兆字节
1 Terabyte(T/TB)=2^40 bytes=1,099,511,627,776 bytes吉字节

2 操作实例
2.1收集数据库内存分配现状

# free -m
totalusedfreesharedbufferscached
Mem:3209629072302404922406
-/+ buffers/cache:661625480
Swap:3276584731918
su - oracle
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Mar 29 16:09:42 2013
Copyright (c) 1982, 2010, Oracle.All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter sga
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
lock_sgabooleanFALSE
pre_page_sgabooleanFALSE
sga_max_sizebig integer 8000M
sga_targetbig integer 8000M
SQL> show parameter pga
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_targetbig integer 5606M

SQL> show parameter processes
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
aq_tm_processesinteger0
db_writer_processesinteger8
gcs_server_processesinteger0
job_queue_processesinteger10
log_archive_max_processesinteger2
processesinteger900
SQL> show parameter sessions
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_sizeinteger0
java_soft_sessionspace_limitinteger0
license_max_sessionsinteger0
license_sessions_warninginteger0
logmnr_max_persistent_sessionsinteger1
sessionsinteger995
shared_server_sessionsinteger

数据库内存分配现状：
总内存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 systemcheckpoint;
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

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