**最近接到客户需求需要优化HED服务器上数据库GASTST和GASHADR的SGA和PGA的大小，
需求如下：**

SERVER :HED    
DB: GASTST     (SGA: 2.5G) 
DB: GASHADR (SGA: 3G + PGA: 737M) 

需求很简单，具体操作也很简单，改完之后需要重新启动数据库才可以生效：

Schedule Plan ：

DB: GASTST (SGA: 2.5G)

export ORACLE_SID=gastst
sqlplus / as sysdba
alter system set sga_max_size=2.5G scope=spfile;
alter system set sga_target=2.5G scope=spfile;
shutdown immediate;
startup;
show sga;

DB: GASHADR (SGA: 3G + PGA: 737M)

export ORACLE_SID=gashadr
sqlplus / as sysdba
alter system set sga_max_size=3G scope=spfile;
alter system set sga_target=3G scope=spfile;
alter system set workarea_size_policy=auto scope=both; 
alter system set pga_aggregate_target=737M scope=both; 
 shutdown immediate;
startup;
show sga;

但是实操之后发现数据库启动不了了；

SQL >startup
ORA-27102: out of memory
SVR4 Error: 22 : Invalid argument 

首先检查了下是不是因为本地内存不足引起的：

prtconf -vp | grep Mem 
Memory size: 28672 Megabytes

本机上面其他数据库所有SGA使用的总和约为 14G ，内存充足；
后来有找了几个有经验的老司机帮忙看了一下，也没有找到具体的原因；

后来采用应急措施把数据库先起起来；
因为此时spfile已经被修改且启动不了数据库，所以借助之前的备份重新create pfile ，通过pfile来启动数据库；(在备份所在路径下执行下面的命令其中 pfilegasmrinf.ora.bkp' 为之前spfile的备份)

cd /u01/app/oracle/product/11.2/dbs
create pfile='initgastst.ora' from spfile='spfilegastst.ora.bkp';
startup pfile='initgastst.ora' 

此时数据库总算起来了，但是SGA和PGA的change只能backout了。

**后来给Oracle Support 团队开了一张 S1 的 SR 请求帮忙查找失败的原因;
下面是Oracle Team给出的分析结果和解决方案：**

Goal：

This note lists most of the causes for hitting ORA-27102: out of memory  during startup and explains how to resolve them.

Solution：

A-Check Physical RAM is larger than the sum of sga_max_size / sga_target for all running instances.

 Physical RAM should be larger than the sum of all the instances sga running on the server. Else, either the server RAM has to be increased or the sga sizes should be reduced to resolve ORA-27102 errors. 
 

首先检查物理内存是否够用，物理RAM的值必须大于所有实例SGA之和，前面已经拍查过故此种可能排除掉；

 prtconf -vp | grep Mem 
Memory size: 28672 Megabytes

B- Check SHMMAX Kernel/project.max-shm-memory (On Solaris) or kernel.shmmax(On linux) is larger than the sum of sga_max_size / sga_target for all running instances B- Check SHMMAX Kernel/project.max-shm-memory (On Solaris) or kernel.shmmax(On linux) is larger than the sum of sga_max_size / sga_target for all running instances

These kernel parameters must be larger than the sum of all instances sga. 

**检查Kernel 参数project.max-shm-memory (On Solaris)或者kernel.shmmax(On linux)的值要大于所有实例Sum (sga_max_size / sga_target)的值：
自查结果如下:**

>id -p oracle
>uid=204(oracle) gid=205(dba) projid=100(group.dba)
>prctl -i project  group.dba
project: 100: group.dba
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
    privileged      15.6.0GB      -   deny                                 -
    system          16.0EB    max   deny                                 -

可以看到系统参数project.max-shm-memory 目前的数值设置为15.6G，而查询其他数据库使用的Sum (sga_max_size / sga_target)约为14G，所以可用于分配的内存实际剩余约1.6G，小于我们的需求5.5G(2.5G+3G)；所以这可能就是引起数据库无法启动的原因，但可能并不是唯一原因；
根据需要我们将参数值设置为23G(不能大于28672M)

>id -p oracle
>uid=204(oracle) gid=205(dba) projid=100(group.dba)
>projmod -s -K    "project.max-shm-memory=(privileged,23G,deny)" group.dba
>prctl -i project  group.dba
project: 100: group.dba
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
    privileged      23.0GB      -   deny                                 -
    system          16.0EB    max   deny   

具体语法参考

**PS：project是Solaris10新增加的特性，可以通过设置project参数为一个用户或一组用户设置参数值。设置后可立即生效。
'但是,root用户的结果只受/etc/system里参数的影响，而不受project user.oracle影响，root用户不属于此project. '**

如果Oracle用户还没有project可以用下面的命令新建一个，因为本服务器已经存在project所以选择 projmod

# projadd -U oracle -K   "project.max-shm-memory=(priv,4096MB,deny)" user.oracle

projadd & projmod

C- Chcek Ulimit settings are unlimited for the memory related parameters

Check that the ulimits are set as per Oracle recommendations is Doc ID 169706.1

检查操作系统和数据库版本

> showrev
Hostname: hed
Hostid: ssssss
Release: 5.10
Kernel architecture: sun4u
Application architecture: sparc
Hardware provider: Oracle Corporation
Domain:
Kernel version: SunOS 5.10 Generic_150400-62
 > isainfo -b -v
    64-bit sparcv9 applications
    ima fmaf vis2 vis popc

检查Oracle 版本

SQL> select * from v$version ;
 BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

D- Set the Swap size and the kernel parameters

The guidelines in Doc ID 169706.1need to b e followed for setting swap and kernel parameters, else Error: 12: Cannot allocate memory might be encountered.

E- If oracle version is 32bit, the maximum SGA_TARGET / SGA_MAX_SIZE is 1.75gb

If Oracle software version is 32bit:
unix> file $ORACLE_HOME/bin/oracle

If the software version is 32bit, literal string like 'ELF 32-bit' is returned in the response
If the software version is 64bit, literal string like 'ELF 64-bit' is returned in the response

Possible workaround:

• Linux only - review Doc ID 260152.1"Linux Big SGA, Large Memory, VLM - White Paper"
• Windows - reviewDoc ID 225349.1"Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms"

• Solaris: review Doc ID 1028623.6"SOLARIS: How to Relocate the SGA"
  检查Oracle 版本

  SQL> select * from v$version ;

  BANNER

  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  PL/SQL Release 11.2.0.4.0 - Production
  CORE 11.2.0.4.0 Production
  TNS for Solaris: Version 11.2.0.4.0 - Production
  NLSRTL Version 11.2.0.4.0 - Production

明显的这台服务器数据库为 64bit 11g ，没有 32bit, the maximum SGA_TARGET / SGA_MAX_SIZE is 1.75g 的限制；

F- Automatic Memory Management is enabled with the parameter LOCK_SGA :

When attempting to lock the SGA into memory and although there is enough memory is available on the server, one receives the following error when trying to startup the database :

SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
SQL> SHUT
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory

检查LOCK_SGA参数的值是不是TRUE，如果是的话SGA将被锁定无法修改；

SQL> show parameter lock_sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
SQL>

所以这种情况也可以排除

当然如果出现这种情况Oracle也给出了解决方案如下：

Cause : Automatic Memory Management MEMORY_TARGET and MEMORY_MAX_TARGET cannot be used when LOCK_SGA is enabled or with huge pages on Linux.

Solution:

A. Disable the Use of Automatic Memory Management , Set the MEMORY_TARGET initialization parameter to 0.

SQL> alter system set MEMORY_TARGET=0 scope=spfile;
 Then restart the instance

OR

B. Do Not lock the SGA.

G-Small default project setting, project.max-shm-memory than the SGA parameter specified:

**project是Solaris10新增加的特性，可以通过设置project参数为一个用户或一组用户设置参数值。设置后可立即生效。
'但是,root用户的结果只受/etc/system里参数的影响，而不受project user.oracle影响，root用户不属于此project. '**

>id -p oracle
>uid=204(oracle) gid=205(dba) projid=100(group.dba)

可以看到Oracle 用户隶属于group.dba，不属于系统project 也不适用于此情况；

下面是系统用户设置不当时候的解决方案：

system project setting:

# prctl -i project system
project: 0: system
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
 project.max-shm-memory
privileged     6.00GB     -         deny     -    >>>>>>>>>
system         16.0EB     max     deny     -

While checking the Default project setting:

#prctl -n project.max-shm-memory -i process $$
process: 16703: -bash
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
            privileged     1.95GB     -         deny

System project setting of porject.max-shm-memory is 6GB. But in default project setting, project.max-shm-memory is only 1.95GB, and oracle user without its own project setting will inherit this default setting, which is not large enough for 3.7GB SGA.

Solution:

1. log in as root

2. projmod -sK "project.max-shm-memory=(privileged,6G,deny)" default
   After the changes, login 'oracle' and use the prctl utility to check:

   prctl -n project.max-shm-memory -i process $$
   process: 7511: -bash
   NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
   project.max-shm-memory

        privileged     6.00GB     -         deny

3. start instance using sqlplus, now it should work.
   Note. If you have problem start instance via srvctl but sqlplus can start instance on Solaris 10, please refer Note 603051.1.
   Note. If a project is assigned to oracle. Can also try this command:
   $ prctl -n project.max-shm-memory -r -v <size> -i project <project number>
   eg, prctl -n project.max-shm-memory -r -v 6G -i project 0

H-Small zone.max-locked-memory at zone level

Solution: Set a larger value

# prctl -i zone -n zone.max-locked-memory -r -v 50g <zone_name>

I-Actions while having the below OS errors:

可能出现的其他一些OS 层面的问题

1-Solaris OS error SVR4 Error: 22: Invalid argument

A-On Solaris 10 when the SGA is large (greater than 4G)

When trying to shutdown and startup the DB in the same session receive the below errors:
SQL*Plus: Release - Production on Sat Apr 26 23:59:14 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> Connected.
SQL> ORACLE instance shut down.
SQL> ORA-27102: out of memory
SVR4 Error: 22: Invalid argument
SQL> ORA-01012: not logged on
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 -
64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Warning on startup from alert.log

WARNING: EINVAL creating segment of size 0x0000000100004000 (transalates to 4GB)
fix shm parameters in /etc/system or equivalent
Solution:
This is not an Oracle issue , it has to do with how Sun frees memory after a DB shutdown.
A couple of workarounds exists:
1- Don't do a shutdown and startup in same session.
2- Sleep 5 minutes between the shutdown abort and startup
(this doesn't always work)
3-
Disabling DISM seems to avoid the problem in the first place so this might be somehow related to CR
6603296 - "Multiple writes into dism segment reduces available swap"
set as duplicate of Sun bug
CR 6559612 - "multiple softlocks on a DISM segment should decrement availrmem just once" which then makes a pointer to CR 6423097 -"segvn_pagelock() may perform very poorly" of which the workaround is to disable large page support.

Workaround for S10U4 and above is to add to /etc/system:

set max_uheap_lpsize = 0x2000
set max_ustack_lpsize = 0x2000
set max_privmap_lpsize = 0x2000
set max_shm_lpsize = 0x2000
set use_brk_lpg = 0
set use_stk_lpg = 0

and reboot.

for S10U3 and earlier S10 releases, the workaround is to add to /etc/system:

set exec_lpg_disable = 1
set use_brk_lpg = 0
set use_stk_lpg = 0
set use_zmap_lpg = 0

and reboot

Essentially the /etc/system settings disable Large Page Out Of Box (LPOOB) support which was enabled by default with Solaris 10. The only downside that I could possibly see would be some performance
degradation, but how much depends on the application and workload so we couldn't give you any specific metrics on that as it's unfortunately different in every customer application environment. You might find the
following documents interesting reading for the background around large page out of box (LPOOB) support:

Solution 214369 : Taking advantage of Multiple Page Size Support
(MPSS) in Solaris[TM] 9 and later
Solution 207232 : Large-Page Out of Box Problems with some Solaris
[TM] 10 Operating System (OS) Applications
Solution 215536 : Oracle 10g on Solaris[TM] 10 may run slow

B-Issue due to instance has not released shared memory and semaphores with an earlier database shutdown.

Below error is encountered while starting up a database on Solaris 10 while Total physical memory of the server is greater than the sum of the SGA of all instances and 'project.max-shm-memory' parameter is set for all the OS users owning an instance and this value is greater than the SGA of the corresponding instance.

   SQL > startup
    ORA-27102: out of memory
   SVR4 Error: 22: Invalid argument
   

Below warning message shown in alert log file.
WARNING: The system does not seem to be configured
optimally. Creating a segment of size 0x000000019a000000
failed. Please change the shm parameters so that
a segment can be created for this size. While this is
not a fatal issue, creating one segment may improve performance.

Solution:

Determine which instance owns which shared memory segment and which semaphore

SQL> oradebug setmypid
SQL> oradebug ipc

The information is written to a trace file that can be found in USER_DUMP_DEST.
The shared memory segment id can be found by looking in the trace file for Shmid.
The semaphore id can be found by looking in the trace file for Semaphore List=

Then remove the shared memory segment(s) and semaphore(s) of the problematic instance by using following commands:

% ipcrm -m <shared memory id>

% ipcrm -s <semaphore id>

C-Issue related to Insufficient value of the 'project.max-shm-memory' parameter.:

The following error is reported while starting up the database on Solaris 10:

SQL> startup
ORA-27102: out of memory

SVR4 Error: 22: Invalid argument

Total physical memory installed on the server is greater than sum of all the SGAs.

'project.max-shm-memory' parameter is being used to control the shared memory size.

Solution:

> Set the value of project.max-shm-memory greater than sum of all the SGAs required to run on this server.

projmod -s -K "project.max-shm-memory=(privileged,32.00GB,deny)" user.oracle

Start the database.

2-Solaris OS error SVR4 Error: 28: No space left on device

A- Issue due to Limit of maximum number of allowed shared memory identifiers is reached:

Starting database fails on Solaris 10 with:
SQL> startup
ORA-27102: out of memory
SVR4 Error: 28: No space left on device

project.max-shm-memory is enough for all SGAs.

Truss of the startup show the below:
12491: shmget(IPC_PRIVATE, 4194304, 0660) Err#28 ENOSPC
12491: shmget(IPC_PRIVATE, 8388608, 0660) Err#28 ENOSPC
12491: shmget(IPC_PRIVATE, 16777216, 0660) Err#28 ENOSPC
12491: shmget(IPC_PRIVATE, 33554432, 0660) Err#28 ENOSPC

Solution:

    log on as 'root' user
    change the parameter using the projmod command:
    projmod -s -K "project.max-shm-ids=(privileged,1024,deny)" 'group.oinstall'
    connect as the 'oracle' user and run next command to check if parameter has changed:
    prctl -n project.max-shm-ids -i process $$
    retry startup

3-Solaris error SVR4 Error: 11: Resource temporarily unavailable

A-Issue related to the usage of the OS feature Optimized Shared Memory (OSM).

Optimized Shared Memory (OSM) is a feature in Solaris OS that allows some application flexibility for memory usage/allocation. There is also ISM, and
DISM, OSM. OSM allows some of the same flexibility of DISM with some improvements.

SQL> startup

ORA-27102: out of memory
SVR4 Error: 11: Resource temporarily unavailable

Truss output for and failed startup shows the error on the memcntl() function:
8121: shmget_osm(IPC_PRIVATE, 3204448256, 0640|IPC_CREAT|IPC_EXCL, 0x00400000) = 2030043232
28121: shmat(2030043232, 0x3C0000000, 0) = 0x3C0000000
28121: write(16, " s s k g m _ a c t i o n".., 72) = 72
28121: write(16, "\n", 1) = 1
28121: write(16, " s s k g m l o s m : l".., 69) = 69
28121: write(16, "\n", 1) = 1
28121: memcntl(0x3C0000000, 3204448256, 8, 0, 0, 0) Err#11 EAGAIN <<================
28121: write(16, "\n * 2 0 1 5 - 0 2".., 29) = 29
28121: write(16, " s s k g m l o s m : m".., 60) = 60

Solution:

1. Upgrade the OS to at least Oracle Solaris 11.1.19.6.0 release (or greater).

OR

2 A workaround that has proven successful is to set the following parameter in the init/spfile and restart the database

_use_osm=FALSE

4-Solaris error SVR4 Error: 12: Not enough space

A-sga_max_size or memory_target exceeded "process.max-address-space" and "project.max-shm-memory".

Issue occurred when creating a DB using DBCA , starting up the instance generate the below error:
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1
Additional information: 285212724

Solution:

Ensure memory_target (or max_sga_size) does not exceed "process.max-address-space" and "project.max-shm-memory".

Alternately you may change file /etc/project on each node, change process.max-address-space to a bigger value than memory_target or sga_max_size, for example to set process.max-address-space to 4GB add:
process.max-address-space=(priv,4096000000,deny)

NOTE:
Next to the setting for project.max-shm-memory, a wrong setting for zone.max-shm-memory can also cause these errors. To verify the settings:
$ id -p
$prctl -i project

The project.max-shm-memory and zone.max-shm-memory are limits on the total size of all shared memory segments [within a project or zone]

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

A-Insufficient value for SHMALL kernel parameter

Hugespages is set as per recommended by hugepages_settings.sh in Doc ID 401749.1, also Kernel parameters "memlock" and "shmmax" are set properly.

Database failed to start due to following error after enabling hugepages :
SQL> startup nomount
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

OR
ORA-27102: out of memory Linux-x86_64 Error: 12: Cannot allocate memory

Command "free" shows enough free space, also SWAP is configured with enough size and not full.

The Database can be started after setting USE_LARGE_PAGES=false with same SGA size (this case - 25g), however The Database can't be started with hugepages

Strace for the Instance startup shows error ENOENT on Function shmget ,which indicates that the SHMALL is not set enough.

$strace -f -o /tmp/strace.txt sqlplus /nolog
SQL> startup nomount

strace.txt

17826 open("/proc/meminfo", O_RDONLY) = 10
17826 fstat(10, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
17826 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b1fad62d000
17826 read(10, "MemTotal: 74177420 kB\nMemFre"..., 4096) = 777
17826 read(10, "", 4096) = 0
17826 close(10) = 0
17826 munmap(0x2b1fad62d000, 4096) = 0
17826 shmget(0x8c0d3b13, 0, 0) = -1 ENOENT (No such file or directory) <<<shmget call is failed>>>

Solution:
Increase the value of the kernel parameter "SHMALL" and start database with hugepages

$cat /proc/sys/kernel/shmall
Modifying /etc/sysctl.conf is a permanent workaround (applies at boot time).
If for some reason you do not want to change the system wide configuration, you can do it on the fly by directly changing the kernel pseudo FS AKA procfs.
e.g. echo "104857600" > /proc/sys/kernel/shmall

B-The OS kernel SEMMNI value is too low for the size of the DB PROCESSES parameter in the pfile/spfile.

High setting of PROCESSES, e.g. 5000, and receiving a ORA-27102 like below even though there is plenty of memory available on the server.
ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

SHMMAX and SHMALL OS parameters were at the proper levels.
Solution:
SEMMNI should be increased to accommodate more semaphores until the error stop.

The SEMMNI kernel parameter controls the number of semaphores per array. If set too low the additional semaphores needed cannot be created and a error will occur.
There is also a direct correlation to the DB init.ora parameter PROCESSES. With a higher PROCESSES number there needs to be a higher SEMMNI value at the OS level to compensate.

OR

     Decrease the value of the PROCESSES parameter.
     C-/etc/security/limits.conf shows small memlock 

     Solution:
      increase memlock value of limits.conf and reboot OS

对以上Oracle Support 提供的文档详尽分析会发现，B种情况描述的问题和系统出现的问题最为相近；

B- Check SHMMAX Kernel/project.max-shm-memory (On Solaris) or kernel.shmmax(On linux) is larger than the sum of sga_max_size / sga_target for all running instances B- Check SHMMAX Kernel/project.max-shm-memory (On Solaris) or kernel.shmmax(On linux) is larger than the sum of sga_max_size / sga_target for all running instances

These kernel parameters must be larger than the sum of all instances sga. 

按照Solution步骤修改：

>id -p oracle
>uid=204(oracle) gid=205(dba) projid=100(group.dba)
>prctl -i project  group.dba
project: 100: group.dba
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
    privileged      15.6.0GB      -   deny                                 -
    system          16.0EB    max   deny      
>projmod -s -K    "project.max-shm-memory=(privileged,23G,deny)" group.dba
>prctl -i project  group.dba
project: 100: group.dba
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
    privileged      23.0GB      -   deny                                 -
    system          16.0EB    max   deny   

Note:

 When you use the prctl command (Resource Control) to change system parameters, you do not have to restart the system for these parameter changes to take effect. However, the changed parameters do not persist after a system restart.

No reboot is needed, the user will get the new valueat their next login.

DB: GASTST (SGA: 2.5G)

export ORACLE_SID=gastst
sqlplus / as sysdba
alter system set sga_max_size=2.5G scope=spfile;
alter system set sga_target=2.5G scope=spfile;
shutdown immediate;
startup;
show sga;

DB: GASHADR (SGA: 3G + PGA: 737M)

export ORACLE_SID=gashadr
sqlplus / as sysdba
alter system set sga_max_size=3G scope=spfile;
alter system set sga_target=3G scope=spfile;
alter system set workarea_size_policy=auto scope=both; 
alter system set pga_aggregate_target=737M scope=both; 
 shutdown immediate;
startup;
show sga;

改完project.max-shm-memory参数之后，再执行上面命令修改SGA和PGA的操作，数据库成功restart；

【原创声明】版权所有非经授权请勿做任何商业用途，仅限技术交流； 转载请注明出处,谢谢；