Top Oracle GoldenGate Commands Every DBA Must Master

 GoldenGate is not just replication.

It’s real-time data movement with precision.
But most DBAs only scratch the surface.
If you’re serious about production-grade replication, these are the commands you must know inside GGSCI


1. INFO ALL
Your first diagnostic command.
GGSCI> INFO ALL
Shows status of Extract, Replicat, Manager.
Also gives checkpoint lag.
If lag increases, you investigate immediately.
This is your heartbeat monitor.


2. VIEW REPORT
When something fails, this is where truth lives.
GGSCI> VIEW REPORT EXT1
Gives detailed execution logs.
Error codes, trail issues, checkpoint problems.
Never guess. Always read report files.


3. STATS
Understand throughput and performance.
GGSCI> STATS EXTRACT EXT1
Shows number of operations processed.
Insert / Update / Delete counts.
Helps validate replication correctness during testing.


4. LAG
Real-time latency check.
GGSCI> LAG EXTRACT EXT1
or
GGSCI> LAG REPLICAT REP1
Critical in real-time systems (banking, telecom).
Even seconds matter.


5. START / STOP
Basic but dangerous if misused.
GGSCI> STOP EXTRACT EXT1
GGSCI> START EXTRACT EXT1
Always coordinate with downstream systems.
Stopping Extract blindly can cause backlog explosion.


6. ALTER EXTRACT / REPLICAT
Used for repositioning.
GGSCI> ALTER EXTRACT EXT1, BEGIN NOW
or
GGSCI> ALTER REPLICAT REP1, EXTSEQNO 15, EXTRBA 12345
Used during recovery scenarios.
Requires precision. One mistake = data inconsistency.


7. INFO DETAIL
Deep dive into process state.
GGSCI> INFO EXTRACT EXT1, DETAIL
Shows checkpoint, trail file, SCN position.
Essential during troubleshooting.


8. SEND Command
Real-time interaction with running process.
GGSCI> SEND EXTRACT EXT1 STATUS
No need to stop process.
Useful for live debugging.


9. LOGDUMP (Advanced)
Not GGSCI, but critical tool.
logdump> open ./dirdat/aa
Lets you read trail files.
This is where elite DBAs operate.


10. DELETE / ADD / REGISTER
Lifecycle management.
GGSCI> ADD EXTRACT EXT1, INTEGRATED TRANLOG
GGSCI> REGISTER EXTRACT EXT1 DATABASE
Especially important in integrated capture mode.


Quick Takeaway / Summary
GoldenGate is simple… until it breaks.
These commands are not optional—they’re survival tools.
Master them, and you control replication.
Ignore them, and replication controls you.

Golden Gate – Extract and Replicat Checkpoint

 In Oracle GoldenGate, checkpoints in the Extract and Replicat processes ensure data consistency and fault tolerance. They record read/write positions, allowing processes to resume after failures without data loss. Checkpoints can be stored in checkpoint tables or files, helping optimize performance and monitor replication progress effectively.

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.

 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;

Common issue in Golden Gate along with Solution

 Common issue in Golden Gate along with Solution

1. Processes Startup Issues
❗Problem:
GoldenGate processes (like Extract or Replicat) fail to start due to missing parameters, port issues, or environment settings.

🧪 Example:
GGSCI> start extract EXT1
ERROR: Missing required parameter: USERID
✅ Solution:
Check the parameter file:
GGSCI> edit params EXT1
Ensure you have the required lines like:
USERID ggs_admin, PASSWORD ggs_pwd
TABLE schema.table;
USERID ggs_admin, PASSWORD ggs_pwd
TABLE schema.table;
Also, make sure the GoldenGate home and environment variables (like LD_LIBRARY_PATH) are set correctly:
echo $ORACLE_HOME
echo $LD_LIBRARY_PATH
Check if the Manager process is running:
GGSCI> info mgr
GGSCI> start mgr

🔹 2. Data Not Getting Captured
❗Problem:
Extract is running but no data is captured in the trail files.

🧪 Example:
GGSCI> stats extract EXT1, totalsonly
Shows 0 inserts/updates/deletes even though source table has changes.

✅ Solution:
Check if the table is included in the Extract parameter file:
TABLE hr.employees;

Supplemental logging may not be enabled on the source:
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Check if the database is in ARCHIVELOG mode:
ARCHIVE LOG LIST;
Ensure DDL include/exclude rules aren't filtering it out.

🔹 3. Data Not Getting Applied onto Target DB
❗Problem:
Replicat is running, but target database is not getting updated.

🧪 Example:
GGSCI> stats replicat REP1, totalsonly
Shows 0 operations, even though trail files exist.

✅ Solution:
Check if trail file path is correct in Replicat parameters:
EXTTRAIL ./dirdat/aa
Validate table mapping:
MAP hr.employees, TARGET hr.employees;
Check for errors in the report file:
GGSCI> view report REP1
Use logdump to inspect the trail file and confirm it contains valid data.

🔹 4. Data Integrity Issues
❗Problem:
Rows are missing, duplicated, or mismatched between source and target.

🧪 Example:
Target has duplicate rows or missing foreign key entries.

✅ Solution:
Use HANDLECOLLISIONS temporarily for mismatch scenarios during initial sync:
HANDLECOLLISIONS
Use Oracle GoldenGate Veridata or run SQL MINUS to compare source/target data:
SELECT * FROM source_table
MINUS
SELECT * FROM target_table;
If using bidirectional replication, enable REPERROR (default, discard) to avoid looping conflicts and use TRIGGERSUPPRESS.

Use BATCHSQL in Replicat for better performance and consistency on large updates.