scope有三个值,分别是memory立即生效,重启失效,spfile重启数据库生效,both立即生效,重启不失效
1.修改sga 参数,重启后生效
SQL> show parameter sga;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
1532M
sga_target big integer
0
SQL>
show parameter
alter system set memory_max_target=1600m scope=spfile;
alter system set memory_target=1600m scope=spfile;
shutdown immediate
startup
再次查看sga
SQL> show parameter sga;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
1600M
sga_target big integer
0
SQL>
2.修改参数错误导致启动数据库失败,重建spfile来恢复(pfile可以和spfile来互相做为重建基础,spfile不能编辑,pfile编辑好之后再给spfile来重建,之后再启动数据库就是使用了修正了的spfile文件了)
alter system set memory_max_target=160m scope=spfile;
alter system set memory_target=160m scope=spfile;
shutdown immediate
SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 256M
SQL> exit
cd $ORACLE_HOME/dba
[oracle@oracle11 dbs]$ ll
total 24
-rw-rw---- 1 oracle oinstall 1544 Mar 3 11:05 hc_orcl.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Mar 1 15:27 lkORCL
-rw-r----- 1 oracle oinstall 1536 Mar 1 15:50 orapworcl
-rw-r----- 1 oracle oinstall 2560 Mar 3 11:06 spfileorcl.ora
-rw-r----- 1 oracle oinstall 2560 Mar 3 10:57 spfileorcl.ora.bak
[oracle@oracle11 dbs]$
从spfile文件spfileorcl.ora创建一个静态的pfile文件
SQL> create pfile='pfile.ora' from spfile='spfileorcl.ora';
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11 dbs]$ ll
total 28
-rw-rw---- 1 oracle oinstall 1544 Mar 3 11:05 hc_orcl.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Mar 1 15:27 lkORCL
-rw-r----- 1 oracle oinstall 1536 Mar 1 15:50 orapworcl
-rw-r--r-- 1 oracle oinstall 955 Mar 3 11:18 pfile.ora
-rw-r----- 1 oracle oinstall 2560 Mar 3 11:06 spfileorcl.ora
-rw-r----- 1 oracle oinstall 2560 Mar 3 10:57 spfileorcl.ora.bak
[oracle@oracle11 dbs]$
编辑pfile文件pfile.ora,以下改正确值
*.memory_max_target=1677721600
*.memory_target=1677721600
然后再使用pfile文件创建spfile
3.修改Oracle的pga上限
修改Oracle的pga上限值为20G
alter system set pga_aggregate_limit = 20G;
不设置限制
alter system set pga_aggregate_limit =0;
pga默认不受限制
SQL> show parameter pga
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
pga_aggregate_target big integer
0
SQL>