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

梳理客户问题发现之前一个典型的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.重启后观察一段时间数据库运行正常。