ORACLE无法调整SGA_TARGET值的案例记录

 数据库     |      2020-01-27 11:09

在一数据库版本为(标准版)Oracle Database 10g Release 10.2.0.4.0 - 64bit Production 的服务器上调整 sga_target时,遇到命令执行了非常久都没有执行完成的异常情况,觉得非常诧异、不解,因为一般调整sga_targt命令非常快速,检查了告警日志,并没有任何异常错误,等了好几分钟都没有执行完成,于是执行了CTRL+C命令结束了SQL命令,具体过程如下:

$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Sep 3 22:35:59 2016

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

 

SQL> show parameter sga

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 8G

sga_target                           big integer 6G

 

SQL> 

SQL> alter system set sga_target=8g scope=both;

 

^Calter system set sga_target=8g scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-01013: user requested cancel of current operation

 

 

 

SQL> 

SQL> 

 

 

后面检查时,在$ORACLE_BASE/admin/$ORACLE_SID/bdump目录下发现scm2_mmon_16798.trc跟踪文件,在里面看到大量“SGA POLICY: Cache below reserve request pending 1”这里错误信息

/u01/app/oracle/admin/SCM2/bdump/scm2_mmon_16798.trc

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

System name:    Linux

Node name:      xxxxxxxxx

Release:        2.6.32-200.13.1.el5uek

Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011

Machine:        x86_64

Instance name: SCM2

Redo thread mounted by this instance: 1

Oracle process number: 11

Unix process pid: 16798, image: xxxxxx (MMON)

 

*** 2016-09-03 22:36:49.845

*** SERVICE NAME:(SYS$BACKGROUND) 2016-09-03 22:36:49.836

*** SESSION ID:(931.1) 2016-09-03 22:36:49.836

SGA POLICY: Cache below reserve getting from component1

SGA POLICY: Cache below reserve request pending 1

SGA POLICY: Cache below reserve request pending 1

SGA POLICY: Cache below reserve request pending 1

*** 2016-09-03 22:37:19.880

SGA POLICY: Cache below reserve getting from component1

SGA POLICY: Cache below reserve request pending 1

SGA POLICY: Cache below reserve request pending 1

SGA POLICY: Cache below reserve request pending 1

*** 2016-09-03 22:37:49.892

SGA POLICY: Cache below reserve getting from component1

SGA POLICY: Cache below reserve request pending 1

SGA POLICY: Cache below reserve request pending 1

SGA POLICY: Cache below reserve request pending 1

*** 2016-09-03 22:38:19.909

必赢体育登录 1

 

当时查了一下资料,觉得有可能与db_cache_size被手工设定有关(这个参数被同事手工设置过),当时检查了一下V$SGA_RESIZE_OPS,发现并没有SGA组件重定义大小的操作失败的记录。由于这个不是急于调整,手头还有正事处理,于是当时就搁置下来。

 

今天晚上去检查、处理时,居然无法重现这个错误了, 调整sga_target_size一下子就成功了,没法重现这个错误了,于是我又特意检查了一下V$SGA_RESIZE_OPS,发现期间出现了shared pool 收缩, DEFAULT buffer cache的增长, 还有一个DEFAULT buffer cache的增长从2016-09-03 22:45:21持续到了2016-09-04 21:13:26,而且状态为INACTIVE。

必赢体育登录 2

必赢体育登录 3

 

在MMON Trace generated with -- Sga Policy: Cache Below Reserve And Cant Get Memory (文档 ID 422954.1)这里面介绍了这个错误出现的原因,是因为在SGA_TARGET里面并没有空闲的内存分配给cache buffer因为它当前的值低于intit.ora或spfile中的设置值。

 

There is no more free memory in the SGA_TARGET to give to the cache buffer as

its value is below the value set in the init.ora file or spfile.

What's happening is that there is no more free memory in the SGA_TARGET to give

to the cache buffer as its value is below the value set.

To stop these messages from occurring, we either:

- Increase the sga_target.

or

- unset the auto-tuned parameters or lower their values:

*.shared_pool_size

*.large_pool_size

*.java_pool_size

*.db_cache_size

When SGA_TARGET is set and no MINIMUM value is set for the automatically tuned

components manually,then they will be assigned a value of 0 initially and an

internal tuning algorithm will optimize the values gradually.

 

当时很奇怪的是,当时我是增加sga_target的值。另外,以前由于一些原因,同事手工设置了db_cache_size和shared_pool_size的值。如下所示

SQL> show parameter db_cache_size

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 2G

SQL> show parameter shared_pool_size

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

shared_pool_size                     big integer 2512M

 

分析至此,能确定一些东西: 这个情况的出现,与手工设置db_cache_size和shared_pool_size参数有关,但是还是有很多地方无法解释得清楚,例如,为什么这种情况不能重现了呢?具体又是什么什么原因导致调整SGA_必赢体育登录,TARGET无法成功(当然,这里由于当时SQL执行时间长,我取消了SQL语句,后面又无法重现这个场景,所以现在已经很难判断是无法修改还是这个SQL需要非常长的时间?)。

 

很多没有想明白的事情,有些只是一些猜测,不能确认。 那么先搁置这些,还是执行下面命令,让SGA返回Automatic Shared Memory Management (ASMM)模式比较靠谱一些。

SQL> alter system set shared_pool_size=0 scope=both;

 

System altered.

 

SQL> alter system set db_cache_size=0 scope=both;

 

System altered.

 

SQL> 
上一篇:没有了 下一篇:数据删除设计