ASM & Shared Pool (ORA-4031) (文档 ID 437924.1)故障解决一例

 

ASM & Shared Pool (ORA-4031) (文档 ID 437924.1)

一、概述

收到数据库故障申告，现象为数据库hang住，用户无法连接数据库服务器。

plsql连接数据库服务器报错如下

二、分析过程

主要对2014年6月7日、8日的数据库服务器性能和数据库日志进行分析。

检查了2014年6月7日、8日的数据库服务器的CPU、内存和数据库连接会话数的历史数据，发现都正常，下面就从数据库性能进行分析。

1、RAC数据库实例ynzddb1日志报错

Sun Jun 08 06:04:52 2014

ARC1: Error 19504 Creating archive log file to '+DG_DATA01/backup/ynzddb/archivelog/1_18623_816172951.arc'

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance ynzddb1 - Archival Error

ORA-16038: log 3 sequence# 18623 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 3 thread 1: '+DG_DATA01/oradata/ynzddb/redo03_1.log'

ORA-00312: online log 3 thread 1: '+DG_DATA01/oradata/ynzddb/redo03.log'

Sun Jun 08 06:04:52 2014

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance ynzddb1 - Archival Error

ORA-16014: log 3 sequence# 18623 not archived, no available destinations

ORA-00312: online log 3 thread 1: '+DG_DATA01/oradata/ynzddb/redo03_1.log'

ORA-00312: online log 3 thread 1: '+DG_DATA01/oradata/ynzddb/redo03.log'

Sun Jun 08 06:10:47 2014

Archived Log entry 37325 added for thread 1 sequence 18623 ID 0xb1631617 dest 1:

krse_arc_driver_core: Successful archiving of previously failed ORL

Archiver process freed from errors. No longer stopped

Sun Jun 08 06:22:17 2014

报错为无法进行数据库在线日志的切换，疑似数据库归档满了。

2、检查数据库归档情况

发现数据库归档目录还有空闲空间，否定数据库故障为归档满了，需要继续检查其他日志。

3、检查ASM日志情况

发现rac数据库的第二个节点的asm实例日志有异常报错

/u02/app/grid/diag/asm/+asm/+ASM2/trace/alert_+ASM2.log

Sun Jun 08 18:30:46 2014

Errors in file /u02/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_27036.trc  (incident=233492):

ORA-04031: unable to allocate 3768 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues")

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

 Insufficient shared pool to allocate a GES object (ospid 27036)

Sun Jun 08 18:30:47 2014

Sweep [inc][233492]: completed

Sun Jun 08 18:32:10 2014

Errors in file /u02/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_27036.trc  (incident=233493):

ORA-04031: unable to allocate 3768 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues")

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

 Insufficient shared pool to allocate a GES object (ospid 27036)

Sun Jun 08 18:32:11 2014

根据MOS官方文档ASM & Shared Pool (ORA-4031) (文档 ID 437924.1)，在11.2.0.3中，Oracle增加了ASM实例所允许的默认进程数PROCESSES，但是默认的MEMORY_TARGET参数没有增加。

目前数据库asm实例参数如下

SQL> show parameter memory

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

memory_max_target                    big integer 4G

memory_target                        big integer 1536M

综调数据库服务器的内存为120G，根据Oracle的建议，数据库的MEMORY_TARGET和MEMORY_MAX_TARGET的值可以设置为更高一些，来出来该问题。

三、结论

综调数据库出现hang住情况，疑似ASM实例的内存参数过小导致，需要申请停库，进行内存参数调整，把数据库ASM实例的MEMORY_TARGET和MEMORY_MAX_TARGET的值可以设置为更高一些。

SQL> ALTER system SET memory_max_target=8192M scope=spfile;

SQL> ALTER system SET memory_target=8192M scope=spfile;

四、相关知识

In 11.2.0.3/11.2.0.4, we increase the default PROCESSES based on the number of CPU cores, but the default MEMORY_TARGET value is not increased.   If in 11.2.0.2, customers explicitly set MEMORY_TARGET to some value that may not be big enough for 11.2.0.3/11.2.0.4, when they upgrade to 11.2.0.3/11.2.0.4, ASM will fail to start with error "memory_target is too small".    We should add additional check for MEMORY_TARGET during the upgrade prerequisite check.

You can unset MEMORY_TARGET so that ASM can use the default value, but if MEMORY_TARGET is explicitly set, please make sure it's large enough, following the next rules:

1) If PROCESSES parameter is explicitly set:

The MEMORY_TARGET should be set to no less than:
       256M + PROCESSES  * 132K (64bit) 
 or
      256M + PROCESSES  * 120K (32bit)

2) If PROCESSES parameter is not set:

The MEMORY_TARGET should be set to no less than:

      256M + (available_cpu_cores * 80 + 40) * 132K  (64bit) 
or

      256M + (available_cpu_cores * 80 + 40) * 120K  (32bit)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/645199/viewspace-1385620/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/645199/viewspace-1385620/