收集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' 

测试验证

可以通过如下的测试用例验证结论。

SQL> show parameter MEMORY NAME 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 sga NAME TYPE VALUE ------------------------------------ ------------------------------------------------------------------------------------------------ ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 628M sga_target big integer 0 SQL> l 1* 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    COMPLETE 2015/06/24 23:11:07 2015/06/24 23:11:07　　　★★★★★★ DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size    COMPLETE 2015/06/01 13:03:44 2015/06/01 13:03:44 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size    COMPLETE 2015/06/01 13:03:42 2015/06/01 13:03:42 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size    COMPLETE 2015/06/01 10:37:26 2015/06/01 10:37:26 DEFAULT buffer cache SHRINK DEFERRED db_cache_size    COMPLETE 2015/06/01 09:56:25 2015/06/01 09:56:25 DEFAULT buffer cache SHRINK DEFERRED db_cache_size    COMPLETE 2015/06/01 09:49:55 2015/06/01 09:49:55 DEFAULT buffer cache SHRINK DEFERRED db_cache_size    COMPLETE 2015/06/01 09:42:25 2015/06/01 09:42:25 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size    COMPLETE 2015/06/01 09:36:48 2015/06/01 09:36:48 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size    COMPLETE 2015/06/01 09:36:39 2015/06/01 09:36:39 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size    COMPLETE 2015/06/01 09:36:39 2015/06/01 09:36:39 DEFAULT buffer cache INITIALIZING IMMEDIATE db_cache_size    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   COMPLETE 2015/06/01 09:36:37 2015/06/01 09:36:37 DEFAULT buffer cache STATIC IMMEDIATE db_cache_size    COMPLETE 2015/06/01 09:36:37 2015/06/01 09:36:37 14 rows selected. SQL> ALTER SYSTEM SET db_cache_size= scope=spfile; System altered. SQL> ALTER SYSTEM SET db_cache_size= scope=spfile; System altered. SQL> ALTER SYSTEM SET db_cache_size= scope=spfile; ALTER SYSTEM SET db_cache_size= scope=spfile * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-00384: Insufficient memory to grow cache SQL> select CURRENT_SIZE ,COMPONENT from V$MEMORY_DYNAMIC_COMPONENTS; CURRENT_SIZE COMPONENT ------------ ------------------------------  shared pool  large pool  java pool  streams pool  SGA Target  DEFAULT buffer cache 　　　　　★★★★ 0 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache  Shared IO Pool  PGA Target 0 ASM Buffer Cache 16 rows selected. SQL>