Understanding Checkpoints in Oracle GoldenGate Extract and Replicat

In Oracle GoldenGate, a checkpoint in the Extract process is a mechanism used to record the current read and write positions in the data stream. This ensures data consistency, fault tolerance, and recovery in case of failures.

 What is a Checkpoint in Extract?

A checkpoint is a record of the current position in the source database’s transaction log (like the redo log in Oracle or transaction log in SQL Server) that the Extract process has read and processed.

🧩 Purpose of Checkpoints

1. Fault Tolerance: If the Extract process stops or crashes, it can resume from the last checkpoint instead of starting over.
2. Data Consistency: Ensures that no transactions are missed or duplicated.
3. Performance Optimization: Helps manage memory and disk usage by purging old data that has already been processed.

📌 Types of Checkpoints in Extract

1. Read Checkpoint:
   • Marks the position in the transaction log where Extract last read.
   • Ensures Extract knows where to resume reading after a restart.
2. Write Checkpoint:
   • Marks the position in the trail file where Extract last wrote data.
   • Ensures that data is not written twice or skipped.

In the diagram, the extract process starts by reading the oldest open transaction and keeps track until it is committed. Once committed, the pointer moves to the next uncommitted transaction. This way, the extract process tracks each transaction and notes which one is committed first to write to the trail file.

🗂 Where Are Checkpoints Stored?

• Checkpoint Table (recommended): A table in the database that stores checkpoint information.
• Checkpoint Files: Local files on disk used when a checkpoint table is not configured.

🔄 How It Works (Simplified Flow)

1. Extract reads from the source database log.
2. It processes the data and writes it to a trail file.
3. After writing, it updates the checkpoint to reflect the new position.
4. If Extract is restarted, it resumes from the last checkpoint.

Check the restore point with the following command:

Info all   -- Give all process running
OR 

INFO <extract_name> showch

OR 

INFO EXTRACT <extract_name>, SHOWCH

Give information as:

• Current read and write checkpoint positions
• Trail file details
• Recovery checkpoint
• Oldest unprocessed transaction
• Checkpoint table (if used)

What is a Replicat Checkpoint?

A Replicat checkpoint records the position in the trail file from which the Replicat process last successfully applied a transaction to the target database.

🧩 Purpose of Replicat Checkpoints

1. Recovery: If Replicat stops or crashes, it can resume from the last checkpoint without reapplying already committed transactions.
2. Data Integrity: Prevents duplication or loss of data during replication.
3. Monitoring: Helps administrators track replication lag and performance.

Types of Checkpoints in Replicat

1. Read Checkpoint:
   • The position in the trail file that Replicat has read up to.
2. Write Checkpoint:
   • The position in the target database where Replicat has successfully applied changes.

Where Are Replicat Checkpoints Stored?

• Checkpoint Table (recommended): A table in the target database.
• Checkpoint Files: Local files on disk (used if checkpoint table is not configured). Checkpoint files on disk in the dirchk sub directory of the Oracle Golden Gate directory.

Use this command in GGSCI:

INFO REPLICAT <replicat_name>, SHOWCH

Note: Replicat keeps track of the last CSN/SCN from the source that has been applied to the target using a checkpoint.

Check checkpoint table at target database:

SELECT GROUP_NAME,GROUP_KEY,LAST_UPDATE_TS,LOG_CSN,LOG_CMPLT_CSN from gguser.chkpt;