Oracle GoldenGate – Checkpoint Table and Avoiding/Handling Collisions

Checkpoint Table

When database checkpoints are being used, Oracle GoldenGate creates a checkpoint table with a user-defined name in the database upon execution of the ADD CHECKPOINTTABLE command, or a user can create the table by using the chkpt_db_create.sql script (where db is an abbreviation of the type of database that the script supports).

Replicat uses it to couple the read checkpoint within the same atomic transaction as the ‘change’ to the target table.

Checkpoint Table Benefits:

It makes recovery easier if you lose the Replicat checkpoint file.
It should be faster since the Replicat uses a nowait commit with a checkpoint table. Without a checkpoint table it uses a wait commit.

MA: Change Delivery Tasks

  • Create a checkpoint table in the target database – Best Practice. (OGG> Add CheckpointTable c##oggadmin.myckpt)
  • Create a parameter file for Replicat. (OGG> Edit Params <group>
  • Create a Replicat group. (OGG> Add Replicat <params>
  • Start the Replicat process. (OGG> Start Replicat <group>)

You can provide checkpoint instructions by:

  • Specifying a default checkpoint table in the GLOBALS file
  • Using CheckpointTable or NoDBCheckpoint in the Add Replicat command to override the default
  • Using the file system (Text file act as checkpoint table) or database for storing the checkpoint table

In MA (Microservice Architecture) GLOBALS file is created automatically, whereas in CA (Classic Architecture) you have to create it manually.

Avoiding Collisions with Initial Load

  • If the source database remains active during an initial load, you must either avoid or handle any collisions when updating the target with interim changes.
  • If you can backup, restore, or clone the database at a point in time, you can avoid collisions by starting Replicat to read trail records from a specific transactions commit sequence number (CSN).
  • You may use AtCSN or AfterCSN to avoid collisions. For example: GGSCI> Start Replicat <group> AtCSN | AfterCSN <csn>

Handling Collisions with Initial Load

If you cannot avoid collisions, you must handle them.
The Replicat HandleCollisions parameter can be used.
When Replicat encounters a duplicate-record error on an insert, it writes the change record over the initial data load record.
When Replicat encounters a missing-record error for an update or delete, the change record is discarded.
After all the change data generated during the load has been replicated, turn off.

HandleCollisions:

  • Temporary Remove – GGSCI> Send Replicat <group> NoHandleCollisions
  • Permanently Remove Parameter from Parameter File – GGSCI> Edit Param <group>
Share This

Muhammad Rawish Siddiqui

Master in Computer Science (Database Engineering) and PGD in Management Information System.
45+ Online Certifications, which includes EDRP (EC-Council Disaster Recovery Professional), Data Management, Security+, DBaaS - Cloud Certified Expert, Oracle Cloud Autonomous Database Specialist, Oracle Database Cloud Certified Professional, Oracle Cloud Infrastructure, OCP 7.3, 10g, 11g, 11i, R12, OCE/OCS 11i System Administration, Solaris, Linux and Real Application Cluster 10g, 11g, Oracle Autonomous Database, Real Application Cluster, Oracle Data Guard and Performance Tuning etc. .

Foremost interested areas include, Data Management, Oracle Databases and EBS Health Checks, Real Application Cluster, Disaster Recovery, GoldenGate and Oracle Cloud.
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments