EasyReliableDBA: Frequently Used GoldenGate Commands

Question : What is structures the evolution of Oracle GoldenGate (OGG) from 11g to 23ai (the latest long-term support release)

1. Oracle GoldenGate 11g
Feature: Classic Extract & Classic Replicat. Reads Redo/Archive logs via the OS/file system and applies changes sequentially via individual trails.
Feature: Heterogeneous Support. Replicates data across different operating systems, platforms, and non-Oracle databases.
Advantage: Low database server footprint, as the capture and apply mechanisms run entirely separate from the database kernel processes.
2. Oracle GoldenGate 12c
Feature: Integrated Extract & Integrated Replicat. The processes are moved into the database kernel, leveraging database APIs (e.g., Streams).
Feature: Multitenant (CDB/PDB) Support and Coordinated Replicat. Allows multi-threaded processing for a single Replicat group.
Advantage: Eliminates trigger-based DDL replication. The Integrated Replicat automatically manages apply parallelism for dependent transactions, significantly increasing throughput and reducing lag.
3. Oracle GoldenGate 19c
Feature: Microservices Architecture. Shifts management away from the command line to a web-based REST API interface, separating the Admin, Distribution, and Receiver servers.
Feature: Autonomous Database and Cloud Support. Unified builds that integrate deeply with Oracle Cloud Infrastructure (OCI) GoldenGate.
Advantage: Centralized web management, simplified monitoring, and zero-downtime upgrades to Oracle Database 19c. It handles conflicts and large data volumes at scale with minimal operational overhead.
Q: What is the main difference between Classic and Integrated Replicat?
A: Classic Replicat applies data sequentially, which can cause bottlenecks. Integrated Replicat utilizes parallel apply streams managed by the database server. It is database-aware, meaning it automatically resolves dependency issues among transactions, leading to faster data delivery.
Q: Why would you choose Integrated Extract over Classic Extract?
A: Integrated Extract interacts directly with the database log-mining server. This gives it native support for Oracle features like Transparent Data Encryption (TDE), compression, and multi-tenant architectures.
Q: How do you handle a lagging Replicat process in GoldenGate 19c?
A: To resolve lag, you first identify the root cause using the `STATS REPLICAT` command to pinpoint slow transactions. Solutions include splitting heavy, sequential batches using the Coordinated Replicat feature, increasing memory allocation (`MAXTRANSOPS`), or tuning the target database (e.g., adding missing indexes on the target)

Can you explain how Oracle GoldenGate has evolved from version 11g, through 12c and 19c, up to the latest 23ai version? Structure your answer around Features and Advantages with practical examples."

Direct Answer First

The evolution of Oracle GoldenGate transitions the product from an external command-line utility reliant on database triggers (11g) to a deeply kernel-integrated engine (12c/19c), culminating in a fully decentralized, API-first Microservices Architecture (23ai). This shift maximizes replication throughput, completely removes Classic architecture, and natively supports cloud-edge environments.

Architectural Evolution & Comparison

Version	Core Feature	Core Advantage
OGG 11g	Classic Architecture, Trigger-based DDL, Classic Replicat.	Simple setup for legacy databases; light framework.
OGG 12c	Integrated Replicat, Coordinated Replicat, Multitenant (CDB/PDB) support.	Bypasses SQL layer to apply changes straight to SGA; removes trigger management overhead.
OGG 19c	Automatic Conflict Detection and Resolution (Auto CDR), Hub-and-Spoke support.	Safe active-active replication without custom PL/SQL; offloads production mining.
OGG 23ai (Latest)	Microservices-Only, Unified Builds, Auto-Capture for Tables.	Zero database-version installation dependencies; automated REST-API-driven scaling.

Deep-Dive: Feature vs. Advantage by Version

1. Oracle GoldenGate 11g

Feature: Classic Capture & Replicat. Processes read redo logs file-system-style and apply data using standard SQL execution.
Feature: Trigger-based DDL. Requires installing dedicated schema objects and database triggers to catch structural DDL changes.
Advantage: Minimal footprint inside the database engine, but heavily bottlenecked by single-threaded performance and massive performance impacts during massive batch updates.

2. Oracle GoldenGate 12c

Feature: Integrated Replicat Mode. Leverages internal Oracle Database Streams inbound servers.
Advantage: Replicat streams data straight to the database processes in the SGA. It coordinates dependencies automatically, boosting transactional ingestion rates up to 5x.
Feature: Multitenant Container Support. Captures data natively from multiple pluggable databases (PDBs) out of a single container trail.
Advantage: Consolidated infrastructure management for cloud-ready architectures.

3. Oracle GoldenGate 19c

Feature: Auto CDR (Conflict Detection and Resolution). Embedded resolution logic via database-level timestamp configurations.
Advantage: Simplifies multi-master (Active-Active) topologies. The system natively drops or keeps records based on a "latest timestamp wins" philosophy without relying on complex, error-prone custom exception coding.

4. Oracle GoldenGate 23ai (Latest Release)

Feature: Microservices Architecture Only. Classic GGSCI architecture is entirely desupported. It uses a decoupled layout containing Service Manager, Administration Service, Distribution Service, and Receiver Service.
Advantage: Built entirely on secure REST APIs, enabling web UI operations, cloud integration, and native containerization (Docker/Kubernetes).
Feature: Unified Installation Build. One installation binary handles various underlying database target and source versions.
Advantage: Eliminates the historical restriction of matching target library libnnnz variants to precise target database patch sets.

Interview Examples & Practical Test Cases

Test Case 1: Testing High-Throughput Performance (11g Classic vs. 12c/19c Integrated Replicat)

Objective: Validate performance gains of shifting from Classic to Integrated apply models.
Setup Scenario: A huge end-of-day batch processing job pushes 1,000,000 updates to a single core customer table.
The Execution (11g vs 12c/19c):
- 11g Classic configuration:
  text
  -- REPLICAT Parameter file (rep_cl) REPLICAT rep_cl USERIDGSB APP_OGG, PASSWORD AADAA MAP HR.CUSTOMERS, TARGET HR.CUSTOMERS;
  12c/19c Integrated configuration:
- text
  -- REPLICAT Parameter file (rep_int) REPLICAT rep_int INTEGRATED USERIDGSB APP_OGG, PASSWORD AADAA MAP HR.CUSTOMERS, TARGET HR.CUSTOMERS;
  Expected Test Result: The 11g Classic Replicat shows high latency ("Lag") as single-threaded processes serialize transactions. The 12c/19c Integrated Replicat coordinates thread dependency inside database memory, dividing work seamlessly across parallel background servers without conflict.

Test Case 2: Testing Active-Active Loop Detection (19c Auto CDR)

Objective: Confirm a multi-master deployment handles bidirectional update loops natively without manual filtering.
Setup Scenario: Server A updates Row 1. Server B updates Row 1 concurrently.
The Execution:
sql
-- Enabled at target database level for Auto CDR in 19c+ EXEC DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(schema_name => 'HR', table_name => 'CUSTOMERS');
In the parameter files on both nodes, simply use:
text
MAP HR.CUSTOMERS, TARGET HR.CUSTOMERS, COMPARECOLS (REQUIRED (ID), ON UPDATE);
Expected Test Result: In 11g, this configuration results in data loop feedback loops or unique constraint errors. In 19c/23ai, the GoldenGate engine automatically reads the timestamp token embedded inside the database row trailer, applying the truest modification and quietly discarding the echo transaction.

Test Case 3: Zero-Dependency Modern Deployment (23ai Microservices)

Objective: Deploy and verify an extraction mechanism completely headless via automation without launching command-line consoles (GGSCI).
Setup Scenario: Continuous Integration (CI/CD) orchestration engine spins up an autonomous target engine.
The Execution: Instead of relying on manual text parameters, execute an authorized HTTP REST payload directly against the deployment service manager:
bash
curl -u oggadmin:MySecurePass123! \ -H "Content-Type: application/json" \ -X POST http://ogg-hub-node:9001/services/v2/extracts/ext_sales \ -d '{"config":["EXTRACT ext_sales","USERIDALIAS target_db_auth","EXTTRAIL ./dirdat/aa","TABLE sales.orders;"]}'
Expected Test Result: The service instantiates, hooks directly into the database engine, assigns resources via the web wrapper, and starts streaming trail bytes securely over HTTPS via the Distribution Service. No local OS profile configuration or underlying database-specific client setup is needed on the platform host.

Question : Golden Gate architecture and how workflow work

Oracle GoldenGate is a robust Change Data Capture (CDC) tool that reads database transaction logs (redo/archive logs) and replicates committed transactions across heterogeneous systems in near real-time. It is widely used for zero-downtime migrations, active-active high availability, and real-time operational data lakes.

Key Components of GoldenGate

Extract: Captures committed data changes (inserts, updates, deletes) from the source database.
Trail Files: A staging area where captured data is written in a standard, canonical format.
Data Pump: An Extract process that reads the local trail files and sends them over the network to the target system.
Replicat: Applies the trail data into the target database.

Detailed Unidirectional Example

Below is an example of a simple unidirectional (Source to Target) configuration using the classic command-line interface (GGSCI) for a schema named SALES_DB.

1. Source Database Setup

First, ensure database-level supplemental logging is enabled to capture row modifications:

sql

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE SALES_DB.ORDERS ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

2. Source Extract Parameter File (dirprm/ext1.prm)

This tells GoldenGate to capture changes from the redo logs and write to trail files:

text

EXTRACT ext1
USERIDALIAS oggadmin
EXTTRAIL ./dirdat/lt
TABLE SALES_DB.ORDERS;

3. Source Data Pump Parameter File (dirprm/pmp1.prm)

This reads the local trail (lt) and sends it across the network (target_server) to the remote trail (rt):

text

EXTRACT pmp1
USERIDALIAS oggadmin
RMTHOST target_server, MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE SALES_DB.ORDERS;

4. Target Replicat Parameter File (dirprm/rep1.prm)

This reads the remote trail files and applies the transactions directly into the target database:

text

REPLICAT rep1
USERIDALIAS oggadmin
ASSUMETARGETDEFS
MAP SALES_DB.ORDERS, TARGET SALES_DB.ORDERS;

Standard GoldenGate Test Cases

When deploying or upgrading GoldenGate—especially in high-availability or active-active multi-master setups—you must execute a rigorous suite of test cases to guarantee data integrity.

1. DML Latency & Consistency Test

Objective: Ensure committed DML changes replicate accurately and in order, with minimal latency.
Test Case:
1. Insert 1,000 rows into SALES_DB.ORDERS on the Source.
2. Check GoldenGate lag/delay metrics using INFO EXTRACT ext1, DETAIL in GGSCI.
3. Verify all 1,000 rows have populated the Target database via SQL.
4. Execute an UPDATE on a subset of these rows, and verify target reflection.

2. DDL Replication Test

Objective: Ensure structural changes propagate without breaking the Extract/Replicat mapping.
Test Case:
1. Add a new column on the source table: ALTER TABLE SALES_DB.ORDERS ADD shipping_date DATE;
2. Insert a record on the Source mapping to the new column.
3. Verify that the schema update was applied on the Target and the new column correctly received the data. []

3. Network Outage & Recovery Test

Objective: Test the resilience of trail queues and ensure zero data loss (ZDL) during disconnection.
Test Case:
1. Stop the Target Manager process or block the network port.
2. Perform 500 DML operations on the Source. Notice the Data Pump process queueing up trail files.
3. Restart the Target Manager and Replicat processes.
4. Confirm that all 500 operations process and clear from the queue automatically. []

4. Conflict Detection and Resolution (CDR) Test

Objective: Validate active-active bidirectional environments to ensure database convergence if the same row is updated simultaneously in two different regions.
Test Case:
1. Configure an active-active RESOLVECONFLICT rule using TIMESTAMP columns or a conflict exception table.
2. Update Row A on Database 1 at 10:00:01 AM.
3. Update the exact same Row A on Database 2 at 10:00:05 AM.
4. Confirm that Database 2's later update overwrites Database 1's update, and ensure no database loops or infinite replications occur.

Verification and Management

To ensure data trust and system continuity across complex architectures, it is highly recommended to use Oracle GoldenGate Veridata to perform automated, cross-platform data integrity validations. For distributed microservice environments and next-gen data streaming, explore the updated Oracle GoldenGate Features pag

Question : if table structure are different in source and target then How to map it

Oracle GoldenGate handles differences in column structures between source and target tables using Mapping Functions and Data Definitions (Defsgen).

Here is a detailed guide, examples, and test cases for managing mismatched columns.

Common Mapping Scenarios

1. Different Column Names

Source and target columns have identical data types but different names.

Solution: Use the COLMAP clause in the MAP statement.
Example: Source has EMP_ID, target has EMPLOYEE_ID.

2. Missing Columns in Target

Source has more columns than the target table.

Solution: GoldenGate automatically drops unmapped source columns.
Rule: Target columns must allow NULLs or have default values.

3. Extra Columns in Target

Target has columns that do not exist in the source table.

Solution: Map constants, use SQL tokens, or let the database apply default values.
Example: Target has an INSERT_TIMESTAMP column populated during replication.

4. Data Type Mismatches

Source and target columns have different data types (e.g., NUMBER to VARCHAR2).

Solution: Use GoldenGate conversion functions like @CONCAT, @STRNUM, or @DATE.

Detailed Example Configuration

This example handles different names, an extra target column, and a data type conversion.

Source Table

sql

CREATE TABLE HR.SRC_EMPLOYEES (
    ID NUMBER PRIMARY KEY,
    FIRST_NAME VARCHAR2(50),
    LAST_NAME VARCHAR2(50),
    SALARY NUMBER
);

Target Table

sql

CREATE TABLE HR.TGT_EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,        -- Different name
    FULL_NAME VARCHAR2(101),          -- Combined column
    SALARY VARCHAR2(20),              -- Different data type
    MODIFIED_BY VARCHAR2(30)          -- Extra column
);

Replicat Parameter File (r_emp.prm)

text

REPLICAT r_emp
USERIDGSSTYPE OGGADMIN, PASSWORD Oracle_123
ASSUMETARGETDEFS
MAP HR.SRC_EMPLOYEES, TARGET HR.TGT_EMPLOYEES,
COLMAP (
    EMP_ID = ID,
    FULL_NAME = @CONCAT(FIRST_NAME, ' ', LAST_NAME),
    SALARY = @STRNUM(SALARY, "FIXED", 2),
    MODIFIED_BY = "GG_REPLICAT"
);

Test Cases and Validation

Execute these test cases to verify the configuration.

Test Case 1: Standard Insert (Name Mapping & Concatenation)

Action (Source):

sql

INSERT INTO HR.SRC_EMPLOYEES (ID, FIRST_NAME, LAST_NAME, SALARY) 
VALUES (101, 'John', 'Doe', 5000);
COMMIT;

Expected Result (Target):

sql

SELECT * FROM HR.TGT_EMPLOYEES WHERE EMP_ID = 101;
-- EMP_ID: 101
-- FULL_NAME: John Doe
-- SALARY: 5000.00
-- MODIFIED_BY: GG_REPLICAT

Test Case 2: Update Operations

Action (Source):

sql

UPDATE HR.SRC_EMPLOYEES SET LAST_NAME = 'Smith', SALARY = 5500 WHERE ID = 101;
COMMIT;

Expected Result (Target):

sql

SELECT FULL_NAME, SALARY FROM HR.TGT_EMPLOYEES WHERE EMP_ID = 101;
-- FULL_NAME: John Smith
-- SALARY: 5500.00

Test Case 3: Handling Null Values

Action (Source):

sql

INSERT INTO HR.SRC_EMPLOYEES (ID, FIRST_NAME, LAST_NAME, SALARY) 
VALUES (102, 'Alice', NULL, NULL);
COMMIT;

Expected Result (Target):

sql

SELECT FULL_NAME, SALARY FROM HR.TGT_EMPLOYEES WHERE EMP_ID = 102;
-- FULL_NAME: NULL (or 'Alice ' depending on database null handling)
-- SALARY: NULL

Critical Best Practices

Use Defsgen: If table structures differ significantly, generate a source definitions file using the defsgen utility.
Enable Supplemental Logging: Ensure all columns used in @CONCAT or filtering are part of the source database's supplemental log group.
Handle Truncation: When mapping to a smaller target column, use @STRSUB to prevent Replicat abends caused by overflow.

Question : How to Resolve (Conflict Resolution Strategy)

Oracle GoldenGate (OGG) conflicts happen primarily in active-active (bi-directional) or multi-master replication environments where the same row is updated at different nodes nearly simultaneously.

Here is a breakdown of the three main types of conflicts:

Uniqueness Conflict: A source transaction inserts/updates a row, but the primary key or unique key already exists on the target.
Update Conflict: A source transaction updates a row, but the row on the target has been modified by another transaction, resulting in a mismatch of "before" values.
Delete Conflict: A source attempts to delete or update a row, but the row does not exist on the target because it was already deleted by another node.

Detailed Scenario: The "Last Writer Wins" Update Conflict

1. Setup

Imagine a bi-directional replication setup between Node A (Mumbai) and Node B (Bengaluru).
Table: EMPLOYEES
Columns: EMP_ID, SALARY, LAST_UPD_TIMESTAMP

2. Test Case Execution

Step 1: At 10:00 AM, user at Node A updates EMP_ID = 101 with SALARY = 5000 and LAST_UPD_TIMESTAMP = 10:00 AM.
Step 2: Before this change reaches Node B, a user directly at Node B updates EMP_ID = 101 with SALARY = 6000 and LAST_UPD_TIMESTAMP = 10:05 AM.
Step 3: The change from Node A arrives at Node B. Since Node B already has a newer timestamp (10:05 AM > 10:00 AM), OGG detects a collision.

3. How to Resolve (Conflict Resolution Strategy)

OGG uses CDR (Conflict Detection and Resolution). In this scenario, we use the timestamp to enforce a "Last Writer Wins" resolution.

How to Configure Conflict Detection in OGG

To detect and resolve conflicts automatically, OGG uses Replicat parameters and database-level tracking.

Method A: Legacy Replicat Parameter (ResolveConflict)

In your Replicat parameter file, you define how to behave when collisions happen:

text

MAP hr.employees, TARGET hr.employees, 
  KEYCOLS(emp_id),
  RESOLVECONFLICT (UPDATECONFLICT, RESOLVE(MAX(last_upd_timestamp)), COLS(salary, last_upd_timestamp));

KEYCOLS: Defines the key used to identify the row.
RESOLVECONFLICT: Instructs the Replicat to compare the last_upd_timestamp and resolve by picking the maximum (newest) timestamp value.

Method B: Oracle Database Automatic CDR (PL/SQL)

If you are replicating between Oracle databases, you can use built-in Automatic Conflict Detection and Resolution via DBMS_GOLDENGATE_ADM without changing application code.

sql

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
    schema_name      => 'hr',
    table_name       => 'employees');
END;
/

Oracle automatically generates and maintains invisible timestamp columns for inserts, updates, and delete tombstones to ensure changes are synced correctly based on row chronology.

1. What is a Checkpoint Table in Oracle GoldenGate, and which process uses it?

Answer:
A Checkpoint Table is a database table created inside the target database that stores the current read position of the Replicat process within the trail files.

It records metadata like the current Trail File Sequence Number, Relative Byte Address (RBA), and System Commitment Number (CSN/SCN).
It is strictly used by the Replicat (delivery) process. The Extract and Data Pump processes do not use checkpoint tables; they rely on on-disk checkpoint files (.cpt).

2. Why do we need a Checkpoint Table? Why isn't a disk checkpoint file enough for Replicat?

Answer:
The primary reason is Data Consistency and Atomicity.

When the Replicat applies a transaction to the target database, it writes the updated checkpoint data inside the same database transaction.
If a system crash or power failure occurs, the database will roll back or commit both the data changes and the checkpoint change together.
The Risk of File-Only Checkpoints: If you only use an on-disk checkpoint file, the Replicat might commit data to the database but crash before updating the file. Upon restart, it would re-read the trail file and attempt to apply duplicate data, causing primary key violations or data corruption.

3. How do you create and configure a Checkpoint Table?

Answer:
You configure it using a combination of the GLOBALS file and GGSCI commands:

Add the parameter to the GLOBALS file (located in the OGG home directory):
text
CHECKPOINTTABLE ogg_admin.ggs_checkpoint
Log into the target database via GGSCI / Admin Client:

sql

GGSCI> DBLOGIN USERIDALIAS target_db_auth

Execute the creation command:

sql
GGSCI> ADD CHECKPOINTTABLE
(Alternatively, you can bypass the GLOBALS parameter and specify it directly: ADD CHECKPOINTTABLE ogg_admin.ggs_checkpoint).

4. How do you map a Replicat process to use a specific Checkpoint Table?

Answer:
When adding the Replicat process via GGSCI, you pass the CHECKPOINTTABLE argument:

sql

GGSCI> ADD REPLICAT rep_name, EXTTRAIL ./dirdat/et, CHECKPOINTTABLE ogg_admin.ggs_checkpoint

If the checkpoint table is already defined in the GLOBALS file, you can simply run ADD REPLICAT rep_name, EXTTRAIL ./dirdat/et and OGG will pull the default name automatically.

5. Is a Checkpoint Table mandatory for all Replicat modes?

Answer:

Classic / Non-Integrated Replicat: Yes, it is highly critical to prevent duplicate data injection upon failures.
Integrated Replicat (IR): Historically, it was optional because IR leverages the database's internal streams infrastructure to manage its checkpoints. However, in modern GoldenGate versions (e.g., OGG 19c / 21c and later), Oracle has made the checkpoint table mandatory for Integrated Replicat as well to streamline tracking and unified recovery boundaries.

6. What columns or information exist inside a Checkpoint Table?

Answer:
While the structure shouldn't be altered manually, it contains critical fields such as:

GROUP_NAME: Name of the Replicat process (e.g., REP_FIN).
GROUP_KEY: A unique identifier key assigned to the group.
SEQNO: The current trail file sequence number being processed.
RBA: The Relative Byte Address (byte offset) inside that trail file.
LOG_CSN: The Commit Sequence Number (or SCN in Oracle) of the last applied transaction.
LAST_UPDATE_TS: Timestamp showing when the checkpoint row was last updated.

7. What happens if the Checkpoint Table becomes corrupted or is accidentally dropped?

Answer:
If the table is lost or corrupted, the Replicat process will abend (crash) because it cannot update its position upon transaction commits.

Resolution: You must recreate the checkpoint table. Because the historical row mapping is gone, you must find the last successfully applied RBA/Sequence from the old Replicat report files or target database logs, and manually alter the Replicat position before restarting it:
sql
GGSCI> ALTER REPLICAT rep_name, EXTSEQNO 123, EXTRBA 456789
8. Can multiple Replicat processes share the same Checkpoint Table?

Answer:
Yes. A single checkpoint table can support multiple Replicat processes simultaneously. The table uses the GROUP_NAME (the Replicat name) and GROUP_KEY as part of its primary key structure, ensuring that each process updates only its own unique row.

Q: What is the purpose of the PASSTHRU parameter in GoldenGate, and how does it improve performance?

A: The PASSTHRU parameter tells the Data Pump process to operate in "pass-through" mode. [

How it works: The Data Pump reads the local trail file and immediately writes it to the remote trail over the network without verifying column lengths, data types, or mappings. [
Performance Benefit: Because it skips parsing and looking up object definitions from the database (or a SOURCEDEFS file), it drastically reduces CPU usage and memory overhead, making the Data Pump run at maximum throughput.

Comparative Analysis: PASSTHRU vs. NOPASSTHRU

Interviewers will often ask you to contrast PASSTHRU with its default counterpart, NOPASSTHRU.

Feature	`PASSTHRU` Mode	`NOPASSTHRU` Mode (Default)
Data Manipulation	Strictly forbidden. No row filtering or column mapping.	Allowed. Fully supports data transformation and filtering.
Object Definitions Lookup	Bypassed completely. Does not check table structures.	Required. Must connect to database or use a definitions file.
Performance Overhead	Extremely Low. Fast binary streaming over TCP/IP.	Moderate. Higher CPU and memory usage to inspect data.
Structure Requirement	Identical structures on source and target schemas.	Structures can be different (handles structural mapping).

Common Follow-up Interview Questions

1. When can you NOT use the PASSTHRU parameter?

You cannot use PASSTHRU if the Data Pump needs to perform any form of data alteration. You must drop PASSTHRU and use NOPASSTHRU if you need to:

Filter rows using a WHERE clause.
Map columns or filter columns using functions (e.g., @COMPUTE, @STRCAT).
Replicate data between source and target tables that have different schemas or structures.
Use certain user exits (like CUSEREXIT or COBOLUSEREXIT).

2. Does GoldenGate 12c / 19c / 21c still strictly require PASSTHRU for Data Pumps?

In older versions (like OGG 11g), PASSTHRU was heavily mandated to save processing time. In newer versions of Oracle GoldenGate (12.2 and above), metadata is inherently encapsulated inside the trail files. While the PASSTHRU parameter is still fully operational, the modern distribution server and newer pumps automatically bypass external definition lookups unless a specific transformation rule requires it.

3. How do you configure PASSTHRU and NOPASSTHRU together in a single Parameter (.prm) file?

PASSTHRU is position-dependent in the configuration file. You can toggle it on for identical tables and off for tables that require mapping.

text

-- Extract Data Pump Parameter File Examples
EXTPUMP pm_src

USERIDGS credentialstore:src_auth
RMTHOST target_server, MGRPORT 7809
RMTTRAIL ./dirdat/rt

-- Step 1: Turn on PASSTHRU for identical tables
PASSTHRU
TABLE hr.employees;
TABLE hr.departments;

-- Step 2: Turn on NOPASSTHRU for tables needing filtering/mapping
NOPASSTHRU
TABLE hr.salary, WHERE (salary > 5000);

Q1: What is the purpose of the ADD HEARTBEATTABLE command in Oracle GoldenGate?

Answer: The ADD HEARTBEATTABLE command creates a built-in framework that helps DBAs monitor and diagnose end-to-end replication lag. It acts by automatically generating an artificial transaction at set intervals (every 60 seconds by default). As the transaction passes through the Extract, Data Pump, and Replicat, it tracks the exact timing, allowing you to identify exactly which part of your replication stream is experiencing a bottleneck.

Q2: What exact database objects are created when you run ADD HEARTBEATTABLE?

Answer: Running this command creates three main tables and a set of backend scheduler jobs and procedures in the designated GoldenGate schema:

Heartbeat Seed Table: Contains the source database’s unique timing reference.
Heartbeat Table (GG_HEARTBEAT): Receives the actual timestamp information for every component in the replication flow.
Heartbeat History Table (GG_HEARTBEAT_HISTORY): Stores historical lag data for performance analysis over time.
Jobs/Procedures: Includes background jobs for auto-updating the heartbeat and periodically purging old history records.

Q3: What prerequisites must be met before issuing ADD HEARTBEATTABLE?

Answer: To successfully set up and use heartbeat tables, you must ensure:

The GGSCHEMA and ENABLE_HEARTBEAT_TABLE parameters are defined in your ./GLOBALS file.
The GoldenGate database user must be logged in with the necessary privileges to create tables, views, and Oracle Scheduler jobs (usually the DBA role).
For Oracle databases, the JOB_QUEUE_PROCESSES parameter must be set to a value greater than 0 so the backend jobs can run.
The GoldenGate processes (Extract and Replicat) must be restarted after the configuration to begin capturing and applying the heartbeat transactions.

Q4: How do you check the end-to-end replication lag using the heartbeat table?

Answer: You can check the end-to-end replication lag using either the command line or database views:

Via GGSCI CLI: Log into your GoldenGate command prompt and run the LAG command (e.g., LAG REPLICAT <replicat_name>).
Via SQL Views: Query the GG_LAG_HISTORY view created by GoldenGate in your designated schema.

Q5: Can you customize the frequency of heartbeat generation and data retention?

Answer: Yes. You can override the defaults using command line modifiers when you create the table. For example:

FREQUENCY: Changes how often heartbeat records are generated (default is 60 seconds).
PURGEFREQUENCY: Dictates how often the purge routine runs.
RETENTIONTIME: Sets the number of days history records are kept before being purged (default is 30 days).

Question : How checkpoint work in source and target

GoldenGate maintains checkpoints to track exactly which transactions have been processed, creating seamless, fault-tolerant data replication across source and target systems. If a failure or reboot occurs, the process resumes exactly where it left off, eliminating data loss and preventing duplicates.

The checkpointing process is handled differently across the architecture depending on whether it's reading from the source or writing to the target.

1. Source Side (Extract and Data Pump)

What it tracks: Tracks the specific Read position in the database transaction logs (e.g., Redo or Archive logs) and the Write position in the intermediate trail files.
How it saves: Extract checkpoints are primarily saved as binary files on the disk in the $OGG_HOME/dirchk/ directory.
Behavior on restart: The Extract process reads its checkpoint file, finds the exact last recorded Commit Sequence Number (CSN) or System Change Number (SCN), and pulls only the transactions occurring after that point.

2. Target Side (Replicat)

What it tracks: Tracks the exact Read position of the trail files and the Write position as transactions are applied (committed) into the target database.
How it saves: Target Replicat checkpoints are highly recommended to be stored as database tables (named CHECKPOINTTABLE). A Replicat commits the checkpoint along with the data transaction, so if a network drop occurs mid-apply, the system knows exactly which rows were processed.
Behavior on restart: The Replicat reads the CHECKPOINTTABLE, compares its current progress to the trail file, establishes a "high water mark" and "low water mark" to isolate applied and pending transactions, and resumes applying data from that exact Commit Sequence Number.

Key Benefits

No Data Loss: Since the read/write positions are continually stored, no transactions are skipped, even if a server hard-reboots.
No Duplicate Data: Because the Replicat transactions and checkpoints are executed atomically, a failed transaction will not be partially re-applied or redundantly written.
Flexibility: You can configure a centralized checkpoint table or maintain separate checkpoint files for each process.

Question :Overview of OGG Architectural Evolution

Oracle GoldenGate (OGG) has shifted entirely from the legacy Classic Architecture (GGSCI) to the modern Microservices Architecture (MA). While OGG 19c established Microservices as a robust option, the latest release, Oracle GoldenGate 26ai, locks it in as the primary long-term framework. The 26ai release integrates AI-Powered Vector Embeddings, Automatic Schema Evolution, and native cloud integrations directly into the Microservices REST engines.

Part 1: Core Architecture Interview Questions

Q1: What are the 5 main service components of Oracle GoldenGate Microservices Architecture?

Service Manager: Acts as the watchdog. It manages the lifecycle of all other services within a single home deployment.
Administration Service: Handles the creation, configuration, and execution of Extract and Replicat processes via a REST API or Web UI.
Distribution Service: Replaces the classic Data Pump. It pushes local trail files over HTTPS/WebSockets to remote destinations.
Receiver Service: Runs on the target hub. It listens for incoming trails from the Distribution Service and writes them to local target trails.
Performance Metrics Service: Collects real-time statistics, throughput details, and process lags using operational APIs.

Q2: What are the primary differences between OGG 19c Microservices and the latest OGG 26ai?

Feature / Metric	OGG 19c Microservices	OGG 26ai (Latest Release)
Primary Architecture	Supports both Classic and Microservices.	Microservices only (Classic desupported).
AI Integration	None. Relies purely on traditional relational structures.	Native AI Vector Embeddings & Vector Column replication.
DDL Management	Basic DDL replication requiring periodic configuration adjustments.	Automatic Schema Evolution handling DDL adaptively.
Security Framework	Traditional SSL/TLS Certificates and local wallets.	Advanced Security Plug-in Framework and Air-Gap network data streams.
Network Protocol	TCP/IP or early HTTPS.	Optimized, low-latency HTTPS Proxies and WebSockets.

Q3: How do you configure an Extract process without using GGSCI utility commands?

You call the underlying REST API directly on the Administration Service.

Example HTTP Request payload:

http

POST /services/v2/extracts/EX_PROD HTTP/1.1
Host: ://example.com
Content-Type: application/json
Authorization: Basic dXNlcjpwYXNzd29yZA==

{
  "config": [
    "EXTRACT EX_PROD",
    "USERIDALIAS source_db_auth",
    "EXTTRAIL ./dirdat/et",
    "TABLE hr.employees;"
  ],
  "status": "running"
}

Part 2: Advanced Scenario Questions (With Code Examples)

Q4: How do you resolve a "Unique Constraint Violation" in an OGG 26ai Parallel Replicat without stopping the data stream?

Use the Conflict Detection and Resolution (CDR) parameters inside your Replicat parameter file.

Parameter Configuration (RE_PROD.prm):

text

REPLICAT RE_PROD
USERIDALIAS target_db_auth
MAP hr.employees, TARGET hr.employees,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, IGNORE));

Q5: A microservice distribution path fails with network drops. How does OGG 26ai guarantee zero data loss without a Classic Data Pump?

The Distribution Service tracks trail reading using internal disk-backed checkpoints. If a network connection breaks, the target Receiver Service keeps its last received position acknowledged. Once online, the Distribution Service restarts exactly at the last unacknowledged sequence number via HTTPS chunked transfer protocol.

Part 3: Test Cases and Real-World Validation

Use the following real-world validation scripts to verify and debug an environment.

Test Case 1: End-to-End Replication Smoke Test

Objective: Verify functional replication from the Source table to the Target table via microservices.

Step 1 (Source Database Execution):

sql

-- Insert a tracking record on Source
INSERT INTO hr.employees (employee_id, first_name, last_name, hire_date) 
VALUES (9999, 'Test', 'OGG26ai', SYSDATE);
COMMIT;

Step 2 (REST API Check to verify data capture):

bash

curl -u admin:password -X GET http://ogg-source:9001/services/v2/extracts/EX_PROD/info

Look for Trail Type: Exttrail and confirmation that the write checkpoint shifted.

Step 3 (Target Database Verification):
sql
SELECT employee_id, first_name, last_name FROM hr.employees WHERE employee_id = 9999;
Expected Result: The query returns exactly 9999 | Test | OGG26ai within milliseconds.

Test Case 2: Simulating and Verifying Process Lag

Objective: Test if an operational alert fires during target database resource locking.
Step 1 (Target Database Execution): Simulate a resource lock to stall the Replicat.
sql
-- Open an uncommitted transaction on target to block Replicat SELECT * FROM hr.employees WHERE employee_id = 9999 FOR UPDATE;
Step 2 (Source Database Execution): Generate a change.

sql

UPDATE hr.employees SET first_name = 'Stalled' WHERE employee_id = 9999;
COMMIT;

Step 3 (Query Performance Metrics Service API):

bash

curl -u admin:password -X GET http://ogg-target:9004/services/v2/metrics

Expected Metric Output Validation:

json

{
  "processName": "RE_PROD",
  "status": "running",
  "lagSeconds": 45,
  "workingStatus": "Blocked by target Database Lock"
}

Step 4 (Resolution): Execute COMMIT; or ROLLBACK; on the target database manual session. The Replicat process will clear its lag instantly.

Frequently Used GoldenGate Commands

· GGSCI is the command interface that executes GoldenGate commands.

• Start GGSCI from the GoldenGate install directory:

Shell> cd <GoldenGate install location>

Shell> GGSCI

For Help on a specific command:

GGSCI> HELP <command> <object>

GGSCI> HELP ADD EXTRACT

• [oracle@ggtest12c1 dirprm]$ $OGG_HOME/ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34

Operating system character set identified as UTF-8.

GGSCI (ggtest12c1.acme.com) 1>

— Log in as the GoldenGate database user.

GGSCI 1> dblogin userid ggate password ggate

Successfully logged into database.

GGSCI (@src12c) 2>

— Run the desired TRANDATA option. In our case we will use SCHEMATRANDATA because this an Oracle-to-Oracle setup.

GGSCI (@src12c) 4> add schematrandata scott

2015-07-25 14:06:26 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott.

2015-07-25 14:06:26 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott.

GGSCI (@src12c) 5>

• History: Lists the most recent GGSCI commands issued

• Info All: Displays the status and lag for all Oracle GoldenGate online processes on a system (Info All AllProcesses also displays tasks.)

• Obey: Runs a file containing a list of GGSCI commands

• Shell: Runs shell commands from within GGSCI

• Show: Displays the Oracle GoldenGate environment

• Versions: Displays OS and database versions

• View GGSEvt: Displays the Oracle GoldenGate event/error log

ggserr.log -- known as error log

• View Report: Displays a process report for Extract or Replicat health check

• To start and monitor golden Gate ,Go to golden Gate home folder and run GGSCI

Server1:TestDB:/ogg/TestDB/ggs $ls –ltr

drwxr-xr-x 26 oracle dba 4096 May 05 15:35 gghome

drwxr-xr-x 2 oracle dba 16384 Jun 09 12:48 backup_arch

Server1:TestDB:/ogg/TestDB/ggs $cd gghome

Server1:TestDB:/ogg/TestDB/ggs/gghome $./ggsci

for windows

C:\GG> ggsci.exe

TO CHECK PROCESS

GGSCI (Server1) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT ABENDED CAM_CAP 00:00:03 03:48:38

EXTRACT RUNNING CAM_PUMP 00:00:00 00:00:08

GGSCI (Server1) 3> info mgr

Manager is running (IP port Server1.7809, Process ID 47317384).

• GGSCI (Server1) 3> SHOW ALL

Parameter settings:

SET SUBDIRS ON

SET DEBUG OFF

Current directory: /ogg/TestDB/ggs/gghome

Using subdirectories for all process files

Editor: vi

Reports (.rpt) /ogg/TestDB/ggs/gghome/dirrpt

Parameters (.prm) /ogg/TestDB/ggs/gghome/dirprm

Replicat Checkpoints (.cpr) /ogg/TestDB/ggs/gghome/dirchk

Extract Checkpoints (.cpe) /ogg/TestDB/ggs/gghome/dirchk

Process Status (.pcs) /ogg/TestDB/ggs/gghome/dirpcs

SQL Scripts (.sql) /ogg/TestDB/ggs/gghome/dirsql

Database Definitions (.def) /ogg/TestDB/ggs/gghome/dirdef

Dump files (.dmp) /ogg/TestDB/ggs/gghome/dirdmp

Masterkey wallet files (.wlt) /ogg/TestDB/ggs/gghome/dirwlt

Credential store files (.crd) /ogg/TestDB/ggs/gghome/dircrd

GGSCI (Server1) 6> info extract CAM_CAP

EXTRACT CAM_CAP Last Started 2017-06-13 10:43 Status RUNNING

Checkpoint Lag 00:00:08 (updated 00:00:03 ago)

Process ID 49480138

Log Read Checkpoint Oracle Integrated Redo Logs

2017-06-14 15:44:30

SCN 2355.4077014034 (10118724996114)

GGSCI (Server1) 7> info extract CAM_PUMP

EXTRACT CAM_PUMP Last Started 2017-06-13 10:46 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:00 ago)

Process ID 32440748

Log Read Checkpoint File /ogg/TestDB/ggs/gghome/dirdat/cc000000013

2017-06-14 15:44:17.000000 RBA 3570596

• GGSCI (TARGET) > Lag Replicat rep_2a

Sending GETLAG request to REPLICAT REP_2A ... Last record lag: 34 seconds.

At EOF, no more records to process.

• GGSCI (TARGET) >

GGSCI (SOURCE) > Info RmtTrail *

Extract Trail: ./dirdat/vv Extract: EXT_2A

Seqno: 2

RBA: 2399277

File Size: 5M

Extract Trail: ./dirdat/rt Extract: PUMP_2A

Seqno: 2

RBA: 2399565

File Size: 5M

GGSCI (SOURCE) >

GGSCI (SOURCE) > Info All, AllProcesses

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT_2A 00:00:00 00:00:08

EXTRACT STOPPED INITLOAD

EXTRACT RUNNING PUMP_2A 00:00:00 00:00:07

ggsci> INFO EXTRACT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] -- To display Status of Extract, Approximate Extract lag, Checkpoint information, Process run history

ggsci> info extract emp_ext

ggsci> info extract cust_ext, detail

ggsci> info extract ext*, showch

ggsci> info extract *, detail

ggsci> info extract hr, tasks

ggsci> INFO REPLICAT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] -- To display status of Replicat, Approximate replication lag, the trail from which Replicat is reading, Replicat run history, including checkpoints in the trail, Information about the Replicat environment.

ggsci> info replicat emp_rep

ggsci> info replicat emp_rep, detail

ggsci> info replicat prd*, detail, allprocesses

ggsci> info replicat *, tasks

ggsci> info replicat fin, showch

ggsci> INFO EXTTRAIL trail_name -- To retrieve configuration information for a local trail

ggsci> info exttrail *

ggsci> info exttrail e:\ogg\dirdat\ex

ggsci> INFO RMTTRAIL trail_name -- To retrieve configuration information for a remote trail

ggsci> info rmttrail *

ggsci> info rmttrail d:\ogg\dirdat\e

EasyReliableDBA

Saturday, 6 June 2020

Frequently Used GoldenGate Commands

Question : What is structures the evolution of Oracle GoldenGate (OGG) from 11g to 23ai (the latest long-term support release)

Question : Golden Gate architecture and how workflow work

Question : if table structure are different in source and target then How to map it

Question : How to Resolve (Conflict Resolution Strategy)

Question : How checkpoint work in source and target

No comments:

Post a Comment

Search This Blog