求助，ORA-00471错误，大数据量UPDATE表导致数据库非正常中止

kiki2000

求助，ORA-00471错误，大数据量UPDATE表导致数据库非正常中止

我单位一台HP LH6000,RedhatLinux7.2,Oracle816,内存2G,回滚表空间3G,临时表空间6G,Redo日志文件50M*4个,在多次执行一个SQL语句后数据库非正常中止。

以下是数据库的一些配置：
shared_pool_size= 629145600
large_pool_size= 10485760
java_pool_size= 32768
db_block_buffers= 81920
db_block_size= 4096
compatible= 8.1.0
log_buffer= 10486272
log_checkpoint_interval  = 10000
log_checkpoint_timeout   = 1800
sort_area_size           = 104857600
sort_area_retained_size  = 104857600

SQL语句如下：
update tab_# set field_1=1 where field_2 in (select field_2 from tab_0);
其中tab_0有40万条记录(field_2唯一索引)，tab_#(#=1-31，一天一张表)有400万条记录，一般tab_#有40万左右条记录匹配查询条件，一个语句需时10分钟左右，每执行完一个语句就提交一次，但连续执行2、3次就会导致整个数据库崩溃。

alert日志提示：
PMON: terminating instance due to error 471
Instance terminated by PMON, pid = 12345

tab_#表的storage参数：
  pctfree 10
  pctused 80
  initrans 2
  maxtrans 255
  storage
  (
    initial 20M
    next 20M
    minextents 1
    maxextents 256
    pctincrease 0
  );

kiki2000

高手大侠们都渡假去了吗？还请各位不吝赐教！
谢谢！

rchsh

看了一下，你的数据库配置和表的参数设置问题还是有的：
在数据库初始化参数配置方面
1。shared_pool_size= 629145600 600多M太大了，一般在150---250M之间就可以了，除非你的系统是比较特殊的ERP系统，否则shared_pool_size太大会耗费很多的CPU来管理这些共享内存，把剩下的内存给数据库高速缓冲吧
2。DB_CACHE_SIZE=db_block_buffers*db_block_size/1024/1024=320M太小了，你的物理内存是2GB,建议给DB_CACHE_SIZE分1个GB吧，还有你的系统是OLTP吗，db_block_size=4096是否有点小了，一般为8192就可以了
3。log_buffer= 10486272 为10M大了，一般log_buffer为1-3m就可以了，太大没有什么好处
4。sort_area_size = 104857600
sort_area_retained_size = 104857600
为100M,太大了，很容易造成机器的内存耗尽出现数据库down的情况，一般根据你系统的排序情况和物理内存的大小设置为小于10M就可以了，sort_area_size 是指一个session排序耗费的内存大小，你想想如果有5个session进行排序就是500M很恐怖的
表的存储参数的问题：
pctused 80 太大了，pctused是指当数据表的数据被删除的时候，块中存储的数据的使用率小于pctused 的时候，这个块就被联接到freelist中，以说明改块可以被插入数据。这样不好，该小成40
语句问题
在表tab_# 的filed2建立索引，可能快一些

先将我以上提出的进行调整，然后看看怎么样
再做进一不的调整

kiki2000

前面几天一直等不到大侠们回复，就自己到精华区淘金，发现一篇《[转载]关于SGA设置的一点总结》（http://www.chinaunix.net/jh/19/148118.html），赶紧依样画葫芦，改完以后系统稳定了许多。有时跑个十几张表都没问题，有时撑了几张表就又DOWN掉了。不过这回倒是报了个600错误：
ORA-00600: internal error code, arguments: [17578], [2503799112], [], [], [], [], [], []
DBW0: terminating instance due to error 600
Instance terminated by DBW0, pid = 12345

以下是init.ora的一些新的配置：
shared_pool_size= 209715200 #200M
large_pool_size= 52428800 #50M
java_pool_size= 31457280 #30M
db_block_buffers= 204800 #800M
db_block_size= 4096
compatible= 8.1.0
log_buffer= 20971520 #加了
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
dbwr_io_slaves = 4  #新增
sort_area_size = 10485760 #10M
sort_area_retained_size = 10485760 #10M

ORACLE启动时报的信息：
ORACLE instance started.
Total System Global Area                       1189761008 bytes
Fixed Size                                          69616 bytes
Variable Size                                   329850880 bytes
Database Buffers                                838860800 bytes
Redo Buffers                                     20979712 bytes
Database mounted.
Database opened.

感谢rchsh大侠的回复，高手啊！咱这DB属于白天OLTP（量不是很大），晚上DSS那种（主要），db_block_size=4096是当初建库时不懂设置的，后来想改大点一直没机会，因为里面有几百G的数据，难办。PCTUSED我倒是没有留意过，因那些表只会一次性地插数据，不会删除部分记录，一般都直接TRUNCATE后再循环使用。至于索引，刚开始有建，后来感觉有建没建耗时差不多，就免了。log_buffer的问题，主要是看到alert.log中的日志切换太快，2秒左右切一次，就给它加了点，不知太大会有何坏处，当然我会试试小点的情况。我曾在命令行观察过UPDATE和COMMIT的耗时，发现COMMIT耗时比UPDATE还久，不知如何处理，我给加了“dbwr_io_slaves = 4”。

kiki2000

对了，俺们那台机子用的是RAID5磁盘阵列，晚上跑那些UPDATE比较稳定。

nocode

你指的两秒切换一次是说你的redo log吗？太快了些吧，你用的是非归档模式还是归档模式？太频繁的log切换同样会消耗主机性能，建议你把log加大，而不是把log buffer加大，这参数超过1M没太大作用。
你不建索引的话，你update同样做全表扫描，难道都是无条件更新？commit太慢我怀疑是io有冲突，不知道你的数据文件都是怎么分布的。

rchsh

现在报600的错误：
“DBW0: terminating instance due to error 600
Instance terminated by DBW0, pid = 12345
”
是否与你的dbwr_io_slaves = 4参数有关系，去掉它试试，除非你的系统支持异步IO,否则没有必要加这个

还有你的日志切换太频繁了，加大日志文件的大小，日志切换频繁与日志缓冲没有关系，与日志文件的大小有关系
commit 慢，是什么原因呢？一般在commit 的时候会出发LGWR，与数据库文件写没有什么关系，可能是LGWR出现写的瓶颈，你最好做一个statispack对你的数据库做一个全面的报告

kiki2000

谢谢nocode和rchsh二位大侠！去掉了dbwr_io_slaves 设置，log_buffer设为1M，日志切换半分钟到1分半钟不等，运行稳定。INIT.ORA如下：
shared_pool_size= 209715200 #200M
large_pool_size= 52428800 #50M
java_pool_size= 31457280 #30M
db_block_buffers= 204800 #800M
db_block_size= 4096
compatible= 8.1.0
log_buffer= 1048576 #1M
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
sort_area_size = 10485760 #10M
sort_area_retained_size = 10485760 #10M