ORA-27102: out of memory故障解決

lygle發表於2013-04-06
最近的UAT資料庫遷移，由於是多個DB需要遷移到同一臺機器，一部分完成後，啟動後續資料庫碰到了ORA-27102錯誤，提示記憶體超出，檢視系統可用記憶體，遠大於需要啟動資料庫的sga和pga，究竟是怎麼一回事呢？如果你也碰到類似錯誤，不妨往下看。
1、故障現象
oracle@v2013db02u:~> cat /etc/issue
Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64) - Kernel \r (\l).
oracle@v2013db02u:~> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 30 09:46:57 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup pfile=/u02/database/MHUAT02/initMHUAT02.ora
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
SQL> ho oerr ora 27102
27102, 00000, "out of memory"
// *Cause: Out of memory
// *Action: Consult the trace file for details
2、分析與解決
--首先檢視了系統實際可用記憶體
--系統實際可用記憶體還剩50多個GB呢
SQL> exit
Disconnected
oracle@v2013db02u:/u02/database> free -m
total used free shared buffers cached
Mem: 64435 5968 58467 0 3 4045
-/+ buffers/cache: 1919 62516
Swap: 32773 1232 31540
--MetaLink上給出了關於這個問題的描述 [ID 301830.1]
--是由於kernel.shmall值設定太小引起的
kernel.shmall
該引數用於配置系統一次能夠使用的最大的共享頁面數，該引數的值總是ceil(shmmax/PAGE_SIZE)
Oracle 9i,10g的x86以及x86-64平臺推薦以及預設的大小通常為2097152
在大多數情況下，該引數是夠用的。按照上述計算方式頁面記憶體總大小可以達到8GB(2097152*4096 bytes (shmall*PAGE_SIZE))
通常情況下，PAGE_SIZE 的大小為4096byte，除非使用了Big Pages 或 Huge Pages
對於超出8GB系統記憶體，如共享記憶體段(shmmax)的最大大小是16G，則所需要共享記憶體頁數(shmall)為16GB/4KB=16777216KB/4KB=4194304(頁)
32GB，64GB可以依上述方式類推。
--下面檢視本機的設定
v2013db02u:~ # getconf PAGE_SIZE
4096
v2013db02u:~ # /sbin/sysctl -a | grep shm
vm.hugetlb_shm_group = 0
kernel.shmmni = 4096 --定義共享記憶體段的最大數量，也就是可以開闢多少個共享記憶體段，一般夠用
kernel.shmall = 2097152 --由於當前server有多個db執行，應該是總頁數超出所致
kernel.shmmax = 4294967296 --定義單個共享記憶體段的最大值，由於當前server沒有sga大於4gb的，可以保留原值
--前面我們在使用free時，系統使用記憶體已經超出了4gb，也表明shmall的超出了最大的共享頁面數
v2013db02u:~ # vi /etc/sysctl.conf --修改kernel.shmall的值，將其改為16gb能容納的頁面數，使用root身份
v2013db02u:~ # sysctl -p --使修改立即生效，無需重啟server
v2013db02u:~ # /sbin/sysctl -a | grep shm
vm.hugetlb_shm_group = 0
kernel.shmmni = 4096
kernel.shmall = 4194304
kernel.shmmax = 4294967296
--再次啟動db正常
oracle@v2013db02u:~> export ORACLE_SID=MHUAT02
oracle@v2013db02u:~> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 30 10:29:18 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup pfile=/u02/database/MHUAT02/initMHUAT02.ora
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2074568 bytes
Variable Size 167774264 bytes
Database Buffers 423624704 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
3、[ID 301830.1]
Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device [ID 301830.1] To Bottom
--------------------------------------------------------------------------------
Modified:Oct 4, 2012Type:PROBLEMStatus:PUBLISHEDPriority:3 Comments (0)
In this Document
Symptoms
Ask Questions, Get Help, And Share Your Experiences With This Article
Changes
Cause
Solution
References
--------------------------------------------------------------------------------
Applies to:
Oracle Server - Standard Edition - Version 9.2.0.1 and later
Oracle Server - Enterprise Edition - Version 9.2.0.1 and later
Linux x86-64
Symptoms
When trying to increase the SGA to approach half available RAM with an Oracle 64-bit version on a Linux 64-bit operating system, even though shmmax is set to match half the amount of RAM, you get the following error when trying to start the instance:
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
4096
For 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
4194304
NOTE:
The above command loads the new value and a reboot is not necessary.
Switch back to being the oracle user and retry the startup command.
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 "4194304" > /proc/sys/kernel/shmall
Using HUGEPAGES does not alter the calculation for configuring shmall.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-757826/，如需轉載，請註明出處，否則將追究法律責任。

相關文章