慈溪住房和城乡建设部网站,爱站工具包的主要功能,seo外贸仿牌网站换域名,怎样用阿里云建设网站文章目录 【Oracle】ORA-32017和ORA-00384错误处理问题描述问题原因和解决测试验证 【声明】文章仅供学习交流#xff0c;观点代表个人#xff0c;与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB)
收集Oracle数据库内存相关的信息
【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_size100M scopespfile错误内容信息
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 COMPONENTDEFAULT 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_size1258291 scopespfile;System altered.SQL ALTER SYSTEM SET db_cache_size12582911 scopespfile;System altered.SQL ALTER SYSTEM SET db_cache_size12582913 scopespfile;
ALTER SYSTEM SET db_cache_size12582913 scopespfile
*
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
