ORA-00838: Specified value of MEMORY_TARGET is too small

huangdazhu發表於2016-01-06
1. 測試環境
OS: RHEL5U5(32bit)
DB: Oracle 11.2.0.3.0(32bit)

2.   異常原因. 
2.1 oracle 11g預設sga_target為0，如下圖, Oracle 10g開始,引入SGA_TARGET初始化引數, 讓oracle自動管理SGA中(Buffer cache (DB_CACHE_SIZE),Shared pool (SHARED_POOL_SIZE),Large pool (LARGE_POOL_SIZE),Java pool (JAVA_POOL_SIZE),Streams pool (STREAMS_POOL_SIZE))的記憶體自動分配，即ASSM(Automatic Shared Memory Management). 如下為引自oracle官方文件解釋.如下.

SYS> show parameter sga;

NAME                                 TYPE           VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean        FALSE
pre_page_sga                     boolean       FALSE
sga_max_size                      big integer   632M
sga_target                           big integer    0
SYS> 

SGA_TARGET官方解釋.

SGA_TARGET

Property Description
Parameter type Big integer
Syntax SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests)
Modifiable ALTER SYSTEM
Range of values 64 MB to operating system-dependent
Basic Yes

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

  • Buffer cache (DB_CACHE_SIZE)

  • Shared pool (SHARED_POOL_SIZE)

  • Large pool (LARGE_POOL_SIZE)

  • Java pool (JAVA_POOL_SIZE)

  • Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to nonzero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

  • Log buffer

  • Other buffer caches, such as KEEP, RECYCLE, and other block sizes

  • Fixed SGA and other internal allocations


2.2 給SGA_TARGET設定與SGA_MAX_SIZE(632M)相簿的值,開啟ASSM功能.
SYS> alter system set sga_target=632M SCOPE=SPFILE;
System altered.

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


3.3 檢視ORA-00838 報錯, ORACLE解釋.

jietestdb<*test11g*/u01/product/oracle/dbs>$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<*test11g*/u01/product/oracle/dbs>$
jietestdb<*test11g*/u01/product/oracle/dbs>$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的值.
結束......

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28869493/viewspace-1972320/，如需轉載，請註明出處，否則將追究法律責任。

相關文章