ggsci : Collision delete handling example
source database : sourcegg1
Target database : targetgg1
Source GG Hostname : srv6.example.com
Target GG Hostname : srv7.example.com
Scenario :
- In this scenario we will delete value from object level and stop the target replication . Again insert the data on same object id on same table.
- Starting the replication process at target database and we face erroras unable to map table .
- Adding HANDLECOLLISIONS parameter on target replication process and start again to sort out error and sync again.
Error:
ORA-00001: unique constraint (HR.SUPPLIER_PK) violated
ORA-00001: unique constraint (HR.SUPPLIER_PK) violated) while processing the record at SEQNO 4, RBA 2673 in Integrated mode. REPLICAT will retry in Direct mode.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SGG1 READ WRITE NO
SQL> select * from hr.supplier order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
1 ABCD1 ABCD1
2 ABCD2 ABCD2
3 ABCD3 ABCD3
4 ABCD4 ABCD4
5 ABCD5 ABCD5
6 ABCD6 ABCD6
7 ABCD7 ABCD7
8 ABCD8 ABCD8
8 rows selected.
SQL>
GGSCI (srv6.example.com) 42> view param extint
EXTRACT extint
USERID c##ggsource, PASSWORD ggsource --connection to CDB$ROOT database
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
EXTTRAIL /u01/app/oracle/product/sourcegg1/dirdat/lt
--GETINSERTS
--GETUPDATES
--GETDELETES
--GETTRUNCATES
IGNOREDELETES
TABLE sgg1.hr.supplier;
GGSCI (srv6.example.com) 43>
GGSCI (srv6.example.com) 43> stop extint
Sending STOP request to EXTRACT EXTINT ...
Request processed.
GGSCI (srv6.example.com) 44> start extint
EXTRACT EXTINT is already running.
GGSCI (srv6.example.com) 45>
GGSCI (srv6.example.com) 45> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPINT 00:00:00 00:00:07
EXTRACT STOPPED EXTINT 00:00:00 00:00:26
GGSCI (srv6.example.com) 46> start extint
Sending START request to MANAGER ...
EXTRACT EXTINT starting
GGSCI (srv6.example.com) 47> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPINT 00:00:00 00:00:05
EXTRACT RUNNING EXTINT 00:00:00 00:00:07
GGSCI (srv6.example.com) 48>
SQL> select * from hr.supplier order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
1 ABCD1 ABCD1
2 ABCD2 ABCD2
3 ABCD3 ABCD3
4 ABCD4 ABCD4
5 ABCD5 ABCD5
6 ABCD6 ABCD6
7 ABCD7 ABCD7
8 ABCD8 ABCD8
8 rows selected.
SQL> delete from hr.supplier where SUPPLIER_ID=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hr.supplier order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
2 ABCD2 ABCD2
3 ABCD3 ABCD3
4 ABCD4 ABCD4
5 ABCD5 ABCD5
6 ABCD6 ABCD6
7 ABCD7 ABCD7
8 ABCD8 ABCD8
7 rows selected.
SQL>
GGSCI (srv6.example.com) 49> stats extint
Sending STATS request to EXTRACT EXTINT ...
Start of Statistics at 2021-12-02 19:24:37.
Output to /u01/app/oracle/product/sourcegg1/dirdat/lt:
Extracting from SGG1.HR.SUPPLIER to SGG1.HR.SUPPLIER:
*** Total statistics since 2021-12-02 19:24:27 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 1.00
Total operations 0.00
*** Daily statistics since 2021-12-02 19:24:27 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 1.00
Total operations 0.00
*** Hourly statistics since 2021-12-02 19:24:27 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 1.00
Total operations 0.00
*** Latest statistics since 2021-12-02 19:24:27 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 1.00
Total operations 0.00
End of Statistics.
GGSCI (srv6.example.com) 50>
TARGET database
SQL> select * from hr.supplier order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
1 ABCD1 ABCD1
2 ABCD2 ABCD2
3 ABCD3 ABCD3
4 ABCD4 ABCD4
5 ABCD5 ABCD5
6 ABCD6 ABCD6
7 ABCD7 ABCD7
8 ABCD8 ABCD8
8 rows selected.
SQL> select * from hr.supplier2 order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
7 ABCD7 ABCD7
8 ABCD8 ABCD8
SQL>
source database
SQL> insert into hr.supplier values (1,'ABCD11','ABCD11');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.supplier order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
1 ABCD11 ABCD11
2 ABCD2 ABCD2
3 ABCD3 ABCD3
4 ABCD4 ABCD4
5 ABCD5 ABCD5
6 ABCD6 ABCD6
7 ABCD7 ABCD7
8 ABCD8 ABCD8
8 rows selected.
SQL>
GGSCI (srv6.example.com) 51> stats extint
Sending STATS request to EXTRACT EXTINT ...
Start of Statistics at 2021-12-02 19:32:05.
Output to /u01/app/oracle/product/sourcegg1/dirdat/lt:
Extracting from SGG1.HR.SUPPLIER to SGG1.HR.SUPPLIER:
*** Total statistics since 2021-12-02 19:24:27 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 1.00
Total operations 1.00
*** Daily statistics since 2021-12-02 19:24:27 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 1.00
Total operations 1.00
*** Hourly statistics since 2021-12-02 19:24:27 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 1.00
Total operations 1.00
*** Latest statistics since 2021-12-02 19:24:27 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 1.00
Total operations 1.00
End of Statistics.
GGSCI (srv6.example.com) 52>
target database
SQL> select * from hr.supplier order by SUPPLIER_ID
2 ;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
1 ABCD1 ABCD1
2 ABCD2 ABCD2
3 ABCD3 ABCD3
4 ABCD4 ABCD4
5 ABCD5 ABCD5
6 ABCD6 ABCD6
7 ABCD7 ABCD7
8 ABCD8 ABCD8
8 rows selected.
SQL> select * from hr.supplier2 order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
7 ABCD7 ABCD7
8 ABCD8 ABCD8
SQL>
target ggsci
GGSCI (srv7.example.com) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REPINT 00:00:00 00:04:09
GGSCI (srv7.example.com) 21>
Target ggserr.log
2021-12-02T19:17:27.835+0530 INFO OGG-04048 Oracle GoldenGate Delivery for Oracle, repint.prm: Processed graceful restart record at seq 4 rba 2,415.
2021-12-02T19:28:54.740+0530 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle, repint.prm: MAP resolved (entry sgg1.hr.supplier): MAP "SGG1"."HR"."SUPPLIER", TARGET tgg1.hr.supplier.
2021-12-02T19:28:57.383+0530 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle, repint.prm: The definition for table SGG1.HR.SUPPLIER is obtained from the trail file.
2021-12-02T19:28:57.383+0530 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, repint.prm: Using following columns in default map by name: SUPPLIER_ID, SUPPLIER_NAME, CONTACT_NAME.
2021-12-02T19:28:57.383+0530 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, repint.prm: Using the following key columns for target table TGG1.HR.SUPPLIER: SUPPLIER_ID.
2021-12-02T19:28:57.533+0530 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle, repint.prm: MAP resolved (entry sgg1.hr.supplier): MAP "SGG1"."HR"."SUPPLIER", TARGET tgg1.hr.supplier2.
2021-12-02T19:28:58.833+0530 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, repint.prm: Using following columns in default map by name: SUPPLIER_ID, SUPPLIER_NAME, CONTACT_NAME.
2021-12-02T19:28:58.833+0530 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, repint.prm: Using the following key columns for target table TGG1.HR.SUPPLIER2: SUPPLIER_ID.
2021-12-02T19:29:01.261+0530 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repint.prm: Unhandled error (ORA-26799: unique constraint HR.SUPPLIER_PK violated for table HR.SUPPLIER with column values ("SUPPLIER_ID") = (1)
ORA-00001: unique constraint (HR.SUPPLIER_PK) violated
ORA-00001: unique constraint (HR.SUPPLIER_PK) violated) while processing the record at SEQNO 4, RBA 2673 in Integrated mode. REPLICAT will retry in Direct mode.
2021-12-02T19:29:01.335+0530 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, repint.prm: Aborted grouped transaction on TGG1.HR.SUPPLIER, Database error 1 (OCI Error ORA-00001: unique constraint (HR.SUPPLIER_PK) violated (status = 1), SQL <INSERT INTO "HR"."SUPPLIER" ("SUPPLIER_ID","SUPPLIER_NAME","CONTACT_NAME") VALUES (:a0,:a1,:a2)>).
2021-12-02T19:29:01.335+0530 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repint.prm: Repositioning to rba 2673 in seqno 4.
2021-12-02T19:29:01.335+0530 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repint.prm: SQL error 1 mapping SGG1.HR.SUPPLIER to TGG1.HR.SUPPLIER OCI Error ORA-00001: unique constraint (HR.SUPPLIER_PK) violated (status = 1), SQL <INSERT INTO "HR"."SUPPLIER" ("SUPPLIER_ID","SUPPLIER_NAME","CONTACT_NAME") VALUES (:a0,:a1,:a2)>.
2021-12-02T19:29:01.335+0530 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repint.prm: Error mapping from SGG1.HR.SUPPLIER to TGG1.HR.SUPPLIER.
2021-12-02T19:29:06.597+0530 INFO OGG-02333 Oracle GoldenGate Delivery for Oracle, repint.prm: Reading /u01/app/oracle/product/targetgg1/dirdat/rt000000004, current RBA 2,673, 6 records, m_file_seqno = 4, m_file_rba = 2,835.
2021-12-02T19:29:06.649+0530 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repint.prm: PROCESS ABENDING.
2021-12-02T19:32:55.012+0530 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
GGSCI (srv7.example.com) 22> view report REPINT
2021-12-02 19:28:58 INFO OGG-06510 Using the following key columns for target table TGG1.HR.SUPPLIER2: SUPPLIER_ID.
2021-12-02 19:29:01 WARNING OGG-02544 Unhandled error (ORA-26799: unique constraint HR.SUPPLIER_PK violated for table HR.SUPPLIER with column values ("SUPPLIER_ID") = (1)
ORA-00001: unique constraint (HR.SUPPLIER_PK) violated
ORA-00001: unique constraint (HR.SUPPLIER_PK) violated) while processing the record at SEQNO 4, RBA 2673 in Integrated mode. REPLICAT will retry in Direct mode.
2021-12-02 19:29:01 WARNING OGG-01004 Aborted grouped transaction on TGG1.HR.SUPPLIER, Database error 1 (OCI Error ORA-00001: unique constraint (HR.SUPPLIER_PK) violated (status = 1), SQL <INSERT INTO "HR"."SUPPLIER" ("SUPPLIER_ID","SU
PPLIER_NAME","CONTACT_NAME") VALUES (:a0,:a1,:a2)>).
GGSCI (srv7.example.com) 23> edit param REPINT
REPLICAT repint
USERID c##ggtarget@TGG1,PASSWORD ggtarget
DBOPTIONS INTEGRATEDPARAMS(parallelism 4)
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP sgg1.hr.supplier, TARGET tgg1.hr.supplier;
MAP sgg1.hr.supplier, TARGET tgg1.hr.supplier2;
:wq!
GGSCI (srv7.example.com) 25> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPINT 00:00:00 00:00:08
GGSCI (srv7.example.com) 26>
GGSCI (srv7.example.com) 26> view report repint
2021-12-02 19:39:20 INFO OGG-06604 Database TGG1 CPU info: CPU Count 1, CPU Core Count 1, CPU Socket Count 1.
2021-12-02 19:39:20 WARNING OGG-05673 CSN-based duplicate suppression is disabled because there is no checkpoint table for this Replicat.
2021-12-02 19:39:21 INFO OGG-02545 Parameter GROUPTRANSOPS is ignored by Integrated Replicat when parallelism is greater than 1.
2021-12-02 19:39:21 INFO OGG-02527 Integrated Replicat does not populate a trace table.
2021-12-02 19:39:21 INFO OGG-06627 Replicat sets the key columns for each table replicated by inbound server OGG$REPINT.
2021-12-02 19:39:22 INFO OGG-02530 Integrated replicat successfully attached to inbound server OGG$REPINT.
Target database table after update collisions parameter
SQL> select * from hr.supplier order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
1 ABCD11 ABCD11
2 ABCD2 ABCD2
3 ABCD3 ABCD3
4 ABCD4 ABCD4
5 ABCD5 ABCD5
6 ABCD6 ABCD6
7 ABCD7 ABCD7
8 ABCD8 ABCD8
8 rows selected.
SQL> select * from hr.supplier2 order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
1 ABCD11 ABCD11
7 ABCD7 ABCD7
8 ABCD8 ABCD8
SQL>
GGSCI (srv7.example.com) 27> stats repint
Sending STATS request to REPLICAT REPINT ...
Start of Statistics at 2021-12-02 19:43:14.
Integrated Replicat Statistics:
Total transactions 0.00
Redirected 1.00
Replicated procedures 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Operation type functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from SGG1.HR.SUPPLIER to TGG1.HR.SUPPLIER:
*** Total statistics since 2021-12-02 19:39:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total insert collisions 1.00
*** Daily statistics since 2021-12-02 19:39:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total insert collisions 1.00
*** Hourly statistics since 2021-12-02 19:39:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total insert collisions 1.00
*** Latest statistics since 2021-12-02 19:39:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total insert collisions 1.00
Replicating from SGG1.HR.SUPPLIER to TGG1.HR.SUPPLIER2:
*** Total statistics since 2021-12-02 19:39:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2021-12-02 19:39:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2021-12-02 19:39:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2021-12-02 19:39:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (srv7.example.com) 28>