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)