oracle 10g rac 修改sga_target不生效,oracle 10g rac调整sga

双节点的10g rac需要把sga调大，之前使用是默认的sga大小。两个节点都是32G内存。

内核参数已经设置的足够大了，不需要调整

# Controls the use of TCP syncookies

net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes

kernel.msgmnb = 65536

net.core.rmem_default = 1048576

# Controls the default maxmimum size of a mesage queue

kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

kernel.shmmax = 68719476736 #这个内核参数只要设置到17179869184也就是16G就可以了，不过大一点好像对系统也没影响

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 4294967296

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 1048576

net.core.rmem_max = 1048576

net.core.wmem_default = 262144

net.core.wmem_default = 2622144

调整sga很简单

[oracle@RAC1 ~]$ srvctl stop instance -d racdb -i racdb1

[oracle@RAC1 ~]$ srvctl start instance -d racdb -i racdb1

[oracle@RAC1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jun 21 13:39:32 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> conn /as sysdba

????

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

RACDB1

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

RACDB1 OPEN

SQL> exit

? Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options ??

[oracle@RAC1 ~]$ srvctl stop instance -d racdb -i racdb1

[oracle@RAC1 ~]$ srvctl start instance -d racdb -i racdb1 -o mount

[oracle@RAC1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jun 21 13:43:02 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

???:

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

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> ALTER SYSTEM SET sga_max_size = 17179869184 COMMENT='internally adjusted' SCOPE=SPFILE SID='RACDB1';

System altered.

SQL> ALTER SYSTEM SET sga_target = 17179869184 SCOPE=SPFILE SID='RACDB1';

System altered.

SQL> exit

? Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options ??

[oracle@RAC1 ~]$ srvctl stop instance -d racdb -i racdb1

[oracle@RAC1 ~]$ srvctl start instance -d racdb -i racdb1

[oracle@RAC1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jun 21 13:44:24 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

???:

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

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> show parameter sga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 16G

sga_target big integer 16G

在另一个节点也执行类似的操作就OK了。

不过有一点需要注意的。就是这两条语句：

ALTER SYSTEM SET sga_max_size = 17179869184 COMMENT='internally adjusted' SCOPE=SPFILE SID='RACDB1';

ALTER SYSTEM SET sga_target = 17179869184 SCOPE=SPFILE SID='RACDB1';

后面的SID都必须是大写的，如果写成racdb1的话，执行不会报错，但是会没有任何效果的。