Few weeks ago I had to modify an instance parameter to a different value. In this case, I just wanted to modify it for only one instance and leave the other instance as it was.
I thought that if I didn’t specify the instance name using the SID clause, the default is going to be just that instance. I was really wrong about that and the command ended modifying both instances.
After looking at Oracle’s documentation here’s what I’ve found.
SID Clause
· Specify SID = ‘*’ if you want Oracle Database to change the value of the parameter for all instances.
· Specify SID = ‘sid’ if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = ‘*’.
If you do not specify this clause:
· If the instance was started up with a pfile (client-side initialization parameter file), then Oracle Database assumes the SID of the current instance.
· If the instance was started up with an spfile (server parameter file), then Oracle Database assumes SID = ‘*’.
If you specify an instance other than the current instance, then Oracle Database sends a message to that instance to change the parameter value in the memory of that instance.
So now I know that in future I need to specify the SID in a RAC instance using spfile.
https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_2013.htm
Thanks,
Alfredo