案例介紹:

在Linux平台測試時,切換數據庫到不同集羣(HPE Serviceguard for Linux (SGLX))的服務器時,遇到ORA-00821錯誤,數據庫啓動不了,報如下錯誤信息.

ORA-01078: failure in processing system parameters
ORA-00821: Specified value of sga_target 2704M is too small, needs to be at least 4752M

集羣HPE Serviceguard(曾用名：MC/ServiceGuard，簡稱 MC/SG)，這是惠普公司的高可用性 (HA) 集羣解決方案,我們用的是它的Linux版本,集羣有4台服務器, 此數據庫實例是從測試環境的備份還原過來,之前的sga_target值設置很小. 數據庫實例在切換過程中,前面兩台服務器正常啓動,但是第三台服務器就遇到了這個問題.

案例分析

其實這4台服務器的CPU的核心不一致, 出現問題的服務器的CPU核心數是96.

# cat /proc/cpuinfo |grep "siblings" | uniq
siblings        : 96

官方文檔介紹, sga_target的最小值受到下面因素影響:

Calculating the sga_target should consider the following:

The calculation for min cache size = max (number of CPUs, number of processor groups) * max (granule size, 4MB)

So the SGA size will mainly depend on CPU_COUNT.

也就是説,sga_target的最小值其實跟CPU_COUNT的值有關係.其實集羣的服務器A/B的CPU是32核, 而C/D這兩台服務器的CPU核數為96核. sga_target的最小值非固定值，而是由CPUs/number of processor groups/granule size共同決定. 而此處環境由於sga_target的值設置的非常小. 從而觸發了這個問題.

官方文檔"ORA-821 Specified Value Of Sga_target Is Too Small, Needs To Be At Least %sM KB82442"有具體分析,如下所示:

Applies To
All Users
Summary

Newly created database fails to startup with Error ORA-821:Specified value of sga_target is too small, needs to be at least %m.

These same initialization parameters are used for all other databases and start fine with the same sga_target size on different machines.


Solution

We should consider the following: -

1.The minimum buffer cache size min_granules is calculated

2. The calculation for min cache size =

max (number of CPUs, number of processor groups) * max (granule size, 4MB)
3.This used to cause issues when number of CPUs was very high and granule size was also high.

4.There is no way to change it other than lowering the number of CPUs (threads) that Oracle may see
at startup time.

5.Most of the other dynamically sized components will not be dependent on CPU_COUNT. 

To resolve the issue, we need to fine tune the cpu_count parameter

解決方案

調整sga_target和sga_max_size的值就可以解決這個問題, 有些環境(docker)還可以設置參數cpu_count來解決這個問題. 其實一般來説很少碰到這種情況,因為一般不會給sga_target設置一個很小的值, 而這個案例中,數據庫的備份來源於一個測試服務器,測試服務器資源給的非常少.數據庫還原過來後,參數文件也是使用原來的值,還沒有修改sga_target等參數,就去測試其它問題.恰巧就遇到了這個問題.

參考資料

ORA-821 Specified Value Of Sga_target Is Too Small, Needs To Be At Least %sM KB82442