日志切换 redo log 频繁

2011-09-02 11:37:24|  分类： oracle学习 |  标签：redo日志  切换频繁  |字号 订阅

最近发现日志服务器切换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/