ORACLE ASMM 与AMM之间相互切换

AMM (Automatic Memory Management)自动内存管理 ， 它是通过设置MEMORY_TARGET和MEMORY_MAX_TARGET控制的，使用在oracle 11g release。
ASMM(Automatic shared Memory Management）自动共享内存管理，它是通过设置SGA_TARGET不为0，同时需要参数STATISTICS_LEVEL为TYPICAL 或ALL 实现，使用在oracle 10g中。
通过实验简单的学习一下oracle的AMM和ASMM之间特征以及它们之间相互切换的方式
一、oracle 采用 ASMM 形式显著特征就是SGA_TARGET和PGA_AGGREGATE_TARGET不为0 ，而MEMORY_TARGET和MEMORY_MAX_TARGET 为0 

点击(此处)折叠或打开

1. [oracle@mhxy171 ~]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 29 10:12:56 2016
   
4. 
   
5. Copyright (c) 1982, 2013, Oracle. All rights reserved.
   
6. 
   
7. 
   
8. Connected to:
   
9. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
11. SQL> show parameter target
    
12. 
    
13. 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                32
    pga_aggregate_target                 big integer            383M
    sga_target                           big integer            1152M
    
14. 
    

通过改变一下静态参数的值，把oracle DB 把ASMM修改为AMM，修改之前备份下spfile文件。重启DB实例，查看参数 memory_max_target和memory_target 不为0，表示已经启动AMM

点击(此处)折叠或打开

1. SQL> show parameter spfile;
   
2. 
   
3. NAME                                 TYPE                   VALUE
   ------------------------------------ ---------------------- ------------------------------
   spfile                               string                 /oracle/database/product/11.2.
                                                               0.4.0/db_1/dbs/spfileyzxd.ora
   
4. 
   
5. SQL> create pfile from spfile;
   
6. 
   
7. File created.
   
8. 
   
9. SQL> alter system set memory_max_target=1535m scope=spfile;
   
10. 
    
11. System altered.
    
12. 
    
13. SQL> alter system set memory_target=1535m scope=spfile;
14. 
    
15. System altered.
    
16. 
    
17. SQL> alter system set sga_target=0m scope=spfile;
    
18. 
    
19. System altered.
    
20. 
    
21. SQL> alter system set pga_aggregate_target=0m scope=spfile;
    
22. 
    
23. System altered.
    
24. 
    
25. SQL> alter system set sga_max_size=0m scope=spfile;
    
26. 
    
27. System altered.
    
28. 
    
29. SQL> startup force
    
30. ORACLE instance started.
    
31. 
    
32. Total System Global Area 1202614272 bytes
    
33. Fixed Size 2261248 bytes
    
34. Variable Size 360713984 bytes
    
35. Database Buffers 834666496 bytes
    
36. Redo Buffers 4972544 bytes
    
37. Database mounted.
    
38. Database opened.
    
39. SQL> show parameter target
    
40. 
    
41. 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            1536M
    memory_target                        big integer            1536M
    parallel_servers_target              integer                32
    pga_aggregate_target                 big integer            0
    sga_target                           big integer            0
    

二、实现oracle 11g 从AMM到ASMM的切换。关闭AMM ，需要把 memory_max_target和 memory_target设置为0。
在从AMM切换到ASMM的过程需要通过pfile文件启动，并且把文件中的memory_max_target和memory_target删除。在默认的pfile文件，发现这两个参数值还是0，表示AMM内存管理。
以下是从AMM切换到ASMM的操作步骤：1、修改参数值

点击(此处)折叠或打开

1. SQL> alter system set memory_target=0m scope=spfile;
   
2. 
   
3. System altered.
   
4. 
   
5. SQL> alter system set memory_max_target=0m scope=spfile;
   
6. 
   
7. System altered.
   
8. 
   
9. SQL> alter system set pga_aggregate_target=350m scope=spfile;
   
10. 
    
11. System altered.
    
12. 
    
13. SQL> alter system set sga_target=1186m scope=spfile;
    
14. 
    
15. System altered.
16. 
    
17. SQL> alter system set sga_max_size=1186m scope=spfile;
    
18. 
    
19. System altered.

 在 直接通过spfile启动数据库会提示以下错误，看来是内存参数值冲突

点击(此处)折叠或打开

1. SQL> shutdown immediate
   
2. Database closed.
   
3. Database dismounted.
   
4. ORACLE instance shut down.
   
5. SQL> startup mount
   
6. ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
   
7. ORA-00849: SGA_TARGET 1258291200 cannot be set to more than MEMORY_MAX_TARGET 0.
   
8. SQL> startup nomount
   
9. ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
   
10. ORA-00849: SGA_TARGET 1258291200 cannot be set to more than MEMORY_MAX_TARGET 0.
    
11. SQL> create pfile from spfile;

点击(此处)折叠或打开

1. [oracle@mhxy171 ~]$ oerr ora 00843
2. 00843, 00000, "Parameter not taking MEMORY_MAX_TARGET into account"
3. // *Cause: The parameter was larger than MEMORY_MAX_TARGET.
4. // *Action: Check the other error messages for additional information about the parameter. Set the parameter to a lower value than MEMORY_MAX_TARGET.
5. [oracle@mhxy171 ~]$
6. [oracle@mhxy171 ~]$ oerr ora 00849
7. 00849, 00000, "SGA_TARGET %s cannot be set to more than MEMORY_MAX_TARGET %s."
8. // *Cause: SGA_TARGET value was more than MEMORY_MAX_TARGET value.
9. // *Action: Set SGA_TARGET to be less than MEMORY_MAX_TARGET.
10. [oracle@mhxy171 ~]$

2、通过修改pfile文件中参数值，使用pfile文件启动。

点击(此处)折叠或打开

1. SQL> create pfile from spfile;                                #通过spfile生成最新的pfile文件
   
   File created.
   
2. [oracle@mhxy171 ~]$ cd $ORACLE_HOME
   
3. [oracle@mhxy171 dbs]$ cp inityzxd.ora inityzxd.ora_bak        #备份pfile文件
   
4. [oracle@mhxy171 dbs]$ vi inityzxd.ora
   
5. *.memory_max_target=0
   
6. *.memory_target=0
   
7. yzxd.__db_cache_size=897581056
   
8. yzxd.__java_pool_size=4194304
   
9. yzxd.__large_pool_size=8388608
   
10. yzxd.__oracle_base='/oracle/database'#ORACLE_BASE set from environment
    
11. yzxd.__pga_aggregate_target=402653184
    
12. yzxd.__sga_target=1207959552
    
13. yzxd.__shared_io_pool_size=0
    
14. yzxd.__shared_pool_size=285212672
    
15. yzxd.__streams_pool_size=0
    
16. *.audit_file_dest='/oracle/database/admin/yzxd/adump'
    
17. *.audit_trail='db'
    
18. *.compatible='11.2.0.4.0'
    
19. *.control_file_record_keep_time=20
    
20. *.control_files='/oracle/database/oradata/yzxd/control01.ctl','/oracle/database/fast_recovery_area/yzxd/control02.ctl'
    
21. *.db_block_size=8192
    
22. *.db_domain=''
    
23. *.db_name='yzxd'
    
24. *.db_recovery_file_dest='/oracle/database/fast_recovery_area'
    
25. *.db_recovery_file_dest_size=4385144832
    
26. *.diagnostic_dest='/oracle/database'
    
27. *.dispatchers='(PROTOCOL=TCP) (SERVICE=yzxdXDB)'
    
28. *.log_archive_format='%t_%s_%r.dbf'
    
29. *.memory_max_target=0                        #删除memory_max_target 和memory_target 参数
    
30. *.memory_target=0
    
31. *.open_cursors=300
    
32. *.pga_aggregate_target=367001600
    
33. *.processes=150
    
34. *.remote_login_passwordfile='EXCLUSIVE'
    
35. *.sessions=170
    
36. *.sga_max_size=1243611136
    
37. *.sga_target=1243611136
    
38. *.undo_tablespace='UNDOTBS1'

3、通过pfile文件启动数据库，检查正确后生成最新的spfile文件

点击(此处)折叠或打开

1. [oracle@mhxy171 dbs]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 29 11:12:14 2016
   
4. 
   
5. Copyright (c) 1982, 2013, Oracle. All rights reserved.
   
6. 
   
7. Connected to an idle instance.
   
8. 
   
9. SQL> startup pfile=inityzxd.ora
   
10. ORACLE instance started.
    
11. 
    
12. ORACLE instance started.
    
    
    Total System Global Area 1252663296 bytes
    Fixed Size                  2252824 bytes
    Variable Size             402657256 bytes
    Database Buffers          838860800 bytes
    Redo Buffers                8892416 bytes
    Database mounted.
    Database opened.
    
13. 
    
14. SQL> show parameter target                   #是我们修改后的参数值
15. 
    
    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                32
    pga_aggregate_target                 big integer            350M
    sga_target                           big integer            1200M
    
16. 
    
17. SQL> create spfile from pfile;
    
18. 
    
19. File created.
    
20. 
    
21. SQL> startup force
    
22. ORACLE instance started.
    
23. 
    
24. Total System Global Area 1252663296 bytes
    Fixed Size                  2252824 bytes
    Variable Size             402657256 bytes
    Database Buffers          838860800 bytes
    Redo Buffers                8892416 bytes
    Database mounted.
    Database opened.
    
25. 
    
26. SQL> show parameter target
    
27. 
    
28. 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                32
    pga_aggregate_target                 big integer            350M
    sga_target                           big integer            1200M
    
29. 
    
30. SQL>

文件参考： http://blog.itpub.net/17203031/viewspace-774928/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27039319/viewspace-2124136/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27039319/viewspace-2124136/