Cannot set MEMORY_TARGET for Oracle 11.2

memoryoracle

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?

Best Answer

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';
Related Question