Modify SGA operations under RAC

In the production environment's Em check ADDM, we will always see the suggestion to increase the SGA size recently. The current SGA is 4 GB, and we decided to increase it to 5 GB according to the suggestion.

Environment:
Dual-node RAC environment, node 1 pdmcn1 node 2pdmcn2

Database pdmcn instance 1
Pdmcn1 instance 2 pdmcn2

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production
CRS 10.2.0.4.0
32 GB memory

Note:In the RAC environment, to modify SGA, you must specify the instance to be modified one by one. sga_max_size is the maximum size of SGA that can be allocated. This time, it is set to 8 GB, modify the spfile and restart the instance because it is a static parameter. sga_target is the size of the SGA to be allocated. It is recommended to reach 5 GB according to ADDM. This is a dynamic parameter that can be dynamically modified.

1. Modify operating system kernel parameters

ANode 1
[Root @ pdmcn1 ~] # Vi/etc/sysctl. conf
Kernel. Shmall = 4194304 # That is, the maximum 16 GB shared memory segment is supported. We need to set the maximum SGA to 8 GB this time, so we need to modify this parameter.
[Root @ pdmcn1 ~] # Sysctl-P # effective modification

B. Node 2
[Root @ pdmcn2 ~] # Vi/etc/sysctl. conf
Kernel. Shmall = 4194304 # That is, the maximum 16 GB shared memory segment is supported. We need to set the maximum SGA to 8 GB this time, so we need to modify this parameter.
[Root @ pdmcn2 ~] # Sysctl-P # effective modification

A supplement to Shmall:

Kernel. Shmall
This parameter controls the total amount of shared memory that can be used by the system at a time (in the unit of pages, 4 k = 4098 per page). The value must be determined based on the size of kernel. shmmax.
Kernel. Shmall = 2097152 is 2097152*4098 = 8g, that is, the maximum memory available for 8g at a time (this should be noted when adjusting SGA, the SGA size setting cannot exceed this value)
The default value is 2097152, but the system memory size and usage can be referred to as follows:
Kernel. Shmall = 2097152 --- memory 8 GB
Kernel. Shmall = 4194304 --- memory 16 GB
Kernel. Shmall = 8388608 --- 32 GB memory

 

Ii. Modify SGA

A. Modify the SGA of instance 2

Check instances
[Oracle @ pdmcn2 ~] $ Srvctl status database-D pdmcn

Instance pdmcn1 is running on node pdmcn1
Instance pdmcn2 is running on node pdmcn2

Make sure all instances are running normally

Start to close instance 2

[Oracle @ pdmcn2 ~] $ Srvctl stop instance-D pdmcn-I pdmcn2
Check instances

[Oracle @ pdmcn2 ~] $ Srvctl status database-D pdmcn

Instance pdmcn1 is running on node pdmcn1
Instance pdmcn2 is not running on node pdmcn2

Make sure instance 2 is disabled

Determine that the business is normal when a database instance

Start the database on DB 2 to the Mount status
[Oracle @ pdmcn2 ~] Srvctl start instance-D pdmcn-I pdmcn2-O Mount

Modify the sga_max_size of instance 2 to 8 GB.
Modify the sga_target of DB2 to 5 GB.

[Oracle @ pdmcn2 ~] $ Sqlplus/As sysdba
Alter system set sga_max_size = 8589934592 comment = 'internally adjusted 'scope = spfile SID = 'pdmcn2'
System altered.

Alter system set sga_target = 5368709120 scope = spfile SID = 'pdmcn2'
System altered.

Disable instance 2
Srvctl stop instance-D pdmcn-I pdmcn2

Start instance 2
Srvctl start instance-D pdmcn-I pdmcn2 open

View the SGA information of instance 2
[Oracle @ wtdb2 ~] $ Sqlplus/As sysdba

SQL> show parameter SGA;

Name type value
-----------------------------------------------------------------------------
Lock_sga Boolean false
Pre_page_sga Boolean false
Sga_max_size big integer 8g
Sga_target big integer 5g

SGA modified

B. Modify the SGA of instance 1

Disable instance 1
[Oracle @ wtdb1 ~] $ Srvctl stop instance-D pdmcn-I pdmcn1

Check instance status
[Oracle @ wtdb1 ~] $ Srvctl status database-D pdmcn

Instance pdmcn1 is not running on node pdmcn1
Instance pdmcn2 is running on node pdmcn2

Make sure instance 1 is disabled

Start the database on db1 to the Mount status
Srvctl start instance-D pdmcn-I pdmcn1-O Mount

Modify the sga_max_size of instance 1 to 8 GB.
Modify the sga_target of DB2 to 5 GB.

[Oracle @ pdmcn2 ~] $ Sqlplus/As sysdba
Alter system set sga_max_size = 8589934592 comment = 'internally adjusted 'scope = spfile SID = 'pdmcn1'
System altered.
Alter system set sga_target = 5368709120 scope = spfile SID = 'pdmcn1'
System altered.

Disable instance 1
Srvctl stop instance-D pdmcn-I pdmcn1

Start instance 1
Srvctl start instance-D pdmcn-I pdmcn1-o open

View the SGA information of instance 1
[Oracle @ pdmcn1 ~] $ Sqlplus/As sysdba

SQL> show parameter SGA;

Name type value
-----------------------------------------------------------------------------
Lock_sga Boolean false
Pre_page_sga Boolean false
Sga_max_size big integer 8g
Sga_target big integer 5g

SGA modified

Check database status
[Oracle @ pdmcn1 ~] $ Srvctl status database-D pdmcn

Instance pdmcn1 is running on node pdmcn1
Instance pdmcn2 is running on node pdmcn2

OK all normal