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.
The Initial Load can be achieved via various methods like Export / Import, Data Pump etc. Even Golden Gate provides Initial Load option and in this article, we will be looking at same.
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.
Always setup change sync before Initial load so that once initial load has finished, the change sync will start immediately to keep source - target in sync.
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
At this stage, we have configured the regular Extract / Pump / Replicat process which are ready to keep tables in sync once Initial load is completed
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!!!