Shulou(Shulou.com)06/01 Report--

How to adjust ORACLE RAC SGA parameters, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Environment:

Two-node RAC environment, node 1 wtdb1 node 2 wtdb2

Database zjcsc instance 1 zjcsc1 instance 2 zjcsc2

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

CRS 10.2.0.4.0

32 GB of memory

Note:

To modify SGA in RAC environment, you must specify the instance to modify it one by one.

Sga_max_size is the maximum SGA size allowed to be allocated. This time it is set to 8G, so it is necessary to modify the spfile and restart the instance because it is a static parameter.

Sga_target is the current SGA size to be allocated to 5G as recommended by ADDM, which is a dynamic parameter that can be dynamically modified.

1. Modify operating system kernel parameters

A, Node 2

[root@wtdb2 ~] # vi / etc/sysctl.conf

Kernel.shmall = 4194304, which means a maximum of 16 GB of shared memory is available. This time, we need to set the maximum SGA to 8 GB, so modify this parameter.

[root@wtdb2] # sysctl-p

Effective modification

B, Node 1

[root@wtdb1 ~] # vi / etc/sysctl.conf

Kernel.shmall = 4194304, which means a maximum of 16 GB of shared memory is available. This time, we need to set the maximum SGA to 8 GB, so modify this parameter.

[root@wtdb1] # sysctl-p

Effective modification

One additional point about shmall:

Kernel.shmall

This parameter controls the total amount of shared memory (in pages, one page 4k=4098) that can be used by the system at a time. The setting of this value needs to be determined according to the kernel.shmmax size.

Kernel.shmall = 2097152 is 2097152 / 4098 = 8 GB, that is, the maximum memory available at a time is 8 GB (this should be noted when adjusting SGA, the SGA size setting must not exceed this value)

This value defaults to 2097152, but depending on the memory size and usage of the system, you can refer to the following:

General rules of setting

Kernel.shmall = 2097152-8GB of memory

Kernel.shmall = 4194304-16GB of memory

Kernel.shmall = 8388608-32 GB of memory

2. Modify SGA

A. Modify the SGA of instance 2

Check the instance

[oracle@wtdb2] $srvctl status database-d zjcsc

Instance zjcsc1 is running on node wtdb1

Instance zjcsc2 is running on node wtdb2

Make sure that each instance is running properly

Start shutting down instance 2

[oracle@wtdb2] $srvctl stop instance-d zjcsc-I zjcsc2

Check the instance

[oracle@wtdb2] $srvctl status database-d zjcsc

Instance zjcsc1 is running on node wtdb1

Instance zjcsc2 is not running on node wtdb2

Make sure instance 2 is closed

Business is normal when determining a database instance

Start the database to mount status on db2

[oracle@wtdb2] srvctl start instance-d zjcsc-I zjcsc2-o mount

Modify the sga_max_size of instance 2 to 8G

Modify the sga_target of db2 to 5G

[oracle@wtdb2 ~] $sqlplus / as sysdba

ALTER SYSTEM SET sga_max_size = 8589934592 COMMENT='internally adjusted' SCOPE=SPFILE SID='zjcsc2'

System altered.

ALTER SYSTEM SET sga_target = 5368709120 SCOPE=SPFILE SID='zjcsc2'

System altered.

Close instance 2

Srvctl stop instance-d zjcsc-I zjcsc1

Start instance 2

Srvctl start instance-d zjcsc-I zjcsc2 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 successfully

B. Modify the SGA of instance 1

Close instance 1

[oracle@wtdb1] $srvctl stop instance-d zjcsc-I zjcsc1

Check instance status

[oracle@wtdb1] $srvctl status database-d zjcsc

Instance zjcsc1 is not running on node wtdb1

Instance zjcsc2 is running on node wtdb2

Make sure that instance 1 is closed

Start the database to mount status on db1

Srvctl start instance-d zjcsc-I zjcsc1-o mount

Modify the sga_max_size of instance 1 to 8G

Modify the sga_target of db2 to 5G

[oracle@wtdb2 ~] $sqlplus / as sysdba

ALTER SYSTEM SET sga_max_size = 8589934592 COMMENT='internally adjusted' SCOPE=SPFILE SID='zjcsc1'

System altered.

ALTER SYSTEM SET sga_target = 5368709120 SCOPE=SPFILE SID='zjcsc1'

System altered.

Close instance 1

Srvctl stop instance-d zjcsc-I zjcsc1

Start instance 1

Srvctl start instance-d zjcsc-I zjcsc1-o open

View the SGA information of instance 1

[oracle@wtdb1 ~] $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 successfully

Check database status

[oracle@wtdb1] $srvctl status database-d zjcsc

Instance zjcsc1 is running on node wtdb1

Instance zjcsc2 is running on node wtdb2

Ok is all normal.

This is the end of the answer to the question on how to adjust the ORACLE RAC SGA parameters. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.