最近发现日志服务器切换redo log太频繁了,大概一分钟一次,如此过于
频繁也不是个办法,
于是加大日志大小和增加日志组的数量来解决。
另外除了日志切换频繁以外,还有Thread 1 cannot allocate new log、
Checkpoint not complete错误提示
获知关于"Thread 1 cannot allocate new log"警告提示的解释:
一旦发生"Thread 1 cannot allocate new log",表明系统的checkpoint
没有来得及完成,也就是说 buffer cache 中的dirty data还没有完全写
到数据文件,就已经有大量的日志需要写入到系统。而系统只能通知应用:
checkpoint 还没有完成,你只能等待。这个时候,系统就基本处于hang
状态了 When the database waits on checkpoints,redo generation is
stopped until the log switch is done
下面我们来看看日志的实时刷新信息
[root@radius ~]# tail -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/
alert_$ORACLE_SID.log
Thu Jan 6 22:26:35 2011
Thread 1 advanced to log sequence 151078 (LGWR switch)
Current log# 3 seq# 151078 mem# 0: /ora/oradata/radius/redo03.log
Thu Jan 6 22:27:08 2011
Thread 1 cannot allocate new log, sequence 151079
Checkpoint not complete
Current log# 3 seq# 151078 mem# 0: /ora/oradata/radius/redo03.log
Thu Jan 6 22:27:12 2011
Thread 1 advanced to log sequence 151079 (LGWR switch)
Current log# 1 seq# 151079 mem# 0: /ora/oradata/radius/redo01.log
Thu Jan 6 22:27:45 2011
Thread 1 cannot allocate new log, sequence 151080
Checkpoint not complete
Current log# 1 seq# 151079 mem# 0: /ora/oradata/radius/redo01.log
可以看出切换日志间隔不到一分钟,增加日志组容量和数据刻不容缓呐
1.查询下当前redo log 情况
SQL> select group#,status,archived,bytes/1024/1024 from v$log;
GROUP# STATUS ARC BYTES/1024/1024
---------- ---------------- --- ---------------
1 INACTIVE YES 50
2 CURRENT NO 50
3 UNUSED YES 50
可以看出有3组50M redo log
2.根据目前的切换日志的频率,决定将日志组增加至5组,每组200M.
SQL> alter database add logfile group 4 '/ora/oradata/radius/
redo04.log' size 200m;
Database altered.
SQL> alter database add logfile group 5 '/ora/oradata/radius/
redo05.log' size 200m;
Database altered.
SQL> alter database add logfile group 6 '/ora/oradata/radius/
redo06.log' size 200m;
Database altered.
SQL> alter database add logfile group 7 '/ora/oradata/radius/
redo07.log' size 200m;
Database altered.
SQL> alter database add logfile group 8 '/ora/oradata/radius/
redo08.log' size 200m;
Database altered.
查询现在redo log状态
SQL> select group#,status,archived,bytes/1024/1024 from v$log;
GROUP# STATUS ARC BYTES/1024/1024
---------- ---------------- --- ---------------
1 INACTIVE YES 50
2 ACTIVE YES 50
3 CURRENT NO 50
4 UNUSED YES 200
5 UNUSED YES 200
6 UNUSED YES 200
7 UNUSED YES 200
8 UNUSED YES 200
3.删除之前的三个50M的redo log组
由于当前日志还在group# 3 redo log上,所以需要切换日志到其他的日志组中去.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,archived,bytes/1024/1024 from v$log;
GROUP# STATUS ARC BYTES/1024/1024
---------- ---------------- --- ---------------
1 INACTIVE YES 50
2 ACTIVE YES 50
3 ACTIVE YES 50
4 CURRENT NO 200
5 UNUSED YES 200
6 UNUSED YES 200
7 UNUSED YES 200
8 UNUSED YES 200
从上面的status字段可以看出group# 2,3 redo log 的状态均是ACTIVE,
也就是内存中的脏数据还没有写到数据文件中,这时oracle是不允许你删除的,
如果你硬要删除会出现下面这个提示
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance radius (thread 1)
ORA-00312: online log 2 thread 1: '/ora/oradata/radius/redo02.log'
如果你需要马上删除这个redo log ,你可以使用checkpoint来将脏数据写
进数据文件(磁盘)中,之后再将group# 1,2,3的redo log一一删除。
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
查看当前redo log状态
SQL> select group#,status,archived,bytes/1024/1024 from v$log;
GROUP# STATUS ARC BYTES/1024/1024
---------- ---------------- --- ---------------
4 CURRENT NO 200
5 INACTIVE YES 200
6 INACTIVE YES 200
7 INACTIVE YES 200
8 ACTIVE YES 200
4.删除不可用的redo log
使用Linux命令删除之。
5.每天日志switch数量的查询方法
select to_char(first_time,'yyyy-mm-dd') day1,count(*)
from v$log_history where first_time>=
to_date('2011-01-11','yyyy-mm-dd')
group by to_char(first_time,'yyyy-mm-dd');
转自:http://netclassroom.blog.163.com/blog/static/135751344201182113724970/