案例环境
- 操作系统
Red Hat Enterprise Linux release 8.10 (Ootpa)
- 数据库版本:
19.24.0.0.0 Enterprise Edition
现象描述:
一个Oracle数据库突然收到大量的邮件告警,提示告警日志中出现大量的ORA-04031错误,部分信息如下所示:
2025-02-27T10:19:20.885697+08:00
Errors in file /******/diag/rdbms/unity/unity/trace/unity_ora_1983878.trc (incident=43372) (PDBNAME=CDB$ROOT):
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^34","kglseshtTable")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-02-27T10:19:20.938373+08:00
Errors in file /******/diag/rdbms/unity/unity/trace/unity_p00e_1983815.trc (incident=43307) (PDBNAME=CDB$ROOT):
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^80","kglseshtTable")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-02-27T10:19:20.949940+08:00
Errors in file /******/diag/rdbms/unity/unity/trace/unity_ora_1983916.trc (incident=43349) (PDBNAME=CDB$ROOT):
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^512","kglseshtTable")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-02-27T10:19:21.027461+08:00
Errors in file /******/diag/rdbms/unity/unity/trace/unity_mz00_1983918.trc (incident=43322) (PDBNAME=CDB$ROOT):
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^76","kglseshtTable")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-02-27T10:19:21.056947+08:00
Errors in file /******/diag/rdbms/unity/unity/trace/unity_p00f_1983817.trc (incident=43338) (PDBNAME=CDB$ROOT):
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^531","kglseshtTable")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-02-27T10:19:21.093570+08:00
Errors in file /******/diag/rdbms/unity/unity/trace/unity_mz00_1983918.trc:
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^76","kglseshtTable")
2025-02-27T10:19:21.100680+08:00
Process MZ00 died, see its trace file
检查SGA组件,发现有大量DEFAULT buffer cache收缩(SHRINK)和shared pool增长(GROW)的记录,进一步查询share pool内存空间信息发现, shared pool里面的”DB Replay sess info”和”free memory”两个子组件占用了最多内存,如下截图所示
在Oracle Support官网中查到相关资料ORA-4031 With High Allocation For “DB REPLAY SESS INFO” (Doc ID 3045900.1)[1] 从这篇文章分析来看,这个是一个Bug来着,如下所示:
APPLIES TO:
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Gen 2 Exadata Cloud at Customer - Version N/A and later
Oracle Database - Enterprise Edition - Version 19.24.0.0.0 and later
Oracle Cloud Infrastructure - Exadata Cloud Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
A 19.24DBRU database may crash after multiple ORA-4031 errors as:
ORA-04031: unable to allocate 232 bytes of shared memory ("shared pool","unknown object","KKSSP^1724","kgllk")
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select dummy from dual where...","KGLH0^eee30b3d","kglHeapInitialize:temp")
From AWR reports, the "DB Replay sess info" component was continuously increasing, from 250M to 2G throughout one day:
SGA breakdown difference by Pool and Name
Pool Name Begin MB End MB % Diff
shared DB Replay sess info 205.91
shared free memory 10,596.15 10,479.43 -1.10
SGA breakdown difference by Pool and Name
Pool Name Begin MB End MB % Diff
shared DB Replay sess info 408.71 515.66 26.17
shared free memory 10,265.03 10,154.18 -1.08
SGA breakdown difference by Pool and Name
Pool Name Begin MB End MB % Diff
shared DB Replay sess info 1,874.19 1,962.80 4.73
shared free memory 8,727.29 8,533.04 -2.23
Database Replay feature is not used in the database.
CHANGES
Upgrade to 19.24DBRU.
CAUSE
The errors were investigated in the unpublished Bug 36982817 – ORA-4031 DUE TO “DB REPLAY SESS INFO”.
In 19c database, we allocate memory for a structure that stores information about capture/replay during session login, which is not freed when the session is gone. When the instance has a lot of user logins, the total memory for “DB Replay sess info” will become large.
SOLUTION
- Download and apply Patch 36982817.
OR
- Download and apply 19.25DBRU or higher, where this fix in included.
There is no workaround for this issue.
这个Oracle数据库实例也是不久前升级到Oracle 19.24,我们升级了一大批数据库实例,但是目前似乎只有这一个实例遇到了这个问题。补丁一时半会儿不会安排,由于此数据库属于三级应用。可以 安排重启,在重启过后的似乎暂时未出现此类告警,这里先暂且记录一下这个案例。
参考资料
[1]
1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=329313434391912&id=3045900.1&_afrWindowMode=0&_adf.ctrl-state=jqvb4yr67_391
来源链接:https://www.cnblogs.com/kerrycode/p/18783953
如有侵犯您的版权,请及时联系3500663466#qq.com(#换@),我们将第一时间删除本站数据。
暂无评论内容