OGG-ORA0001unique constraint

以你之姓@ 2022-08-02 00:20 243阅读 0赞

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文件里。