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

案例环境

操作系统

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.