修改数据库内存管理方式AMM为ASMM遭遇ORA-00843 ORA-00849

系统:windows 208" target="_blank">008 server r2 64位
数据库:oracle 11.2.0.4 64位
问题描述:将数据库内存管理方式由AMM修改为ASMM过程中,出现ORA-00843、ORA-00849告警,如下所示.
SQL> show parameter sga

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

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
pga_aggregate_target big integer 0
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1648M
memory_target big integer 1648M
shared_memory_address integer 0
sga_max_size
> alter system sga_max_size=2048m scope=spfile;
> alter system pga_aggregate_target=1024m scope=spfile;
> alter system memory_target =0 scope=spfile;
> alter system reset memory_max_target scope=spfile;
alter system reset memory_max_target scope=spfile
*
第 1 行出现错误:
ORA-32010: 无法在 SPFILE 中找到要删除的条目

> alter system set memory_max_target=0 scope=spfile;

系统已更改.

SQL> shutdown immediate;
SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 2147483648 cannot be set to more than MEMORY_MAX_TARGET 0.

此处异常肯定很多人都遇到过,处理过程如下:
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 9月 23 22:34:30 2022

Copyright (c) 1982, 2010, Oracle. All rights reserved.

已连接到空闲例程.

SQL> create pfile from spfile;

文件已创建.

系统层面手动删除pfile中memory_target和memory_max_target相关行.
然后用pfile启动数据库生成spfile,具体操作如下:
> startup nomount pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ora';
> alter database mount;
> create spfile from pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ora';
> shutdown immediate
> startup
> show parameter sga

NAME TYPE VALUE
------------------------------------ ---------------------- ---------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2G
sga_target big integer 2G
> show parameter pga

NAME TYPE VALUE
------------------------------------ ---------------------- ---------
pga_aggregate_target big integer 1G
> show parameter memory

NAME TYPE VALUE
------------------------------------ ---------------------- ---------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0

结论:数据库内存管理方式已成功修改为ASMM.
参考MOS【ID 1397761.1】
SYMPTOMS
When trying to set SGA_TARGET using an ALTER SYSTEM command, the following errors are rai by the MEMORY_MAX_TARGET parameter explicitly being set to 0. In case AMM should not be used, MEMORY_MAX_TARGET should not be set at all.

SOLUTION
The solutions to the problem are:
create a PFILE from the SPFILE being used and remove the MEMORY_MAX_TARGET=0 and MEMORY_TARGET=0 lines.
After that, use the modified PFILE to create a new SPFILE and start the instance with this new setup.should the instance be running, then use the following commands to remove the explicit setting of MEMORY_TARGET=0 and MEMORY_MAX_TARGET=0:
alter system reset memory_target;
alter system reset memory_max_target;