[ORA-04031] unable to allocate xx bytes of shared memory

概要

Oracle ORA-04031は，メモリー不足に起因するエラー。自動メモリー管理を有効にしていても，物理メモリーを増設しても発生する場合がある。「共有メモリーのxxxバイトを割当てできません」

解決方法

ひとまずサーバーを再起動

なにせ停止したままだと都合がわるいので，サーバーを再起動すれば稼働し始める。メモリーが一旦リセットされることにより，不具合が解消されるのだろう。何か不具合があればとりあえず再起動させるというセオリー通り。ただし，根本的な解決にはなっていない。再発する。

物理メモリーを増やしたはずなのに…。自動メモリ管理が有効なはずなのに…10日後にエラー再発。「最大メモリーサイズ 」の設定が要因？

メモリ不足が発生しているとのことなので，メモリーを増やしたりしたが再発した。自動メモリ管理も有効になっている。色々調べてみると，どうも最大メモリーサイズが小さいままになっていた。下図は，サーバー – メモリーアドバイザ で表示

SQL Developer で行う場合

ALTER SYSTEM SET sga_max_size=2G COMMENT='' SCOPE=SPFILE;

注意：
・sga_target より小さい値にするとDBが起動しなくなる（同値はOK）
　　SQL Developer から DBA 接続して 初期化パラメータの値を調べておくといい
　　以下のSQLでもわかる（単位はbyte。1024の倍数でK,M,G換算する）
　　SELECT name, value FROM v$parameter where name = ‘sga_target’;
・不測の事態に備えて，とにかく作業前に C:\App\Oracle\server\database にある SPFILExxxx.ora をコピーしてバックアップしておく。設定値を誤って起動しなくなった場合はこのファイルを戻してDBを再起動

初期化パラメータ SHARED_POOL_RESERVED_SIZE を増やす

増やすかどうかは，以下のSQLでまず判断する

SELECT free_space, used_space, request_misses, last_failure_size 
FROM v$shared_pool_reserved;

値の意味
FREE_SPACE USED_SPACE の合計が 共有プール領域のサイズ
REQUEST_MISSES 確保場所 この値が 0以外なら，SHARED_POOL_RESERVED_SIZE を増やす
LAST_FAILURE_SIZE 確保しようとして失敗したサイズ
(サイズ単位はbyte)

上記の例では最初3Mほどだったので，6Mに倍増させてみた

DBを再起動

初期化パラメータ SGA_TARGET を増やす

512Mbyte だと，expdp による エクスポートで ORA04031 が発生することがある。数GByteの _minidump_xxxx.dmp ファイルもエラー対応用のファイルとして作成される。

メモリ割り当て不足に起因するので，1Gに変更する。

変更する際は，SGA_MAX_SIZE が 1G以上になっている必要がある。
SGA_TARGET > SGA_MAX_SIZE としてしまうと，データベースが起動しなくなり初期化パラメータの作り直しという最悪の状況になるので注意する。（ともかく初期化パラメータを変更する前には 初期化パラメータファイルをバックアップしておく。”C:\App\Oracle\server\database\SPFILE__.ORA”）

ALTER SYSTEM SET sga_target=1G COMMENT='' SCOPE=BOTH

DBの再起動は不要。即時適用される。

プロパティ

Oracle Server 11.2.0.4.0 (Hyper-V上で動作。本文で「物理メモリー」と記載しているが，実際は仮メモリの割当サイズのことを意味する。

Oracle Server 19c