ORA-04031: unable to allocate X bytes of shared memory in ASM issue(11g Rel 2)
Recently i saw this issue in our ASM during rebalance operation. Our db size is more than 7 TB and when we add 200 GB to existing disk group with rebalance power 10. Rebalance was hung and our one of the db instance in RAC continuously throwing unable to extend shared pool error. During that time we are not able to connect to ASM instance but application working fine without any issues.
Solution to fix this issue.
1. Shutdown the effected database instance
it will continuously throw shared pool error. No issues just use shutdown abort command.
2. Then i am able to connect to the ASM instance
sqlplus / as sysasm
3.alter system set memory_max_target=X;
(X value will be calculated based on the formula. formula will be explained in detail later)
alter system set memory_target=X
4. Restart the CRS using below command
$ORACLE_GRID_HOME/bin/crsctl stop has (as a root user)
$ORACLE_GRID_HOME/bin/crsctl start has (as a root user)
5. start the database instance.
Determining the Value X:
-----------------------
The setting for the SHARED_POOL_SIZE
parameter determines the amount of memory required to manage the
instance. The setting for this parameter is also used to determine the
amount of space that is allocated for extent storage. The default value
for this parameter is suitable for most ASM environments.
Oracle strongly recommends AMM parameters.
The default value used for MEMORY_TARGET (272 MB) is acceptable for most environments
If you are not using Automatic Memory Management, then the default value for this parameter is suitable for most environments.
For 32-bit environments 32 MB is the default and minimum requirement for an ASM instance, but 128 MB is recommended.
On 64-bit platforms 88 MB are required for an ASM instance, recommended values is 150 MB.
