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.

SOLUTION

1. Download and apply Patch 36982817.

OR

2. 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