LOCK_SGA and ASMM

With the introduction of AMM or ASMM as of Oracle 10g the option to lock the SGA into memory has become obsolete. Creating a database using dbca, especially with Oracle 11g, will turn on ASMM. 

By default lock_sga is set to FALSE.

If one favors the use of LOCK_SGA, AMM needs to be disabled. 

To do this, both MEMORY_TARGET & MEMORY_MAX_TARGET needs to be removed from the init- or spfile. 

 For databases started with a plain-text init.ora file, edit the file and simply remove the lines with these two variables. 

If either one does not exist in the file, it doesn't matter, just remove the existing ones. 

Now add a value for SGA_TARGET, and optionally minimum values for the different buffers. 

Finally set LOCK_SGA to TRUE. 

Save the file and bounce the database. 

For databases started with a spfile, follow the below instructions. First an example of how settings can look:

SQL> show parameter memory_target 

NAME                                 TYPE        VALUE 

------------------------------------ ----------- ------------------------------ memory_target                        big integer 4544M 

SQL> show parameter memory_max_target 

NAME                                 TYPE        VALUE 

------------------------------------ ----------- ------------------------------ memory_max_target                    big integer 4544M 

SQL> show parameter sga_target 

NAME                                 TYPE        VALUE 

------------------------------------ ----------- ------------------------------ sga_target                           big integer 0 

MEMORY_TARGET is set. MEMORY_MAX_TARGET also. 

This parameter most of time calculates or inherits its value from MEMORY_TARGET. 

SGA_TARGET is set to zero. 

Now execute the following: 

SQL> alter system reset memory_target scope=spfile sid='*'; 

 System altered. 

SQL> alter system reset memory_max_target scope=spfile sid='*'; 

alter system reset memory_max_target scope=spfile sid='*' * 

ERROR at line 1: ORA-32010: cannot find entry to delete in SPFILE 

Most of the times there is no entry for MEMORY_MAX_TARGET in the spfile 

SQL> alter system set sga_target=4544M scope=spfile ; 

System altered. 

SQL> alter system set lock_sga=true scope=spfile; 

System altered. 

Now bounce the database. 

Please note: It might be necessary for RAC systems to repeat the 'reset' statements specifically for each different instance name.