Manual fix OGG Replicat Broken Demo
Here in our demo , I have stimulated the issue as below.
-- insert into target first
-- insert into source later
SQL> insert into tb1 values (1, 'DEEKSHI');
1 row created.
SQL> commit;
Commit complete.
SQL> set time on
22:52:05 SQL> select * from tb1;
ID NAME
---------- --------------------
1 DEEKSHI
22:50:10 SQL> insert into tb1 values (1, 'DEEKSHI');
1 row created.
22:52:18 SQL> insert into tb1 values (2, 'PRITHVI');
1 row created.
22:53:27 SQL> commit;
Commit complete.
22:54:01 SQL> select * from tb1;
ID NAME
---------- --------------------
1 DEEKSHI
2 PRITHVI
Replicat rep2 abended with below error message in ggserr.log file.
File: $GGS_HOME/ggserr.log
2018-12-18T22:54:03.033+0530 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep2.prm: Aborted grouped transaction on DEMO.TB1, Database error 1 (OCI Error ORA-00001: unique constraint (DEMO.SYS_C0010157) violated (status = 1), SQL ) .
2018-12-18T22:54:03.033+0530 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep2.prm: Repositioning to rba 2146 in seqno 8.
2018-12-18T22:54:03.033+0530 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep2.prm: SQL error 1 mapping US01.TB1 to DEMO.TB1 OCI Error ORA-00001: unique constraint (DEMO.SYS_C0010157) violated (status = 1), SQL.
2018-12-18T22:54:03.033+0530 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep2.prm: Error mapping from US01.TB1 to DEMO.TB1.
2018-12-18T22:54:03.033+0530 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep2.prm: PROCESS ABENDING.
Solution:-
GGSCI (rac1.localdomain) 21> info rep2
REPLICAT REP2 Last Started 2018-12-18 22:48 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:02:47 ago)
Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008
2018-12-18 22:50:10.000000 RBA 2146
2018-12-18T22:54:03.033+0530 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep2.prm: Repositioning to rba 2146 in seqno 8.
2018-12-18T22:54:03.033+0530 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep2.prm: SQL error 1 mapping US01.TB1 to DEMO.TB1 OCI Error ORA-00001: unique constraint (DEMO.SYS_C0010157) violated (status = 1), SQL
2018-12-18T22:54:03.033+0530 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep2.prm: Error mapping from US01.TB1 to DEMO.TB1.
2018-12-18T22:54:03.033+0530 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep2.prm: PROCESS ABENDING.
Solution:-
GGSCI (rac1.localdomain) 21> info rep2
REPLICAT REP2 Last Started 2018-12-18 22:48 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:02:47 ago)
Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008
2018-12-18 22:50:10.000000 RBA 2146
GGSCI (rac1.localdomain) 22> view params rep2
REPLICAT rep2
USERID gguser@ggtest1, PASSWORD gguser
DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90
---HANDLECOLLISIONS
---ASSUMETARGETDEFS
---DDL INCLUDE ALL
---DDLERROR DEFAULT IGNORE
---MAPEXCLUDE us01.test1
MAP us01.*, TARGET demo.*;
GGSCI (rac1.localdomain) 23> info rep2
REPLICAT REP2 Last Started 2018-12-18 22:48 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:05:38 ago)
Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008
2018-12-18 22:50:10.000000 RBA 2146
Open discardfile to see the failed transaction data seq no and RBA
File: $GGS_HOME/dirrpt/rep2.dsc
Process Abending : 2018-12-18 21:24:06
Oracle GoldenGate Delivery for Oracle process started, group REP2 discard file opened: 2018-12-18 21:29:19.471291
Oracle GoldenGate Delivery for Oracle process stopped, group REP2 : 2018-12-18 21:30:13.275460
Oracle GoldenGate Delivery for Oracle process started, group REP2 discard file opened: 2018-12-18 22:47:51.097218
Current time: 2018-12-18 22:54:03
Discarded record from action ABEND on error 1
OCI Error ORA-00001: unique constraint (DEMO.SYS_C0010157) violated (status = 1), SQL
Aborting transaction on /home/oracle/gghome/dirdat/ex beginning at seqno 8 rba 2146
error at seqno 8 rba 2146
Problem replicating US01.TB1 to DEMO.TB1
Mapping problem with insert record (target format)...
*
ID = 1
000000: 31 |1 |
NAME = DEEKSHI
000000: 44 45 45 4b 53 48 49 |DEEKSHI |
*
Process Abending : 2018-12-18 22:54:03
/home/oracle/gghome
[oracle@rac1 gghome]$ ./logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
Logdump 15 >open /home/oracle/gghome/dirdat/ex000000008
Current LogTrail is /home/oracle/gghome/dirdat/ex000000008
Logdump 16 >pos 2146
Reading forward from RBA 2146
Logdump 17 >n
2018/12/18 22:54:01.000.000 Insert Len 24 RBA 2146
Name: US01.TB1 (TDR Index: 1)
After Image: Partition 12 G b
0000 0500 0000 0100 3101 000b 0000 0007 0044 4545 | ........1........DEE
4b53 4849 | KSHI
Logdump 18 >n
2018/12/18 22:54:01.000.000 Insert Len 24 RBA 2277
Name: US01.TB1 (TDR Index: 1)
After Image: Partition 12 G e
0000 0500 0000 0100 3201 000b 0000 0007 0050 5249 | ........2........PRI
5448 5649 | THVI
GGSCI (rac1.localdomain) 27> info rep2
REPLICAT REP2 Last Started 2018-12-18 23:01 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:01:43 ago)
Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008
First Record RBA 2146
GGSCI (rac1.localdomain) 28> alter replicat rep2 extseqno 8, extrba 2277
2018-12-18 23:02:50 INFO OGG-06594 Replicat REP2 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP2 with NOFILTERDUPTRANSACTIONS option.
REPLICAT altered.
GGSCI (rac1.localdomain) 29> start replicat rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (rac1.localdomain) 30> info rep2
REPLICAT REP2 Last Started 2018-12-18 23:02 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Process ID 19591
Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008
2018-12-18 22:54:01.000000 RBA 2381
now the issue is resolved. verify the data in demo schema tb1 table.
22:55:48 SQL> show user
USER is "DEMO"
before fixing
22:56:04 SQL> select * from demo.tb1;
ID NAME
---------- --------------------
1 DEEKSHI
after fixing
23:01:45 SQL> /
ID NAME
---------- --------------------
1 DEEKSHI
2 PRITHVI
Hope, this helps
Source and Reference:- http://www.dbaglobe.com/2012/05/manual-fix-ogg-replicat-broken-example.html
No comments:
Post a Comment