ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account ORA-00849: SGA_TARGET 838860800 cannot be set to more than MEMORY_MAX_TARGET 0.

I encountered this error when switching from AMM to ASMM.

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL> alter system set sga_target=800M scope=spfile;

System altered.

SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=200M scope=spfile;

System altered.

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

System altered.

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

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 838860800 cannot be set to more than MEMORY_MAX_TARGET 0.
SQL> alter system reset memory_max_target scope=spfile;
alter system reset memory_max_target scope=spfile
*

Workaround: Create pfile from spfile, edit pfile and remove lines with memory_target and memory_max_target, and then recreate spfile from pfile

SQL> create pfile from spfile;

File created.

SQL> startup pfile=e:\app\oracle\product\11.2.0\dbhome_1\database\INITorcl.ORA
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             230687912 bytes
Database Buffers          595591168 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL> 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
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             230687912 bytes
Database Buffers          595591168 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL> show parameter sga

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 200M
SQL>

Correct implementation:

SQL> show parameter memory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL>
SQL>
SQL>
SQL> alter system set sga_target=800M scope=spfile;

System altered.

SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=200M scope=spfile;

System altered.

SQL> alter system reset memory_target scope=spfile;

System altered.


SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             171967656 bytes
Database Buffers          654311424 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL>