ORA-00849: SGA_TARGET cannot be set to more than MEMORY_MAX_TARGET

ORA-00849: SGA_TARGET string cannot be set to more than MEMORY_MAX_TARGET string.
Cause: SGA_TARGET value was more than MEMORY_MAX_TARGET value.
Action: Set SGA_TARGET to be less than MEMORY_MAX_TARGET.

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm

While reducing the size of "MEMORY_MAX_TARGET" and "MEMORY_TARGET", I encountered "ORA-00843" and "ORA-00849". In this post I have tried to provide the steps to reproduce "ORA-00843" and "ORA-00849" and the steps to solve both "ORA-00843" and "ORA-00849".

Problem:

C:\Users\nimish.garg>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 27 18:30:09 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> show sga
Total System Global Area 1.3469E+11 bytes
Fixed Size                  2271992 bytes
Variable Size            1.1274E+10 bytes
Database Buffers         1.2321E+11 bytes
Redo Buffers              199041024 bytes

SQL> show parameter memory
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 126G
memory_target                        big integer 0
shared_memory_address                integer     0

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 126G
sga_target                           big integer 125G

SQL> alter system set memory_max_target=32G scope=spfile;
System altered.

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 135291469824 cannot be set to more than MEMORY_MAX_TARGET 34359738368.

Solution:
To resolve "ORA-00843" and "ORA-00849", I created the "PFILE" from "SPFILE" and set both "MEMORY_MAX_TARGET" and "MEMORY_TARGET" to 34359738368 (32GB) and removed "PGA_AGGREGATE_TARGE", "SGA_MAX_SIZE" and "SGA_TARGET" from "PFILE". Then I backup my "SPFILE" by moving it to other location. Finally I recreated "SPFILE" from new "PFILE" and started the database.

SQL> create pfile = 'PFILEORCL.ORA' from spfile ='SPFILEORCL.ORA';
File created.

-- modify pfile as per your need using some editor, and remove your spfile

SQL> create spfile = 'SPFILEORCL.ORA' from pfile ='PFILEORCL.ORA';
File created.

SQL> STARTUP
ORACLE instance started.
Total System Global Area 3.4206E+10 bytes
Fixed Size                  2256912 bytes
Variable Size            1.9864E+10 bytes
Database Buffers         1.4227E+10 bytes
Redo Buffers              112771072 bytes
Database mounted.
Database opened.

Related Posts:
ORA-04031: unable to allocate n bytes of shared memory
- ORA-01157: cannot identify/lock data file string - see DBWR trace file
- ORA-27101: shared memory realm does not exist
- ORA-00600: internal error code
- ORA-00257: archiver error. Connect internal only, until freed
- ORA-16000: database open for read-only access