二、实现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
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=inityzxd.ora
ORACLE instance started.
ORACLE instance started.