Oracle遭遇bug导致共享内存无法分配报ORA-04031错误

1.故障描述

     在7月17日上午11时左右，收到告警短信，提示集群节点2宕机，当即登陆该节点进行查看，发现数据库状态正常。但日志里出现大量的ORA-04031报错，提示无法分配shared_pool，当时手动执行shared pool刷新脚本进行刷新，刷新后shared pool使用率依旧为70%左右。

        此时有业务反馈数据库节点3无法连接，客户决定对节点3进行重启，重启后恢复正常，经过后续观察，节点2 ORA-04031报错也再没有出现。

2.原因分析

        经分析，故障原因为触发了 Bug 26405036  Large Allocation Of "ges enqueues" and "ges resource dynamic" In The Shared Pool ，导致数据库shared pool内存爆满所致。

详细BUG详情及补丁参考附件。

3.建议解决措施

1. 打补丁26405036: VERY HIGH "GES ENQUEUES" ON THE SHARED POOL
2.  workaround可以在出现问题时临时使用如下命令清理内存：

        SQL> oradebug setmypid

        SQL> oradebug lkdebug -m reconfig lkdebug

4.关于BUG说明

在MOS上搜索到相关的BUG现象：

        版本和本库匹配(本库版本Linux 11.2.0.4.181016，fix在19.1.0才包含),现象也匹配。

5.解决方法

Apply patch 26405036

可以通过打补丁解决此问题：

6.打补丁操作步骤

（1）环境确认

$ unzip -d <PATCH_TOP_DIR>  p26405036_12201181016DBOCT2018RU_Linux-x86-64.zip

$ cd <PATCH_TOP_DIR>/26405036

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

注：

For a RAC environment, shut down all the services (database, ASM, listeners, nodeapps, and CRS daemons) running from the Oracle home of the node you want to patch. After you patch this node, start the services on this node.Repeat this process for each of the other nodes of the Oracle RAC system. OPatch is used on only one node at a time.

对于RAC,打这个补丁需要停掉该节点上面的所有服务，然后同一时间只能在一个节点打补丁。

（2）补丁实施

To install the patch, follow these steps:

1. Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:

$ cd <PATCH_TOP_DIR>/26405036

$ opatch apply

2. Verify whether the patch has been successfully installed by running the following command:

$ opatch lsinventory

3. Start the services from the Oracle home.

7.风险预估

补丁回退：

1. Deinstall the patch by running the following command:

$ opatch rollback -id 26405036

2. Start the services from the Oracle home.

3. Ensure that you verify the Oracle Inventory and compare the output with the one run before the patch installation and re-apply any patches that were rolled back as part of this patch apply. To verify the inventory, run the following command:

$ opatch lsinventory

8.参考文档

Bug 26405036 - Large Allocation Of "ges enqueues" and "ges resource dynamic" In The Shared Pool (文档 ID 26405036.8)