Oracle ASM instance crash due to ORA-04031 error

Hello,

Recently, I made a career change from a operations DBA to consulting DBA. This is a huge jump for me as I will be exposed to more Oracle database environment. I am very delighted with this change. Instead of blogging about the daily tasks/issues that I encounter, I will now blog about major issues or projects that my customer face. Hopefully, you will find it useful!

I have a customer who had their ASM instance crashing almost everyday without fail due to ORA-04031 error. It was an Oracle 11gR2 ASM instance running on SPARC Solaris.

Output from alert log

Errors in file /OraCRS/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_asmb_11024.trc:
ORA-04031: unable to allocate 3912 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","ASM extent pointer array")
Errors in file /OraCRS/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_asmb_11024.trc:
ORA-04031: unable to allocate 3912 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","ASM extent pointer array")
ASMB (ospid: 11024): terminating the instance due to error 4031

Pfile Contents

cluster_database_instances integer 4
cpu_count integer 136
db_cache_size big integer 0
db_unique_name string +ASM
instance_name string +ASM1
instance_type string asm
large_pool_size big integer 12M
memory_max_target big integer 400M
memory_target big integer 400M
pga_aggregate_target big integer 0
processes integer 1400
sga_max_size big integer 816M
sga_target big integer 0
shared_pool_reserved_size big integer 17616076
shared_pool_size big integer 0
sort_area_size integer 65536

The issue was resolved after making the following changes to the ASM instance.

  1. Memory_max_target = 600M
  2. Memory_target = 500M
  3. Processes = 1200
  4. sga_max_size = 0

AMM (memory_target & memory_max_target) was undersized. Please refer to this link for more information. 

 

Update: Please refer to MOS [437924.1] for more information. 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s