Adding Replicats with @RANGE function in GG

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.

First check if the existing Replicat process is running:

GGSCI (server2) 1> info all

Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RITSM01 00:00:00 00:00:01

Comment MANGER parameter file FOR AUTOSTART REPLICAT

GGSCI (osekilx411p) EDIT PARAM MGR
GGSCI (osekilx411p) REFRESH MGR

Stop the existing Replicat process:

GGSCI (server2) 2> stop REPLICAT RITSM01
Sending STOP request to REPLICAT RITSM01...
Request processed.

Add the new Replicat process, using the existing trail file.

GGSCI (server2) 3> add REPLICAT RITSM02, exttrail ./dirdat/tb
REPLICAT added.

Now add the configuration by creating a new parameter file for RITSM02.

GGSCI (server2) 4> edit params RITSM02
—
— Example Replicator parameter file to apply changes
— to target tables
—
REPLICAT ROLAP02
SOURCEDEFS ./dirdef/mydefs.def
SETENV (ORACLE_SID= OLAP)
USERID ggs_admin, PASSWORD ggs_admin
DISCARDFILE ./dirrpt/ritsm02.dsc, PURGE
ALLOWDUPTARGETMAP
CHECKPOINTSECS 30
GROUPTRANSOPS 2000

MAP 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.

GGSCI (server2) 5> edit params RITSM01
—
— Example Replicator parameter file to apply changes
— to target tables
—
REPLICAT RITSM01
SOURCEDEFS ./dirdef/mydefs.def
SETENV (ORACLE_SID=OLAP)
USERID ggs_admin, PASSWORD ggs_admin
DISCARDFILE ./dirrpt/ritsm01.dsc, PURGE
ALLOWDUPTARGETMAP
CHECKPOINTSECS 30
GROUPTRANSOPS 2000
MAP 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.

GGSCI (dbserver2) 6> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED RITSM01 00:00:00 00:75:10

REPLICAT 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.

GGSCI (server2) 7> info REPLICAT RITSM1, detail

REPLICAT ROLAP01 Last Started 2022-10-01 15:35 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:12:43 ago)

Log Read Checkpoint File ./dirdat/tb000379
2022-10-08 12:27:00.001016 RBA 43750979
Extract Source Begin End

./dirdat/tb000379 2022-10-01 12:47 2022-10-08 12:27
./dirdat/tb000357 2022-10-01 10:30 2022-10-01 12:47
./dirdat/tb000355 2022-09-30 13:50 2022-10-01 10:30
./dirdat/tb000306 2022-09-30 13:50 First Record
./dirdat/tb000306 2022-09-30 10:30 2022-09-30 13:50
./dirdat/tb000284 2022-09-30 10:30 First Record
./dirdat/tb000284 2022-09-30 00:00 2022-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.

GGSCI (server2) 8> alter replicat RITSM02, extseqno 379
REPLICAT altered.

GGSCI (server2) 9> alter replicat RITSM02, extrba 43750979
REPLICAT altered.

Start both Replicat processes. Note the use of the wildcard (*).

GGSCI (server2) 10> start replicat RITSM*
Sending START request to MANAGER …
REPLICAT RITSM01 starting

Sending START request to MANAGER …
REPLICAT RITSM02 starting

Check if both Replicat processes are running.

GGSCI (server2) 11> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING RITSM01 00:00:00 00:00:30

REPLICAT RUNNING RITSM02 00:00:00 00:00:12

Generate a report for the new Replicat process RITSM02.

GGSCI (server2) 13> send REPLICAT RITSM02, report
Sending REPORT request to REPLICAT RITSM02 …
Request processed.

Hope it worked for you !! 🙂