在生产环境下,几乎每天都会发生一次数据库被几百个insert和update堵死
造成应用中间件死掉
这个时候查看oracle是没有锁的,所以肯定排除掉了死锁的可能性
如下内容为数据库日志(有朋友说是redo过小造成的,但放大到多少合适呢?)Thu Jan 10 09:53:03 2013
Thread 1 cannot allocate new log, sequence 5024
Checkpoint not complete
Current log# 1 seq# 5023 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5023 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 09:53:46 2013
Thread 1 advanced to log sequence 5024 (LGWR switch)
Current log# 2 seq# 5024 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5024 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 09:55:13 2013
Thread 1 cannot allocate new log, sequence 5025
Checkpoint not complete
Current log# 2 seq# 5024 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5024 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 09:55:51 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 09:55:51 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 09:55:52 2013
Thread 1 advanced to log sequence 5025 (LGWR switch)
Current log# 3 seq# 5025 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5025 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 09:57:05 2013
Thread 1 cannot allocate new log, sequence 5026
Checkpoint not complete
Current log# 3 seq# 5025 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5025 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 09:57:46 2013
Thread 1 advanced to log sequence 5026 (LGWR switch)
Current log# 1 seq# 5026 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5026 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 09:57:47 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 09:59:01 2013
Thread 1 cannot allocate new log, sequence 5027
Checkpoint not complete
Current log# 1 seq# 5026 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5026 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 09:59:40 2013
Thread 1 advanced to log sequence 5027 (LGWR switch)
Current log# 2 seq# 5027 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5027 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:00:53 2013
Thread 1 cannot allocate new log, sequence 5028
Checkpoint not complete
Current log# 2 seq# 5027 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5027 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:01:31 2013
Thread 1 advanced to log sequence 5028 (LGWR switch)
Current log# 3 seq# 5028 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5028 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 10:02:51 2013
Thread 1 cannot allocate new log, sequence 5029
Checkpoint not complete
Current log# 3 seq# 5028 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5028 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 10:03:31 2013
Thread 1 advanced to log sequence 5029 (LGWR switch)
Current log# 1 seq# 5029 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5029 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 10:04:54 2013
Thread 1 cannot allocate new log, sequence 5030
Checkpoint not complete
Current log# 1 seq# 5029 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5029 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 10:05:31 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 10:05:31 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 10:05:31 2013
Thread 1 advanced to log sequence 5030 (LGWR switch)
Current log# 2 seq# 5030 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5030 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:06:51 2013
Thread 1 cannot allocate new log, sequence 5031
Checkpoint not complete
Current log# 2 seq# 5030 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5030 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:07:29 2013
Thread 1 advanced to log sequence 5031 (LGWR switch)
Current log# 3 seq# 5031 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5031 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 10:08:58 2013
Thread 1 cannot allocate new log, sequence 5032
Checkpoint not complete
Current log# 3 seq# 5031 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5031 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 10:09:32 2013
Thread 1 advanced to log sequence 5032 (LGWR switch)
Current log# 1 seq# 5032 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5032 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 10:10:55 2013
Thread 1 cannot allocate new log, sequence 5033
Checkpoint not complete
Current log# 1 seq# 5032 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5032 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 10:11:32 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 10:11:32 2013
Thread 1 advanced to log sequence 5033 (LGWR switch)
Current log# 2 seq# 5033 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5033 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:12:50 2013
Thread 1 cannot allocate new log, sequence 5034
Checkpoint not complete
Current log# 2 seq# 5033 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5033 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:13:23 2013
Thread 1 advanced to log sequence 5034 (LGWR switch)
Current log# 3 seq# 5034 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5034 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
造成应用中间件死掉
这个时候查看oracle是没有锁的,所以肯定排除掉了死锁的可能性
如下内容为数据库日志(有朋友说是redo过小造成的,但放大到多少合适呢?)Thu Jan 10 09:53:03 2013
Thread 1 cannot allocate new log, sequence 5024
Checkpoint not complete
Current log# 1 seq# 5023 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5023 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 09:53:46 2013
Thread 1 advanced to log sequence 5024 (LGWR switch)
Current log# 2 seq# 5024 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5024 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 09:55:13 2013
Thread 1 cannot allocate new log, sequence 5025
Checkpoint not complete
Current log# 2 seq# 5024 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5024 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 09:55:51 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 09:55:51 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 09:55:52 2013
Thread 1 advanced to log sequence 5025 (LGWR switch)
Current log# 3 seq# 5025 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5025 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 09:57:05 2013
Thread 1 cannot allocate new log, sequence 5026
Checkpoint not complete
Current log# 3 seq# 5025 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5025 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 09:57:46 2013
Thread 1 advanced to log sequence 5026 (LGWR switch)
Current log# 1 seq# 5026 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5026 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 09:57:47 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 09:59:01 2013
Thread 1 cannot allocate new log, sequence 5027
Checkpoint not complete
Current log# 1 seq# 5026 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5026 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 09:59:40 2013
Thread 1 advanced to log sequence 5027 (LGWR switch)
Current log# 2 seq# 5027 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5027 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:00:53 2013
Thread 1 cannot allocate new log, sequence 5028
Checkpoint not complete
Current log# 2 seq# 5027 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5027 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:01:31 2013
Thread 1 advanced to log sequence 5028 (LGWR switch)
Current log# 3 seq# 5028 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5028 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 10:02:51 2013
Thread 1 cannot allocate new log, sequence 5029
Checkpoint not complete
Current log# 3 seq# 5028 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5028 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 10:03:31 2013
Thread 1 advanced to log sequence 5029 (LGWR switch)
Current log# 1 seq# 5029 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5029 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 10:04:54 2013
Thread 1 cannot allocate new log, sequence 5030
Checkpoint not complete
Current log# 1 seq# 5029 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5029 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 10:05:31 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 10:05:31 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 10:05:31 2013
Thread 1 advanced to log sequence 5030 (LGWR switch)
Current log# 2 seq# 5030 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5030 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:06:51 2013
Thread 1 cannot allocate new log, sequence 5031
Checkpoint not complete
Current log# 2 seq# 5030 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5030 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:07:29 2013
Thread 1 advanced to log sequence 5031 (LGWR switch)
Current log# 3 seq# 5031 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5031 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 10:08:58 2013
Thread 1 cannot allocate new log, sequence 5032
Checkpoint not complete
Current log# 3 seq# 5031 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5031 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
Thu Jan 10 10:09:32 2013
Thread 1 advanced to log sequence 5032 (LGWR switch)
Current log# 1 seq# 5032 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5032 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 10:10:55 2013
Thread 1 cannot allocate new log, sequence 5033
Checkpoint not complete
Current log# 1 seq# 5032 mem# 0: +DATA/gx/onlinelog/group_1.667.802435761
Current log# 1 seq# 5032 mem# 1: +DATA/gx/onlinelog/group_1.647.802435765
Thu Jan 10 10:11:32 2013
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Thu Jan 10 10:11:32 2013
Thread 1 advanced to log sequence 5033 (LGWR switch)
Current log# 2 seq# 5033 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5033 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:12:50 2013
Thread 1 cannot allocate new log, sequence 5034
Checkpoint not complete
Current log# 2 seq# 5033 mem# 0: +DATA/gx/onlinelog/group_2.669.802435767
Current log# 2 seq# 5033 mem# 1: +DATA/gx/onlinelog/group_2.668.802435771
Thu Jan 10 10:13:23 2013
Thread 1 advanced to log sequence 5034 (LGWR switch)
Current log# 3 seq# 5034 mem# 0: +DATA/gx/onlinelog/group_3.267.802435775
Current log# 3 seq# 5034 mem# 1: +DATA/gx/onlinelog/group_3.266.802435779
select * from v$logfile;一般增加redo大小,默认是50M,
再者增加redo日志组的数量
Thread 1 cannot allocate new log, sequence 5024
Thu Jan 10 09:55:13 2013
Thread 1 cannot allocate new log, sequence 5025
Thu Jan 10 09:57:05 2013
Thread 1 cannot allocate new log, sequence 5026
Thu Jan 10 09:59:01 2013
Thread 1 cannot allocate new log, sequence 5027
====================================================
同意楼上,上午9-10点,应该是业务高峰期,日志切换太过频繁,平均 2 分钟。可能都来不及归档。建议增加日志大小。把日志切换间隔控制在30分钟左右为好。