oracle报错00838,ORA-00838: Specified value of MEMORY_TARGET is too small

3 重启DB

3.1  SYS> startup force

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 644M

SYS> show parameter pga;

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 54 Serial number: 5

3.2 根据如上报错信息.可以看出SGA设置大了. 导致PGA小了, 故报错. MEMORY_TARGET=PGA+SGA.   SGA的最少值为10M,最大值为4096 GB - 1, 见官方文档.

PGA_AGGREGATE_TARGET

Property

Description

Parameter type

Big integer

Syntax

PGA_AGGREGATE_TARGET =integer[K | M | G]

Default value

10 MB or 20% of the size of the SGA, whichever is greater

Modifiable

ALTER SYSTEM

Range of values

Minimum: 10 MB

Maximum: 4096 GB - 1

Basic

Yes

PGA_AGGREGATE_TARGETspecifies the target aggregate PGA memory available to all server processes attached to the instance.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的值.

结束......