{"id":5363,"date":"2025-03-19T07:00:00","date_gmt":"2025-03-19T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=5363"},"modified":"2025-06-08T03:37:53","modified_gmt":"2025-06-08T00:37:53","slug":"pos-mporoyme-na-ayxisoyme-tin-apodosi","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/post-mortem-of-the-apodosis\/","title":{"rendered":"How can we increase performance on Oracle GoldenGate Replicat target with parallelism?"},"content":{"rendered":"

If we choose to do it Initial Load<\/strong> before synchronization via Replicat<\/strong> of GoldenGate<\/strong> or simply Replicat has a large amount of data to synchronize, one Replicat thread is not enough.<\/p>\n\n\n\n

In this article we will see two ways to run the Replicat process in parallel. One way is through Classic Replicat with RANGE FILTER which has very good performance at Initial Load<\/strong>The other way is through Coordinated Replicat<\/strong> which is recommended for transaction synchronization because it protects against conflicts, blocks and deadlocks <\/strong>as the head Coordinator process coordinates the execution of DDL (ALTER, CREATE) and DML (UPDATE, INSERT, DELETE) actions in the correct order<\/p>\n\n\n\n

Classic Replicat with RANGE FILTER<\/h3>\n\n\n\n

In this mode we can do MAP<\/code> and putting RANGE FILTER<\/strong> <\/code>in the tables that are in the parameter file of each Replicat, we can divide them into multiple partitions. This way we can create multiple different Replicat processes, each of which is responsible for applying the specific partition of the table.<\/p>\n\n\n\n

\"\"
01 (docs.oracle.com)<\/figcaption><\/figure>\n\n\n\n

This method has excellent performance for use in Initial Load as it consists of massive INSERTS<\/code>. However, we must be careful because this method cannot be used in the second phase of synchronization where we will pass transactions as the fact that DDL (ALTER, CREATE) and DML (UPDATE,INSERT,DELETE) actions are performed does not guarantee that they will be passed in the correct order. For this reason, if we try to pass transactions with this method we will have conflicts, blocks, deadlocks and errors.<\/p>\n\n\n\n

Coordinated Replicat<\/strong> <\/h3>\n\n\n\n

In this function, by defining during creation that it is a coordinated replica and making MAP<\/code> putting THREADRANGE<\/strong><\/code> in the tables found in the Replicat parameter file, you create a single process called Replicat Coordinator, this in turn creates threads accordingly THREADRANGE<\/code> that we have defined and undertakes to coordinate the order in which each thread must run so that transactions do not have conflicts, blocks, deadlocks and errors. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

The differences between Replicat modes<\/h3>\n\n\n\n
\"\"
03 (docs.oracle.com)<\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

The prerequisites<\/h2>\n\n\n\n

For this article we assume that we already have a complete GoldenGate installation.<\/p>\n\n\n\n

How to install and configure can be read in the article here<\/a>.<\/p>\n\n\n\n

Then for our example we will assume that we have made the following settings in the target for Extracts.<\/p>\n\n\n\n

We have created and started the extract for transaction synchronization:<\/p>\n\n\n\n

edit param MIS_EXT<\/pre>\n\n\n\n
EXTRACT MIS_EXT\nUSERIDALIAS GGADMIN\nRMTHOST sqlserverdb.mshome.net, MGRPORT 7809\nRMTFILE .\/dirdat\/mi, PURGE\nTABLE STRATOS.CUSTOMERS;<\/code><\/pre>\n\n\n\n
add extract mis_ext, integrated tranlog, exttrail .\/dirdat\/mi, BEGIN NOW<\/pre>\n\n\n\n
start mis_ext<\/pre>\n\n\n\n
We have created and started the extract for the Initial Load:<\/p>\n\n\n\n
edit param INI_EXT<\/pre>\n\n\n\n
EXTRACT INI_EXT\nUSERIDALIAS GGADMIN\nRMTHOST sqlserverdb.mshome.net, MGRPORT 7809\nRMTFILE .\/dirdat\/fe, PURGE\nTABLE STRATOS.CUSTOMERS;<\/code><\/pre>\n\n\n\n
add extract ini_ext, sourceistable<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Installing Classic Replicat with RANGE FILTER for Initial Load<\/h2>\n\n\n\n
For its installation, the only difference from a Replicat is the addition of RANGE FILTER <\/code>after the MAP<\/code> with a definition depending on how many Replicat Processes we want to create. For our example we will have two processes:<\/p>\n\n\n\n
edit param IN1_REP<\/pre>\n\n\n\n
REPLICAT in1_rep\nTARGETDB sqlserverdb, USERIDALIAS ggadmin\nSOURCECHARSET PASSTHRU\nBATCHSQL\nREPERROR(60,TRANSABORT, MAXRETRIES 10, DELAYSECS 60)\nDISCARDFILE .\/dirrpt\/sql_discard.txt, APPEND, MEGABYTES 50\nFUNCTIONSTACKSIZE 500\nMAP STRATOS.CUSTOMERS, TARGET DBO.CUSTOMERS FILTER (@RANGE(1,2));\n<\/code><\/pre>\n\n\n\n
edit param IN2_REP<\/pre>\n\n\n\n
REPLICAT in2_rep\nTARGETDB sqlserverdb, USERIDALIAS ggadmin\nSOURCECHARSET PASSTHRU\nBATCHSQL\nREPERROR(60,TRANSABORT, MAXRETRIES 10, DELAYSECS 60)\nDISCARDFILE .\/dirrpt\/sql_discard.txt, APPEND, MEGABYTES 50\nFUNCTIONSTACKSIZE 500\n\nMAP STRATOS.CUSTOMERS, TARGET DBO.CUSTOMERS FILTER (@RANGE(2,2));<\/code><\/pre>\n\n\n\n
Beyond the RANGE FILTER<\/code> we have added the BATCHSQL<\/code><\/strong>, the TRANSABORT<\/code><\/strong> for re-executing transactions from timeouts\/deadlocks and we have increased the FUNCTIONSTACKSIZE<\/strong><\/code> so that insert statements can be run en masse in batches, increasing performance.<\/p>\n\n\n\n
Then we add these two replicat processes:<\/p>\n\n\n\n
add replicat in1_rep, exttrail .\/dirdat\/fe\nadd replicat in2_rep, exttrail .\/dirdat\/fe<\/pre>\n\n\n\n
And let's get started:<\/p>\n\n\n\n
start in*<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Installing Coordinated Replicat for transaction synchronization<\/h2>\n\n\n\n
To install it, we will only create a Replicat to which we will add the THREADRANGE<\/code> that we want after MAP<\/code> with a definition depending on how many threads we want to use. For our example we will set 8 threads:<\/p>\n\n\n\n
edit param mirep<\/pre>\n\n\n\n
REPLICAT mirep\nTARGETDB sqlserverdb, USERIDALIAS ggadmin\nSOURCECHARSET PASSTHRU\nREPERROR(60,TRANSABORT, MAXRETRIES 10, DELAYSECS 60)\nDISCARDFILE .\/dirrpt\/sql_discard.txt, APPEND, MEGABYTES 50\nDISCARDROLLOVER AT 03:00 ON TUESDAY\nREPORTROLLOVER AT 03:00 ON TUESDAY\nFUNCTIONSTACKSIZE 500\nHANDLECOLLISIONS\nAPPLY_PARALLELISM 8<\/code>MAP STRATOS.CUSTOMERS, TARGET DBO.CUSTOMERS THREADRANGE(1-8);\n<\/code><\/pre>\n\n\n\n
The parameter HANDLE COLLISIONS<\/strong><\/code> we define it so that we don't have a problem with the initial synchronization with conflicts from the Initial Load, we can remove it later. If we want it to run only in one thread on the specific table instead of THREADRANGE <\/strong><\/code>we put THREAD(1)<\/strong><\/code>Optionally if we want it to run with 8 threads by default on any table we define APPLY_PARALLELISM 8<\/strong><\/code>.<\/p>\n\n\n\n
Then we add and start the Coordinated Replicat process:<\/p>\n\n\n\n
add replicat mirep, coordinated, exttrail .\/dirdat\/mi\nstart mirep<\/pre>\n\n\n\n
In case we want to start from a specific trail file and rba:<\/p>\n\n\n\n
add replicat mirep, coordinated, exttrail .\/dirdat\/fe, extseqno=55, extrba=1292840<\/pre>\n\n\n\n
When we start it we set the parameter FILTERDUMPTRANSACTIONS<\/code><\/strong> so that transactions that have already passed can be checked:<\/p>\n\n\n\n
start mirep FILTERDUPTRANSACTIONS<\/pre>\n\n\n\n
Once it starts, we will see that Coordinated Replicat has created eight threads underneath it:<\/p>\n\n\n\n
info mirep, detail<\/pre>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
In case of abnormal stop of Coordinated Replicat<\/h3>\n\n\n\n
In the event that Coordinated Replicat aborts or closes unexpectedly, we must synchronize it with the command before starting it again. synchronize<\/code><\/strong> so that all threads are at the same point in time:<\/p>\n\n\n\n
synchronize replicat mirep<\/pre>\n\n\n\n
When the synchronization is complete we will see that it has stopped, then we can start it again:<\/p>\n\n\n\n
start mirep<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n