Oracle AMM与ASMM

1.场景分析

 

由于AMM不稳定，需要在停机窗口调整为ASMM，并且启用大页，接下来就给大家分享一下，什么是AMM与ASMM，以及如何去调整与监控。

1.1什么是AMM

The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. The maximum memory size serves as an upper limit so that you cannot accidentally set the target memory size too high, and so that enough memory is set aside for the Oracle Database instance in case you do want to increase total instance memory in the future. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting the target memory size too low.

If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. If you choose advanced installation, Database Configuration Assistant (DBCA) enables you to select automatic memory management.

AMM：automatic memory management(11g新加的特性），让资料库完全管理SGA与PGA的大小，对于DBA来说，只需要设置目标内存大小初始化参数（MEMORY_TARGET）和可选的最大内存大小初始化参数（MEMORY_MAX_TARGET）。然后，实例将调整为目标内存大小，并根据需要在系统全局区域（SGA）和实例程序全局区域（实例PGA）之间重新分配内存。由于目标内存初始化参数是动态的，因此可以随时更改目标内存大小，而无需重新启动数据库。最大内存大小是一个上限，因此不会意外地将目标内存大小设置得过高，并为Oracle数据库实例留出足够的内存，以防将来您想增加实例总内存。由于某些SGA组件不能轻易收缩或必须保持最小大小，因此该实例还阻止将目标内存大小设置得太小。

1.2 涉及参数

MEMORY_TARGET：动态控制SGA和PGA时Oracle可以使用的共享内存量。此参数是动态的，因此可以增加或减少Oracle可用的内存总量，前提是它不超过MEMORY_MAX_TARGET限制。默认值为“0”。
MEMORY_MAX_TARGET：这定义了MEMORY_TARGET在不重新启动实例的情况下可以增加的最大大小。如果MEMORY_MAX_TARGET未指定，则默认为MEMORY_TARGET设置。

官方文档中描述的是通过v$pgastat中选择值来进行分配  

memory_target = sga_target + max（pga_aggregate_target，分配的最大PGA）

1.3如何调整为AMM

以下是我在测试环境做的测试过程

测试环境信息：

操作系统：rhel7.6

数据库版本：11.2.0.4

数据库类型：RAC

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1840M
sga_target                           big integer 1840M
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 510M
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     900
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 510M
sga_target                           big integer 1840M

可以看到数据库是一个ASMM的内存管理方式，现在需要调整为AMM的管理方式。

1.设置memory_max_target与memory_target的值

SQL> alter system set memory_max_target=2G scope=spfile sid='*';

System altered.

修改完成之后

在文本初始化参数文件中，如果省略的行MEMORY_MAX_TARGET并包含的值MEMORY_TARGET，则数据库将自动设置MEMORY_MAX_TARGET为的值MEMORY_TARGET。如果省略的行MEMORY_TARGET并包含的值MEMORY_MAX_TARGET，则MEMORY_TARGET参数默认为零。启动后MEMORY_TARGET，只要不超过的值，就可以动态更改为非零值MEMORY_MAX_TARGET。

 

SQL> alter system set memory_target=2G scope=spfile sid='*';

System altered.

重启实例

2.调整sga_target与pga_aggregate_target

SQL> alter system set pga_aggregate_target=0M scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=0M scope=spfile sid='*';

System altered.

 

srvctl stop database -d peiyang

会发现存在报错

[oracle@peiyang01 ~]$ srvctl start database -d peiyang
PRCR-1079 : Failed to start resource ora.peiyang.db
CRS-5017: The resource action "ora.peiyang.db start" encountered the following error:
ORA-00845: MEMORY_TARGET not supported on this system
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/peiyang02/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-5017: The resource action "ora.peiyang.db start" encountered the following error:
ORA-00845: MEMORY_TARGET not supported on this system
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/peiyang01/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.peiyang.db' on 'peiyang02' failed
CRS-2674: Start of 'ora.peiyang.db' on 'peiyang01' failed
CRS-2632: There are no more servers to try to place resource 'ora.peiyang.db' on that would satisfy its placement policy

为啥报错啦，因为这个特性需要用到/dev/shm共享文件系统，而且要求/dev/shm必须大于MEMORY_TARGET，如果/dev/shm比MEMORY_TARGET小，就会报错。

ORA-00845: MEMORY_TARGET not supported on this system

[oracle@peiyang01 ~]$ df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 2.0G 116M 1.9G 6% /dev/shm

处理方式

[root@peiyang01 ~]# umount tmpfs
[root@peiyang01 ~]# mount -t tmpfs shmfs -o size=2250m /dev/shm
[root@peiyang01 ~]# su - grid
Last login: Wed Jan 20 14:28:59 CST 2021 on pts/0
[grid@peiyang01 ~]$ srvctl start database -d peiyang
PRCR-1079 : Failed to start resource ora.peiyang.db
CRS-5017: The resource action "ora.peiyang.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/peiyang/spfilepeiyang.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/peiyang/spfilepeiyang.ora
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/peiyang01/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-5017: The resource action "ora.peiyang.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/peiyang/spfilepeiyang.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/peiyang/spfilepeiyang.ora
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/peiyang02/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.peiyang.db' on 'peiyang01' failed
CRS-2674: Start of 'ora.peiyang.db' on 'peiyang02' failed
CRS-2632: There are no more servers to try to place resource 'ora.peiyang.db' on that would satisfy its placement policy

这个很明显是参数文件有问题。