一． 故障 现 象 简述
1. 应用 短信报 **** 数据库 实例 无 法 连 接，重 启 ***** 数据库 实例后 恢复正常。
二． 故障原 因分析
1. 数据 库 告警 日志 2023.11.8 记录 如下异常 ：
|
Tue Nov 8 00:00:19 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:00:55 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:01:29 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:02:00 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:02:33 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:03:05 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:03:36 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:04:10 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:04:41 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:07:14 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:07:44 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:08:15 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:08:47 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:09:17 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:09:48 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:10:19 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:10:50 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:11:22 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:11:52 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:12:23 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:12:55 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:13:25 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:13:57 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:14:27 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:14:58 2023 WARNING: ran out of shared pool for GES enqueue object. Tue Nov 8 00:15:03 2023 Trace dumping is performing id=[cdmp_20231108001503] Tue Nov 8 00:15:06 2023 |
2. 以 上信息表明2023 .11.8 00:00 SHARED POOL 已 耗尽 ， 没有 空间 处理 GES 。
|
Tue Nov 8 00:15:07 2023 Error occured while spawning process P003; error = 4031 Tue Nov 8 00:15:07 2023 ORA-04031: unable to allocate 3832 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","osp allocation") |
3. 告警 日志记录 4031 错误 ，这个 是因为 SHARE POOL 没有 足够 空间 处理 新增 SQL 语句 。
|
Tue Nov 8 00:15:08 2023 Errors in file /oracle/ ***** .trc: ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select 1 from aq$_schedules","sga heap(1,0)","kglsim heap") Tue Nov 8 00:15:08 2023 Error occured while spawning process q003; error = 4031 Tue Nov 8 00:15:09 2023 Errors in file /oracle/ ***** .trc: ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select count(*) from sys.aq_...","sga heap(2,0)","kglsim heap") |
4. 告警 日志 再 次记录 4031 错误 ， 提示 SHARE POOL 已 没有足够空间处理 新增 SQL 语句 。
5. 综合 分析 以上 报错信息，可知 因 为 SHARE POOL 内 存不足导致异常 ，因 为 SHARE POOL 是 SGA 组 成部分， 也 就意味着 SGA 空间不 足或是分 配 不合理， 经 过数据 库 SGA 检查 ， SGA 总 大 为 2 G ，确认 原因是 SGA 空间 不足导致此故障 发生 ，建议增加 SGA 内存 空间。
三． 故障处理方案
1. 服务 器总内 存 量为 128G ， 共运行 14 套 数据库 其使用 SGA 总 和 为 68G ，大 页 内 存 总大小为 74G ，大 页内存剩余可用为 6G ，系统总 体 内存使用率少 于 85% ，系统可 用 内存数 为 20-30G 之间 ， GGVV 数据 库当前SGA 大 小为 2G ， 综合以上 因素建 议 统一 增 大 GGVV 数据 库 两 节点SG A 为 4 G ，数据 库当前 SHARED_POOL_SIZE 参数 为 1G ，建议 将此参数值增大到1.5G ，此 更改不会影 响数据 库 服务 器 稳定 运行。