Showing posts with label Goldengate. Show all posts

Saturday, 18 July 2026

SQL Queries for Goldengate Replications

set linesize 200
set pagesize 100
set trimspool on

column table_owner                format a25 heading 'TABLE_OWNER'
column table_name                 format a35 heading 'TABLE_NAME'
column scn                        format 99999999999999999999 heading 'SCN'
column timestamp                  format a20 heading 'TIMESTAMP'
column supplemental_log_data_pk   format a8 heading 'LOG_PK'
column supplemental_log_data_ui   format a8 heading 'LOG_UI'
column supplemental_log_data_fk   format a8 heading 'LOG_FK'
column supplemental_log_data_all  format a8 heading 'LOG_ALL'

select table_owner,
       table_name,
       scn,
       timestamp,
       supplemental_log_data_pk,
       supplemental_log_data_ui,
       supplemental_log_data_fk,
       supplemental_log_data_all
from   dba_capture_prepared_tables;

set linesize 250
set pagesize 100
set trimspool on

column source_database        format a25 heading 'SOURCE_DB'
column source_object_owner    format a20 heading 'OWNER'
column source_object_name     format a35 heading 'OBJECT_NAME'
column source_object_type     format a12 heading 'OBJ_TYPE'
column instantiation_scn      format 99999999999999999999 heading 'INST_SCN'
column ignore_scn             format 99999999999999999999 heading 'IGNORE_SCN'
column apply_database_link    format a30 heading 'DB_LINK'
column source_root_name       format a25 heading 'SOURCE_ROOT'

select source_database,
       source_object_owner,
       source_object_name,
       source_object_type,
       instantiation_scn,
       ignore_scn,
       apply_database_link,
       source_root_name
from   dba_apply_instantiated_objects;

On-premises Oracle Database to Exadata migration using a GoldenGate Hub architecture

If you are looking for a one-premises Oracle Database to Oracle Exadata migration using a GoldenGate Hub architecture, the typical design is as follows:

Source Oracle DB
     |
     |  (Extract)
     v
+------------------+
| GoldenGate HUB   |
| Server           |
| - Extract        |
| - Data Pump      |
| - Trail Files    |
+------------------+
     |
     |  (Trail Files)
     v
Target Exadata DB
     |
     |  (Replicat)
     v
Oracle Exadata

Enable supplemental logging on the source database.
Add schema/table supplemental logging (SCHEMATRANDATA/TABLETRANDATA).
Create and start GoldenGate Extract process.
Capture the current SCN from the source database.
Export the schema using Data Pump with FLASHBACK_SCN.
Extract schema DDL (user, tablespaces, grants, synonyms, etc.).
Copy dump files and DDL scripts to Exadata using SCP.
Create required tablespaces on Exadata.
Create the target schema/user and apply grants.
Import the schema dump into Exadata using IMPDP.
Create the GoldenGate Replicat process on the target.
Start Replicat using AFTERCSN <Export_SCN>.
Validate data and monitor replication lag.
Stop application writes and wait for lag to reach zero.
Perform final validation and cutover application connections to Exadata.
Decommission GoldenGate after business sign-off.

Step 1: Enable supplemental logging on the source database.

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

Goldengate HUB - Installation and Software location

[oracle@rac1-oracle GG_26]$ ls -lrt

total 508292

drwxr-xr-x.  3 oracle oinstall      4096 Jan 18 11:03 fbo_ggs_Linux_x64_Oracle_services_shiphome

-rw-r--r--.  1 oracle oinstall      2125 Jan 29 08:33 OGG-26ai_OUI_PATCH_README.txt

-rw-r--r--.  1 oracle oinstall    324413 Jan 29 08:54 oracle-goldengate-release-notes_23.26.1.0.0.pdf

-rw-r--r--.  1 oracle oinstall 520132861 Mar 13 14:27 V1054774-01.zip

drwxr-xr-x. 19 oracle oinstall      4096 Mar 23 12:27 ogg26AI_MA

drwxr-xr-x.  5 oracle oinstall      4096 Mar 23 12:44 ogg26AI_SM_NEW

drwxr-xr-x.  4 oracle oinstall      4096 Mar 23 12:44 DEPLOYMENT_26AI

-rwxrwxr-x.  1 oracle oinstall      3944 Jul 16 13:39 tnsnames.ora

Add below entries into tnsnames.ora files so GG will understand Source and Target DB Connections

ORCL_S =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.orcl-oracle.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.orcl-oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCLDB)

(UR = A)

)

ORCL_T =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)

(HOST=exadata-orcl.oraclevcn.com)(PORT = 1521))

(connect_data = (server = dedicated)(SERVICE_NAME=AMIT_ORCL.oraclevcn.com))

)

Step 2: Add schema/table supplemental logging (SCHEMATRANDATA/TABLETRANDATA).

Connect to GG Console

How to add DB Connections to Source DB from Goldgen Gate HUB

Once DB connection is completed then add Schema Trandata

Add Schema Transdata:-

In Oracle GoldenGate, TRANDATA is GoldenGate's way of enabling supplemental logging for tables/schemas so that enough information is available in Oracle redo logs to uniquely identify and replicate row changes.

Why it is required?
Captures UPDATEs and DELETEs correctly.
Provides primary key/unique key values in redo logs.
Allows Replicat to identify the exact target row.
Without TRANDATA, GoldenGate may fail with:
OGG-01432 No unique key defined

2. Scheduling ColumnsWhen Scheduling Columns is enabled, GoldenGate logs columns used to determine transaction ordering and dependency tracking.
Purpose:
Maintains correct commit order.
Improves parallel Replicat processing.
Prevents data consistency issues when dependent transactions are applied in parallel.
Common Usage:
Integrated Replicat
Parallel Replicat
High-volume OLTP databases
Example:
UPDATE ORDER_HEADER
UPDATE ORDER_LINES

Scheduling columns help GoldenGate understand parent-child relationships and apply transactions in the correct sequence.

3. All ColumnsWhen All Columns is selected, GoldenGate logs all column values for UPDATE operations.
Instead of only logging changed columns:
Before:
ID=100
NAME=AMIT
CITY=MUMBAI
Update CITY='PUNE'

Oracle normally logs only the modified column.
With All Columns enabled:

ID=100
NAME=AMIT
CITY=PUNE

all column values are available to GoldenGate.

Benefits
Better conflict detection.
Supports active-active replication.
Easier data comparison and troubleshooting.
Required for some replication topologies.

Note:- For Source we are not adding anything for "Checkpoint" and "Heartbeat"

Step 3. Create and start GoldenGate Extract process

Create and Run

You can see the Extract process "AMIT_EX" is created and Running

Step 4. Capture the current SCN from the source database. Export the schema using Data Pump with FLASHBACK_SCN.

Check for long-running transactions before starting the export:

SELECT COUNT(*) FROM gv$transaction t, dba_capture g WHERE t.start_scn < g.start_scn;

Expected Result:

If the count is 0, no transactions are older than the capture start SCN, and the export can proceed.

If the count is greater than 0, investigate the long-running transactions before initiating the export, as they may affect export consistency or capture processes.

[oracle@rac1-oracle AMIT]# cat EXP_AMIT_SCHEMA.sh

nohup expdp parfile=/mnt/exadata_export/AMIT/EXP_AMIT_SCHEMA.par &

[PROD:oracle@rac1-oracle AMIT]# cat EXP_AMIT_SCHEMA.par
userid="/ as sysdba"
directory=AMIT
dumpfile=EXP_AMIT_SCHEMA_%U.dmp
logfile=EXP_AMIT_SCHEMA.log
schemas=AMIT
parallel=10
EXCLUDE=STATISTICS
FLASHBACK_SCN=1234567890


[oracle@rac1-oracle AMIT] sh EXP_AMIT_SCHEMA.sh ----> executing sh script for taking Export on Source


                                                                                                                            Elapsed       Time
                                                    Start                                                Total                  Time     Remain
    SID    SERIAL# Module             SQL_ID        Time         Operation    Object                      Work       Sofar    (Mins)      (Sec)
------- ---------- ------------------ ------------- ------------ ------------ -------------------- ----------- ----------- --------- ----------
    766      36729 Data Pump Master   bjf05cwcj5s6p 18-jul:03:53 SYS_EXPORT_S                           178227      153468        31        304
   4873      12780 Data Pump Worker   335q3w4mk0rwt 18-jul:03:53 Rowid Range  AMIT.ORDER_01           4294967296    83962686      32      95341

Step 5. Copy dump files and DDL scripts to Exadata using SCP.

[oracle@rac1 AMIT]# ls -lrt
total 222931048
-rwxrwxr-x 1 oracle oinstall 75 Jul 16 14:12 EXP_AMIT_SCHEMA.sh
-rwxrwxr-x 1 oracle oinstall 171 Jul 18 03:49 EXP_AMIT_SCHEMA.par
-rw-r----- 1 oracle asmadmin 20815839232 Jul 18 05:03 EXP_AMIT_SCHEMA_01.dmp
-rw-r----- 1 oracle asmadmin 10212769792 Jul 18 05:03 EXP_AMIT_SCHEMA_04.dmp
-rw-r----- 1 oracle asmadmin 35046813696 Jul 18 05:03 EXP_AMIT_SCHEMA_06.dmp
-rw-r----- 1 oracle asmadmin 19759411200 Jul 18 05:03 EXP_AMIT_SCHEMA_03.dmp
-rw-r----- 1 oracle asmadmin 12735889408 Jul 18 05:03 EXP_AMIT_SCHEMA_05.dmp
-rw-r----- 1 oracle asmadmin 17126072320 Jul 18 05:03 EXP_AMIT_SCHEMA_02.dmp
-rw-r--r-- 1 oracle asmadmin 108981 Jul 18 05:03 AMIT_SCHMA.log
-rw-r----- 1 oracle asmadmin 67296854016 Jul 18 05:03 EXP_AMIT_SCHEMA_08.dmp
-rw-r----- 1 oracle asmadmin 13521874944 Jul 18 05:03 EXP_AMIT_SCHEMA_10.dmp
-rw-r----- 1 oracle asmadmin 14047080448 Jul 18 05:03 EXP_AMIT_SCHEMA_09.dmp
-rw-r----- 1 oracle asmadmin 17718259712 Jul 18 05:03 EXP_AMIT_SCHEMA_07.dmp
-rw------- 1 oracle oinstall 216006 Jul 18 05:03 nohup.out

Exadata Target DB:-

Step 6. Enable supplemental logging on the Target database

Enable supplemental logging on the Target Exadata database.

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 PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;-------------> source is pdb then only we need to run this one

Step 7: Create the same user AMIT and it's tablesapce on Exadata Target DB (PDB):-
Create required tablespaces on Exadata.
Create the target schema/user and apply grants.


-- Create Tablespace
CREATE TABLESPACE AMIT_TS DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- Create User
CREATE USER AMIT IDENTIFIED BY "*****" DEFAULT TABLESPACE AMIT_TS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON AMIT_TS;

-- Grant Required Privileges
GRANT CREATE SESSION TO AMIT;
GRANT CREATE TABLE TO AMIT;
GRANT CREATE VIEW TO AMIT;
GRANT CREATE SEQUENCE TO AMIT;
GRANT CREATE PROCEDURE TO AMIT;
GRANT CREATE TRIGGER TO AMIT;

Step 7. Import the schema dump into Exadata using IMPDP

Import into Exadata 

[oracle@exadata-rac1 IMPORT_AMIT]$ cat IMP_AMIT_SCHEMA.sh
nohup impdp parfile=/import/IMPORT_AMIT/IMP_AMIT_SCHEMA.par &

[oracle@exadata-rac1 IMPORT_AMIT]$ cat IMP_AMIT_SCHEMA.par
userid="/ as sysdba"
directory=AMIT_IMP
dumpfile=EXP_AMIT_SCHEMA_%U.dmp
logfile=AMIT.log
schemas=AMIT
remap_tablespace=AMIT:AMIT
parallel=16
EXCLUDE=STATISTICS
METRICS=YES
LOGTIME=ALL
transform=OID:N
transform=disable_archive_logging:Y



[oracle@exadata-rac1 IMPORT_AMIT]$ ls -lrt
total 222969896
-rwxrwxr-x. 1 oracle oinstall         248 Jul 16 12:55 IMP_AMIT_SCHEMA.par
-rwxrwxr-x. 1 oracle oinstall          66 Jul 16 12:55 IMP_AMIT_SCHEMA.sh
-rwxrwxr-x. 1 oracle oinstall 20815839232 Jul 18 03:25 EXP_AMIT_SCHEMA_01.dmp
-rwxrwxr-x. 1 oracle oinstall 17126072320 Jul 18 03:31 EXP_AMIT_SCHEMA_02.dmp
-rwxrwxr-x. 1 oracle oinstall 19759411200 Jul 18 03:38 EXP_AMIT_SCHEMA_03.dmp
-rwxrwxr-x. 1 oracle oinstall 10212769792 Jul 18 03:41 EXP_AMIT_SCHEMA_04.dmp
-rwxrwxr-x. 1 oracle oinstall 12735889408 Jul 18 03:46 EXP_AMIT_SCHEMA_05.dmp
-rwxrwxr-x. 1 oracle oinstall 35046813696 Jul 18 03:58 EXP_AMIT_SCHEMA_06.dmp
-rwxrwxr-x. 1 oracle oinstall 17718259712 Jul 18 04:04 EXP_AMIT_SCHEMA_07.dmp
-rwxrwxr-x. 1 oracle oinstall 67296854016 Jul 18 04:25 EXP_AMIT_SCHEMA_08.dmp
-rwxrwxr-x. 1 oracle oinstall 14047080448 Jul 18 04:30 EXP_AMIT_SCHEMA_09.dmp
-rwxrwxr-x. 1 oracle oinstall 13521874944 Jul 18 04:34 EXP_AMIT_SCHEMA_10.dmp
-rw-r--r--. 1 oracle asmadmin      195961 Jul 18 05:28 AMIT.log

Step 8. Create/Add the GoldenGate Replicat process on the target -

Create Checkpoint for Replicate process

Go to DB Connections and add Checkpoint

Now Add Replicat

Start Replicat using AFTERCSN <Export_SCN>.

Validate data and monitor replication lag.

Stop application writes and wait for lag to reach zero.

Perform final validation and cutover application connections to Exadata.

Tuesday, 14 July 2026

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:-

Extract captures changes from Oracle redo logs.
EXTTRAIL stores captured transactions in trail files.
TABLE defines which tables/schemas are captured.
Replicat applies changes on target.
MAP defines source-to-target object mapping.
DDL INCLUDE MAPPED replicates DDL for mapped objects.
DDLOPTIONS REPORT logs DDL actions in report files.
OVERRIDEDUPS handles duplicate record situations.
INSERTMISSINGUPDATES converts missing-row updates into inserts.
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

Sunday, 14 June 2026

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


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)


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


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


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.

Disclaimer

Saturday, 18 July 2026

2. Scheduling Columns

3. All Columns

Tuesday, 14 July 2026

Key Points:-

Sunday, 14 June 2026

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

Better Interview Answer

2. What replaced Data Pump in GoldenGate Microservices?

Better Interview Answer

3. What is a Checkpoint Table?

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

Common Interview Question

4. Classic Extract vs Integrated Extract

Interview Table

Diagram

Interview Favorite Question

5. Types of Replicat

Complete Answer

1. Classic Replicat

2. Integrated Replicat

3. Coordinated Replicat

4. Parallel Replicat

6. Replicat hits ORA-00001

Better Senior-Level Answer

Interview Trick

7. How do you troubleshoot Replicat lag?

Step-by-Step Senior DBA Answer

Check Lag

Check Long Running Transactions

Check Database Wait Events

Common Causes

Missing Indexes

Large Transactions

Insufficient Parallelism

8. Explain Zero Downtime Migration using GoldenGate

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Q9. What are Trail Files?

Q10. Difference between CSN and SCN?

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