Oracle GoldenGate Extract and Replicat Parameter Files

 The provided configuration is a typical Oracle GoldenGate unidirectional replication setup where:

• Extract Process captures changes from the source database.
• Replicat Process applies those changes to the target database.
• DDL (CREATE, ALTER, DROP, etc.) and DML (INSERT, UPDATE, DELETE) operations are replicated.

Source GoldenGate Extract Parameter file

============================================

EXTRACT EXA_ORCL

USERIDALIAS ORCL_S DOMAIN OracleGoldenGate

EXTTRAIL AA

--- End of auto generated Parameter File ---

DDL INCLUDE MAPPED  --- Mandatory

DDLOPTIONS REPORT   --- Mandatory

TABLE AMIT.*;               

TABLE SIVA.*;

TABLE PAYAL.*;

TABLE VAISH.*;

Target GoldenGate REplicat Parameter file:-

============================================

REPLICAT REP_ORCL

USERIDALIAS ORCL_T DOMAIN OracleGoldenGate

--- End of auto generated Parameter File ---

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

DDLERROR DEFAULT IGNORE RETRYOP  --- This is optional, if you face any issue

OVERRIDEDUPS                     --- This is optional, if you face any issue

INSERTMISSINGUPDATES

MAP AMIT.*,TARGET AMIT.*;

MAP SIVA.*,TARGET SIVA.*;

MAP PAYAL.*,TARGET PAYAL.*;

MAP VAISH.*,TARGET VAISH.*;

Key Points:-

1. Extract captures changes from Oracle redo logs.
2. EXTTRAIL stores captured transactions in trail files.
3. TABLE defines which tables/schemas are captured.
4. Replicat applies changes on target.
5. MAP defines source-to-target object mapping.
6. DDL INCLUDE MAPPED replicates DDL for mapped objects.
7. DDLOPTIONS REPORT logs DDL actions in report files.
8. OVERRIDEDUPS handles duplicate record situations.
9. INSERTMISSINGUPDATES converts missing-row updates into inserts.
10. USERIDALIAS provides secure credential management instead of hardcoded passwords.

This configuration implements a schema-level Oracle-to-Oracle GoldenGate replication for the schemas AMIT, SIVA, PAYAL, and VAISH, including both DML and DDL replication.

Goldengate Configuration

 

SQL> COLUMN CURRENT_SCN FORMAT 99999999999999999999

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

 

SQL> SELECT scn_to_timestamp(12161785205359) FROM dual;

 

SQL> create tablespace gg_tbs datafile '+DATA/ORCL/gg_tbs01.dbf' size 2G;

SQL> create user ggadmin identified by ggadmin default tablespace gg_tbs;

sql> grant connect,resource to ggadmin;

SQL> alter user ggadmin quota unlimited on gg_tbs;

SQL> grant dba to ggadmin; 

SQL> exec dbms_goldengate_auth.grant_admin_privilege ('GGADMIN');

SQL> alter database add supplemental log data (all) columns;---------------> CDB level and also in pdb level

SQL> select supplemental_log_data_min from v$database;    

SQL> alter system set STREAMS_POOL_SIZE=1G scope=BOTH;---------------------> CDB level

SQL> alter system set enable_goldengate_replication=TRUE;------------------> CDB level

 

 

/home/oracle/@sequence.sql -- Run

GRANT EXECUTE on ggadmin.updateSequence TO ggadmin;-----------------------> in PDB level

 

ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;-----------no need to run this one 

ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;-------------> source is pdb then only we need to run this one 

ALTER SESSION SET CONTAINER=ORCL24;

 

Source: 

 

SQL> alter database add supplemental log data (all) columns;---------------> CDB level and also in pdb level

SQL> select supplemental_log_data_min from v$database;    

SQL> alter system set STREAMS_POOL_SIZE=1G scope=BOTH;-------------------> CDB level

SQL> alter system set enable_goldengate_replication=TRUE;----------------> CDB level

Repeat this process for Target DB

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.

Fast-Connect in OCI

 

Connecting with Multiple VNC in OCI

 

Scenario 1 -

This diagram illustrates a networking architecture in Oracle Cloud Infrastructure (OCI) called "Connecting with multiple VCNs (Scenario 1)". It shows how an on-premises data center securely links to multiple separate cloud networks using dedicated hardware connections.

---

1. Left Side: Your Physical Data Center (On-Premises)

• On-Premises: This represents your company's physical office, server room, or corporate data center.
• Customer Premises Equipment (CPE): This is the physical edge router or firewall hardware sitting inside your data center that initiates the outbound connection to the cloud.

---

2. Middle Section: The Dedicated Connection Highway

• FastConnect: This is Oracle's private, high-speed network connection service. Instead of sending data over the risky public internet, traffic travels across a dedicated, physical fiber-optic line managed by a third-party telecom provider.
• Private Virtual Circuit: This is a dedicated, isolated digital lane carved out inside the physical FastConnect line. It ensures your traffic stays completely separated from other companies' cloud traffic.
• Oracle Edge: The entry point/border routers at the edge of Oracle's cloud data center facility that receive your incoming traffic.

---

3. Right Side: The Isolated Cloud Networks (OCI Region)

Inside the Oracle Cloud Region, your cloud infrastructure is divided into three separate Virtual Cloud Networks (VCNs). Each VCN operates as its own isolated software-defined network, used for keeping different projects or environments apart:

• VCN-A (10.0.0.0/16): An isolated network room using a specific local IP range.
• VCN-B (192.168.0.0/16): A second isolated network room using a completely different IP range.
• VCN-C (172.16.0.0/16): A third separate network room.

---

4. The Core Concept: Multiple Dynamic Routing Gateways (DRGs)

The main point of "Scenario 1" shown in this architectural diagram is how traffic enters each individual VCN:

• Individual DRGs: Notice that VCN-A has DRG-1, VCN-B has DRG-2, and VCN-C has DRG-3.
• How it works: A Dynamic Routing Gateway (DRG) acts like a virtual entrance gate. In this specific older scenario design, your on-premises network has to build a separate virtual connection path directly to each individual gateway (DRG-1, DRG-2, and DRG-3) over the single FastConnect circuit to access the resources inside that specific VCN.

Scenario 2 -

This diagram illustrates an updated, more efficient networking architecture in Oracle Cloud Infrastructure (OCI) called "Scenario 2: Hub & Spoke Model – Transit Routing".

Instead of building a separate road to every single cloud network (like Scenario 1), this model uses a central intersection to manage all your traffic.

---

1. Left Side: The On-Premises Entry Point

• On-Premises: Your physical office or data center infrastructure.
• Single Connection Path: Your hardware connects to the cloud via a single FastConnect line or a secure VPN Connect internet tunnel.

---

2. The Core Network Pieces (Inside the Cloud)

The Hub (Hub-VCN-A)

• The Intersection: This central network acts like a grand central station or transit hub.
• Single DRG: Unlike Scenario 1, there is only one Dynamic Routing Gateway (DRG) here. Your office connects directly to this single gateway to enter the entire cloud region.

The Spokes (Spoke-VCN-B & Spoke-VCN-C)

• Isolated Rooms: These are separate virtual cloud networks used for hosting different departments, environments (like testing or production), or applications.

Local Peering

• Internal Bridges: The Hub connects to the Spokes using internal cloud bridges called Local Peering Gateways (LPGs). Traffic moves across these bridges instantly without ever leaving Oracle's internal backend network.

---

3. How Traffic Flows (Transit Routing)

If a computer in your office needs to talk to a server inside Spoke-VCN-B:

1. Traffic leaves On-Premises.
2. It travels over the FastConnect/VPN link.
3. It passes through the single DRG into Hub-VCN-A.
4. The Hub passes (transits) the traffic across the Local Peering bridge into Spoke-VCN-B.

---

Key Advantages Over Scenario 1

• Lower Costs: You only pay for and manage a single gateway (DRG) and physical connection layout.
• Easier Management: Adding a new network (Spoke-VCN-D) is simple. You just link it to the central Hub instead of rewiring your entire corporate office connection.
• Central Security: You can place a single fire-wall appliance inside the Hub VCN to inspect all incoming traffic before it goes out to any of the spokes.

This diagram illustrates a high-availability, fault-tolerant networking design called "VPN Connect with Redundant Customer Edge Devices" in Oracle Cloud Infrastructure (OCI).

It details how an on-premises data center sets up backup roads to the cloud so that if one physical hardware device or internet path breaks, the network instantly switches to the secondary path without dropping your connection.

---

1. Left Side: On-Premises (Your Data Center)

• Customer Premises Network (10.0.0.0/16): This is the internal local network range of your physical corporate office or data center.
• Redundant CPEs (CPE 1 & CPE 2): Instead of relying on a single router, you deploy two separate physical firewalls/routers called Customer Premises Equipment.
  • CPE 1 is your primary active path.
  • CPE 2 acts as your backup hardware.

---

2. Middle Section: The Public Internet Tunnels

Because this setup uses a Virtual Private Network (VPN) over the public internet, data must be encrypted using secure IPSec tunnels:

• Tunnel 1 (Solid Green Line): The primary path connecting CPE 1 to Oracle's cloud. Notice the route table labeled (preferred) next to CPE 1, indicating it is configured with higher routing priority.
• Backup Tunnel 2 (Dashed Green Line): The standby path connecting CPE 2 to the cloud. It is labeled (less preferred) and only takes over active traffic if Tunnel 1 fails.
• VPN Headends (1 & 2): These are the entry doors on Oracle's side that securely terminate the encrypted internet tunnels coming from your office.

---

3. Right Side: The OCI Cloud Region

Once your traffic successfully crosses the internet tunnels, it hits the OCI infrastructure components:

• Dynamic Routing Gateway (DRG): A single central virtual router gate that terminates both incoming VPN headend pathways and securely hands the traffic over to your internal cloud assets.
• VCN (10.40.0.0/16): Your Virtual Cloud Network room, divided into two distinct sections:
  • Public Subnet A (10.40.1.0/24): A network layer configured to allow resources to connect to both the public internet and your private on-premises office.
  • Private Subnet B (10.40.2.0/24): A strictly isolated network zone hosting sensitive assets (like your PostgreSQL database system). It can only communicate with your office through the DRG and never directly touches the public internet.

---

4. The Gray Tables: How Routing Works

The gray boxes show the underlying Route Tables that dictate exactly how data travels:

• On-Premises Route Tables: They tell your office computers that if they want to reach the cloud subnets (10.40.0.0/17), they must send the packets out through the Tunnel Interface.
• OCI Subnet Route Tables: They tell your cloud databases that if they need to send replies back to your office (10.0.0.0/16), they must forward those packets directly to the DRG, which handles routing them back through the active VPN line.

This diagram details the OCI Transit Routing architecture using a Hub-and-Spoke model with Local Peering Gateways (LPGs). It shows an older/classical OCI architectural pattern where multiple separate Spoke VCNs transit through a single central Hub VCN to reach an on-premises data center.

---

Understanding the Visual Components

• On-premises Network: Your physical office or data center network infrastructure.
• HUB VCN: The middleman network that directly holds the connection to your office via a Dynamic Routing Gateway (DRG).
• SPOKE VCN-1, 2, 3: Isolated cloud networks hosting workloads that cannot talk to your office directly.
• Local Peering (LPGs): Internal cloud routing bridges linking each Spoke back to the Hub VCN. Traffic moving across these bridges stays within the Oracle backbone.

---

Key Interview Questions & Technical Answers on This Diagram

Q1: In the architecture shown, what specific components must be configured on the HUB VCN to allow traffic to move from Spoke VCN-1 to On-Premises?

Answer:
The Hub VCN requires two primary components:

1. Local Peering Gateways (LPGs): To terminate the internal peering connections coming from each Spoke.
2. Transit Routing Tables: A special route table must be attached to the Hub's LPGs. This route table must contain rules that explicitly forward incoming traffic destined for the on-premises CIDR block toward the Hub's DRG. Without this transit route table, the Hub will drop the packets instead of passing them along.

Q2: Looking at this diagram, can SPOKE VCN-1 talk directly to SPOKE VCN-2 across the Hub VCN?

Answer:
No, not out-of-the-box. This specific diagram illustrates Transit Routing between On-Premises and Spokes, not Spoke-to-Spoke transit. By default, VCN peering in OCI is non-transitive. If Spoke 1 needs to talk to Spoke 2, you would either need a direct LPG link between them, or a modern DRG attachment architecture that explicitly permits cross-spoke transit.

Q3: The text on the slide states "The VCNs must be in the same region". Why is this limitation present for this specific diagram?

Answer:
The limitation exists because this diagram uses Local Peering (LPGs) to link the Spokes to the Hub. LPGs are strictly software-defined network bridges that cannot cross regional boundaries. If spokes were located in a different geographical cloud region, you would have to use Remote Peering Gateways (RPGs) or upgrade to a modern multi-attached DRG model.

Q4: How does a server inside SPOKE VCN-3 know how to find the On-Premises network?

Answer:
Through its subnet route tables. The route table inside SPOKE VCN-3 must have a rule stating: "If the destination IP matches the On-Premises corporate network range, forward the packet to the local LPG pointing at the HUB VCN." Once the packet lands on the Hub VCN's LPG, the Hub's transit routing system takes over to push it to the DRG.

Q5: OCI recently upgraded how DRGs function. How does modern OCI networking simplify the architecture shown in this diagram?

Answer:
In the modern OCI network design, you no longer need to use LPGs or complex transit route tables to build a Hub-and-Spoke model. An upgraded DRG allows you to directly attach multiple VCNs (up to 300 per DRG) to a single gateway. The DRG handles all hub, spoke, and transit routing internally using built-in route tables and distributions, completely bypassing the need for an intermediate Hub VCN architecture for simple transit connectivity.

Note:- Hub VCP will be replaced with DRGV2

What is the new feautre in DRG? and how it works as compare to trandational one ? DRGV2

The upgraded Dynamic Routing Gateway (DRG v2) turns the DRG from a basic connection gate into a powerful, intelligent Virtual Software Router.

Instead of just connecting your office to a single network room, it can manage and route traffic directly between hundreds of independent cloud networks on its own.

---

Key New Features in DRG v2

• One-to-Many VCN Attachments: A single DRG v2 can now have up to 300 Virtual Cloud Networks (VCNs) plugged directly into it at the same time.
• Multiple Isolated Route Tables: You can build multiple distinct routing tables directly inside the DRG itself (similar to VRF/Virtual Routing and Forwarding on physical corporate routers).
• Import/Export Route Distributions: You can create rules that state: "Automatically broadcast routes learned from my office VPN only to Spoke VCN-A, but keep Spoke VCN-B completely hidden."
• Cross-Region Transit Traffic: It allows an on-premises network to enter a DRG in one country (e.g., India) and seamlessly travel through Oracle’s private backbone network to a VCN in another country (e.g., the US) via an attached Remote Peering Connection (RPC).

---

Core Structural Differences: Traditional vs. DRG v2

The table below breaks down how the new architectural features compare directly with traditional legacy capabilities:

Feature Capability	Traditional DRG (Legacy)	Modern DRG v2 (Upgraded)
VCN Connections Allowed	Strictly 1 VCN per gateway.	Upto 300 VCNs attached to one gateway.
Hub-and-Spoke Setup	Required tedious Local Peering Gateways (LPGs) and manual transit route tables.	Built natively. The DRG itself becomes the Hub intersection.
Routing Intelligence	Basic logic. It can only route traffic straight forward (In \(\rightarrow \) Out).	Full Layer 3 Router engine with multiple independent VRF-like route tables.
Cross-VCN Peering	Required building individual software bridges (LPGs) between pairs of networks.	Every attached VCN can talk to any other VCN instantly through the DRG.
Inter-Region Transit	Traffic from on-premises could not transit through to a remote cloud region.	Supported natively. On-premise traffic can cross region links automatically.

---