Oracle 19c告警日志出现大量ORA-04031案例

案例环境

• 操作系统
  

Red Hat Enterprise Linux release 8.10 (Ootpa)

• 数据库版本：
  

19.24.0.0.0 Enterprise Edition
现象描述：

一个Oracle数据库突然收到大量的邮件告警，提示告警日志中出现大量的ORA-04031错误，部分信息如下所示:

1. 2025-02-27T10:19:20.885697+08:00
   
2. Errors in file /******/diag/rdbms/unity/unity/trace/unity_ora_1983878.trc  (incident=43372) (PDBNAME=CDB$ROOT):
   
3. ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^34","kglseshtTable")
   
4. Use ADRCI or Support Workbench to package the incident.
   
5. See Note 411.1 at My Oracle Support for error and packaging details.
   
6. 2025-02-27T10:19:20.938373+08:00
   
7. Errors in file /******/diag/rdbms/unity/unity/trace/unity_p00e_1983815.trc  (incident=43307) (PDBNAME=CDB$ROOT):
   
8. ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^80","kglseshtTable")
   
9. Use ADRCI or Support Workbench to package the incident.
   
10. See Note 411.1 at My Oracle Support for error and packaging details.
    
11. 2025-02-27T10:19:20.949940+08:00
    
12. Errors in file /******/diag/rdbms/unity/unity/trace/unity_ora_1983916.trc  (incident=43349) (PDBNAME=CDB$ROOT):
    
13. ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^512","kglseshtTable")
    
14. Use ADRCI or Support Workbench to package the incident.
    
15. See Note 411.1 at My Oracle Support for error and packaging details.
    
16. 2025-02-27T10:19:21.027461+08:00
    
17. Errors in file /******/diag/rdbms/unity/unity/trace/unity_mz00_1983918.trc  (incident=43322) (PDBNAME=CDB$ROOT):
    
18. ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^76","kglseshtTable")
    
19. Use ADRCI or Support Workbench to package the incident.
    
20. See Note 411.1 at My Oracle Support for error and packaging details.
    
21. 2025-02-27T10:19:21.056947+08:00
    
22. Errors in file /******/diag/rdbms/unity/unity/trace/unity_p00f_1983817.trc  (incident=43338) (PDBNAME=CDB$ROOT):
    
23. ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^531","kglseshtTable")
    
24. Use ADRCI or Support Workbench to package the incident.
    
25. See Note 411.1 at My Oracle Support for error and packaging details.
    
26. 2025-02-27T10:19:21.093570+08:00
    
27. Errors in file /******/diag/rdbms/unity/unity/trace/unity_mz00_1983918.trc:
    
28. ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^76","kglseshtTable")
    
29. 2025-02-27T10:19:21.100680+08:00
    
30. 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:

1. Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
   
2. Gen 2 Exadata Cloud at Customer - Version N/A and later
   
3. Oracle Database - Enterprise Edition - Version 19.24.0.0.0 and later
   
4. Oracle Cloud Infrastructure - Exadata Cloud Service - Version N/A and later
   
5. Information in this document applies to any platform.

复制代码

SYMPTOMS

A 19.24DBRU database may crash after multiple ORA-4031 errors as:

1. ORA-04031: unable to allocate 232 bytes of shared memory ("shared pool","unknown object","KKSSP^1724","kgllk")
   
2. ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select dummy from dual where...","KGLH0^eee30b3d","kglHeapInitialize:temp")
   
3. From AWR reports, the "DB Replay sess info" component was continuously increasing, from 250M to 2G throughout one day:

复制代码

1. SGA breakdown difference by Pool and Name
   
2. Pool    Name            Begin MB    End MB         % Diff
   
3. shared    DB Replay sess info         205.91     
   
4. shared    free memory    10,596.15    10,479.43    -1.10

复制代码

1. SGA breakdown difference by Pool and Name
   
2. Pool    Name                    Begin MB    End MB        % Diff
   
3. shared    DB Replay sess info      408.71    515.66       26.17
   
4. shared    free memory           10,265.03    10,154.18    -1.08

复制代码

1. SGA breakdown difference by Pool and Name
   
2. Pool    Name                    Begin MB    End MB    % Diff
   
3. shared    DB Replay sess info    1,874.19    1,962.80    4.73
   
4. 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
扫描上面二维码关注我如果你至心以为文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者全部，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文毗连.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。