一次典型的ORA-04031问题的处理

最新推荐文章于 2025-01-29 18:21:25 发布

Yushan Bai

最新推荐文章于 2025-01-29 18:21:25 发布

阅读量4.6k

点赞数 2

分类专栏： ORACLE 故障排查文章标签： java 数据库开发语言 oracle 运维

本文链接：https://blog.csdn.net/haibusuanyun/article/details/130423500

版权

ORACLE 故障排查专栏收录该内容

196 篇文章

订阅专栏

文章描述了处理ORA-04031错误的过程，包括检查数据库alert日志，发现shared_pool内存不足，尝试flush和在线修改未果，最终通过调整SGA参数，增大shared_pool_size至2048M并重启数据库解决问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

梳理客户问题，发现之前一个典型的ORA-04031报错，相关处理步骤思路如下：

1.接到应用软件使用人员反馈，程序报错ORA-04031

2.检查数据库alert日志，发现后台日志不停报错ORA-04031: 无法分配 ORA-04031: 无法分配 4160 字节的共享内存 ("shared pool","unknown object","sga heap(1,0)","modification ")
字节的共享内存 ("","","","")

3.查看内存分配，发现shared pool过小

4.临时flush shared_pool，无法根本性解决问题

5.在线修改shared_pool，无法成功

6.调整内存SGA参数，增大shared_pool_size=2048m scope=spfile，重启生效后解决问题。

相关操作步骤如下：

1.大量的ORA-04031报错

[oracle@x2pacsdb ~]$ tail -n 200 alert_ris.log 
 字节的共享内存 ("","","","")
Incident details in: /app/oracle/diag/rdbms/risdx/ris/incident/incdir_56997/ris_m000_30937_i56997.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Apr 28 03:00:11 2021
Sweep [inc][56997]: completed
Sweep [inc][56988]: completed
Sweep [inc2][56997]: completed
Sweep [inc2][56988]: completed
Wed Apr 28 04:00:05 2021
Errors in file /app/oracle/diag/rdbms/risdx/ris/trace/ris_m000_1205.trc  (incident=56643):
ORA-04031: 无法分配 4160 字节的共享内存 ("shared pool","unknown object","sga heap(1,0)","modification ")
Incident details in: /app/oracle/diag/rdbms/risdx/ris/incident/incdir_56643/ris_m000_1205_i56643.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /app/oracle/diag/rdbms/risdx/ris/trace/ris_m000_1205.trc  (incident=56644):
ORA-04031: 无法分配 ORA-04031: 无法分配 4160 字节的共享内存 ("shared pool","unknown object","sga heap(1,0)","modification ")
 字节的共享内存 ("","","","")
Incident details in: /app/oracle/diag/rdbms/risdx/ris/incident/incdir_56644/ris_m000_1205_i56644.trc
Wed Apr 28 04:00:07 2021
Dumping diagnostic data in directory=[cdmp_20210428040007], requested by (instance=1, osid=1205 (M000)), summary=[incident=56643].
Wed Apr 28 04:00:09 2021
Sweep [inc][56644]: completed
Sweep [inc][56643]: completed
Sweep [inc2][56643]: completed
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20210428040012], requested by (instance=1, osid=1205 (M000)), summary=[incident=56644].
Wed Apr 28 04:01:09 2021
Sweep [inc2][56644]: completed
Wed Apr 28 05:00:08 2021
Errors in file /app/oracle/diag/rdbms/risdx/ris/trace/ris_m000_3806.trc  (incident=56782):
ORA-04031: 无法分配 4160 字节的共享内存 ("shared pool","unknown object","sga heap(1,0)","modification ")
Incident details in: /app/oracle/diag/rdbms/risdx/ris/incident/incdir_56782/ris_m000_3806_i56782.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /app/oracle/diag/rdbms/risdx/ris/trace/ris_m000_3806.trc  (incident=56783):
ORA-04031: 无法分配 ORA-04031: 无法分配 4160 字节的共享内存 ("shared pool","unknown object","sga heap(1,0)","modification ")
 字节的共享内存 ("","","","")
Incident details in: /app/oracle/diag/rdbms/risdx/ris/incident/incdir_56783/ris_m000_3806_i56783.trc
Wed Apr 28 05:00:11 2021
Dumping diagnostic data in directory=[cdmp_20210428050011], requested by (instance=1, osid=3806 (M000)), summary=[incident=56782].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

2.分析内存分配情况，尝试改大

SQL> col name for a30
SQL> select name,bytes/1024/1024 mb from v$sgainfo;

NAME                                   MB
------------------------------ ----------
Fixed SGA Size                 2.16016388
Redo Buffers                   2.61328125
Buffer Cache Size                    4848
Shared Pool Size                      992
Large Pool Size                       224
Java Pool Size                         32
Streams Pool Size                       0
Shared IO Pool Size                     0
Granule Size                           16
Maximum SGA Size               6100.77734
Startup overhead in Shared Poo 234.283409

NAME                                   MB
------------------------------ ----------
l

Free SGA Memory Available               0

12 rows selected.

SQL> show parameter sga

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
lock_sga                             boolean
FALSE
pre_page_sga                         boolean
FALSE
sga_max_size                         big integer
6128M
sga_target                           big integer
0

3.尝试 flush shared_pool及改大，均未成功

SQL> alter system flush shared_pool;

SQL> alter system set shared_pool_size=1200m;
alter system set shared_pool_size=1200m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool


SQL> alter system set shared_pool_size=1024m;
alter system set shared_pool_size=1024m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

4.调整SGA、shared_pool并重启

SQL> !free -m
             total       used       free     shared    buffers     cached
Mem:         15950      15784        166       5659         11      13968
-/+ buffers/cache:       1804      14146
Swap:         8015        370       7645
SQL> alter system set sga_max_size=10240m scope=spfile;
System altered.
SQL> alter system set sga_target=10000m scope=spfile;
System altered.
SQL> alter system set shared_pool_size=2048m scope=spfile;
System altered.

5.重启后，观察一段时间，数据库运行正常。