3.3 查看ORA-00838 报错, ORACLE解释.
jietestdb$oerr ora 00838
00838, 00000, "Specified value of MEMORY_TARGET is too small, needs to be at least %sM"
// *Cause: The specified value of MEMORY_TARGET was less than the sum of the
// specified values for SGA_TARGET and PGA_AGGREGATE_TARGET.
// *Action: Set MEMORY_TARGET to at least the recommended value.
由上ORA-00838,ORACLE解释可以看出, 减少SGA值,或增大MEMORY_TARGET值, 或还原修改前状态即可解决问题.
3.1 第一种解决方法: 还原先前状态,不做SGA_TARGET或MEMORY_TARGET值改变.
3.1.1 此DB已经被shutdown了, 直接startup DB无法启动, 如下报错.
SYS> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 644M
3.1.2 因DB在关闭状态下,也可以由spfile创建pfile,或由pfile 创建spfile. 此时我们测试DB spfiletest11g.ora中,已经包含sga_target值,如下图.
jietestdb$
jietestdb$strings spfiletest11g.ora
test11g.__db_cache_size=155189248
test11g.__java_pool_size=4194304
test11g.__large_pool_size=4194304
test11g.__oracle_base='/u01/product'#ORACLE_BASE set from environment
test11g.__pga_aggregate_target=230686720
test11g.__sga_target=432013312
test11g.__shared_io_pool_size=0
test11g.__shared_pool_size=251658240
test11g.__streams_pool_size=8388608
*.audit_file_dest='/u01/product/admin/test11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/product/oradata/test
11g/control01.ctl','/u01/product/oradata/test11g/control02.ctl','/data/test11g/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test11g'
*.db_recovery_file_dest='/u01/product/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest='/u01/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test11gXDB)'
*.fast_start_mttr_target=0
*.local_listener='LISTENER_TEST11G'
*.log_archive_dest_1='LOCATION=/data/test11g/arch/'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=661651456
*.open_cursors=1000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=1105
*.sga_target=662700032
*.undo_tablespace='UNDOTBS1'
3.1.3此时由 SPFILETEST11G.ORA创建INITTEST11G.ORA
SYS> create pfile from spfile;
File created.
3.1.4 VIM修改创建的inittest11g.ora文件,删除*.sga_target=662700032的值 (因为spfiletest11g.ora为二进制文件,不能用VI/VIM命令直接修改), 重新创建spfiletest11g.ora,此时spfiletest11g.ora中就不会包含sga_target的值.
SYS> create spfile from pfile;
File created.
3.1.5 启动DB OK
SYS> startup
ORACLE instance started.
Total System Global Area 661209088 bytes
Fixed Size 1346980 bytes
Variable Size 499122780 bytes
Database Buffers 155189248 bytes
Redo Buffers 5550080 bytes
Database mounted.
Database opened.
3.2 第二种方法,由如上方法创建出来的inittest11.ora PFILE初始化文档中, 直接加上*.sga_target=X (X为一个数值, 前提包证SGA的X值,被MEMORY_TARGET的值632M,减去剩余PGA的值大于10M(PGA最小值)),由修改后的PFILE创建SPFILE, 直接启动DB即可.
3.3 第三种方法,与第二种方法类似, 假如不想修改SGA_TARGET的值, 可以在pfile初始化文档中, 修改增大*.memory_target=Y(Y为一个数值,Y值不能大于物理内存大小)的值. 在由修改后的PFILE,创建SPFILE启动DB.
4. 小结.
4.1 如果是正式库PGA不要设置太小,要根据业务需要, 如果用户进程有大的并发,排序等需要把PGA设置大点. 正常PGA默认值为20%的MEMORY_TARGET值, SGA为80%的MEMORY_TARGET值.
4.2 pfile与spfile如上文设置的新值, sga_target或memory_target在DB启动后,都会覆盖先前DB设置的sga_target或memory_target的值.
结束......