OGG-ORA0001unique constraint
来源：互联网 发布：python中九九乘法表 编辑：程序博客网 时间：2024/06/05 09:45
GGSCI (host_100.160) 9> view report REP160
2015-07-03 08:23:03 WARNING OGG-00869 Aborting BATCHSQL transaction. Detected inconsistent result: executed 1 operations in batch, resulting in 0 aff
ected rows.
2015-07-03 08:23:03 WARNING OGG-01137 BATCHSQL suspended, continuing in normal mode.
2015-07-03 08:23:03 WARNING OGG-01003 Repositioning to rba 115192468 in seqno 4002.
2015-07-03 08:23:03 WARNING OGG-00869 OCI Error ORA-00001: unique constraint (RADAR.PK_ON_BUSIN_CHANCE) violated (status = 1), SQL <INSERT INTO "RADA
R"."ON_BUSIN_CHANCE" ("BC_ID","INDIVIDFLAG","SORTTAG","TITLE","TYPE","BRANDID","KEYWORD","KEYWORD2","KEYWORD3","LPACKAGE","DELIVER","TRANSPORT","UNIT",
"VALIDDATE","PUBDATE","PROVIDERI>.
2015-07-03 08:23:03 WARNING OGG-01004 Aborted grouped transaction on 'RADAR.ON_BUSIN_CHANCE', Database error 1 (OCI Error ORA-00001: unique constrain
t (RADAR.PK_ON_BUSIN_CHANCE) violated (status = 1), SQL <INSERT INTO "RADAR"."ON_BUSIN_CHANCE" ("BC_ID","INDIVIDFLAG","SORTTAG","TITLE","TYPE","BRANDID
","KEYWORD","KEYWORD2","KEYWORD3","LPACKAGE","DELIVER","TRANSPORT","UNIT","VALIDDATE","PUBDATE","PROVIDERI>).
2015-07-03 08:23:03 WARNING OGG-01003 Repositioning to rba 115192468 in seqno 4002.
2015-07-03 08:23:03 WARNING OGG-01154 SQL error 1 mapping NEWHC.ON_BUSIN_CHANCE to RADAR.ON_BUSIN_CHANCE OCI Error ORA-00001: unique constraint (RADA
R.PK_ON_BUSIN_CHANCE) violated (status = 1), SQL <INSERT INTO "RADAR"."ON_BUSIN_CHANCE" ("BC_ID","INDIVIDFLAG","SORTTAG","TITLE","TYPE","BRANDID","KEYW
ORD","KEYWORD2","KEYWORD3","LPACKAGE","DELIVER","TRANSPORT","UNIT","VALIDDATE","PUBDATE","PROVIDERI>.
2015-07-03 08:23:03 WARNING OGG-01003 Repositioning to rba 115192468 in seqno 4002.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Reading ./dirdat/td004002, current RBA 115192468, 0 records
Report at 2015-07-03 08:23:03 (activity since 2015-07-03 08:22:58)
From Table NEWHC.ON_BUSIN_CHANCE to RADAR.ON_BUSIN_CHANCE:
# inserts: 0
# updates: 0
# deletes: 0
# discards: 1
BATCHSQL statistics:
Batch operations: 115
Batches: 0
Batches executed: 0
Queues: 0
Batches in error: 1
Normal mode operations: 2
Immediate flush operations: 1
PK collisions: 4
UK collisions: 1
FK collisions: 0
Thread batch groups: 0
Commits: 0
Rollbacks: 1
Queue flush calls: 0
Ops per batch: N/A
Ops per batch executed: N/A
Ops per queue: N/A
Parallel batch rate: N/A
DDL replication statistics:
Operations: 0
Mapped operations: 0
Unmapped operations: 0
Other operations: 0
Excluded operations: 0
Errors: 0
Retried errors: 0
Discarded errors: 0
Ignored errors: 0
Last log location read:
FILE: ./dirdat/td004002
SEQNO: 4002
RBA: 115192468
TIMESTAMP: 2015-07-02 21:20:10.518672
EOF: NO
READERR: 0
2015-07-03 08:23:03 ERROR OGG-01668 PROCESS ABENDING.
查看
[oracle@192.168.100.160 gg]$ view ./dirrpt/rep160.dsc
OCI Error ORA-00001: unique constraint (RADAR.PK_ON_BUSIN_CHANCE) violated (status = 1), SQL <INSERT INTO "RADAR"."ON_BUSIN_CHANCE" ("BC_ID","INDIVIDFLAG","SORTTAG","TITLE","TYPE","BRANDID","KEYWORD","KEYWORD2","KEYWORD3","LPACKAGE","DELIVER","TRANSPORT","UNIT","VALIDDATE","PUBDATE","PROVIDERI>
Aborting transaction on ./dirdat/td beginning at seqno 4002 rba 115192468
error at seqno 4002 rba 115192468
Problem replicating NEWHC.ON_BUSIN_CHANCE to RADAR.ON_BUSIN_CHANCE
Mapping problem with insert record (target format)...
*
BC_ID = 441933626
INDIVIDFLAG = NULL
SORTTAG = 0
TITLE = <Raw Data>
可以知道目标端RADAR.PK_ON_BUSIN_CHANCE表上的BC_ID = 441933626字段重复
处理办法
1.跳过当前事务
2.尝试加入 handlecollisions 参数解决该问题
3.尝试手动修复 target 上的数据
4.尝试加入REPERROR 参数 ignore这个错误
1.跳过当前事务
由以上信息查找下一个事务
[oracle@192.168.100.160 gg]$ ./logdump
Oracle GoldenGate Log File Dump Utility
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Logdump 1 >open ./dirdat/td004002
Current LogTrail is /oracle/oradat1/gg/dirdat/td004002
Logdump 2 >pos 115192468
Reading forward from RBA 115192468
Logdump 3 >n
2015/07/02 21:20:10.518.672 Insert Len 744 RBA 115192468
Name: NEWHC.ON_BUSIN_CHANCE
After Image: Partition 4 G b
0000 000d 0000 0009 3434 3139 3333 3632 3600 0100 | ........441933626...
03ff ff00 0002 0003 0000 3000 0300 3c00 0000 38cc | ..........0...<...8.
d8bc db20 bae3 d4b4 cfe9 20b0 dfec b5ca c0bd e720 | ... ...... ........
c8ab c3de b9b1 b6d0 beab c6b7 b4b2 c9cf d3c3 c6b7 | ....................
cbc4 bcfe ccd7 20bd adcb d5d7 dcb4 fa00 0400 04ff | ...... .............
ff00 0000 0500 0500 0000 0130 0006 0022 0000 001e | ...........0..."....
bae3 d4b4 cfe9 b4b2 c9cf d3c3 c6b7 2cb4 b2c9 cfd3 | ..............,.....
Logdump 4 >n
2015/07/02 21:20:10.518.672 Insert Len 732 RBA 115193344
Name: NEWHC.ON_BUSIN_CHANCE
After Image: Partition 4 G m
0000 000d 0000 0009 3434 3139 3333 3633 3700 0100 | ........441933637...
03ff ff00 0002 0003 0000 3000 0300 3000 0000 2cd2 | ..........0...0...,.
bbbc b6b4 fac0 edbd f0cb bfc0 f220 bac0 bbaa ccec | ............. ......
cbbf d0e5 bba8 ccd7 bcfe 20bd adcb d5ca a1d7 dcb4 | .......... .........
fac0 ed00 0400 04ff ff00 0000 0500 0500 0000 0130 | ...................0
0006 0022 0000 001e bdf0 cbbf c0f2 d0e5 bba8 ccd7 | ..."................
bcfe 2cd0 e5bb a8cc d7bc fe2c ccec cbbf d0e5 0007 | ..,........,........
GGSCI (host_100.160) 9> alter replicat rep160, extrba 115193344
REPLICAT altered.
GGSCI (host_100.160) 12> start *
当然如果还有失败的事务还可以继续next用上面的方法，不过如果有几个连续的事务需要skip，那就可以用另外一个方法
start XXX skiptransaction
2.尝试加入 handlecollisions 参数解决该问题
1）源端删除了目标端没有的记录可以使用
start XXX skiptransaction
2）target丢失update记录(missing update)，更新的键值是非主键=》 忽略该问题并不记录到discardfile
target丢失update记录(missing update)，更新的键值是主键
加入HANDLECOLLISIONS后，rep可以继续工作且不生成discard记录
丢失update的更新操作是针对主键的更新，此时replicat会尝试插入一条记录而非忽略该update。注意插入的记录可能不是完整的行，若要求完整的行记录则要求EXTRACT使用PKUPDATE选项。需要加入的选项是FETCHOPTIONS FETCHPKUPDATECOLS，将以上选项加入到EXTRACT参数文件中，并重启EXTRACT。 这将引起extract捕获完整的主键更新镜像。
3）重复插入已存在的主键值到target表中，这将被replicat转换为UPDATE现有主键值的行的其他非主键列：
处理完之后一定要取消HANDLECOLLISIONS参数
可以通过send 命令动态取消HANDLECOLLISIONS
send XXX, NOHANDLECOLLISIONS
4.尝试加入REPERROR 参数 ignore这个错误
可以忽略掉重复数据的插入而其他类型的报错则abend
REPERROR (DEFAULT, ABEND)
REPERROR (-1, IGNORE)
如当DDL复制报ORA-1430错误，传递了重复的alter语句导致，则可以用ddlerror （1430, discard）将错误信息扔到discard文件里。
2015-07-03 08:23:03 WARNING OGG-00869 Aborting BATCHSQL transaction. Detected inconsistent result: executed 1 operations in batch, resulting in 0 aff
ected rows.
2015-07-03 08:23:03 WARNING OGG-01137 BATCHSQL suspended, continuing in normal mode.
2015-07-03 08:23:03 WARNING OGG-01003 Repositioning to rba 115192468 in seqno 4002.
2015-07-03 08:23:03 WARNING OGG-00869 OCI Error ORA-00001: unique constraint (RADAR.PK_ON_BUSIN_CHANCE) violated (status = 1), SQL <INSERT INTO "RADA
R"."ON_BUSIN_CHANCE" ("BC_ID","INDIVIDFLAG","SORTTAG","TITLE","TYPE","BRANDID","KEYWORD","KEYWORD2","KEYWORD3","LPACKAGE","DELIVER","TRANSPORT","UNIT",
"VALIDDATE","PUBDATE","PROVIDERI>.
2015-07-03 08:23:03 WARNING OGG-01004 Aborted grouped transaction on 'RADAR.ON_BUSIN_CHANCE', Database error 1 (OCI Error ORA-00001: unique constrain
t (RADAR.PK_ON_BUSIN_CHANCE) violated (status = 1), SQL <INSERT INTO "RADAR"."ON_BUSIN_CHANCE" ("BC_ID","INDIVIDFLAG","SORTTAG","TITLE","TYPE","BRANDID
","KEYWORD","KEYWORD2","KEYWORD3","LPACKAGE","DELIVER","TRANSPORT","UNIT","VALIDDATE","PUBDATE","PROVIDERI>).
2015-07-03 08:23:03 WARNING OGG-01003 Repositioning to rba 115192468 in seqno 4002.
2015-07-03 08:23:03 WARNING OGG-01154 SQL error 1 mapping NEWHC.ON_BUSIN_CHANCE to RADAR.ON_BUSIN_CHANCE OCI Error ORA-00001: unique constraint (RADA
R.PK_ON_BUSIN_CHANCE) violated (status = 1), SQL <INSERT INTO "RADAR"."ON_BUSIN_CHANCE" ("BC_ID","INDIVIDFLAG","SORTTAG","TITLE","TYPE","BRANDID","KEYW
ORD","KEYWORD2","KEYWORD3","LPACKAGE","DELIVER","TRANSPORT","UNIT","VALIDDATE","PUBDATE","PROVIDERI>.
2015-07-03 08:23:03 WARNING OGG-01003 Repositioning to rba 115192468 in seqno 4002.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Reading ./dirdat/td004002, current RBA 115192468, 0 records
Report at 2015-07-03 08:23:03 (activity since 2015-07-03 08:22:58)
From Table NEWHC.ON_BUSIN_CHANCE to RADAR.ON_BUSIN_CHANCE:
# inserts: 0
# updates: 0
# deletes: 0
# discards: 1
BATCHSQL statistics:
Batch operations: 115
Batches: 0
Batches executed: 0
Queues: 0
Batches in error: 1
Normal mode operations: 2
Immediate flush operations: 1
PK collisions: 4
UK collisions: 1
FK collisions: 0
Thread batch groups: 0
Commits: 0
Rollbacks: 1
Queue flush calls: 0
Ops per batch: N/A
Ops per batch executed: N/A
Ops per queue: N/A
Parallel batch rate: N/A
DDL replication statistics:
Operations: 0
Mapped operations: 0
Unmapped operations: 0
Other operations: 0
Excluded operations: 0
Errors: 0
Retried errors: 0
Discarded errors: 0
Ignored errors: 0
Last log location read:
FILE: ./dirdat/td004002
SEQNO: 4002
RBA: 115192468
TIMESTAMP: 2015-07-02 21:20:10.518672
EOF: NO
READERR: 0
2015-07-03 08:23:03 ERROR OGG-01668 PROCESS ABENDING.
查看
[oracle@192.168.100.160 gg]$ view ./dirrpt/rep160.dsc
OCI Error ORA-00001: unique constraint (RADAR.PK_ON_BUSIN_CHANCE) violated (status = 1), SQL <INSERT INTO "RADAR"."ON_BUSIN_CHANCE" ("BC_ID","INDIVIDFLAG","SORTTAG","TITLE","TYPE","BRANDID","KEYWORD","KEYWORD2","KEYWORD3","LPACKAGE","DELIVER","TRANSPORT","UNIT","VALIDDATE","PUBDATE","PROVIDERI>
Aborting transaction on ./dirdat/td beginning at seqno 4002 rba 115192468
error at seqno 4002 rba 115192468
Problem replicating NEWHC.ON_BUSIN_CHANCE to RADAR.ON_BUSIN_CHANCE
Mapping problem with insert record (target format)...
*
BC_ID = 441933626
INDIVIDFLAG = NULL
SORTTAG = 0
TITLE = <Raw Data>
可以知道目标端RADAR.PK_ON_BUSIN_CHANCE表上的BC_ID = 441933626字段重复
处理办法
1.跳过当前事务
2.尝试加入 handlecollisions 参数解决该问题
3.尝试手动修复 target 上的数据
4.尝试加入REPERROR 参数 ignore这个错误
1.跳过当前事务
由以上信息查找下一个事务
[oracle@192.168.100.160 gg]$ ./logdump
Oracle GoldenGate Log File Dump Utility
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Logdump 1 >open ./dirdat/td004002
Current LogTrail is /oracle/oradat1/gg/dirdat/td004002
Logdump 2 >pos 115192468
Reading forward from RBA 115192468
Logdump 3 >n
2015/07/02 21:20:10.518.672 Insert Len 744 RBA 115192468
Name: NEWHC.ON_BUSIN_CHANCE
After Image: Partition 4 G b
0000 000d 0000 0009 3434 3139 3333 3632 3600 0100 | ........441933626...
03ff ff00 0002 0003 0000 3000 0300 3c00 0000 38cc | ..........0...<...8.
d8bc db20 bae3 d4b4 cfe9 20b0 dfec b5ca c0bd e720 | ... ...... ........
c8ab c3de b9b1 b6d0 beab c6b7 b4b2 c9cf d3c3 c6b7 | ....................
cbc4 bcfe ccd7 20bd adcb d5d7 dcb4 fa00 0400 04ff | ...... .............
ff00 0000 0500 0500 0000 0130 0006 0022 0000 001e | ...........0..."....
bae3 d4b4 cfe9 b4b2 c9cf d3c3 c6b7 2cb4 b2c9 cfd3 | ..............,.....
Logdump 4 >n
2015/07/02 21:20:10.518.672 Insert Len 732 RBA 115193344
Name: NEWHC.ON_BUSIN_CHANCE
After Image: Partition 4 G m
0000 000d 0000 0009 3434 3139 3333 3633 3700 0100 | ........441933637...
03ff ff00 0002 0003 0000 3000 0300 3000 0000 2cd2 | ..........0...0...,.
bbbc b6b4 fac0 edbd f0cb bfc0 f220 bac0 bbaa ccec | ............. ......
cbbf d0e5 bba8 ccd7 bcfe 20bd adcb d5ca a1d7 dcb4 | .......... .........
fac0 ed00 0400 04ff ff00 0000 0500 0500 0000 0130 | ...................0
0006 0022 0000 001e bdf0 cbbf c0f2 d0e5 bba8 ccd7 | ..."................
bcfe 2cd0 e5bb a8cc d7bc fe2c ccec cbbf d0e5 0007 | ..,........,........
GGSCI (host_100.160) 9> alter replicat rep160, extrba 115193344
REPLICAT altered.
GGSCI (host_100.160) 12> start *
当然如果还有失败的事务还可以继续next用上面的方法，不过如果有几个连续的事务需要skip，那就可以用另外一个方法
start XXX skiptransaction
2.尝试加入 handlecollisions 参数解决该问题
1）源端删除了目标端没有的记录可以使用
start XXX skiptransaction
2）target丢失update记录(missing update)，更新的键值是非主键=》 忽略该问题并不记录到discardfile
target丢失update记录(missing update)，更新的键值是主键
加入HANDLECOLLISIONS后，rep可以继续工作且不生成discard记录
丢失update的更新操作是针对主键的更新，此时replicat会尝试插入一条记录而非忽略该update。注意插入的记录可能不是完整的行，若要求完整的行记录则要求EXTRACT使用PKUPDATE选项。需要加入的选项是FETCHOPTIONS FETCHPKUPDATECOLS，将以上选项加入到EXTRACT参数文件中，并重启EXTRACT。 这将引起extract捕获完整的主键更新镜像。
3）重复插入已存在的主键值到target表中，这将被replicat转换为UPDATE现有主键值的行的其他非主键列：
处理完之后一定要取消HANDLECOLLISIONS参数
可以通过send 命令动态取消HANDLECOLLISIONS
send XXX, NOHANDLECOLLISIONS
4.尝试加入REPERROR 参数 ignore这个错误
可以忽略掉重复数据的插入而其他类型的报错则abend
REPERROR (DEFAULT, ABEND)
REPERROR (-1, IGNORE)
如当DDL复制报ORA-1430错误，传递了重复的alter语句导致，则可以用ddlerror （1430, discard）将错误信息扔到discard文件里。
0 0
- OGG-ORA0001unique constraint
- constraint
- CONSTRAINT
- 【】CONSTRAINT
- ogg
- OGG
- ogg
- OGG
- ogg
- ogg
- ogg
- OGG
- OGG
- OGG
- OGG-01496 OGG-01031
- MySQL constraint
- Oracle Constraint
- Oracle Constraint
- Sprite (2D and UI)
- Strategy Pattern 策略模式
- Android源码编译－－整理
- linux中chkconfig命令的基本使用
- 中介者模式
- OGG-ORA0001unique constraint
- linux下开启FTP服务
- Linux环境进程间通信 信号量
- mysql常用查询语句
- 粒子群算法解决函数优化问题
- Yii CModel中rules验证规则[转]
- cocos2dx 3.5 资源文件加密
- 10个大坑，当你产品上架AppStore会遇到
- 浏览图片
