ORA-32018: Parameter Cannot be modified in memory on another instance

ORA-32018: parameter cannot be modified in memory on another instance

Cause: Parameter adjustment can take a very long time

Action: Modify the parameter individually on each instance using the SID clause of the alter system command

Problem Description
In RAC database setting a memory parameter fails with ORA-32018. For example whenever we try to set streams_pool_size to a value then it fails like below.

SQL> alter system set streams_pool_size=100M;
alter system set streams_pool_size=100M
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

SQL> alter system set streams_pool_size=100M sid='*';
alter system set streams_pool_size=100M sid='*'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Cause of the Problem
The error message indicates that may be in one instance in RAC database parameter can be set successfully but in another instance it fails and overall you can't set the parameter across all instances.

Solution of the Problem
In order to know the instance which is causing the failure, try to set the parameter individually on each instance using the SID clause of the alter system command. Ensure the instance name by,

SQL> show parameter instance

NAME                                            TYPE      VALUE
------------------------------------       -------------- ------------------------------
active_instance_count            integer
cluster_database_instances integer    3
instance_groups                      string
instance_name                        string            3
instance_number                      integer 3
instance_type                          string
open_links_per_instance       integer 4
parallel_instance_group         string
parallel_server_instances      integer 3

From the parameter value we see we are in 3 nodes RAC database and our instance name is ORA3. So set the parameter in our current instance by specifying a SID value.

SQL> alter system set streams_pool_size=100M sid='ORA3';

System altered.

Similarly set it on rest of the instances ORA2 and ORA1 for example.

SQL> alter system set streams_pool_size=100M sid='ORA2';

System altered.

SQL> alter system set streams_pool_size=100M sid='ORA1';

*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

So we see ORA1 instance is causing the problem. This is because there is insufficient memory in ORA1 instance so you might try to increase SGA_TARGET/MEMORY_TARGET parameter or shut down ORA1 instance and restart and then we can set.