In this post I’m going to show you how to change the processes parameter in an Oracle RAC database.
You can use this same method for any RAC parameter that requires instances to be rebooted for the change to take effect.
In an ideal situation, you would just alter system the parameters and then you would stop and start each instance.
But this did not work as expected because some services were only available in one node and not in the other, so I had to relocate them and try again.
I will show you all the details in this post.
First of all I connect and check the current values.
set lines 200
column NAME format a40
column VALUE format a50
select INST_ID,NAME,VALUE from gv$parameter
where NAME in ('processes','sessions')
order by 2,1;
Once I have the values, I want to change the processes parameter to 1500.
sqlplus / as sysdba alter system set processes=1500 scope=spfile sid='*';
So now ideally I would have to just stop and restart each of the 2 RAC instances.
[oracle@node1:XI002PRO1 ~]$ srvctl stop instance -d XI002PRO -i XI002PRO1 PRCD-1315 : failed to stop instances for database XI002PRO PRCR-1014 : Failed to stop resource ora.xi002pro.db PRCR-1065 : Failed to stop resource ora.xi002pro.db CRS-2974: unable to act on resource 'ora.xi002pro.db' on server 'node1' because that would require stopping or relocating resource 'ora.xi002pro.xi002pro_bkup1.svc' but the appropriate force flag was not specified
So I checked all the services for that database like that.
[oracle@node1:XI002PRO1 ~]$ srvctl status service -d XI002PRO Service XI002PROPLUGSVC is running on instance(s) XI002PRO1 Service xi002pro_bkup1 is running on instance(s) XI002PRO1 Service xi002pro_bkup2 is running on instance(s) XI002PRO3 Service XI002PROSVC is running on instance(s) XI002PRO1
It was complaining about the service xi002pro_bkup1.
So I tried to relocate all services to node 3.
[oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service XI002PROPLUGSVC -oldinst XI002PRO1 -newinst XI002PRO3 [oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service xi002pro_bkup1 -oldinst XI002PRO1 -newinst XI002PRO3 [oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service XI002PROSVC -oldinst XI002PRO1 -newinst XI002PRO3
And then tried again
[oracle@node1:XI002PRO1 ~]$ srvctl stop instance -d XI002PRO -i XI002PRO1 [oracle@node1:XI002PRO1 ~]$ srvctl start instance -d XI002PRO -i XI002PRO1
This time it worked.
Now I need to relocate the services back to node 1.
[oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service XI002PROPLUGSVC -oldinst XI002PRO3 -newinst XI002PRO1 [oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service xi002pro_bkup1 -oldinst XI002PRO3 -newinst XI002PRO1 [oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service XI002PROSVC -oldinst XI002PRO3 -newinst XI002PRO1
There is still one service that was on node 3 that I have to move to node 1 to start and stop the instance.
But I faced another problem.
[oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service xi002pro_bkup2 -oldinst XI002PRO3 -newinst XI002PRO1 PRCR-1106 : Failed to relocate resource ora.xi002pro.xi002pro_bkup2.svc from node xfpcon01db03 to node node1 PRCR-1089 : Failed to relocate resource ora.xi002pro.xi002pro_bkup2.svc. CRS-2717: Server 'node1' is not in any of the server pool(s) hosting resource 'ora.xi002pro.xi002pro_bkup2.svc'
The problem is that this service xi002pro_bkup2 is only preferred on node 3 and not even available in node 1 as you can see with this.
[oracle@node1:XI002PRO1 ~]$ srvctl config service -db XI002PRO -service xi002pro_bkup2 Service name: xi002pro_bkup2 Server pool: Cardinality: 1 Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Failover type: Failover method: TAF failover retries: TAF failover delay: Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Edition: Pluggable database name: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Replay Initiation Time: 300 seconds Session State Consistency: GSM Flags: 0 Service is enabled Preferred instances: XI002PRO3 Available instances:
So I added this service as available to node 1 and preferred at node 3 and checked again.
[oracle@node1:XI002PRO1 ~]$ srvctl modify service -db XI002PRO -service xi002pro_bkup2 -modifyconfig -preferred XI002PRO3 -available XI002PRO1 [oracle@node1:XI002PRO1 ~]$ srvctl config service -db XI002PRO -service xi002pro_bkup2 Service name: xi002pro_bkup2 Server pool: Cardinality: 1 Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Failover type: Failover method: TAF failover retries: TAF failover delay: Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Edition: Pluggable database name: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Replay Initiation Time: 300 seconds Session State Consistency: GSM Flags: 0 Service is enabled Preferred instances: XI002PRO3 Available instances: XI002PRO1
As shown above, the service is now preferred on node 3 and available on node 1.
So now I can relocate the service to node 1.
[oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service xi002pro_bkup2 -oldinst XI002PRO3 -newinst XI002PRO1
And then I can stop and start the instance in node 3.
[oracle@node1:XI002PRO1 ~]$ srvctl stop instance -d XI002PRO -i XI002PRO3 [oracle@node1:XI002PRO1 ~]$ srvctl start instance -d XI002PRO -i XI002PRO3
Now I relocate the service back to node 3.
[oracle@node1:XI002PRO1 ~]$ srvctl relocate service -db XI002PRO -service xi002pro_bkup2 -oldinst XI002PRO1 -newinst XI002PRO3
And I check again the services how they are distributed on each node to see if they are back as I had them at the beginning.
[oracle@node1:XI002PRO1 ~]$ srvctl status service -d XI002PRO Service XI002PROPLUGSVC is running on instance(s) XI002PRO1 Service xi002pro_bkup1 is running on instance(s) XI002PRO1 Service xi002pro_bkup2 is running on instance(s) XI002PRO3 Service XI002PROSVC is running on instance(s) XI002PRO1
Now I validate that the change to the processes parameter has been applied.
sqlplus / as sysdba
set lines 200
column NAME format a40
column VALUE format a50
select INST_ID,NAME,VALUE from gv$parameter
where NAME in ('processes','sessions')
order by 2,1;
INST_ID NAME VALUE
---------- ---------------------------------------- --------------------------------------------------
1 processes 1500
3 processes 1500
1 sessions 2272
3 sessions 2272
So everything seems OK.
Of course, if you do some preparation before doing the stop and start of each instance, you could have avoided all this during the intervention.
I hope this was helpful.