Splitting and Merging Replicats

Submitted by dave on Fri, 11/22/2019 - 16:02

If you have tuned a replicat as much as you can and it still is unable to apply transactions on the target database as fast as they are being captured, you may want to consider splitting the load among multiple replicats. In a perfect world we would be able to use 1 replicat for all apply, but realistically there may be factors out of our control that are limiting the apply rate. I do always encourage analyzing the sessions on the target database that are applying transactions. I have often found updates to one or two of the tables may be slow due to a missing index or PK. Adding an appropriate index is an easy fix and will increase performance without requiring workarounds as described here.

Splitting Scenario 1 : Single replicat into multiple replicats by Schema or Table

For this scenario you are really just distributing MAP statements among multiple Replicat processes. You can use the replicat STATS command to determine what tables have heavy DML and may need to be moved or balanced between multiple replicats.

Original Replicat MAP statement:

MAP SCHEMA1.TABLE1, TARGET SCHEMA1.TABLE1;
MAP SCHEMA1.TABLE2, TARGET SCHEMA1.TABLE2;
MAP SCHEMA2.*, TARGET SCHEMA2.*;

New Replicat 1:

MAP SCHEMA1.TABLE1, TARGET SCHEMA1.TABLE1;

New Replicat 2: 

MAP SCHEMA1.TABLE2, TARGET SCHEMA1.TABLE2;

New Replicat 3: 

MAP SCHEMA2.*, TARGET SCHEMA2.*;

Splitting Scenario 2 : Single table replicat changed to be applied by multiple replicats

If you have a replicat that services one table, you can use the @RANGE command so the replicats are each responsible for applying part of the records of the specified table.

Original Replicat MAP statement:

MAP SCHEMA1.TABLE1, TARGET SCHEMA1.TABLE1;

New Replicat 1:

MAP SCHEMA1.TABLE1, TARGET SCHEMA1.TABLE1, FILTER (@RANGE (1,2));

New Replicat 2: 

MAP SCHEMA1.TABLE1, TARGET SCHEMA1.TABLE1, FILTER (@RANGE (2,2));

Procedure:

  1. Stop the existing replicat
  2. Record the current position of the replicat (Seqno, RBA)
  3. Create new parameter files as needed to specify the new replication layout
  4. Create new replicats
  5. ALTER new replicats to start at the Seqno, RBA recorded earlier
  6. Start the replicats

Combining Scenario
This can be tricker since it is difficult to stop multiple replicats at the same point. Oracle has provided a method for stopping replicats at a specific SCN but I haven't seen it work successfully after multiple tries. See How To Stop Replication At A Particular SCN? (Doc ID 2263854.1) for more info on that.

So for combining multiple replicats into one my method is this if trying to absorb Replicat B into Replicat A. Essentially we will move B's responsibilities to A and use HANDLECOLLISIONS to bridge the small overlap of the two.
Procedure:

  1. Stop Replicat A. Record the EXTSEQNO and RBA.
  2. Stop Replicat B. Record the EXTSEQNO and RBA. Ensure B has applied more than A (its RBA is greater than A)
  3. Reconfigure Replicat A's MAP statement to include those from Replicat B
  4. Enable HANDLECOLLISIONS for Replicat A and start it.
  5. Allow Replicat A to run past B's EXTSEQNO and RBA position. Stop Replicat A, remove HANDLECOLLISIONS, start Replicat A again.
  6. Delete replicat B. Replicat A is now handling all of Replicat B's responsibilities and is no longer needed.