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

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.

[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.

Transportable Tablespaces (TTS) Migration - without RMAN

Migration of the Database and the usage of  TTS

If the source and Exadata target are on the same platform/endian and you are moving only tablespaces, you can use **TTS without RMAN**.

Step 1: Check TTS restrictions and self-containment

exec dbms_tts.transport_set_check('TS1,TS2', true);

select * from transport_set_violations;

Validates that the selected tablespaces are self-contained and eligible for transport without dependent objects outside the transport set.

Step 2: Put the tablespaces in READ ONLY mode

alter tablespace TS1 read only;

alter tablespace TS2 read only;

Freezes changes to the tablespaces to ensure consistent datafiles during transport.

Step 3: Export metadata using Data Pump

expdp '/ as sysdba' dumpfile=ORCL_Metadata.dmp \

logfile=ORCL_Metadata.log directory=C02_ESB_Metadata \

transport_tablespaces=AM_TS,SI_TS,PA_TS,VA_TS

Exports only the metadata definitions (tables, indexes, privileges, etc.) required to reconstruct the transported tablespaces on the target database.

Step 4: Copy datafiles and dump file to Exadata (From Source to Target)

Transfers the physical tablespace datafiles and exported metadata from the source server to the Exadata target server.

Step 5: Import metadata and plug in the datafiles

impdp system/password directory=DP_DIR \

dumpfile=tts.dmp logfile=tts_imp.log \

transport_datafiles='/path/ts1.dbf','/path/ts2.dbf'

Registers the transported datafiles in the target database and imports the corresponding metadata.

Step 6: Set tablespaces back to READ WRITE mode

alter tablespace TS1 read write;

alter tablespace TS2 read write;

Opens the transported tablespaces for normal application access and DML operations after successful migration.

TTS Summary :-

Transportable Tablespaces (TTS) is a migration method that moves Oracle tablespaces by transporting their datafiles and metadata, avoiding a full database export/import and significantly reducing migration time for large databases when source and target platforms have the same endian format.