背景: 根據(jù)需求生產環(huán)境為了增加穩(wěn)定性,避免內存組件過度擴張,因此使用ASMM自動共享內存管理方式,并且手工設定 shared_pool_size,db_cache_size的大小。 如下操作的原因是因為:在直接修改操作的時候報錯: ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account ORA-00849: SGA_TARGET 10737418240 cannot be set to more than MEMORY_MAX_TARGET 0.
--1.查看當前內存組件參數(shù)值 col name for a30; col VALUE for a30; select INST_ID,name, VALUE / 1024 / 1024 size_MB, issys_modifiable from gv$parameter where name in ('pga_aggregate_target', 'memory_target', 'memory_max_target', 'sga_max_size', 'sga_target', 'shared_pool_size', 'db_cache_size', 'java_pool_size', 'large_pool_size', 'streams_pool_size') order by 2,1; --2.查看PGA使用過的最大值 select INST_ID,value/1024/1024 SIZE_M from gv$pgastat where name='maximum PGA allocated'; INST_ID SIZE_M ---------- ---------- 1 286.93457 2 269.886719
--3.當前系統(tǒng)參數(shù)配置 NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ memory_target big integer 10G memory_max_target big integer 10G sga_max_size big integer 10G (該系統(tǒng)當前值設置有問題) sga_target big integer 0 pga_aggregate_target big integer 0
--4計劃調整內存組件參數(shù)如下: --自動共享內存管理(ASMM) --注意這里是因為該系統(tǒng)是OLAP和OLTP功能混用,所以內存分配特殊 memory_target big integer 0 memory_max_target big integer 0 sga_max_size big integer 8G sga_target big integer 0 pga_aggregate_target big integer 10G shared_pool_size big integer 4G db_cache_size big integer 2G
--5.確認參數(shù)文件并于操作前備份參數(shù)文件 show parameter spfile;(操作前要有參數(shù)文件備份) NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATEDG/rac11g/spfilerac11g.ora
create pfile='/home/oracle/oldpfile.ora' from spfile ='+DATEDG/rac11g/spfilerac11g.ora';
--6.修改內存組件參數(shù): 1節(jié)點: alter system set memory_max_target=0 scope=spfile sid='*'; alter system set sga_max_size=8192M scope=spfile sid='rac11g1'; alter system set memory_target=0 sid='rac11g1'; alter system set sga_target=0 sid='rac11g1'; alter system set pga_aggregate_target=10240M sid='rac11g1'; shutdown immediate;
2節(jié)點: alter system set sga_max_size=8192M scope=spfile sid='rac11g2'; alter system set memory_target=0 sid='rac11g2'; alter system set sga_target=0 sid='rac11g2'; alter system set pga_aggregate_target=10240M sid='rac11g2'; shutdown immediate;
--7.使用跟新后的spfile創(chuàng)建pfile然后修改 節(jié)點1: 使用跟新后的spfile創(chuàng)建pfile然后修改 create pfile='/home/oracle/newpfile.ora' from spfile ='+DATEDG/rac11g/spfilerac11g.ora'; --8.注釋(參見文檔 ID 1397761.1) 注釋: vi /home/oracle/newpfile.ora #*.memory_max_target=0 #rac11g1.memory_target=0 #rac11g2.memory_target=0
--9.使用修改后的newpfile重建參數(shù)文件(原spfile可以重命名備份): create spfile='+DATEDG/rac11g/spfilerac11g.ora' from pfile='/home/oracle/newpfile.ora';
--10.分別啟動數(shù)據(jù)庫 啟動數(shù)據(jù)庫(兩節(jié)點分別啟動): startup;
--11.確認參數(shù)設置情況(兩節(jié)點): 確認參數(shù)設置情況(兩節(jié)點): show parameter memory_max_target; show parameter sga_max_size; show parameter memory_target; show parameter sga_target; show parameter pga_aggregate_target;
--12.設置shared_pool_size和db_cache_size參數(shù): 設置shared_pool_size和db_cache_size參數(shù): 1節(jié)點: alter system set shared_pool_size=4096M sid='rac11g1'; alter system set db_cache_size= 2048M sid='rac11g1'; 2節(jié)點: alter system set shared_pool_size=4096M sid='rac11g2'; alter system set db_cache_size= 2048M sid='rac11g2';