【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>