瀟湘隱者

案例環境

• 操作系統

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，我們升級了一大批數據庫實例，但是目前似乎只有這一個實例遇到了這個問題。補丁一時半會兒不會安排，由于此數據庫屬于三級應用?？梢?安排重啟，在重啟過后的似乎暫時未出現此類告警，這里先暫且記錄一下這個案例。