11.2.0.3ASM instance ORA-4031 error causes database archive failure

Environment:
Platform: RedHat EnterPrise 5.8 X86_X64
Database: Oracle EnterPrise 11.2.0.3
Cluster software: Oracle grid 11.2.0.3

Fault symptom:
An archive failure occurs in the database, and an instance of one node is HANG dead.

The log information is as follows:

 

 Fri Feb 28 19:49:04 2014 ARC1: Error 19504 Creating archive log file to '+DATA02' ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance orcl1 - Archival Error ORA-16038: log 14 sequence# 68244 cannot be archived ORA-19504: failed to create file "" ORA-00312: online log 14 thread 1: '+DATA02/orcl/onlinelog/group_14.264.792274883' ORA-00312: online log 14 thread 1: '+DATA02/orcl/onlinelog/group_14.265.792274889' Archiver process freed from errors. No longer stopped Fri Feb 28 19:50:22 2014 ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3 ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance orcl1 - Archival Error ORA-16014: log 14 sequence# 68244 not archived, no available destinations ORA-00312: online log 14 thread 1: '+DATA02/orcl/onlinelog/group_14.264.792274883' ORA-00312: online log 14 thread 1: '+DATA02/orcl/onlinelog/group_14.265.792274889' ARC0: Archive log rejected (thread 1 sequence 68240) at host 'orclsh' FAL[server, ARC0]: FAL archive failed, see trace file. ARCH: FAL archive failed. Archiver continuing ORACLE Instance orcl1 - Archival Error. Archiver continuing. 

Analysis:
Because the archive fails to occur on the ASM disk, first check the ASM disk space and DB_RECOVERY_FILE_DEST_SIZE. The ASM disk space is sufficient because there is only one node and the archive fails, it can also be ruled out as a result of insufficient space. Make sure that the DB_RECOVERY_FILE_DEST_SIZE parameter of the two nodes is set to 0. Basically, it can be determined that the problem is related to the abnormal ASM instance status of the current node.

Check the error message of the ASM instance:

 Fri Feb 28 19:41:23 2014 Dumping diagnostic data in directory=[cdmp_20130702164115], requested by (instance=2, osid=2032294 (LMD0)), summary=[incident=165521]. Fri Feb 28 19:49:19 2014 Dumping diagnostic data in directory=[cdmp_20130702164845], requested by (instance=2, osid=2032294 (LMD0)), summary=[incident=165522]. Fri Feb 28 19:55:56 2014 Dumping diagnostic data in directory=[cdmp_20130702165517], requested by (instance=2, osid=2032294 (LMD0)), summary=[incident=165523].

This message is displayed on the ASM instance of the current node, indicating that the error occurred on instance 2:

Fri Feb 28 18:34:25 2014 Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_2032294.trc (incident=186256): 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 2032294) Fri Feb 28 18:29:53 2014 Sweep [inc][186256]: completed Fri Feb 28 18:36:49 2014 Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_2032294.trc (incident=186257): 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 2032294)

Sure enough ASM on instance 2 encountered a lot of ORA-4031 errors. Check the parameter configuration of ASM startup:

Fri Feb 28 20:06:55 2012 NOTE: No asm libraries found in the system ERROR: -5(Duplicate disk DATA_DG01:ASM_DISK1) ERROR: -5(Duplicate disk DATA_DG01:ASM_DISK2) MEMORY_TARGET defaulting to 411041792. * instance_number obtained from CSS = 2, checking for the existence of node 0...  * node 0 does not exist. instance_number = 2  Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Private Interface 'en1' configured from GPnP for use as a private interconnect.[name='en1', type=1, ip=169.254.78.6, mac=00-1a-64-bb-50-7d, net=169.254.0.0/16, mask=255.255.0.0, use=haip:cluster_interconnect/62] Public Interface 'en0' configured from GPnP for use as a public interface.[name='en0', type=1, ip=10.1.16.35, mac=00-1a-64-bb-50-7c, net=10.1.16.32/27, mask=255.255.255.224, use=public/1] Picked latch-free SCN scheme 3 Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/11.2.0.3/grid/dbs/arch Autotune of undo retention is turned on.  LICENSE_MAX_USERS = 0 SYS auditing is disabled NOTE: Volume support enabled Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options. ORACLE_HOME = /u01/app/11.2.0.3/grid System name: AIX Node name: orcldb2 Release: 1 Version: 6 Machine: 00C94E064C00 Using parameter settings in server-side pfile /u01/app/11.2.0.3/grid/dbs/init+ASM2.ora System parameters with non-default values:large_pool_size = 12Minstance_type = "asm"remote_login_passwordfile= "EXCLUSIVE"asm_diskstring = "/dev/ocr_*"asm_diskstring = "/dev/voting_*"asm_diskstring = "/dev/asm_*"asm_diskgroups = "DATA"asm_diskgroups = "DATA_DG01"asm_diskgroups = "SPFILE_DG"asm_power_limit = 1diagnostic_dest = "/u01/app/grid" Cluster communication is configured to use the following interface(s) for this instance169.254.78.6 cluster interconnect IPC version:Oracle UDP/IP (generic) IPC Vendor 1 proto 2

Adjustments and suggestions:
The current ASM instance uses the default MEMORY_TARGET configuration and the allocation size is approximately 400 M, according to Oracle's MOS article: ASM & Shared Pool (ORA-4031) [ID 437924.1], in 11.2.0.3, oracle adds the default PROCESSES allowed by the ASM instance, but the default MEMORY_TARGET parameter is not added.
According to Oracle's suggestion, MEMORY_TARGET of 11.2.0.3 should be set to at least 1536 M, and MEMORY_MAX_TARGET should be set to 4096 M.

SQL> alter system set memory_max_target=4096m scope=spfile;SQL> alter system set memory_target=1536m scope=spfile;

If you cannot restart the database or ASM instance in a short time, you can configure a local archiving path on the faulty node and set the target path to a local disk, in this way, the HANG instance cannot be archived.