记录一次修改 sga 大小之后出现的一系列报错
修改了 oracle 的 sga 大小之后, 遇到了一系列的报错.
00844 和 00851 报错
- 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 报错
- 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'
一, 修改过程
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 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 报错 1
关闭和启动数据库
- 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.
结果出错了. 经过查找资料, 发现 11g 新增了个参数 MEMORY_TARGET, 其大小等于 PGA+SGA, 当 sga 的大小大于 MEMORY_TARGET 就会报如上错误.
查看 MEMORY_TARGET 大小, 发现大小为 1584M, 与 sga 不符.
- SQL> show parameter memory_target;
- NAME TYPE VALUE
- ------------------------------------ --------------------------------- ------------------------------
- memory_target big integer 1584M
- SQL>
修改 spfile 文件中的参数
sga_max_size=1653604352
启动数据库
- 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 报错 2
启动后遇到了 01078 报错. 解决办法如下:
将 test(实例名) 文件夹下的 init.ora.xxx 文件复制到 dbs 目录下即可, 复制后的名字按照之前报错的提示输入.
[[email protected] ~]$ 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 bytes
- Fixed Size 2213896 bytes
- Variable Size 956303352 bytes
- Database Buffers 687865856 bytes
- Redo Buffers 7135232 bytes
- Database mounted.
- Database opened.
- SQL>
二, 总结
归根结底还是对数据库原理不熟导致的, 牢记 memory_target 值需为 sga+pga 内存的和.
来源: http://www.bubuko.com/infodetail-3037380.html