ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information

2013-09-05 22:18 潇湘隐者阅读(1756) 评论(0) 编辑收藏举报

这篇文章是上篇文章”Expdp 导数错误 ORA-00832”的延续，前几天工作比较忙、累，直到今天才整理发出来。这个数据库实例的参数设置比较诡异其实是有原因的，由于这台数据库服务器系统是32位，数据库也是32位的。对于绝大部分32位系统上的32位数据库，SGA最大的设置都不能超过2G，有的系统最大值甚至不能超过1.7G左右。DBA为了让内存充分利用，不至于浪费内存资源，于是想让SGA_MAX_SIZE最大化，对数据库相关参数做了调整，设置参数USE_INDIRECT_DATA_BUFFERS为TRUE，调整SGA_MAX_SIZE为3424M，然而使用USE_INDIRECT_DATA_BUFFERS参数，就不能在使用其他9i以后新增的内存控制参数了，比如SGA_TARGET、DB_CACHE_SIZE等等，必须通过DB_BLOCK_BUFFERS参数来指定内存的容量。所以SGA_TARGET为0，关闭了ASSM特性。下面述说一下当时的解决问题思路和过程。

首先我试着启用ASSM，设置sga_target的大小跟sga_max_size的大小一致，如下所示，结果报错：

epps> alter system set sga_target=3424M scope=both;

alter system set sga_target=3424M scope=both

ERROR at line 1:

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

ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information

使用命令“oerr 错误类型错误编号” ，查看错误信息的详细原因和Action，

[oracle@get-orasvr02 db_com_sql]$ oerr ora 00824

00824, 00000, "cannot set sga_target due to existing internal settings, see alert log for more information"

// *Cause: Unable to set sga_target due to current parameter settings.

// *Action: See alert log for more information.

通过告警日志查看详细出错信息，结果查看告警日志发现如下提示信息：

Cannot set sga_target with db_block_buffers set

Tue Sep 2 16:08:51 2013

在Metalink上查询了一下这方面的资料，发现SGA_TARGET > 0 不能与db_block_buffer这个过时的参数共存，否则就会出现ORA-00824错误。具体信息如下

Cause

If you enable automatic SGA Management by setting SGA_TARGET >0 and also have db_block_buffers(Obsolete parameter) in your parameter file (pfile/spfile)
Startup of Database fails with ORA-00824 Error

Solution

A) Either you need to disable the Automatic SGA Mangement by setting SGA_Target=0

==OR==

B) Replace the db_block_buffers parameter with db_cache_size parameter

STEPS TO RESOLVE

1. Make an OS copy of the spfile if you do not have a pfile for this database
2. Edit the copy of the spfile to remove the binary stuff before the first parameter
3. Remove the binary stuff after the last parameter.
4. Edit parameters needed to be changed.
5. Save the file and note name and location.
6. Start sqlplus and connect / as sysdba
7. Issue startup pfile = '<full path and file name of file just updated>'
8. Create spfile from pfile.

DB_BLOCK_BUFFERS cannot be combined with the dynamic DB_CACHE_SIZE parameter; combining these parameters in the same parameter file will produce an error.

epps> show parameter db_block_buffers

NAME TYPE VALUE

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

db_block_buffers integer 240000

epps> show parameter db_cache_size

NAME TYPE VALUE

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

db_cache_size big integer 0

epps>