This is the 21st day of my participation in the August More Text Challenge

introduce

At 10G, Oracle introduced Automatic Shared Memory Management (ASMM), which implements self-tuning of the internal structure of Oracle SGA and PGA. After 11G, Automatic Memory Management (AMM) implements the parameter MEMORY_TARGET, integrating SGA and PGA planning.

By default, Oracle 11g uses AMM. During installation, we specify the percentage of memory used by Oracle. This value is used as an initial value for MEMORY_TARGET and MEMORY_MAX_TARGET. If these two parameters are set to non-zero values, Then Oracle adopts the AMM management policy.

Also, if we set these two parameters to 0, the AMM shuts down automatically. If the values of SGA_TARGET and PGA_AGGREGATE_TARGET are not zero, Oracle automatically degrades to use THE ASMM feature.

The environment

We chose 11.2.0.3 for the test, and the current state is ASMM.

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE        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 0
memory_target                        big integer 0
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 108M
sga_target                           big integer 252M
Copy the code

MEMORY_TARGET is currently set to zero and AMM is not enabled.

Switch from ASMM to AMM

- parameter file backup SQL > create pfile = '/ home/oracle/pfile20210821 ora' from spfile. SQL> alter system set memory_max_target=360m scope=spfile; System altered SQL> alter system set memory_target=360m scope=spfile; System altered SQL> alter system set sga_target=0m scope=spfile; System altered SQL> alter system set sga_max_size=0 scope=spfile; System altered SQL> alter system set pga_aggregate_target=0 scope=spfile; System ALTERED -- To restart the database to take effect SQL> conn/as sysdba Connected. SQL> startup force ORACLE instance started Global Area 263651328 bytes Fixed Size 1344284 bytes Variable Size 176164068 bytes Database Buffers 83886080 bytes Redo Buffers 2256896 bytes Database mounted. Database opened. 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 360M memory_target big integer 360M parallel_servers_target integer 16 pga_aggregate_target big integer 0 sga_target big integer 0Copy the code

Switch from AMM to ASMM

- parameter file backup SQL > create pfile = '/ home/oracle/pfile20210821 ora' from spfile. SQL> alter system set memory_max_target=0 scope=spfile; System altered SQL> alter system set memory_target=0 scope=spfile; System altered SQL> alter system set pga_aggregate_target=100m scope=spfile; System altered SQL> alter system set sga_target=260m scope=spfile; System altered SQL> alter system set sga_max_size=260m scope=spfile; System ALTERED -- Modify the pfile file and start the database with the altered file (the reason for this step is that the MEMORY_MAX_TARGET "displayed" assignment conflicts with the SGA_TARGET assignment and an error will be reported when the database is started). SQL> create pfile from spfile; Db_recovery_file_dest ='/u01/app/fast_recovery_area' *. Db_recovery_file_dest_size =10737418240 *.diagnostic_dest='/u01/app' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)' *.log_checkpoints_to_alert=TRUE Memory_max_target =0 *. Memory_target =0 *. Open_cursors =300 *. Pga_aggregate_target =104857600 *. Processes =150 This parameter is deleted Memory_max_target, memory_target parameters) *. Db_recovery_file_dest ='/u01/app/fast_recovery_area' *.db_recovery_file_dest_size=10737418240 *.diagnostic_dest='/u01/app' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'  *.log_checkpoints_to_alert=TRUE *.open_cursors=300 *.pga_aggregate_target=104857600 *.processes=150 *. Remote_login_passwordfile ='EXCLUSIVE' Start database with pfile and rebuild SPfile. SQL> conn / as sysdba Connected to an idle instance. SQL> startup pfile=/u01/app/oracle/dbs/initora11g.ora ORACLE instance started. Total System Global Area 272011264 bytes Fixed Size 1344372 bytes Variable Size 176163980 bytes Database Buffers 88080384 bytes Redo Buffers 6422528 bytes Database mounted. Database opened. 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 0 memory_target big integer 0 parallel_servers_target integer 16 pga_aggregate_target big Integer 100M SGA_target BIG INTEGER 260M -- Restores the SPfile and starts the database. SQL> create spfile from pfile; File created. SQL> startup force ORACLE instance started. Total System Global Area 272011264 bytes Fixed Size 1344372 bytes Variable Size 176163980 bytes Database Buffers 88080384 bytes Redo Buffers 6422528 bytes Database mounted. Database opened.Copy the code