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.
