【Oracle】ORA-32017和ORA-00384错误处理
文章目录
- 【Oracle】ORA-32017和ORA-00384错误处理
- 问题描述
- 问题原因和解决
- 测试验证
【声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)
收集Oracle数据库内存相关的信息
【Oracle】ORA-32017和ORA-00384错误处理
问题描述
执行如下命令，修改db_cache_size的大小时候发生了ORA-32017和ORA-00384错误。
ALTER SYSTEM SET db_cache_size=100M scope=spfile
错误内容信息：
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
问题原因和解决
自动内存管理有效的时候（AMM）时候，如果设置了比当前缓存大小较大的值，会发生ORA-384。
作为解决方法，可以通过V$MEMORY_DYNAMIC_COMPONENTS视图确认当前“缓冲区大小”，
然后设定db_cache_size小于该大小的大小。
例：
select COMPONENT,CURRENT_SIZE from
V$MEMORY_DYNAMIC_COMPONENTS where COMPONENT='DEFAULT buffer cache'
参考：
收集Oracle数据库内存相关的信息
测试验证
可以通过如下的测试用例验证结论。
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>