Extract and Replicat - GG Questions

 

1. What is the fundamental difference between Extract and Replicat?

Better Interview Answer

Extract	Replicat
Runs on source database	Runs on target database
Captures committed transactions from redo/archive logs	Applies captured transactions on target
Writes data into local trail files	Reads trail files and executes DML/DDL
Responsible for data capture	Responsible for data delivery

Interview Tip:
Extract never queries application tables directly; it reads transaction logs, which minimizes source database overhead.

---

2. What replaced Data Pump in GoldenGate Microservices?

Better Interview Answer

In GoldenGate Classic Architecture:

Extract
   ↓
Data Pump
   ↓
Remote Trail
   ↓
Replicat

In Microservices Architecture:

Extract
   ↓
Distribution Service
   ↓
Receiver Service
   ↓
Replicat

The Distribution Service performs the same function as Data Pump by moving trail files from source to target using secure HTTPS communication.

---

3. What is a Checkpoint Table?

A Checkpoint Table stores Replicat progress information inside the target database.

It contains:

• Current trail file sequence
• RBA (Relative Byte Address)
• Transaction status
• Commit position

Benefits:

• Fast recovery after restart
• Prevents duplicate transaction processing
• Prevents data loss

Common Interview Question

Is Checkpoint Table mandatory?

Answer:

• Classic Replicat → Recommended
• Coordinated Replicat → Mandatory
• Parallel Replicat → Mandatory

---

4. Classic Extract vs Integrated Extract

Interview Table

Classic Extract	Integrated Extract
Reads redo logs directly	Uses LogMiner server
Runs outside DB	Integrated with Oracle DB
Limited support for new features	Supports TDE, RAC, CDB/PDB
Lower scalability	Higher scalability
Legacy deployments	Recommended for Oracle 12c+

Diagram

Classic

Redo Logs
    ↓
 Extract

Integrated

Redo Logs
    ↓
 LogMiner Server
    ↓
 Integrated Extract

Interview Favorite Question

How do you check Integrated Extract?

SELECT capture_name,status
FROM dba_capture;

---

5. Types of Replicat

Complete Answer

1. Classic Replicat

Trail
  ↓
Single Thread
  ↓
Target DB

• Sequential apply
• Low throughput

---

2. Integrated Replicat

Trail
  ↓
Inbound Server
  ↓
Parallel Apply

Uses Oracle Database Inbound Server.

Advantages:

• Dependency tracking
• Parallel processing
• Better performance

---

3. Coordinated Replicat

Trail
  ↓
Coordinator
  ↓
Multiple Threads

• Multiple worker threads
• User-controlled parallelism

---

4. Parallel Replicat

Newest and fastest.

Trail
   ↓
Mapper
   ↓
Master
   ↓
Apply Servers

Best for:

• High-volume OLTP systems
• Zero downtime migrations

---

6. Replicat hits ORA-00001

Better Senior-Level Answer

By default:

Replicat ABENDS

Investigation:

VIEW REPORT rep1

or

INFO ALL

Check:

ggserr.log
discard file
report file

Possible causes:

• Duplicate records already exist
• Missing transactions
• Out-of-sync target

Temporary workaround:

REPERROR (1, DISCARD)

or

HANDLECOLLISIONS

(only during migration/cutover phases)

Interview Trick

Q: Is HANDLECOLLISIONS recommended permanently?

A: No. Only during initial load synchronization.

---

7. How do you troubleshoot Replicat lag?

Step-by-Step Senior DBA Answer

Check Lag

LAG REPLICAT REP1

or

SEND REPLICAT REP1 STATUS

Check Long Running Transactions

SEND EXTRACT EXT1 SHOWTRANS

---

Check Database Wait Events

SELECT event,total_waits
FROM v$system_event;

---

Common Causes

Missing Indexes

UPDATE target_table
WHERE primary_key = :1

Without PK:

FULL TABLE SCAN

Every update becomes slow.

---

Large Transactions

10 million rows in one commit

Replicat waits until complete transaction arrives.

---

Insufficient Parallelism

Use:

PARALLELISM 8

or

APPLY_PARALLELISM 8

---

8. Explain Zero Downtime Migration using GoldenGate

Step 1

Start Extract

ADD EXTRACT EXT1, INTEGRATED TRANLOG
START EXTRACT EXT1

---

Step 2

Capture SCN

SELECT current_scn FROM v$database;

Example:

SCN = 123456789

---

Step 3

Export Source

expdp system/password flashback_scn=123456789

---

Step 4

Import into Target

impdp system/password

---

Step 5

Start Replicat

START REPLICAT REP1, AFTERCSN 123456789

(AFTERCSN is the commonly used parameter.)

---

Step 6

Monitor Lag

LAG EXTRACT EXT1
LAG REPLICAT REP1

---

Step 7

Cutover

Stop Application
Verify Lag = 0
Point Application to Target
Start Application

---

Q9. What are Trail Files?

Trail files are GoldenGate's proprietary files that store captured transactional data between Extract and Replicat.

Example:

Source DB
   ↓
Extract
   ↓
Trail File (aa000001)
   ↓
Replicat
   ↓
Target DB

---

Q10. Difference between CSN and SCN?

• SCN (System Change Number) → Oracle database commit number.
• CSN (Commit Sequence Number) → Generic GoldenGate commit identifier.

For Oracle databases, CSN is typically derived from SCN.

---

Q11. What is Supplemental Logging and why is it required?

GoldenGate needs enough column information in redo logs to uniquely identify rows.

Enable:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Without supplemental logging:

OGG-xxxxx
Key column missing
Replicat cannot apply updates

This is one of the most frequently asked Oracle GoldenGate interview questions for Oracle DBA, OCI Migration, and Zero-Downtime Migration roles.

Q12: If Extract is stopped for 2 hours, will transactions be lost?

Answer: No.

Oracle Redo Logs and Archive Logs retain the changes. When Extract starts again, it resumes from its last checkpoint and generates the missing trail records from the logs, provided the required archived logs are still available. This is why archive log retention is critical for GoldenGate environments.