ggsci: handle update collision on target database 19c
source database : sourcegg1
Target database : targetgg1
Source GG Hostname : srv6.example.com
Target GG Hostname : srv7.example.com
Scenario
- We disable update collision from replication process and delete object id from target database table
- updating the same object id on source database table and we will find error on target replicat as unable to map object id with target table.
- Adding updating collision on target database and now it will insert and update object data on target table.
Target Database
SQL> alter session set container=TGG1;
Session altered.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TGG1 READ WRITE NO
- Delete object values on target database for example so while updating values of same id we will get error.
SQL> delete from hr.supplier where SUPPLIER_ID=1;
1 row deleted.
SQL> delete from hr.supplier2 where SUPPLIER_ID=1;
1 row deleted.
SQL> commit;
Commit complete.
- Stop Replication process from target GG and disable to handle collisions
GGSCI (srv7.example.com) 28> stop repint
Sending STOP request to REPLICAT REPINT ...
Request processed.
GGSCI (srv7.example.com) 29> 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;
GGSCI (srv7.example.com) 30> start repint
Sending START request to MANAGER ...
REPLICAT REPINT starting
GGSCI (srv7.example.com) 32> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPINT 00:00:00 00:00:06
GGSCI (srv7.example.com) 33>
- Update the value on source database which was deleted by target database above steps
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SGG1 READ WRITE NO
SQL>
SQL> update hr.supplier set SUPPLIER_NAME='ABCD1111' where SUPPLIER_ID=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from hr.supplier order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
1 ABCD1111 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>
- View Target GG replication process, repint is replication process name.
GGSCI (srv7.example.com) 33> view report repint
2021-12-02 19:56:43 INFO OGG-06604 Database TGG1 CPU info: CPU Count 1, CPU Core Count 1, CPU Socket Count 1.
2021-12-02 19:56:43 WARNING OGG-05673 CSN-based duplicate suppression is disabled because there is no checkpoint table for this Replicat.
2021-12-02 19:56:44 INFO OGG-02545 Parameter GROUPTRANSOPS is ignored by Integrated Replicat when parallelism is greater than 1.
2021-12-02 19:56:44 INFO OGG-02527 Integrated Replicat does not populate a trace table.
2021-12-02 19:56:44 INFO OGG-06627 Replicat sets the key columns for each table replicated by inbound server OGG$REPINT.
2021-12-02 19:56:45 INFO OGG-02530 Integrated replicat successfully attached to inbound server OGG$REPINT.
2021-12-02 19:58:48 WARNING OGG-02544 Unhandled error (ORA-26787: The row with key ("SUPPLIER_ID") = (1) does not exist in table HR.SUPPLIER
ORA-01403: no data found) while processing the record at SEQNO 4, RBA 2835 in Integrated mode. REPLICAT will retry in Direct mode.
2021-12-02 19:58:48 WARNING OGG-01004 Aborted grouped transaction on TGG1.HR.SUPPLIER, Database error 1403 (No data found).
2021-12-02 19:58:48 WARNING OGG-01003 Repositioning to rba 2835 in seqno 4.
2021-12-02 19:58:48 WARNING OGG-01154 SQL error 1403 mapping SGG1.HR.SUPPLIER to TGG1.HR.SUPPLIER No data found.
- We can view there is No Change in target database object as it will throuh error on taget GG log above as well.
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> select * from hr.supplier2 order by SUPPLIER_ID;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -------------------------------------------------- --------------------------------------------------
7 ABCD7 ABCD7
8 ABCD8 ABCD8
SQL>
GGSCI (srv7.example.com) 35> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REPINT 00:00:00 00:07:32
- Add HANDLECOLLISIONS process on repint process of target GG
GGSCI (srv7.example.com) 36> edit param repint
GGSCI (srv7.example.com) 37> view 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;
GGSCI (srv7.example.com) 38>
GGSCI (srv7.example.com) 38> start repint
Sending START request to MANAGER ...
REPLICAT REPINT starting
GGSCI (srv7.example.com) 39> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPINT 07:26:04 00:00:02
GGSCI (srv7.example.com) 40> stats repint
Sending STATS request to REPLICAT REPINT ...
Start of Statistics at 2021-12-02 20:08:13.
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 20:07:26 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total update collisions 1.00
*** Daily statistics since 2021-12-02 20:07:26 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total update collisions 1.00
*** Hourly statistics since 2021-12-02 20:07:26 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total update collisions 1.00
*** Latest statistics since 2021-12-02 20:07:26 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total update collisions 1.00
Replicating from SGG1.HR.SUPPLIER to TGG1.HR.SUPPLIER2:
*** Total statistics since 2021-12-02 20:07:26 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total update collisions 1.00
*** Daily statistics since 2021-12-02 20:07:26 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total update collisions 1.00
*** Hourly statistics since 2021-12-02 20:07:26 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total update collisions 1.00
*** Latest statistics since 2021-12-02 20:07:26 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Total update collisions 1.00
End of Statistics.
GGSCI (srv7.example.com) 41>
No comments:
Post a Comment