数据库服务器内存由16G增加为64G,为充分利用内存资源,对Oracle内存参数做了如下调整:
SQL>alter system set sga_max_size=40960M scope=spfile;SQL>alter system set sga_target=40960M scope=spfile;SQL>alter system set pga_aggregate_target=10240M scope=spfile;
在重启数据库时出现如下报错:
SQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startupORA-00844: Parameter not taking MEMORY_TARGET into accountORA-00851: SGA_MAX_SIZE 42949672960 cannot be set to more than MEMORY_TARGET 6979321856.
该问题是由于memory_target小于sga_max_size导致的,通常memory_target值需为sga+pga内存的和,因此需将memory_target值调大。
先备用的pfile启动Oracle,重新创建spfile,并重启数据库。
C:\Users\Administrator>sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on 星期日 11月 11 17:22:34 2018Copyright (c) 1982, 2013, Oracle. All rights reserved.已连接到空闲例程。SQL> startup pfile='E:\app\admin\orcl\pfile\init.ora.7312017152243'ORACLE 例程已经启动。Total System Global Area 6847938560 bytesFixed Size 2294256 bytesVariable Size 3657436688 bytesDatabase Buffers 3170893824 bytesRedo Buffers 17313792 bytes数据库装载完毕。数据库已经打开。SQL> create spfile from pfile='E:\app\admin\orcl\pfile\init.ora.7312017152243';文件已创建。SQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startupORACLE 例程已经启动。
之后重新修改内存参数值,并重启数据库即可
SQL>alter system set memory_target = 52430m scope = spfile;SQL>alter system set sga_max_size=40960M scope=spfile;SQL>alter system set sga_target=40960M scope=spfile;SQL>alter system set pga_aggregate_target=10240M scope=spfile;
SQL> shutdown immediate
数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startupORACLE 例程已经启动。Total System Global Area 4.2758E+10 bytes
Fixed Size 2290648 bytesVariable Size 3221228584 bytesDatabase Buffers 3.9460E+10 bytesRedo Buffers 74391552 bytes数据库装载完毕。数据库已经打开。SQL>SQL> show parameter sgaNAME TYPE VALUE
------------------------------------ ----------- ------------------------------lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 40Gsga_target big integer 40GSQL> show parameter pgaNAME TYPE VALUE
------------------------------------ ----------- ------------------------------pga_aggregate_target big integer 10GOK