ITPub博客

首页 > Linux操作系统 > Linux操作系统 > update导致的redo日志问题

update导致的redo日志问题

原创 Linux操作系统 作者:j3com 时间:2011-04-14 11:21:42 0 删除 编辑
Wed Apr 13 21:26:48 2011
ARCH: Connecting to console port...
FAL[server, ARC0]: Complete FAL noexpedite archive (thread 1 sequence 52849 destination bfcaitong)
Wed Apr 13 22:59:32 2011
Thread 1 cannot allocate new log, sequence 52851
Checkpoint not complete
Current log# 1 seq# 52850 mem# 0: /data/record/redo01.dbf
Private_strands 103 at log switch
Thread 1 advanced to log sequence 52851
Current log# 4 seq# 52851 mem# 0: /data/record/redo004.log
Wed Apr 13 22:59:33 2011
ARC0: Evaluating archive log 1 thread 1 sequence 52850
ARC0: Destination LOG_ARCHIVE_DEST_2 archival not expedited
Wed Apr 13 22:59:33 2011
caitong; ARC0: Beginning to archive log 1 thread 1 sequence 52850 (7.46907323:7.47422115)
ARCH: Connecting to console port...
Wed Apr 13 22:59:33 2011
caitong; ARC0: Creating local archive destination LOG_ARCHIVE_DEST_1: '/data/archive/caitong_1_52850_530105816.dbf' (thread 1 sequence 52850)
ARCH: Connecting to console port...
Wed Apr 13 23:01:01 2011
caitong; ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1: '/data/archive/caitong_1_52850_530105816.dbf'
Wed Apr 13 23:01:01 2011
ARCH: Connecting to console port...
Committing creation of archivelog '/data/archive/caitong_1_52850_530105816.dbf'
Invoking non-expedited destination LOG_ARCHIVE_DEST_2 thread 1 sequence 52850 host bfcaitong
...............................
..... 此处省略 ............
...............................
Wed Apr 13 23:54:36 2011
Thread 1 cannot allocate new log, sequence 52856
Checkpoint not complete
Current log# 1 seq# 52855 mem# 0: /data/record/redo01.dbf
Wed Apr 13 23:54:56 2011
Error 3113 trapped in 2PC on transaction 185.45.58168. Cleaning up.
Wed Apr 13 23:54:56 2011
Error 3113 trapped in 2PC on transaction 111.5.222172. Cleaning up.
Wed Apr 13 23:54:56 2011
Error 3113 trapped in 2PC on transaction 99.34.329052. Cleaning up.
Wed Apr 13 23:54:57 2011
Error 3113 trapped in 2PC on transaction 132.29.151857. Cleaning up.
Wed Apr 13 23:54:57 2011
Error 3113 trapped in 2PC on transaction 190.6.40235. Cleaning up.
Wed Apr 13 23:54:57 2011
Error 3113 trapped in 2PC on transaction 197.43.37632. Cleaning up.
Wed Apr 13 23:54:57 2011
Error 3113 trapped in 2PC on transaction 180.37.74408. Cleaning up.
Wed Apr 13 23:54:57 2011
Error 3113 trapped in 2PC on transaction 146.45.175542. Cleaning up.
Wed Apr 13 23:54:57 2011
Error 3113 trapped in 2PC on transaction 195.30.40897. Cleaning up.
Wed Apr 13 23:54:58 2011
Error 3113 trapped in 2PC on transaction 204.30.36271. Cleaning up.
Wed Apr 13 23:54:58 2011
Error 3113 trapped in 2PC on transaction 130.9.208333. Cleaning up.
Wed Apr 13 23:54:58 2011
Error 3113 trapped in 2PC on transaction 160.34.147293. Cleaning up.
Wed Apr 13 23:54:58 2011
Error 3113 trapped in 2PC on transaction 144.44.137996. Cleaning up.
Wed Apr 13 23:54:58 2011
Error 3113 trapped in 2PC on transaction 150.43.181730. Cleaning up.
Wed Apr 13 23:54:58 2011
Error 3113 trapped in 2PC on transaction 182.44.73998. Cleaning up.
Wed Apr 13 23:54:58 2011
Error 3113 trapped in 2PC on transaction 194.22.41128. Cleaning up.
Wed Apr 13 23:55:17 2011
Private_strands 103 at log switch
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Thread 1 advanced to log sequence 52856
Current log# 4 seq# 52856 mem# 0: /data/record/redo004.log
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-02050: transaction 130.9.208333 rolled back, some remote DBs may be in-doubt
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:17 2011
Error stack returned to user:
ORA-02050: transaction 144.44.137996 rolled back, some remote DBs may be in-doubt
ORA-03113: end-of-file on communication channel
Wed Apr 13 23:55:20 2011
DISTRIB TRAN CAITONG.8176bd16.130.9.208333
is local tran 130.9.208333 (hex=82.09.32dcd)
insert pending collecting tran, scn=30112563905 (hex=7.02d942c1)
Wed Apr 13 23:55:21 2011
DISTRIB TRAN CAITONG.8176bd16.130.9.208333
is local tran 130.9.208333 (hex=82.09.32dcd))
delete pending collecting tran, scn=30112563905 (hex=7.02d942c1)
Wed Apr 13 23:55:23 2011
ARC0: Evaluating archive log 1 thread 1 sequence 52855
ARC0: Destination LOG_ARCHIVE_DEST_2 archival not expedited
Wed Apr 13 23:55:23 2011
caitong; ARC0: Beginning to archive log 1 thread 1 sequence 52855 (7.47727439:7.47792817)
ARCH: Connecting to console port...
-------------------------------------------
分析: 该时间段点有针对几个百万级表的更新操作,经与开发确认居然没有加where条件, 基本可以确认是这些操作引起的.
方案:
1. 增加日志组
2. 协调开发人员, 修改update操作方式.

-------------------------------------------

这里引用: http://tolywang.itpub.net/post/48/314593
的解释->
"假设数据库存在两个日志组log1和log2,首先,-->log1-->log2-->log1,此时(log2切换到log1)触发checkpoint,该checkpoint will flush dirty block to datafile,从而触发DBWn书写dirty buffer,等到log1覆盖的dirty block全部被写入datafile后才能使用log1(循环使用),如果DBWn写入过慢,LGWR必须等待DBWn完成,则这时会出现“checkpoint not completed!"

同时咨询了一个朋友得到类似的回馈:
" 日志组在切换的时候会触发一个checkpoint这个时候ckpt进程会通知dbwn进程把该日志组所保护的脏数据写入到数据文件,checkpoint完成之前该日志组是不能被重用如果试图去重用alter日志里面就会出现这个错误,这个时候oracle会尽可能把所用的处理能力交给dbwn去完成这个操作"
" 增长日志组可以延长日志组的切换时间这样可以为dbwn争取到更多时间."








来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15191593/viewspace-692480/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-04-14

  • 博文量
    4
  • 访问量
    4364