Hey Folks,
We have recently faced issue with huge lag due to bulk transaction(Insert Operation). To resolve the gap quickly, we opted in creating Range Replicat.
The RANGE function
The way the @RANGE function works is it computes a hash value of the columns specified in the input. If no columns are specified, it uses the table’s primary key. GoldenGate adjusts the total number of ranges to optimize the even distribution across the number of ranges specified. This concept can be compared to Hash Partitioning in Oracle tables as a means of dividing data.
With any division of data during replication, the integrity is paramount and will have an effect on performance. Therefore, tables having a relationship with other tables in the source schema must be included in the configuration. If all your source schema tables are related, you must include all the tables!
Adding Replicats with @RANGE function
The @RANGE function accepts two numeric arguments, separated by a comma: Range: The number assigned to a process group, where the first is 1 and the second 2 and so on, up to the total number of ranges. Total number of ranges: The total number of process groups you wish to divide using the @RANGE function.
ISSUE: There was huge transaction done at source side on Table T111 that’s why this GG process is getting lag.
SOLUTION: Creating Range Replicat to overcome lag.
1 2 3 4 5 6 7 | First check if the existing Replicat process is running:GGSCI (server2) 1> info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGREPLICAT RUNNING RITSM01 00:00:00 00:00:01 |
1 2 3 4 | Comment MANGER parameter file FOR AUTOSTART REPLICATGGSCI (server2) EDIT PARAM MGRGGSCI (server2) REFRESH MGR |
1 2 3 4 5 | Stop the existing Replicat process:GGSCI (server2) 2> stop REPLICAT RITSM01Sending STOP request to REPLICAT RITSM01...Request processed. |
Add the new Replicat process, using the existing trail file.1 2 | GGSCI (server2) 3> add REPLICAT RITSM02, exttrail ./dirdat/tbREPLICAT added. |
Now add the configuration by creating a new parameter file for RITSM02.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | GGSCI (server2) 4> edit params RITSM02—— Example Replicator parameter file to apply changes— to target tables—REPLICAT ROLAP02SOURCEDEFS ./dirdef/mydefs.defSETENV (ORACLE_SID= OLAP)USERID ggs_admin, PASSWORD ggs_adminDISCARDFILE ./dirrpt/ritsm02.dsc, PURGEALLOWDUPTARGETMAPCHECKPOINTSECS 30GROUPTRANSOPS 2000MAP SRC.ORDERS, TARGET TGT.ORDERS, FILTER (@RANGE (1,2));MAP SRC.ORDER_ITEMS, TARGET TGT.ORDER_ITEMS, FILTER (@RANGE(1,2));MAP SRC.PRODUCTS, TARGET TGT.PRODUCTS, FILTER (@RANGE (1,2)); |
Now edit the configuration of the existing Replicat process, and add the @RANGE function to the FILTER clause of the MAP statement. Note the inclusion of the GROUPTRANSOPS parameter to enhance performance by increasing the number of operations allowed in a Replicat transaction.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | GGSCI (server2) 5> edit params RITSM01—— Example Replicator parameter file to apply changes— to target tables—REPLICAT RITSM01SOURCEDEFS ./dirdef/mydefs.defSETENV (ORACLE_SID=OLAP)USERID ggs_admin, PASSWORD ggs_adminDISCARDFILE ./dirrpt/ritsm01.dsc, PURGEALLOWDUPTARGETMAPCHECKPOINTSECS 30GROUPTRANSOPS 2000MAP SRC.ORDERS, TARGET TGT.ORDERS, FILTER (@RANGE (2,2));MAP SRC.ORDER_ITEMS, TARGET TGT.ORDER_ITEMS, FILTER (@RANGE(2,2));MAP SRC.PRODUCTS, TARGET TGT.PRODUCTS, FILTER (@RANGE (2,2)); |
Check that both the Replicat processes exist.1 2 3 4 5 6 7 8 9 | GGSCI (dbserver2) 6> info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGREPLICAT STOPPED RITSM01 00:00:00 00:75:10REPLICAT STOPPED RITSM02 00:00:00 00:79:30 |
Before starting both Replicat processes, obtain the log Sequence Number (SEQNO) and Relative Byte Address (RBA) from the original trail file.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | GGSCI (server2) 7> info REPLICAT RITSM1, detailREPLICAT ROLAP01 Last Started 2024-10-01 15:35 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:12:43 ago)Log Read Checkpoint File ./dirdat/tb0003792024-10-08 12:27:00.001016 RBA 43750979Extract Source Begin End./dirdat/tb000379 2024-10-01 12:47 2024-10-08 12:27./dirdat/tb000357 2024-10-01 10:30 2024-10-01 12:47./dirdat/tb000355 2024-09-30 13:50 2024-10-01 10:30./dirdat/tb000306 2024-09-30 13:50 First Record./dirdat/tb000306 2024-09-30 10:30 2024-09-30 13:50./dirdat/tb000284 2024-09-30 10:30 First Record./dirdat/tb000284 2024-09-30 00:00 2024-09-30 10:30./dirdat/tb000000 *Initialized* 2022-09-30 00:00./dirdat/tb000000 *Initialized* First Record |
Adjust the new Replicat process RITSM02 to adopt these values, so that the process knows where to start from on startup.1 2 3 4 5 | GGSCI (server2) 8> alter replicat RITSM02, extseqno 379REPLICAT altered.GGSCI (server2) 9> alter replicat RITSM02, extrba 43750979REPLICAT altered. |
Start both Replicat processes. Note the use of the wildcard (*).1 2 3 4 5 6 | GGSCI (server2) 10> start replicat RITSM*Sending START request to MANAGER …REPLICAT RITSM01 startingSending START request to MANAGER …REPLICAT RITSM02 starting |
Check if both Replicat processes are running.1 2 3 4 5 6 7 8 9 | GGSCI (server2) 11> info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGREPLICAT RUNNING RITSM01 00:00:00 00:00:30REPLICAT RUNNING RITSM02 00:00:00 00:00:12 |
Generate a report for the new Replicat process RITSM02.1 2 3 | GGSCI (server2) 13> send REPLICAT RITSM02, reportSending REPORT request to REPLICAT RITSM02 …Request processed. |
Hope it worked for you !! 