網(wǎng)站開發(fā)和網(wǎng)頁設(shè)計的區(qū)別seo優(yōu)化服務(wù)公司
文章目錄
- 【Oracle】ORA-32017和ORA-00384錯誤處理
- 問題描述
- 問題原因和解決
- 測試驗(yàn)證
【聲明】文章僅供學(xué)習(xí)交流，觀點(diǎn)代表個人，與任何公司無關(guān)。
編輯|SQL和數(shù)據(jù)庫技術(shù)(ID:SQLplusDB)
收集Oracle數(shù)據(jù)庫內(nèi)存相關(guān)的信息
【Oracle】ORA-32017和ORA-00384錯誤處理
問題描述
執(zhí)行如下命令，修改db_cache_size的大小時候發(fā)生了ORA-32017和ORA-00384錯誤。
ALTER SYSTEM SET db_cache_size=100M scope=spfile
錯誤內(nèi)容信息：
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
問題原因和解決
自動內(nèi)存管理有效的時候（AMM）時候，如果設(shè)置了比當(dāng)前緩存大小較大的值，會發(fā)生ORA-384。
作為解決方法，可以通過V$MEMORY_DYNAMIC_COMPONENTS視圖確認(rèn)當(dāng)前“緩沖區(qū)大小”，
然后設(shè)定db_cache_size小于該大小的大小。
例：
select COMPONENT,CURRENT_SIZE from
V$MEMORY_DYNAMIC_COMPONENTS where COMPONENT='DEFAULT buffer cache'
參考：
收集Oracle數(shù)據(jù)庫內(nèi)存相關(guān)的信息
測試驗(yàn)證
可以通過如下的測試用例驗(yàn)證結(jié)論。
SQL> show parameter MEMORYNAME TYPE VALUE
------------------------------------ ------------------------------------------------------------------------------------------------ ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1G
memory_target big integer 1G
shared_memory_address integer 0
SQL> show parameter sgaNAME TYPE VALUE
------------------------------------ ------------------------------------------------------------------------------------------------ ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 628M
sga_target big integer 0
SQL> l1* select * from V$MEMORY_RESIZE_OPS where parameter = 'db_cache_size' order by start_time desc
SQL> /COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- ---------- ------------------------- ------------ ----------- ----------- --------------------------- ------------------------------ ------------------------------
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 16777216 12582912 12582912 COMPLETE 2015/06/24 23:11:07 2015/06/24 23:11:07 ★★★★★★
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 20971520 16777216 16777216 COMPLETE 2015/06/01 13:03:44 2015/06/01 13:03:44
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 25165824 20971520 20971520 COMPLETE 2015/06/01 13:03:42 2015/06/01 13:03:42
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 29360128 25165824 25165824 COMPLETE 2015/06/01 10:37:26 2015/06/01 10:37:26
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 33554432 29360128 29360128 COMPLETE 2015/06/01 09:56:25 2015/06/01 09:56:25
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 37748736 33554432 33554432 COMPLETE 2015/06/01 09:49:55 2015/06/01 09:49:55
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 41943040 37748736 37748736 COMPLETE 2015/06/01 09:42:25 2015/06/01 09:42:25
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 46137344 41943040 41943040 COMPLETE 2015/06/01 09:36:48 2015/06/01 09:36:48
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 50331648 46137344 46137344 COMPLETE 2015/06/01 09:36:39 2015/06/01 09:36:39
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 54525952 50331648 50331648 COMPLETE 2015/06/01 09:36:39 2015/06/01 09:36:39
DEFAULT buffer cache INITIALIZING IMMEDIATE db_cache_size 58720256 54525952 54525952 COMPLETE 2015/06/01 09:36:37 2015/06/01 09:36:38
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015/06/01 09:36:37 2015/06/01 09:36:37
DEFAULT buffer cache STATIC db_cache_size 0 54525952 54525952 COMPLETE 2015/06/01 09:36:37 2015/06/01 09:36:37
DEFAULT buffer cache STATIC IMMEDIATE db_cache_size 58720256 54525952 54525952 COMPLETE 2015/06/01 09:36:37 2015/06/01 09:36:3714 rows selected.SQL> ALTER SYSTEM SET db_cache_size=1258291 scope=spfile;System altered.SQL> ALTER SYSTEM SET db_cache_size=12582911 scope=spfile;System altered.SQL> ALTER SYSTEM SET db_cache_size=12582913 scope=spfile;
ALTER SYSTEM SET db_cache_size=12582913 scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cacheSQL> select CURRENT_SIZE ,COMPONENT from V$MEMORY_DYNAMIC_COMPONENTS;CURRENT_SIZE COMPONENT
------------ ------------------------------83886080 shared pool4194304 large pool4194304 java pool8388608 streams pool658505728 SGA Target12582912 DEFAULT buffer cache ★★★★0 KEEP buffer cache0 RECYCLE buffer cache0 DEFAULT 2K buffer cache0 DEFAULT 4K buffer cache0 DEFAULT 8K buffer cache0 DEFAULT 16K buffer cache0 DEFAULT 32K buffer cache536870912 Shared IO Pool415236096 PGA Target0 ASM Buffer Cache16 rows selected.SQL>