Oracle GoldenGate 21c Initial Load Extract and Change Sync

When you setup Golden Gate for existing tables in production environment, the first step is to copy the existing rows from source to target table. This process of copying existing rows from source to target is called as Initial Load. Once the source & target tables are in sync, you can setup Golden Gate Extract, Pump & Replicat to fetch source transactions (Insert, Update & Delete) and apply on target table.

What is Golden Gate Initial Load?

Golden Gate provides special Extract and Replicat processes to copy existing rows from source to target tables. The Initial Load Extract and Replicat run only once to bring source & target in sync. The regular Extract / Pump / Replicat are know as Change Sync because they fetch changes from source and apply on target.

Add Change Sync Extract on Source

Let's create a sample table on source PDB and insert some records which will be used for Initial Load

CREATE TABLE fox.iload (
  id         NUMBER(10) PRIMARY KEY,
  name       VARCHAR2(100),         
  created_at DATE DEFAULT SYSDATE,  
  status     VARCHAR2(20)           
);

INSERT INTO fox.iload (id, name, status) VALUES (1, 'Load Alpha', 'Pending');
INSERT INTO fox.iload (id, name, status) VALUES (2, 'Load Beta', 'Completed');
INSERT INTO fox.iload (id, name, status) VALUES (3, 'Load Gamma', 'In Progress');
INSERT INTO fox.iload (id, name, status) VALUES (4, 'Load Delta', 'Failed');
INSERT INTO fox.iload (id, name, status) VALUES (5, 'Load Epsilon', 'Pending');
INSERT INTO fox.iload (id, name, status) VALUES (6, 'Load Zeta', 'Completed');
INSERT INTO fox.iload (id, name, status) VALUES (7, 'Load Eta', 'In Progress');
INSERT INTO fox.iload (id, name, status) VALUES (8, 'Load Theta', 'Failed');
INSERT INTO fox.iload (id, name, status) VALUES (9, 'Load Iota', 'Pending');
INSERT INTO fox.iload (id, name, status) VALUES (10, 'Load Kappa', 'Completed');

Connect to ggsci and login to CDB with ggadmin user

cd $GG_HOME
./ggsci

dblogin userid c##ggadmin@AKS01_CDB, password passw0rd

Add change sync extract on source

ADD EXTRACT ILOADE1, INTEGRATED TRANLOG, BEGIN NOW
register extract ILOADE1 database container (PDB)
add exttrail ./dirdat/il, extract ILOADE1

Edit parameters for the extract

edit param ILOADE1

EXTRACT ILOADE1
USERID c##ggadmin@AKS01_CDB, PASSWORD passw0rd
EXTTRAIL ./dirdat/il
TABLE pdb.fox.iload;

We will start the Extract process once the Initial load is completed.

Add Change Sync Pump on Source

Let's add the pump on source which will read from extract trail files and send to target

Add extract ILDP1, EXTTRAILSOURCE ./dirdat/il
Add rmttrail /u01/app/oracle/product/gg/dirdat/it, extract ILDP1

Edit parameters for the pump

edit param ILDP1

EXTRACT ILDP1
USERID c##ggadmin@AKS01_CDB, PASSWORD passw0rd
RMTHOST aks02, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/it
TABLE pdb.fox.iload;

Start the pump process

start ILDP1

Add Change Sync Replicat on Target

On target server, connect to ggsci and dblogin to PDB

cd $GG_HOME
./ggsci

dblogin userid c##ggadmin@AKS02_PDB password passw0rd

Let's add the replicat on target which will apply transactions sent by the pump process

add replicat ILOADR1, integrated exttrail /u01/app/oracle/product/gg/dirdat/it

Edit parameters for the replicat

edit param ILOADR1

REPLICAT ILOADR1
USERID c##ggadmin@AKS02_PDB, PASSWORD passw0rd
ASSUMETARGETDEFS
MAP pdb.fox.iload TARGET pdb.fox.iload;

Start the replicat process

start ILOADR1

Add Golden Gate Initial Load Extract on Source

Let's create the special Extract on source which will read existing records from the source table and send to target table (which is empty)

ADD EXTRACT INITLE, SOURCEISTABLE

Edit parameters for the Initial Load Extract

EDIT PARAM INITLE

EXTRACT INITLE
userid c##ggadmin@AKS01_CDB, PASSWORD passw0rd
RMTHOST aks02, mgrport 7809
RMTTASK REPLICAT, GROUP INITLR
TABLE pdb.fox.iload;

Add Golden Gate Initial Load Replicat on Target

Create the special replicat on target which will load the records coming from source

ADD REPLICAT INITLR, SPECIALRUN

Edit parameters for the Initial Load Replicat

EDIT PARAM INITLR

REPLICAT INITLR
userid c##ggadmin@AKS02_PDB, PASSWORD passw0rd
ASSUMETARGETDEFS
MAP pdb.fox.iload, TARGET pdb.fox.iload;

Start Initial Load Extract

The initial load extract will auto start the replicat on target server. To enable extract to start replicat remotely, we need to update the target manager parameters

edit param mgr

PORT 7809
ACCESSRULE, PROG REPLICAT, IPADDR *, ALLOW

Now we can start the Initial Load Extract on source. No need to start replicat on target as it will be remotely started by the extract

start INITLE

To check the progress of Inital Load Extract

info INITLE

Once all the records are loaded from source to target, start the change sync extract on source

start ILOADE1

Enjoy!!!