Oracle Database Discussions

in RAC (10.2.0.4, AIX, 64bit), instance 2 is down, check altert log,

ORA-04031: unable to allocate 8416 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","KSXR large reply queue")

LCK0: terminating instance due to error 4031


( dba broungt it up.)


( check related trace )
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora _4031_dump_bitvec = 0
=====================================
Allocation Request Summary Informaton
=====================================
Current information setting: 04014fff
SGA Heap Dump Interval=3600 seconds
Dump Interval=300 seconds
Last Dump Time=09/19/2010 15:18:42
Dump Count=1
Allocation request for: KSXR large reply queue
Heap: 700000010036770, size: 8416
******************************************************
HEAP DUMP heap name="sga heap(1,0)" desc=700000010036770
extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-126 opc=0
parent=0 owner=0 nex=0 xsz=0x1000000
latch set 1 of 2
durations enabled for this heap
reserved granules for root 0 (granule size 16777216)
....

check memory,


sql> show parameter shared_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 15938355
shared_pool_size big integer 304M
shared_server_sessions integer
shared_servers integer 0

sql>show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 7G

sql>show parameter cursor_sharing

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT

###############

the reason seems from surface is shared_pool is not big enough, but we can see that sga is pretty big,
so it maybe shared pool fragmentation problem.

by the way, the instance down's resaon, is due to

terminating instance due to error 4031




question is here:
1. what is the reasoan for instance shutdown?


2. how to prevernt it happening again? do we need to run flush shared pool periodically, or cursor_sharing to simailar or force?


thanks