对于Oracle数据库内存参数配置大小，一般满足如下规则

 

sga_target + pga_aggregate_target < memory_target <=memory_max_targetsga_max_size < memory_targetPGA_AGGREGATE_TARGET= memory_target - sga_target

 

 

本篇文档，配置ASMM,ASM进行转换，进行配置。

 

 

1.关闭ASM（关闭）memory_target

SQL> show parameter memoryNAME                VALUE------------------------------memory_max_target    800Mmemory_target        720M

SQL> show parameter pga

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

pga_aggregate_target big integer 175M

SQL> show parameter sga

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

pre_page_sga boolean FALSE

sga_max_size big integer 800M

sga_target big integer 528M

******************关闭memory_target 两种方法 方法一。参数修改方式

SQL> alter system reset memory_max_target;

SQL> alter system set memory_target=0;

方法二（错误修改方式） SQL> alter system set memory_max_target=0 scope=spfile; SQL> alter system set memory_target=0; SQL> shutdown immediate; SQL> alter system set memory_target=0 scope=spfile;  要么数据库实例无法启动，要么并没有关闭memory管理方式 方法三。编辑参数文件方式

SQL> create pfile='/tmp/pfile.ora' from spfile;

vi /tmp/pfile.ora

修改前

*.memory_max_target=0

*.memory_target=0

修改后

#*.memory_max_target=0

#*.memory_target=0

SQL> create spfile from  pfile='/tmp/pfile.ora'; SQL> startup

 

2.启用ASM管理

 

参数说明

Memory_max_target 代表Oracle数据库总内存最大值memory_target 代表总内存最小值参数限制：memory_max_target（初始化参数：需要重启生效）Linux环境下：memory_max_target=>受到操作系统shared memory的影响：memory_max_target < /etc/fstab报错描述：ORA-00845: MEMORY_TARGET not supported on this system参数查看：[root@hukou ~]# df -h |grep tmpfstmpfs                 1.7G     0  1.7G   0% /dev/shm操作系统shared memory大小增加：默认为系统整体内存的一半大小:修改后，size指定值解决方案：增加：vi /etc/fstab      tmpfs                   /dev/shm                tmpfs   defaults,size=3200m        0 0[root@hukou ~]# mount -o remount /dev/shm[root@hukou ~]# df -h |grep shmtmpfs                 3.2G     0  3.2G   0% /dev/shm 参数限制：memory_target（在线即可修改）报错信息： 内存的最小值：需要小于或者等于内存的最大值ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGETmemory_target <= memory_max_target 报错信息：内存的最小值小于512MORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least512Mmemory_target > sga_target +pga_aggregate_targetsga_target                           big integer 400Mpga_aggregate_target                 big integer 100M  参数限制：sga_max_size（初始化参数：需要重启生效）参数说明： SGA区域内存最大值报错信息：sga_max_size的值大于memory_target ORA-00851: SGA_MAX_SIZE 1048576000 cannot be set to more than MEMORY_TARGET 629145600.sga_max_size < memory_target参数限制：sga_target（在线修改即可）参数说明：SGA区域内存最小值报错信息：SGA_TARGET 的值大于sga_max_sizeORA-00823: Specified value of sga_target greater than sga_max_size SQL> show parameter pgaNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------pga_aggregate_target                 big integer 100M参数限制：pga_aggregate_target （在线修改即可）

 

 使用ASM管理，启用memory参数

 

当前内存参数SQL> show parameter sgaNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------lock_sga                             boolean                FALSEpre_page_sga                         boolean                FALSEsga_max_size                         big integer            528Msga_target                           big integer            528MSQL> show parameter pgaNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------pga_aggregate_target                 big integer            192M 按照规则，如果不修改sga/pga内存大小，那么memory_target >620M,memory_max_target >620M,

memory_max_target小于操作系统内存参数最大值3.9G

enmo:/home/oracle df -h |grep tmpfs

tmpfs 3.9G 0 3.9G 0% /dev/shm

本次参数配置： memory_target =620M,memory_max_target=2200m;

方法一：

SQL> alter system set memory_target=620m scope=spfile;

SQL> alter system set memory_max_target=2200m scope=spfile;

SQL> shutdown immediate;

---本次小学生算数错误，因此启动报错

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 720M

---由于本次数据库已关闭，因此无法通过sqlplus alter system 修改参数，可以通过手工编辑参数文件。   开车没开好

方法二：

SQL> create pfile='/tmp/pfile.ora' from spfile;

vi /tmp/pfile.ora

修改前

 

*.memory_max_target=2306867200

*.memory_target=650117120

修改后

*.memory_max_target=2306867200

*.memory_target=720m

SQL> create spfile from pfile='/tmp/pfile.ora';

SQL> startup

SQL> show parameter memory

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

hi_shared_memory_address integer 0

memory_max_target big integer 2208M

memory_target big integer 720M