服务器CPU焦点数多导致ORA-00821错误案例

案例先容:

在Linux平台测试时,切换数据库到差别集群(HPE Serviceguard for Linux (SGLX))的服务器时,碰到ORA-00821错误,数据库启动不了,报如下错误信息.

1. ORA-01078: failure in processing system parameters
2. 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.

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

复制代码

官方文档先容, sga_target的最小值受到下面因素影响:

1. Calculating the sga_target should consider the following:
3. The calculation for min cache size = max (number of CPUs, number of processor groups) * max (granule size, 4MB)
5. 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"有具体分析,如下所示:

1. Applies To
2. All Users
3. Summary
5. Newly created database fails to startup with Error ORA-821:Specified value of sga_target is too small, needs to be at least %m.
7. These same initialization parameters are used for all other databases and start fine with the same sga_target size on different machines.
10. Solution
12. We should consider the following: -
14. 1.The minimum buffer cache size min_granules is calculated
16. 2. The calculation for min cache size =
18. max (number of CPUs, number of processor groups) * max (granule size, 4MB)
19. 3.This used to cause issues when number of CPUs was very high and granule size was also high.
21. 4.There is no way to change it other than lowering the number of CPUs (threads) that Oracle may see
22. at startup time.
24. 5.Most of the other dynamically sized components will not be dependent on CPU_COUNT.
26. 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
扫描上面二维码关注我如果你至心以为文章写得不错，而且对你有所资助，那就不妨帮助“保举"一下，您的“保举”和”打赏“将是我最大的写作动力！本文版权归作者全部，欢迎转载，但未经作者同意必须生存此段声明，且在文章页面显着位置给出原文毗连.
免责声明：如果侵犯了您的权益，请联系站长及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金.