Sunday, May 6, 2012

Manual fix OGG Replicat Broken Example


-- insert into target first
SQL> insert into bbadmin.tbl_p values (1,'p');
SQL> commit;

-- insert into source later
SQL> insert into bbadmin.tbl_p values (1,'p');
SQL> commit;
SQL> insert into bbadmin.tbl_p values (2,'p');
SQL> commit;


File: $GGS_HOME/ggserr.log

2012-05-06 22:56:13  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  REPLICAT RBBP01 started.
2012-05-06 22:56:18  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT RBBP01 starting.
2012-05-06 23:14:44  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL .
2012-05-06 23:14:44  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Aborted grouped transaction on 'BBADMIN.TBL_P', Database error 1 (OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL ).
2012-05-06 23:14:44  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Repositioning to rba 11860 in seqno 13.
2012-05-06 23:14:44  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  SQL error 1 mapping BBADMIN.TBL_P to BBADMIN.TBL_P OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL .
2012-05-06 23:14:44  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Repositioning to rba 11860 in seqno 13.
2012-05-06 23:14:44  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Error mapping from BBADMIN.TBL_P to BBADMIN.TBL_P.
2012-05-06 23:14:44  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  PROCESS ABENDING.
2012-05-06 23:14:44  INFO    OGG-01237  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Trace file /u01/ggs/dirrpt/trace_RBBP01.trc closed.
2012-05-06 23:18:57  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  REPLICAT RBBP01 starting.
2012-05-06 23:18:57  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Operating system character set identified as UTF-8. Locale: en_SG, LC_ALL:.
2012-05-06 23:18:57  INFO    OGG-01236  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Trace file /u01/ggs/dirrpt/trace_RBBP01.trc opened.
2012-05-06 23:18:57  INFO    OGG-01815  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Virtual Memory Facilities for: COM


File: $GGS_HOME/dirrpt/RBBP01.dsc

Current time: 2012-05-06 23:14:44
Discarded record from action ABEND on error 1

OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11860
                         error at seqno 13 rba 11860
Problem replicating BBADMIN.TBL_P to BBADMIN.TBL_P
Mapping problem with insert record (target format)...
*
PID = 1
NAME = p
*

Process Abending : 2012-05-06 23:14:44

Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 23:18:57

[oracle@ncsdb02 ggs]$ logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



Logdump 13 >open /u01/ggs/dirdat/ta000013
Current LogTrail is /u01/ggs/dirdat/ta000013
Logdump 14 >pos 11860
Reading forward from RBA 11860
Logdump 15 >n

2012/05/06 23:14:43.994.911 Insert               Len    18 RBA 11860
Name: BBADMIN.TBL_P
After  Image:                                             Partition 4   G  s  
 0000 0005 0000 0001 3100 0100 0500 0000 0170      | ........1........p 
  
Logdump 16 >n

2012/05/06 23:23:50.917.770 Insert               Len    18 RBA 11995
Name: BBADMIN.TBL_P
After  Image:                                             Partition 4   G  s  
 0000 0005 0000 0001 3200 0100 0500 0000 0170      | ........2........p 



GGSCI (ncsdb02.ncs.edu.sg) 1> info replicat r*

REPLICAT   RBBP01    Last Started 2012-05-06 23:23   Status ABENDED
Checkpoint Lag       00:09:14 (updated 00:03:34 ago)
Log Read Checkpoint  File /u01/ggs/dirdat/ta000013
                     2012-05-06 23:14:43.994911  RBA 11860


GGSCI (ncsdb02.ncs.edu.sg) 6> alter replicat RBBP01 extseqno 13, extrba 11995
REPLICAT altered.


GGSCI (ncsdb02.ncs.edu.sg) 8> start replicat RBBP01

Sending START request to MANAGER ...
REPLICAT RBBP01 starting

1 comment:

  1. Thanks for great information , my question is how can we control this as GG admin , lets say some adhoc developer push bad record like this UQ error , so the process will every time abends which is not good for production , please suggest how can we handle such situation where instead of abending , it gives warning for failed transaction SQL and continue , Thanks in advance Mayank

    ReplyDelete