Cannot set MEMORY_TARGET for Oracle 11.2

Question

I'm trying to configure an Oracle installation on a developer machine (Windows 7 Professional 64bit).

The machine has 16GB of RAM and I'd like to configure MEMORY_MAX_TARGET to 8g but have MEMORY_TARGET set to 4g by default because that is enough for most of the work done on that machine. But in case I need more memory I can dynamically resize it.

However Oracle won't let me set MEMORY_TARGET to 4G:

Here is my current configuration:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 8G
memory_target big integer 0
parallel_servers_target integer 64
pga_aggregate_target big integer 250M
sga_target big integer 0
SQL> show parameter policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
workarea_size_policy string AUTO

Now when I try to run

SQL> ALTER SYSTEM SET MEMORY_TARGET=4G scope=both;
ALTER SYSTEM SET MEMORY_TARGET=4G scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least
6432M

I don't understand why MEMORY_TARGET can't be set to 4G? From all I know, it cannot exceed memory_max_target, but 4G is clearly less than 8G (and where do the 6432M come from?)

According to the manual this should be possible. Additionally I wasn't able to set PGA_AGGREGATE_TARGET to zero though - even if the manual claims that this is possible.

What am I missing here?

• ibre5041: There can be various reasons. For example missing huge(large) pages on OS level. Check alert.log, maybe you will find some OS level error there. I recall in 11gR1 it was not possible simply because there was a bug in Oracle, they never tested this option with more than 2GB of RAM.
• user1822: @Ivan: I can set memory_max_target to 8GB without problems, I just can not set memory_target to anything less than that (and I'm using 11gR2)
• ibre5041: Note that SGA/PGA target as well as other parameters are not turned off when using memory_target. They still represent a "minimal" value for the parameter. So even if you use automatic memory management you should assign at least 40% of RAM to memory regions manually. Try to check paramters like sort area size, buffer cache, sga, pga. If these values exceed 4GB then you have an explanation.
• user1822: @Ivan: sga_target is set to 0 (as you can see from the output) as for pga_aggregate_target I cannot set that to 0 (in contrast to the manual) to enable automatic memory management.
• ibre5041: and what parameters like sort_area_size, sort, sorm_area_retained_size, db_cache_size and all the db_chache paremeters? Try to create pfile from spfile, the delete all the parameters you don't need and try to start the database using that pfile. startup pfile='/tmp/pfile.ora'

Answer 1

First look what other parameters are set:

CREATE pfile='pfile.txt' FROM spfile;

Maybe db_cache_size, shared_pool_size, sga_target or other memory related parameters are set to non zero? Remember that when using AMM those parameters specify minimum memory allocated for particular pool. So if sga_target is 6GB you will not be allowed to set memory_target to 4GB.

Also sum of internal variables __sga_target, __db_cache_size, etc. may be more than your specified value of 4GB.

If you see those symptoms you can cleanup pfile bounce Oracle with pfile and recreate spfile. In the same step you can also set PGA_AGGREGATE_TARGET to zero.

STARTUP pfile='pfile.txt'
CREATE spfile from pfile='pfile.txt';