2

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?

Improve this question
5
  • 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.
    – ibre5041
    Oct 7, 2013 at 8:12
  • @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)
    – user1822
    Oct 7, 2013 at 8:33
  • 1
    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.
    – ibre5041
    Oct 7, 2013 at 9:13
  • @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.
    – user1822
    Oct 7, 2013 at 9:30
  • 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'
    – ibre5041
    Oct 7, 2013 at 9:41

1 Answer 1

Reset to default
3

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';
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.