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.
You can check the current Oracle process limits with this query
SELECT INST_ID, RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM GV$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ( 'sessions', 'processes');
INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALU
---------- ------------------------------ ------------------- --------------- ----------
1 processes 120 250 700
1 sessions 128 255 1074
3 processes 699 700 700
3 sessions 708 711 1074
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.
English