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

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 Columns

When 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 Columns

When 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"

Create and start GoldenGate Extract process.

Create and Run

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

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

Enable supplemental logging on the Target 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 

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

[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

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.