===========================
Resolution
===========================
Remove NOHANDLECOLLISIONS from Replicat configuration on both sites.
Checking output of STATS command, the replicat is 90% of the time, is busy parsing collisions.
Before NOHANDLECOLLISIONS:
GGSCI (ITMIL7DB00001) 30> SEND REPLICAT REP_I_01 STATS
Sending STATS request to REPLICAT REP_I_01 ...
Start of Statistics at 2020-09-11 20:49:03.
Replicating from OWNER.SGA_SUBSCRIBER_SFI to OWNER.SGA_SUBSCRIBER_SFI:
*** Total statistics since 2020-09-09 07:57:00 ***
Total inserts 7047.00
Total updates 159072656.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 9701.00
Total operations 159079703.00
Total insert collisions 1370.00
Total update collisions 159071473.00
*** Daily statistics since 2020-09-11 00:00:00 ***
Total inserts 2657.00
Total updates 54334620.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 9701.00
Total operations 54337277.00
Total insert collisions 365.00
Total update collisions 54333908.00
*** Hourly statistics since 2020-09-11 20:00:00 ***
Total inserts 121.00
Total updates 2078586.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 9701.00
Total operations 2078707.00
Total insert collisions 21.00
Total update collisions 2078554.00
*** Latest statistics since 2020-09-09 07:57:00 ***
Total inserts 7047.00
Total updates 159072656.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 9701.00
Total operations 159079703.00
Total insert collisions 1370.00
Total update collisions 159071473.00
Stop Collision Handling for table SGA_SUBSCRIBER_SFI on both servers, A and B.
SEND REPLICAT REP_I_01 NOHANDLECOLLISIONS OWNER.SGA_SUBSCRIBER_SFI
After NOHANDLECOLLISIONS
Replicating from OWNER.SGA_SUBSCRIBER_SFI to OWNER.SGA_SUBSCRIBER_SFI:
*** Total statistics since 2020-09-09 07:57:00 ***
Total inserts 8091.00
Total updates 159072831.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 46304442.00
Total operations 159080922.00
Total insert collisions 1370.00
Total update collisions 159071473.00
*** Daily statistics since 2020-09-12 00:00:00 ***
Total inserts 384.00
Total updates 37.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 20684763.00
Total operations 421.00
*** Hourly statistics since 2020-09-12 02:00:00 ***
Total inserts 48.00
Total updates 2.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 5417534.00
Total operations 50.00
After some time the lag is resolved:
GGSCI (ITMIL7DB00001) 67> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPM_I_01 00:00:00 00:00:06
EXTRACT RUNNING DPM_P_01 00:00:00 00:00:03
EXTRACT RUNNING DPM_S_01 00:00:00 00:00:02
EXTRACT RUNNING EXT_I_01 00:00:03 00:00:08
EXTRACT RUNNING EXT_P_01 00:00:02 00:00:01
EXTRACT RUNNING EXT_S_01 00:00:01 00:00:04
REPLICAT RUNNING REP_I_01 00:00:00 00:00:05
REPLICAT RUNNING REP_P_01 00:00:00 00:00:03
REPLICAT RUNNING REP_S_01 00:00:02 00:00:00
===========================
Another scenario - no input file
===========================
GGSCI (qanfv-1-dbs-1a) 1> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPM_I_01 00:00:00 00:00:03
EXTRACT RUNNING DPM_P_01 00:00:00 00:00:01
EXTRACT RUNNING DPM_S_01 00:00:00 00:00:01
EXTRACT RUNNING EXT_I_01 00:00:02 00:00:02
EXTRACT RUNNING EXT_P_01 00:00:01 00:00:01
EXTRACT RUNNING EXT_S_01 00:00:01 00:00:05
REPLICAT RUNNING REP_I_01 109:56:59 29:07:15
REPLICAT RUNNING REP_P_01 00:00:00 00:00:04
REPLICAT RUNNING REP_S_01 00:00:00 00:00:01
GGSCI (qanfv-1-dbs-1a) 1> SEND REPLICAT REP_I_01 REPORT
Sending REPORT request to REPLICAT REP_I_01 ...
Request processed.
2020-09-30 09:03:58 INFO OGG-02333 Reading /software/ogg/191/dirdat/01/in/ei000000016, current RBA 453,246,598, 127,887 records, m_file_seqno = 16, m_file_rba = 453,247,190.
Report at 2020-09-30 09:03:58 (activity since 2020-09-30 07:12:38)
No records were replicated.
ls -l /software/ogg/191/dirdat/01/in/ei000000016
ls: cannot access /software/ogg/191/dirdat/01/in/ei000000016: No such file or directory
first available file is:
/software/ogg/191/dirdat/01/in/ei000000243
GGSCI (qanfv-1-dbs-1a) 1> ALTER REPLICAT REP_I_01 extseqno 243 extrba 0
GGSCI (qanfv-1-dbs-1a) 8> send REP_I_01 STATUS
Sending STATUS request to REPLICAT REP_I_01 ...
Current status: Processing data
Sequence #: 16
RBA: 457,712,054
135,430 records in current transaction.
STOP request pending end-of-transaction (135,430 records so far).
Need first to stop the REPLICAT
GGSCI (qanfv-1-dbs-1a) 8> KILL REP_I_01
GGSCI (qanfv-1-dbs-1a) 16> ALTER REPLICAT REP_I_01 extseqno 243 extrba 0
2020-09-30 09:12:00 INFO OGG-06594 Replicat REP_I_01 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP_I_01 with NOFILTERDUPTRANSACTIONS option.
REPLICAT altered.
GGSCI (qanfv-1-dbs-1a) 16> START REP_I_01
GGSCI (qanfv-1-dbs-1a) 19> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPM_I_01 00:00:00 00:00:02
EXTRACT RUNNING DPM_P_01 00:00:00 00:00:09
EXTRACT RUNNING DPM_S_01 00:00:00 00:00:10
EXTRACT RUNNING EXT_I_01 00:00:02 00:00:00
EXTRACT RUNNING EXT_P_01 00:00:02 00:00:09
EXTRACT RUNNING EXT_S_01 00:00:02 00:00:03
REPLICAT RUNNING REP_I_01 00:00:00 00:00:33
REPLICAT RUNNING REP_P_01 00:00:00 00:00:06
REPLICAT RUNNING REP_S_01 00:00:00 00:00:04
Checking REP_I_01 Report in Intervals
2020-09-30 09:13:55 INFO OGG-02333 Reading /software/ogg/191/dirdat/01/in/ei000000243, current RBA 642,943, 1,081 records, m_file_seqno = 243, m_file_rba = 643,535.
2020-09-30 09:16:34 INFO OGG-02333 Reading /software/ogg/191/dirdat/01/in/ei000000243, current RBA 1,695,519, 2,859 records, m_file_seqno = 243, m_file_rba = 1,696,111.
=========================================
HANDLECOLLISIONS/NOHANDLECOLLISIONS
=========================================
Default behavior is NOHANDLECOLLISIONS
Enabling HANDLECOLLISIONS is done to address mismatch in data on two sites.
Golden Gate handles mismatch on below manner:
INSERT Collision
Insert in source whose key column exist on target is converted to update.
Instead of having duplicate insert, the insert is converted to Update.
UPDATE Collision
Updated in source but row not present in target.
- If the row with the old key is not found in the target, the DML is converted to an insert.
- If a row with the new key exists in the target, Replicat deletes the row that has the old key, and the row with the new key is updated, effectively replacing the old row.
UPDATE Collision - Ignored
When Replicat encounters a missing-record error during an update that does not affect a key column, the change is discarded.
DELETE Collision
Deleted in source but row not present in target
Ignored
=========================================
Setting HANDLECOLLISIONS/NOHANDLECOLLISIONS value
=========================================
1. stop GG
GGSCI (qanfv-1-dbs-1a) 13> SEND REPLICAT REP_I_01 REPORT
from REP_I_01.rpt
2020-10-05 14:05:00 INFO OGG-02232 Switching to next trail file /software/ogg/191/dirdat/01/in/ei000000762 at 2020-10-05 14:05:00.576477 due to EOF. with current RBA 499,999,812.
2020-10-05 14:05:21 INFO OGG-01021 Command received from GGSCI: REPORT.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
2020-10-05 14:05:21 INFO OGG-02333 Reading /software/ogg/191/dirdat/01/in/ei000000762, current RBA 24,913,169, 106,074 records, m_file_seqno = 762, m_file_rba = 24,913,761.
Report at 2020-10-05 14:05:21 (activity since 2020-10-05 14:04:20)
No records were replicated.
GGSCI (qanfv-1-dbs-1a) 13> ALTER REPLICAT REP_I_01, BEGIN NOW
ERROR: REPLICAT REP_I_01 is running and cannot be altered (1,2,No such file or directory).
GGSCI (qanfv-1-dbs-1a) 14> STOP REPLICAT REP_I_01
Sending STOP request to REPLICAT REP_I_01 ...
STOP request pending end-of-transaction (199,394 records so far).
GGSCI (qanfv-1-dbs-1a) 14> KILL REPLICAT REP_I_01
Killed process (31188) for REPLICAT REP_I_01
GGSCI (qanfv-1-dbs-1a) 16> ALTER REPLICAT REP_I_01, BEGIN NOW
2020-10-05 14:10:53 INFO OGG-06594 Replicat REP_I_01 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP_I_01 with NOFILTERDUPTRANSACTIONS option.
REPLICAT altered.
GGSCI (qanfv-1-dbs-1a) 17> START REPLICAT REP_I_01
Sending START request to MANAGER ...
REPLICAT REP_I_01 starting
GGSCI (qanfv-1-dbs-1a) 18> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPM_I_01 00:00:00 00:00:09
EXTRACT RUNNING DPM_P_01 00:00:00 00:00:02
EXTRACT RUNNING DPM_S_01 00:00:00 00:00:09
EXTRACT RUNNING EXT_I_01 00:00:00 00:00:00
EXTRACT RUNNING EXT_P_01 00:00:02 00:00:05
EXTRACT RUNNING EXT_S_01 00:00:01 00:00:05
REPLICAT RUNNING REP_I_01 00:00:00 00:00:06
REPLICAT RUNNING REP_P_01 00:00:00 00:00:03
REPLICAT RUNNING REP_S_01 00:00:00 00:00:06
Alternative:
Use in REPLICATE prm file INSERTMISSINGUPDATES
Limitation:
It can be used only if Extract logs all columns, and not just the changed colums + PK columns
In Extract:
To have all columns logged, the Extract should have this setting:
ADD SCHEMATRANDATA <MY_SCHEMA> ALLCOLS
In Replicat:
Edit replicat prm file
INSERTMISSINGUPDATES
MAP MY_SCHEMA.*, target MY_SCHEMA.*;
There is an option to reload parameters files, without restart:
REFRESH REPLICAT <EXTRACT GROUP>
REFRESH EXTRACT <EXTRACT GROUP>
