OracleORA-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 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                 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:37

14 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 cache


SQL> select CURRENT_SIZE ,COMPONENT from V$MEMORY_DYNAMIC_COMPONENTS;

CURRENT_SIZE COMPONENT
------------ ------------------------------
    83886080 shared pool
     4194304 large pool
     4194304 java pool
     8388608 streams pool
   658505728 SGA Target
    12582912 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
   536870912 Shared IO Pool
   415236096 PGA Target
           0 ASM Buffer Cache

16 rows selected.

SQL>