记一次修改sga大小之后出现的一系列报错

记录一次修改sga大小之后出现的一系列报错

修改了oracle的sga大小之后，遇到了一系列的报错。

00844和00851报错

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

01078报错

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

一、修改过程

1.1 为什么我要修改sga大小

在测试的时候，插入大量数据发现30009错误，我以为是sga小导致的

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 operationElapsed: 00:00:13.51SQL>

查看sga

SQL> show parameter sga;NAME                     TYPE                  VALUE------------------------------------ --------------------------------- ------------------------------lock_sga                 boolean                   FALSEpre_page_sga                 boolean                   FALSEsga_max_size                 big integer               1584Msga_target               big integer               1GSQL> 

查看pga

SQL> show parameter pgaNAME                     TYPE                  VALUE------------------------------------ --------------------------------- ------------------------------pga_aggregate_target             big integer               0SQL> 

1.2 修改sga

更改sga

SQL> alter system set sga_max_size=2048M scope=spfile;System altered.Elapsed: 00:00:00.06SQL>

1.3 报错1

关闭和启动数据库

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

结果出错了。经过查找资料，发现11g新增了个参数MEMORY_TARGET，其大小等于PGA+SGA，当sga的大小大于MEMORY_TARGET就会报如上错误。

查看MEMORY_TARGET大小，发现大小为1584M，与sga不符。

SQL> show parameter memory_target;NAME                     TYPE                  VALUE------------------------------------ --------------------------------- ------------------------------memory_target                big integer               1584MSQL> 

修改spfile文件中的参数

sga_max_size=1653604352

启动数据库

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

1.4 报错2

启动后遇到了01078报错。解决办法如下：
将test（实例名）文件夹下的init.ora.xxx 文件复制到dbs目录下即可，复制后的名字按照之前报错的提示输入。

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

启动数据库

SQL> startup;ORACLE instance started.Total System Global Area 1653518336 bytesFixed Size          2213896 bytesVariable Size         956303352 bytesDatabase Buffers      687865856 bytesRedo Buffers            7135232 bytesDatabase mounted.Database opened.SQL> 

二、总结

归根结底还是对数据库原理不熟导致的，牢记memory_target值需为sga+pga内存的和。