In the latest post, I documented how to copy/move high table data volume using GoldenGate Initial Load (with SPECIALRUN option).
Sometimes, we (dba/sysadmins) need to move HIGH data (tables with billion rows), in shortest time possible.
So, sharing useful tips, that helps to reach this goal.
Making GoldenGate Initial load work as PARALLEL:
To run GoldenGate in PARALLEL (with RANGE option), it’s necessary to create remote trail file, so the diference from the first post, this will work with ‘RMTFILE’ option added in GGSCI>.
# On source GoldenGate add follow EXTRACT (ggsci>):
GGSCI> ADD EXTRACT load1, SOURCEISTABLE GGSCI> EDIT PARAMS load1 EXTRACT load1 userid ggate@goldengate RMTHOST target-mgr.grepora.com, MGRPORT 7809 RMTFILE ./dirdat/il, MEGABYTES 1024, format level 4 PURGE ---Loading tables map CUSTOMER.TABLE1; GGSCI> START LOAD*
# On target GoldenGate add follow REPLICAT:
GGSCI> ADD REPLICAT LOAD11, exttrail ./dirdat/ml, checkpointtable GGATE.CHECKPOINT GGSCI> EDIT PARAMS load11 REPLICAT load11 userid ggate@goldengate ASSUMETARGETDEFS SOURCECHARSET PASSTHRU TABLE CUSTOMER.TABLE1, TARGET CUSTOMER_CLOUD.TARGET_TABLE1, filter (@RANGE (1,2));
GGSCI> ADD REPLICAT LOAD12, exttrail ./dirdat/ml, checkpointtable GGATE.CHECKPOINT GGSCI> EDIT PARAMS load12 REPLICAT load12 userid ggate@goldengate ASSUMETARGETDEFS SOURCECHARSET PASSTHRU TABLE CUSTOMER.TABLE1, TARGET CUSTOMER_CLOUD.TARGET_TABLE1, filter (@RANGE (1,2)); GGSCI> START LOAD*
Tuning database inserts/updates:
The following are suggestions that can make the load go faster and help you to avoid errors.
Data: Make certain that the target tables are empty. Otherwise, there may be duplicate-row errors or conflicts between existing rows and rows that are being loaded.
Constraints: Disable foreign-key constraints and check constraints. Foreign-key constraints can cause errors, and check constraints can slow down the loading process. Constraints can be reactivated after the load concludes successfully.
Indexes: Remove indexes from the target tables. Indexes are not necessary for inserts. They will slow down the loading process significantly. For each row that is inserted into a table, the database will update every index on that table. You can add back the indexes after the load is finished.