The actual operation of modifying SGA under RAC

In the production environment of the EM check ADDM, recently always see increasing the size of the SGA recommended, the current SGA is 4G, decided to increase to 5G according to the recommendations.

Environment:
Two-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
32G Memory

Note: modifying the SGA in a RAC environment must be to specify that the instance is modified individually, sga_max_size is the maximum SGA size that is allowed to be allocated, this time set to 8G, which is considered static, so modify SPFile and restart the instance, Sga_ Target is the current size of the SGA to be allocated according to ADDM's recommendation to 5G, which is a dynamic parameter that can be modified dynamically.

modification of operating system kernel parameters

A, Node 1
[Root@pdmcn1 ~]# vi/etc/sysctl.conf
Kernel.shmall = 4194304 #即支持最大16G共享内存段可用, we want to set the maximum SGA to 8G this time, so we want to modify this parameter.
[Root@pdmcn1 ~]# sysctl-p #生效修改

B, Node 2
[root@pdmcn2~]# vi/etc/sysctl.conf
Kernel.shmall = 4194304 #即支持最大16G共享内存段可用, we want to set the maximum SGA to 8G this time, so we want to modify this parameter.
[Root@pdmcn2 ~]# sysctl-p #生效修改

A little supplement to Shmall:

Kernel.shmall
This parameter controls the total amount of shared memory that the system can use at one time (in page, 4k=4098), which is determined according to the Kernel.shmmax size
Kernel.shmall = 2097152 is 2097152*4098= 8G, the largest available 8G of memory (this is required to adjust the SGA, the SGA size setting can not exceed this value)
The value defaults to 2097152, but can be referred to as follows depending on the size and usage of the system memory:
Kernel.shmall = 2097152---memory 8G
Kernel.shmall = 4194304---memory 16G
Kernel.shmall = 8388608---memory 32G

Second, modify the SGA

A, modify the SGA of instance 2

Check instance
[Oracle@pdmcn2 ~]$ srvctl status database-d PDMCN

Instance Pdmcn1 is running on node pdmcn1
Instance PDMCN2 is running on node pdmcn2

To determine that all instances are functioning correctly

start closing Instance 2

[Oracle@pdmcn2 ~]$ srvctl stop instance-d pdmcn-i pdmcn2
Check instance

[ORACLE@PDMCN2 ~] $srvctl status database-d PDMCN

Instance Pdmcn1 is running on node pdmcn1
Instance Pdmcn2 is isn't running on node pdmcn2

Determining that instance 2 is closed

Business normal When determining a database instance

On DB 2, start the database to Mount state
[Oracle@pdmcn2 ~]srvctl start instance-d pdmcn-i pdmcn2-o Mount

Modify the Sga_max_size of instance 2 to 8G
Modify the DB2 Sga_target to 5G

[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.

Close Instance 2
Srvctl Stop instance-d pdmcn-i pdmcn2

Start Instance 2
Srvctl start instance-d pdmcn-i pdmcn2 Open

View SGA information for 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 Modification Succeeded

B, modify the SGA of instance 1

Close Instance 1
[ORACLE@WTDB1 ~]$ srvctl stop instance-d pdmcn-i pdmcn1

Check instance Status
[ORACLE@WTDB1 ~]$ srvctl status database-d PDMCN

Instance Pdmcn1 is isn't running on node pdmcn1
Instance PDMCN2 is running on node pdmcn2

Determine that instance 1 is closed

Start database to mount state on DB1
Srvctl start instance-d pdmcn-i pdmcn1-o Mount

Modify the Sga_max_size of instance 1 to 8G
Modify the DB2 Sga_target to 5G

[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.

Close Instance 1
Srvctl Stop instance-d pdmcn-i pdmcn1

Start Instance 1
Srvctl start instance-d pdmcn-i pdmcn1-o Open

View SGA information for 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 Modification Succeeded

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.