重庆思庄Oracle、Redhat认证学习论坛

标题: ORA-00849: SGA_TARGET 2147483648 cannot be set to more than MEMORY_MAX_TARGET 0. [打印本页]

作者: denglj 时间: 2022-9-28 16:16
标题: ORA-00849: SGA_TARGET 2147483648 cannot be set to more than MEMORY_MAX_TARGET 0.
本帖最后由 denglj 于 2022-9-29 09:45 编辑

系统：windows2008 server r2 64位

数据库：oracle11.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;

altersystem 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

已连接到空闲例程.

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

> showparameter sga

NAME TYPE VALUE

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

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 2G

sga_target big integer 2G

> showparameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 1G

> showparameter 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.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)