This content has been marked as final. Show 8 replies
memory_max_target is the upper for memory_target. memory_target can set without restart. memory_max_target needs restart
SQL> alter system set memory_max_target=300M scope=both sid='*';
alter system set memory_max_target=300M scope=both sid='*'
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set memory_target=250M scope=both sid='*';
Thank you for your reply, but still I am wanting to know how we can modify the parameter that requires restart because both asm instances are using the shared spfile and wanting to know if we will be required to stop
the clusterware or will we need to create a new spfile from a pfile? Not sure exactly the steps in a clustered env where both asm instances are using the shared spfile to startup?
Set your Parameter with scope=spfile like my example. When you want to set memory_max_target you must afterwards restart your ASM Instanz in a rolling manner. The ASM Shares the same spfile but there is no need to recreate it. My examles are also done on a ASM Instance.
If you want to set this Parameter only for one ASM Instance and later for a other then you can use sid='+ASM1' or sid='+ASM2'. If you set sid='*' then the changes are for all ASM Instances. You can have different memory Parameter accross your Cluster Nodes, but you should not do this for a long time.
Ok, So here are my current settings...
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 392M
memory_target big integer 392M
My goal is to change both to 500M
So it sounds like I would have to change the memory_max_target first like follows...Because memory_target cannot exceed the current value for memory_max_target.
So steps would be...With database instances down on the node where I will bounce asm.
alter system set memory_max_target=500M scope=spfile sid='*';
srvctl stop asm -n node1
srvctl start asm -n node1
Start db instance on node1
stop db instance on node2.
srvctl stop asm -n node2
srvctl start asm -n node2
Start db instance on node2
Next login to asm instance from either node...
alter system set memory_target=500M scope=both sid='*';
Does that look like correct procedure?
your action plan should work to reach your goal.
Just for information, if you have not set it now the default vaule for memory_max_target is memory_target. Which means that you did not need to set it in your spfile because it will take the vaule from memory_target. You can do a show parameter after the first node to check this or create pfile='/tmp/init.ora' from spfile and look into the file. This should be only a information.