关于ASM中AMM参数说明

概括

Oracle11gR2 RAC中ocr、voting盘也加入到了ASM中，这也更方便管理，在ASM实例中新功能AMM更是oracle强烈推荐使用的，本以为它的设置会数据库的一样，却在查看、修改中发现许多未解之谜
过程描述
本人是在自己本上搭建的虚拟环境，在搭建完成后，发现系统内存有些告急，修改完数据库内存等相关参数后，发现ASM的memory值也不小，如下：

SQL> show parameter mem

NAME TYPE VALUE

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

memory_max_target big integer 1076M

memory_target big integer 1076M

查看SGA、PGA

SQL> show parameter sga

NAME TYPE VALUE

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

lock_sga boolean FALSE

sga_max_size big integer 256M

sga_target big integer 256M

SQL> show parameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 10M

禁用AMM管理

SQL> alter system reset memory_max_target;

alter system reset memory_max_target

*

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

SQL> alter system reset memory_target;

alter system reset memory_target

*

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

生成PFILE文件，并查看，发现参数文件中并没有memory两个参数

SQL> create pfile='/tmp/a.ora' from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

[grid@rac01 ~]$ cat /tmp/a.ora

+ASM1.asm_diskgroups='DATA'#Manual Mount

+ASM2.asm_diskgroups='DATA'#Manual Mount

*.asm_diskstring='/dev/asm*'

*.asm_power_limit=1

*.diagnostic_dest='/grid/crs_base'

*.instance_type='asm'

*.large_pool_size=12M

*.pga_aggregate_target=10485760

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=268435456

*.sga_target=268435456

再次修改两个参数

SQL> alter system set memory_max_target=1G scope=spfile;

System altered.

SQL> alter system set memory_target=1G scope=spfile;

System altered.

重启ASM（注意，因orc存在与ASM中，所以重启需root用户）

命令crsctl stop/start crs/cluster

重启后，再次查看

SQL> show parameter mem

NAME TYPE VALUE

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

memory_max_target big integer 1G

memory_target big integer 1G

再次设置

SQL> show parameter mem

NAME TYPE VALUE

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

memory_max_target big integer 1G

memory_target big integer 1G

SQL> alter system set memory_max_target=256M scope=spfile;

System altered.

SQL> alter system set memory_target=256M scope=spfile;

System altered.

重启后查看

SQL> show parameter mem

NAME TYPE VALUE

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

memory_max_target big integer 1G

memory_target big integer 1G

发现参数未变，而生成PFILE文件查看，两个参数均为256M

查看alert日志,发现警告，memory_target最小值及sga太小。

WARNING: Minimum MEMORY_TARGET required on ASM instance is 1073741824

* instance_number obtained from CSS = 1, checking for the existence of node 0...

* node 0 does not exist. instance_number = 1

Starting ORACLE instance (normal)

Specified value of sga_max_size is too small, bumping to 641728512

LICENSE_MAX_SESSION = 0

……………….

ORACLE_HOME = /grid/crs_home/product/11.2.0

System name: Linux

Node name: rac01

Release: 2.6.32-279.el6.x86_64

Version: #1 SMP Wed Jun 13 18:24:36 EDT 2012

Machine: x86_64

VM name: VMWare Version: 6

Using parameter settings in server-side spfile +CRS/rac-cluster/asmparameterfile/registry.253.839461197

System parameters with non-default values:

sga_max_size = 612M

large_pool_size = 12M

instance_type = "asm"

sga_target = 0

memory_target = 1G

memory_max_target = 1G

remote_login_passwordfile= "EXCLUSIVE"

pga_aggregate_target = 0

asm_diskstring = "/dev/asm*"

asm_diskgroups = "DATA"

asm_power_limit = 1

diagnostic_dest = "/grid/crs_base"

如果将sga两个参数及pga参数reset，重启，不会警告sga太小，重启后参数值如下：

SQL> show parameter mem

NAME TYPE VALUE

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

memory_max_target big integer 1G

memory_target big integer 1G

SQL> show parameter sga

NAME TYPE VALUE

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

lock_sga boolean FALSE

sga_max_size big integer 1G

sga_target big integer 0

SQL> show parameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 0

再次禁用AMM，AMM管理参数是memory_target,而memory_max_target是作为一个上限来约束前者的。

设置好SGA、PGA

SQL> show parameter sga

NAME TYPE VALUE

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

lock_sga boolean FALSE

sga_max_size big integer 208M

sga_target big integer 208M

SQL> alter system set memory_target=0 scope=spfile;

System altered.

SQL> show parameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 0

SQL> alter system set pga_aggregate_target=10M;

System altered.

查看修改后PFILE文件

SQL> create pfile='/tmp/asm.ora' from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

[grid@rac01 trace]$ vi /tmp/asm.ora

+ASM1.asm_diskgroups='DATA'#Manual Mount

+ASM2.asm_diskgroups='DATA'#Manual Mount

*.asm_diskstring='/dev/asm*'

*.asm_power_limit=1

*.diagnostic_dest='/grid/crs_base'

*.instance_type='asm'

*.large_pool_size=12M

*.memory_max_target=268435456

*.memory_target=0

*.pga_aggregate_target=10485760

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=209715200

*.sga_target=209715200

重启集群，查看mem参数

SQL> show parameter mem

NAME TYPE VALUE

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

memory_max_target big integer 256M

memory_target big integer 0

SQL> show parameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 10M

SQL> show parameter sga

NAME TYPE VALUE

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

lock_sga boolean FALSE

sga_max_size big integer 208M

sga_target big integer 208M

这次看着好像正常点了，前边我们是将memory_target设置为0来取消AMM，那么将两个memory参数都reset后，系统会启用1076M这个值，假如两个参数都设置为0呢个，这个我们将在下一节讨论。

现在我们是禁用了AMM管理，却有几个疑问：

1、但是为什么需要设置memory_max_target这个参数呢，
1. 官方说明，memory_target最小值应为256M，但是设置后为什么不生效，
2. 起初SPFILE参数中并没有设置MEMORY两个参数，ASM是怎么得到的
  
  在其他redhat平台下查看相同版本数据库ASM中缺省memory值也为1076M，AIX/HPUX平台各不一样。
附：官方关于ASM中MEMORY参数说明
主要有两点，一个是oracle强烈建议使用AMM、二是memory_target最小值为256M

还说明一个是AMM在ASM中默认启用

Automatic Memory Management for Oracle ASM

Automatic memory management automatically manages the memory-related parameters for both Oracle ASM and database instances with the MEMORY_TARGETparameter. Automatic memory management is enabled by default on an Oracle ASM instance, even when the MEMORY_TARGET parameter is not explicitly set. The default value used for MEMORY_TARGET is acceptable for most environments. This is the only parameter that you must set for complete Oracle ASM memory management. Oracle strongly recommends that you use automatic memory management for Oracle ASM.

If you do not set a value for MEMORY_TARGET, but you do set values for other memory related parameters, Oracle internally calculates the optimum value forMEMORY_TARGET based on those memory parameter values. You can also increase MEMORY_TARGET dynamically, up to the value of the MEMORY_MAX_TARGET parameter, just as you can do for the database instance.

Although it is not recommended, you can disable automatic memory management by either setting the value for MEMORY_TARGET to 0 in the Oracle ASM parameter file or by running an ALTER SYSTEM SET MEMORY_TARGET=0 statement. When you disable automatic memory management, Oracle reverts to auto shared memory management and automatic PGA memory management. To revert to Oracle Database 10g release 2 (10.2) functionality to manually manage Oracle ASM SGA memory, also run the ALTER SYSTEM SET SGA_TARGET=0 statement. You can then manually manage Oracle ASM memory using the information in "Oracle ASM Parameter Setting Recommendations", that discusses Oracle ASM memory-based parameter settings. Unless specified, the behaviors of the automatic memory management parameters in Oracle ASM instances behave the same as in Oracle Database instances.

Notes:

For a Linux environment, automatic memory management cannot work if /dev/shm is not available or is undersized. For more information, see Oracle Database Administrator's Reference for Linux and UNIX-Based Operating Systems. For information about platforms that support automatic memory management, see Oracle Database Administrator's Guide.
The minimum MEMORY_TARGET for Oracle ASM is 256 MB. If you set MEMORY_TARGET to 100 MB, then Oracle increases the value for MEMORY_TARGET to 256 MB automatically.

ITPub博客

【AMM】关于ASM中AMM参数说明 xysoul