查看alert.log发现有很多Thread 1 cannot allocate new log, sequence 31.这样信息
分析觉得应该是dbwr写的太慢,redo切换的太频繁,日志量比较大造成的.
查看redo
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 103 100288000 1 NO INACTIVE 21308155 2014-4-3 16
2 1 104 100288000 1 NO INACTIVE 21308158 2014-4-3 16
3 1 105 100288000 1 NO CURRENT 21308162 2014-4-3 16
准备把redo增加到200M
1、增加日志组4,5
alter database add logfile group 4 '/oracle/oradata/orcl/redo04.log' size 200M;
alter database add logfile group 5 '/oracle/oradata/orcl/redo05.log' size 200M;
2、切换日志组
alter system switch logfile;
alter system switch logfile;
3、1,2,3个日志组为不活动状态,删除日志组1,2,3
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
4、重新添加日志组1,2,3
alter database add logfile group 1 '/oracle/oradata/orcl/redo01.log' size 200M;
alter database add logfile group 2 '/oracle/oradata/orcl/redo02.log' size 200M;
alter database add logfile group 3 '/oracle/oradata/orcl/redo03.log' size 200M;
5、切换日志组
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
6、删除日志组4,5
alter database drop logfile group 4;
alter database drop logfile group 5;
7、观察alter日志文件