Oracle Goldengate Parallel Replicat and Data Manipulation using OGG
In this session we discussed about below:
1. Parallel Replicat:
-------------------------------
Parallel Replicat is one of the new features introduced in Oracle ®? GoldenGate 12c Release 3 (12.3.0.1).
Parallel Replicat is a highly scalable apply engine for the Oracle database that can automatically parallelize the apply workload and take dependencies between transactions into account.
Parallel Replicat provides all the benefits of Integrated Replicat by performing the dependency computation and parallelism outside of the database.
It parallelizes the reading and mapping of trail files and provides the ability to apply large transactions quickly.
The dependency computation, parallelism of the mapping, and apply are performed outside of the database and can be off-loaded to another server. The transaction integrity is maintained in this process.
2. Types of Parallel Replicat:
-------------------------------
Parallel Integrated Replicat (GG v 12.3 and DB v12.1): This is Integrated Replicat running in parallel mode.
Parallel Non Integrated Replicat (GG v 12.3 and DB v 12.2 onwards): This is non Integrated Replicat running in paralle mode.
3. Basic Parallel Replicat parameters:
-------------------------------
MAP_PARALLELISM: Configures the number of mappers. This parameter controls the number of threads used for reading the trail file. The minimum value is 1, maximum value is 100, and the default value is 2.
APPLY_PARALLELISM: Configures the number of appliers. This parameter controls the number of connections in the target database that are used for applying the changes. The default value is 4.
MIN_APPLY_PARALLELISM and MAX_APPLY_PARALLELISM: The Apply parallelism function is auto-tuned. You can set a minimum and maximum value to define the ranges in which the Replicat automatically adjusts its parallelism. There are no defaults. Do not use this parameter at the same time as the APPLY_PARALLELISM parameter
GGSCIadd replicat parep,parallel exttrail trail loc,checkpointtable chkpttbl name
Non Integrated: - Uses a checkpoint table
add replicat repname,exttrail remote trail of data pump from source
Integrated: Checkpoint table is not mandatory
add replicat repname,integrated exttrail remote trail of data pump from source
Parallel non integrated Replicat: Uses a checkpoint table
add replicat repname,parallel exttrail remote trail of data pump from source
Parallel integrated Replicat:Checkpoint table is not mandatory
add replicat repname,parallel integrated exttrail remote trail of data pump from source
4. Exercise discussed during session:
-------------------------------
When a record is deleted from source, it should not be deleted physically from target database. Only logically it should be deleted, basically it should be soft delete.
When a record is deleted from source, delete_flag column value on target should be Y
When Insert and update on source happens, corresponding delete_flag row on target should have delete_flag as N
We need to achieve it using below parameters:
UPDATEDELETES
ALLOWDUPTARGETMAP
IGNOREINSERTS
IGNOREUPDATES
IGNOREDELETES
UPDATEDELETES: Converts delete operations on Source into update operations.
ALLOWDUPTARGETMAP: Allows Duplicate Target Map Statements for the Same Object
3. For converting the updates to deletes on target we used parameters such as UPDATEDELETES and ALLOWDUPTARGETMAP.
GETINSERTS|IGNOREINSERTS
GETUPDATES|IGNOREUPDATES
GETDELETES|IGNOREDELETES
UPDATEDELETES|NOUPDATEDELETES
ALLOWDUPTARGETMAP|NOALLOWDUPTARGETMAP – Non Integrated Replicat
Two conditions we have to use here :
a. INSERT and UPDATES | IGNORE DELETES
DELETE_FLAG=’N’
b. GETDELETES | UPDATEDELETES | IGNORE INSERTS IGNORE UPDATES
DELETE_FLAG=’Y’