How to Change Processes Parameter in Oracle 19c RAC

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.

Leave a Reply

Your email address will not be published. Required fields are marked *