記一次修改sga大小之后出現(xiàn)的一系列報(bào)錯(cuò)

記錄一次修改sga大小之后出現(xiàn)的一系列報(bào)錯(cuò)

修改了oracle的sga大小之后，遇到了一系列的報(bào)錯(cuò)。

00844和00851報(bào)錯(cuò)

SQL> startup;
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 2147483648 cannot be set to more than MEM
SQL> 

01078報(bào)錯(cuò)

SQL> startup;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.4/db_1/dbs/inittest.ora'

一、修改過(guò)程

1.1 為什么我要修改sga大小

在測(cè)試的時(shí)候，插入大量數(shù)據(jù)發(fā)現(xiàn)30009錯(cuò)誤，我以為是sga小導(dǎo)致的

SQL> insert into t select rownum from dual connect by level<=10000000;
insert into t select rownum from dual connect by level<=10000000
            *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation

Elapsed: 00:00:13.51
SQL>

查看sga

SQL> show parameter sga;

NAME                     TYPE                  VALUE
------------------------------------ --------------------------------- ------------------------------
lock_sga                 boolean                   FALSE
pre_page_sga                 boolean                   FALSE
sga_max_size                 big integer               1584M
sga_target               big integer               1G
SQL> 

查看pga

SQL> show parameter pga

NAME                     TYPE                  VALUE
------------------------------------ --------------------------------- ------------------------------
pga_aggregate_target             big integer               0
SQL> 

1.2 修改sga

更改sga

SQL> alter system set sga_max_size=2048M scope=spfile;

System altered.

Elapsed: 00:00:00.06
SQL>

1.3 報(bào)錯(cuò)1

關(guān)閉和啟動(dòng)數(shù)據(jù)庫(kù)

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 2147483648 cannot be set to more than MEMORY_TARGET 1660944384.

結(jié)果出錯(cuò)了。經(jīng)過(guò)查找資料，發(fā)現(xiàn)11g新增了個(gè)參數(shù)MEMORY_TARGET，其大小等于PGA+SGA，當(dāng)sga的大小大于MEMORY_TARGET就會(huì)報(bào)如上錯(cuò)誤。

查看MEMORY_TARGET大小，發(fā)現(xiàn)大小為1584M，與sga不符。

SQL> show parameter memory_target;

NAME                     TYPE                  VALUE
------------------------------------ --------------------------------- ------------------------------
memory_target                big integer               1584M
SQL> 

修改spfile文件中的參數(shù)

sga_max_size=1653604352

啟動(dòng)數(shù)據(jù)庫(kù)

SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.4/db_1/dbs/inittest.ora'
SQL> 

1.4 報(bào)錯(cuò)2

啟動(dòng)后遇到了01078報(bào)錯(cuò)。解決辦法如下：
將test（實(shí)例名）文件夾下的init.ora.xxx 文件復(fù)制到dbs目錄下即可，復(fù)制后的名字按照之前報(bào)錯(cuò)的提示輸入。

[oracle@localhost ~]$ cp /u01/app/oracle/admin/test/pfile/init.ora.922018114616 /u01/app/oracle/product/11.2.4/db_1/dbs/inittest.ora

啟動(dòng)數(shù)據(jù)庫(kù)

SQL> startup;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size          2213896 bytes
Variable Size         956303352 bytes
Database Buffers      687865856 bytes
Redo Buffers            7135232 bytes
Database mounted.
Database opened.
SQL> 

二、總結(jié)

歸根結(jié)底還是對(duì)數(shù)據(jù)庫(kù)原理不熟導(dǎo)致的，牢記memory_target值需為sga+pga內(nèi)存的和。