update导致的redo日志问题

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--&gtlog2--&gtlog1，此时（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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15191593/viewspace-692480/