Search this Blog

Tuesday, June 8, 2010

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, ASMM 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.

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.

No comments:

Post a Comment