Chinaunix首页 | 论坛 | 博客
  • 博客访问: 655022
  • 博文数量: 70
  • 博客积分: 145
  • 博客等级: 入伍新兵
  • 技术积分: 1150
  • 用 户 组: 普通用户
  • 注册时间: 2012-10-11 08:15
个人简介

没有简介就是最好的简介

文章分类

全部博文(70)

文章存档

2020年(1)

2018年(2)

2017年(3)

2016年(11)

2015年(12)

2014年(16)

2013年(19)

2012年(6)

我的朋友

分类: Oracle

2014-04-09 15:31:25

数据库出现下面的错误:

Tue Apr 08 06:47:49 GMT+08:00 2014Thread 1 advanced to log sequence 5818 (LGWR switch)

  Current log# 2 seq# 5818 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 07:42:51 GMT+08:00 2014Thread 1 advanced to log sequence 5819 (LGWR switch)

  Current log# 1 seq# 5819 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 08:37:46 GMT+08:00 2014Thread 1 advanced to log sequence 5820 (LGWR switch)

  Current log# 2 seq# 5820 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 09:32:53 GMT+08:00 2014Thread 1 advanced to log sequence 5821 (LGWR switch)

  Current log# 1 seq# 5821 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 09:38:32 GMT+08:00 2014Thread 1 advanced to log sequence 5822 (LGWR switch)

  Current log# 2 seq# 5822 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 09:41:44 GMT+08:00 2014Thread 1 cannot allocate new log, sequence 5823

Checkpoint not complete

  Current log# 2 seq# 5822 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 09:41:47 GMT+08:00 2014Thread 1 advanced to log sequence 5823 (LGWR switch)

  Current log# 1 seq# 5823 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 09:47:13 GMT+08:00 2014Thread 1 advanced to log sequence 5824 (LGWR switch)

  Current log# 2 seq# 5824 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 10:27:49 GMT+08:00 2014Thread 1 advanced to log sequence 5825 (LGWR switch)

  Current log# 1 seq# 5825 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 10:53:28 GMT+08:00 2014Thread 1 advanced to log sequence 5826 (LGWR switch)

  Current log# 2 seq# 5826 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 11:32:56 GMT+08:00 2014Thread 1 advanced to log sequence 5827 (LGWR switch)

  Current log# 1 seq# 5827 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 11:57:51 GMT+08:00 2014Thread 1 advanced to log sequence 5828 (LGWR switch)

  Current log# 2 seq# 5828 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 12:22:49 GMT+08:00 2014Thread 1 advanced to log sequence 5829 (LGWR switch)

  Current log# 1 seq# 5829 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 13:17:51 GMT+08:00 2014Thread 1 advanced to log sequence 5830 (LGWR switch)

  Current log# 2 seq# 5830 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 14:02:43 GMT+08:00 2014Thread 1 advanced to log sequence 5831 (LGWR switch)

  Current log# 1 seq# 5831 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 14:51:45 GMT+08:00 2014Thread 1 advanced to log sequence 5832 (LGWR switch)

  Current log# 2 seq# 5832 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 15:12:50 GMT+08:00 2014Thread 1 advanced to log sequence 5833 (LGWR switch)

  Current log# 1 seq# 5833 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 15:37:51 GMT+08:00 2014Thread 1 advanced to log sequence 5834 (LGWR switch)

  Current log# 2 seq# 5834 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 16:02:47 GMT+08:00 2014Thread 1 advanced to log sequence 5835 (LGWR switch)

  Current log# 1 seq# 5835 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 16:17:52 GMT+08:00 2014Thread 1 advanced to log sequence 5836 (LGWR switch)

  Current log# 2 seq# 5836 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 17:12:51 GMT+08:00 2014Thread 1 advanced to log sequence 5837 (LGWR switch)

  Current log# 1 seq# 5837 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 18:07:52 GMT+08:00 2014Thread 1 advanced to log sequence 5838 (LGWR switch)

  Current log# 2 seq# 5838 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 19:02:53 GMT+08:00 2014Thread 1 advanced to log sequence 5839 (LGWR switch)

  Current log# 1 seq# 5839 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 19:57:58 GMT+08:00 2014Thread 1 advanced to log sequence 5840 (LGWR switch)

  Current log# 2 seq# 5840 mem# 0: /dev/rdb_Redo1_2

Tue Apr 08 22:00:37 GMT+08:00 2014Thread 1 advanced to log sequence 5841 (LGWR switch)

  Current log# 1 seq# 5841 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 22:01:39 GMT+08:00 2014Thread 1 cannot allocate new log, sequence 5842

Checkpoint not complete

  Current log# 1 seq# 5841 mem# 0: /dev/rdb_Redo1_1

Tue Apr 08 22:01:41 GMT+08:00 2014Thread 1 advanced to log sequence 5842 (LGWR switch)

  Current log# 2 seq# 5842 mem# 0: /dev/rdb_Redo1_2

Wed Apr 09 02:00:08 GMT+08:00 2014Thread 1 advanced to log sequence 5843 (LGWR switch)

  Current log# 1 seq# 5843 mem# 0: /dev/rdb_Redo1_1

Wed Apr 09 02:01:13 GMT+08:00 2014ALTER SYSTEM ARCHIVE LOG

Wed Apr 09 02:01:13 GMT+08:00 2014Thread 1 cannot allocate new log, sequence 5844

Checkpoint not complete

  Current log# 1 seq# 5843 mem# 0: /dev/rdb_Redo1_1

Wed Apr 09 02:01:15 GMT+08:00 2014Thread 1 advanced to log sequence 5844 (LGWR switch)

  Current log# 2 seq# 5844 mem# 0: /dev/rdb_Redo1_2

Wed Apr 09 02:01:16 GMT+08:00 2014ALTER SYSTEM ARCHIVE LOG

Wed Apr 09 02:01:16 GMT+08:00 2014Thread 1 cannot allocate new log, sequence 5845

Checkpoint not complete

 可能的原因有以下:

  1>日志文件过小,切换过于频繁;

  2>日志组太少,不能满足正常事务量的需要;

  3>日志文件所在磁盘I/O存在瓶颈,导致写出缓慢,阻塞数据库正常运行;

  4>由于数据文件磁盘I/O瓶颈,DBWR写出过于缓慢;

  5>由于事务量巨大,DBWR负荷过高,不堪重负。

如果系统在关键作业时生成的redo size峰值量很高,并且持续很长时间。我们在Alert log上可以看到频繁的online log切换,同时连带出现“check point not complete”或者“could not allocate log sequence”提示。说明日志切换过于频繁。

AWR报告上,我们主要关注两个与redo log相关的事件:log file parallel writelog file sync。如果两个事件出现在top events中,作为dba和调试人员就需要注意了。

Oracle的官方资料中,对redo log的大小设置也是以切换频率而定的,要求调整到15-20分钟进行一次切换。调整的手段主要是增加日志组数量和调大日志成员文件大小。这样,都可以给dbwrarc进程更多的时间在后台进行数据写入和归档。

在实际中,、还要建议关注日志组成员的数目,我们对日志采用多路径冗余手段,是为了防止出现单磁盘文件以外损坏。但是,过多的日志成员数目也会带来性能瓶颈。曾见过一个日志组成员数量是10个文件,散布在10个设备上。这样,就意味lgwr要写入十次才能将一个log entry写入,性能可想而知。
可以通过下面的命令查看oracle两个日志切换之间的时间间隔
select
b.recid,to_char(b.first_time,'yyyy-mon-dd hh24:mi:ss') start_time,
a.recid,to_char(a.first_time,'yyyy-mon-dd hh24:mi:ss') end_time,
round(((a.first_time-b.first_time)*25)*60,2) minutes
from v$log_history a,v$log_history b
where a.recid=b.recid+1
order by a.first_time asc;
使用下面的命令可以查看重做日志的显示信息
select a.member,b.* from v$logfile a,v$log b where a.group#=b.group#;

查看日志文件


 select * from v$logfile;

 

查看oracle日志有4个组并且每个组中只有一个成员。

建议增加group组或者增加组里的成员

使用下面的命令进行增加:

首先创建lv

/usr/sbin/mklv -y 'db_Redo3_1'  -T O -w n -s n -r n -t 'raw' datavg 2

/usr/sbin/mklv -y 'db_Redo3_2'  -T O -w n -s n -r n -t 'raw' datavg 2

/usr/sbin/mklv -y 'db_Redo4_1'  -T O -w n -s n -r n -t 'raw' datavg 2

/usr/sbin/mklv -y 'db_Redo4_2'  -T O -w n -s n -r n -t 'raw' datavg 2

给新建的lv增加权限

#chown oracle:dba /dev/rdb_Redo3_1

#chown oracle:dba /dev/rdb_Redo3_2

#chown oracle:dba /dev/rdb_Redo4_1

#chown oracle:dba /dev/rdb_Redo4_2

增加group组或者将logfile增加到日志组中

alter database add logfile '/dev/rdb_Redo3_1';

alter database add logfile '/dev/rdb_Redo3_2';

alter database add logfile '/dev/rdb_Redo4_1';

alter database add logfile '/dev/rdb_Redo4_2';

或者将logfile增加到日志组中

alter database add logfile member '/dev/rdb_Redo3_2' to group 1;

这里采取第一种方法,增加后的效果如下:

select * from v$logfile;

可以使用下面的方法切换redo写的组,查看状态
alter system switch logfile;
alter system checkpoint;

select GROUP#,THREAD#,STATUS from v$log;



阅读(3734) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~