oracle log file switch (checkpoint incomplete)的解决
昨天晚上发现百万级的数据更新突然变得非常慢,检察系统状况后发现,频繁出现 oracle log file switch (checkpoint incomplete).
之后select * from v$log,发现每个redo log只给了50M空间,初步怀疑是redo log 过小,导致批量更新时无法完成checkpoint .
试着扩4组256M,更新瞬时通过,问题即可解决.
alter database add logfile thread 1 group 5 ('/x/redo05.log') size 256M; alter database add logfile thread 2 group 6 ('/x/redo06.log') size 256M; alter database add logfile thread 1 group 7 ('/x/redo07.log') size 256M; alter database add logfile thread 2 group 8 ('/x/redo08.log') size 256M;
今天,将原来的4组redolog替换成256M
1)观察1-4皆为INACTIVE,删除.
alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; alter database drop logfile group 4;
2)删除物理文件
[oracle@a]$ rm redo01.log [oracle@a]$ rm redo02.log [oracle@a]$ rm redo03.log [oracle@a]$ rm redo04.log
3)重建
alter database add logfile thread 1 group 1 ('/oradata/backera3/redo01.log') size 256M; alter database add logfile thread 2 group 2 ('/oradata/backera3/redo02.log') size 256M; alter database add logfile thread 1 group 3 ('/oradata/backera3/redo03.log') size 256M; alter database add logfile thread 2 group 4 ('/oradata/backera3/redo04.log') size 256M;
至此,每节点4组256M redo log ,问题完满解决.