How to change the SHARED_POOL_SIZE in ASM instance?

Recently, when I was trying to select some information from the ASM view, I stared getting ORA-04031 errors in ASM alert.log, which was quite unusual.

SYS@+ASM> select * from v$asm_diskgroup
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 808 bytes of shared memory (“shared pool”,”select
group_number, name, s…”,”Typecheck”,”seg:kggfaAllocSeg”)

Which was indicating that, it is running out of shared_pool. Current SGA memory settings

SYS@+ASM> SHOW SGA
I checked the default size of 2 parameters

Total System Global Area 130023424 bytes
Fixed Size 2038864 bytes
Variable Size 102818736 bytes
ASM Cache 25165824 bytes

SYS@+ASM> show parameter SHARED_POOL_SIZE

NAME TYPE VALUE
———————————— ———– ——————————
SHARED_POOL_SIZE big integer 84M

SYS@+ASM> show parameter SGA_MAX_SIZE

NAME TYPE VALUE
———————————— ———– ——————————
SGA_MAX_SIZE big integer 124M

Read Metalink Note: 419166.1, which suggests that, “More Appropriate Size of SHARED_POOL_SIZE parameter on 64-bit systems for ASM” is 150Mb. So solution was very simple; change the size of shared_pool i.e. SHARED_POOL_SIZE.

But I was not sure, if I’ve to change SGA_MAX_SIZE before changing SHARED_POOL_SIZE parameter. So I logged a SR with Oracle support, where I got the reply to set the SGA_MAX_SIZE parameter first (which was wrong).

After changing the parameter, ORA-04031 disappeared, but my database started crashing with following error stack in instance alert.log

ORA-27091: unable to queue I/O
ORA-27072: File I/O error
SVR4 Error: 12: Not enough space
Additional information: 4
Additional information: 31056
Additional information: -1
Mon Sep 1 03:30:04 2008
ERROR: cache failed to read fn=6 blk=42 from disk(s): 1
ORA-15080: synchronous I/O operation to a disk failed
NOTE: cache initiating offline of disk 1 group 3
WARNING: offlining disk 1.3950550103 (FLASH_DG_0001) with mask 0x3
NOTE: PST update: grp = 3, dsk = 1, mode = 0x6
Mon Sep 1 03:30:04 2008
ERROR: too many offline disks in PST (grp 3)
Mon Sep 1 03:30:04 2008
NOTE: halting all I/Os to diskgroup FLASH_DG
NOTE: active pin found: 0x3973b0e40
Mon Sep 1 03:30:04 2008
ERROR: PST-initiated MANDATORY DISMOUNT of group FLASH_DG

I also started getting following alerts in asm.log

WARNING: Detected too many memory locking problems.
WARNING: Performance degradation may occur.

Solution to this problem is NOT to explicitly set the SGA_MAX_SIZE parameter in ASM parameter file. After changing the SHARED_POOL_SIZE, SGA_MAX_SIZE will be implicitly resized. This behaviour is similar to Bug 6839332, which closed as not a bug (I’m not sure why?).

Although solution was very simple, it caused database crash.

Advertisements
This entry was posted in Oracle Automatic Storage Management and tagged . Bookmark the permalink.

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