Oracle ASMM 与AMM之间相互切换

91次阅读

共计 7555 个字符，预计需要花费 19 分钟才能阅读完成。

AMM (Automatic Memory Management)自动内存管理，它是通过设置 MEMORY_TARGET 和 MEMORY_MAX_TARGET 控制的，使用在 Oracle 11g release。
ASMM(Automatic shared Memory Management）自动共享内存管理，它是通过设置 SGA_TARGET 不为 0，同时需要参数 STATISTICS_LEVEL 为 TYPICAL 或 ALL 实现，使用在 oracle 10g 中。
通过实验简单的学习一下 oracle 的 AMM 和 ASMM 之间特征以及它们之间相互切换的方式

一、oracle 采用 ASMM 形式显著特征就是 SGA_TARGET 和 PGA_AGGREGATE_TARGET 不为 0，而 MEMORY_TARGET 和 MEMORY_MAX_TARGET 为 0

[oracle@mhxy171 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 29 10:12:56 2016

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter target

NAME TYPE VALUE
———————————— ———————- ——————————
archive_lag_target integer 0
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 32
pga_aggregate_target big integer 383M
sga_target big integer 1152M

通过改变一下静态参数的值，把 oracle DB 把 ASMM 修改为 AMM，修改之前备份下 spfile 文件。重启 DB 实例，查看参数 memory_max_target 和 memory_target 不为 0，表示已经启动 AMM

点击 (此处) 折叠或打开

SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———————- ——————————
spfile string /oracle/database/product/11.2.
0.4.0/db_1/dbs/spfileyzxd.ora

SQL> create pfile from spfile;

File created.

SQL> alter system set memory_max_target=1535m scope=spfile;

System altered.

SQL>alter system set memory_target=1535m scope=spfile;

System altered.

SQL> alter system set sga_target=0m scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=0m scope=spfile;

System altered.

SQL> alter system set sga_max_size=0m scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1202614272 bytes
Fixed Size 2261248 bytes
Variable Size 360713984 bytes
Database Buffers 834666496 bytes
Redo Buffers 4972544 bytes
Database mounted.
Database opened.
SQL> show parameter target

NAME TYPE VALUE
———————————— ———————- ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 1536M
memory_target big integer 1536M
parallel_servers_target integer 32
pga_aggregate_target big integer 0
sga_target big integer 0

二、实现 oracle 11g 从 AMM 到 ASMM 的切换。关闭 AMM，需要把 memory_max_target 和 memory_target 设置为 0。
在从 AMM 切换到 ASMM 的过程需要通过 pfile 文件启动，并且把文件中的 memory_max_target 和 memory_target 删除。在默认的 pfile 文件，发现这两个参数值还是 0，表示 AMM 内存管理。
以下是从 AMM 切换到 ASMM 的操作步骤：1、修改参数值

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

System altered.

SQL> alter system set memory_max_target=0m scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=350m scope=spfile;

System altered.

SQL> alter system set sga_target=1186m scope=spfile;

System altered.

SQL> alter system set sga_max_size=1186m scope=spfile;

System altered.

在直接通过 spfile 启动数据库会提示以下错误，看来是内存参数值冲突

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 1258291200 cannot be set to more than MEMORY_MAX_TARGET 0.
SQL> startup nomount
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 1258291200 cannot be set to more than MEMORY_MAX_TARGET 0.
SQL> create pfile from spfile;
点击 (此处) 折叠或打开

[oracle@mhxy171 ~]$ oerr ora 00843
00843, 00000, “Parameter not taking MEMORY_MAX_TARGET into account”
// *Cause: The parameter was larger than MEMORY_MAX_TARGET.
// *Action: Check the other error messages for additional information about the parameter. Set the parameter to a lower value than MEMORY_MAX_TARGET.
[oracle@mhxy171 ~]$
[oracle@mhxy171 ~]$ oerr ora 00849
00849, 00000, “SGA_TARGET %s cannot be set to more than MEMORY_MAX_TARGET %s.”
// *Cause: SGA_TARGET value was more than MEMORY_MAX_TARGET value.
// *Action: Set SGA_TARGET to be less than MEMORY_MAX_TARGET.
[oracle@mhxy171 ~]$

2、通过修改 pfile 文件中参数值，使用 pfile 文件启动。

SQL> create pfile from spfile; #通过 spfile 生成最新的 pfile 文件

File created.

[oracle@mhxy171 ~]$ cd $ORACLE_HOME

[oracle@mhxy171 dbs]$ cp inityzxd.ora inityzxd.ora_bak #备份 pfile 文件
[oracle@mhxy171 dbs]$ vi inityzxd.ora
*.memory_max_target=0
*.memory_target=0
yzxd.__db_cache_size=897581056
yzxd.__java_pool_size=4194304
yzxd.__large_pool_size=8388608
yzxd.__oracle_base=’/oracle/database’#ORACLE_BASE set from environment
yzxd.__pga_aggregate_target=402653184
yzxd.__sga_target=1207959552
yzxd.__shared_io_pool_size=0
yzxd.__shared_pool_size=285212672
yzxd.__streams_pool_size=0
*.audit_file_dest=’/oracle/database/admin/yzxd/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4.0′
*.control_file_record_keep_time=20
*.control_files=’/oracle/database/oradata/yzxd/control01.ctl’,’/oracle/database/fast_recovery_area/yzxd/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’yzxd’
*.db_recovery_file_dest=’/oracle/database/fast_recovery_area’
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest=’/oracle/database’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=yzxdXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_max_target=0 #删除 memory_max_target 和 memory_target 参数
*.memory_target=0
*.open_cursors=300
*.pga_aggregate_target=367001600
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sessions=170
*.sga_max_size=1243611136
*.sga_target=1243611136
*.undo_tablespace=’UNDOTBS1′

3、通过 pfile 文件启动数据库，检查正确后生成最新的 spfile 文件

[oracle@mhxy171 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 29 11:12:14 2016

Connected to an idle instance.

SQL> startup pfile=inityzxd.ora
ORACLE instance started.

ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 402657256 bytes
Database Buffers 838860800 bytes
Redo Buffers 8892416 bytes
Database mounted.
Database opened.

SQL> show parameter target #是我们修改后的参数值

NAME TYPE VALUE
———————————— ———————- ——————————
archive_lag_target integer 0
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 32
pga_aggregate_target big integer 350M
sga_target big integer 1200M

SQL> create spfile from pfile;

File created.

SQL> startup force
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 402657256 bytes
Database Buffers 838860800 bytes
Redo Buffers 8892416 bytes
Database mounted.
Database opened.

SQL> show parameter target

NAME TYPE VALUE
———————————— ———————- ——————————
archive_lag_target integer 0
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 32
pga_aggregate_target big integer 350M
sga_target big integer 1200M

SQL>

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-02/140649.htm

正文完

星哥说事-微信公众号