Every GoldenGate configuration requires an initial instantiation of the target database, often referred to as “Initial Load”. GoldenGate for Oracle is very flexible and allows customers to use different methodology and approaches to instantiate the target database. The most common approaches are using database-specific tools like Datapump, RMAN, and other file system approaches.
GoldenGate however has its own Initial Load process by querying the source tables and writing to the proprietary trail file, that trail is then used by the replicat (Apply Process) to load the target database.
Objectives
This document will demonstrate how to create a unidirectional replication with a native GoldenGate initial load instantiation and delta replication in the GoldenGate Microservices WebUI.
The demonstration solution will have a GoldenGate installed as a HUB architecture with one source and one target database in an Oracle Pluggable Architecture.
Database Environment
| Source Database: | Target Database: |
| Database: GGWEST (19.3) | Database: GGEAST (19.3) |
| PDB: WEST | PDB: EAST |
| OGG Alias: cdb_west | OGG Alias: pdb_east |
| Tables: | Tables: |
| COUNTRIES | COUNTRIES |
| DEPARTMENTS | DEPARTMENTS |
| EMPLOYEES | EMPLOYEES |
| JOBS | JOBS |
| JOB_HISTORY | JOB_HISTORY |
| LOCATIONS | LOCATIONS |
| REGIONS | REGIONS |
GoldenGate HUB:
# adminclient -v
Oracle GoldenGate Administration Client for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle Linux 7, x64, 64bit (optimized) on Jul 28 2021 12:32:46
Operating system character set identified as US-ASCII
| Source Extracts: | Target Replicats: |
| extw – Extract Change Replication | repw – Replicat Change Replication |
| ea – Trail File | |
| einit – Extract Initial Load | rinit – Replicat Initial Load |
| ei – Initial Load Trail File | |
Since this demonstration will be using a GoldenGate HUB architecture, there will be no need to create a Distribution Path to send the trail file to the target.
Note: Both databases were pre-configured with all GoldenGate users and database configuration changes. In addition, the Oracle HR sample schema was pre-created in both source and target databases.
Goldengate Step by Step Initial Load
Step 1: Log in to the GoldenGate WebUI
Step 2: Check the environment variables by clicking on the West deployment:
This first screen describes the Service Manager’s central dashboard, where you can see the deployments and all its services. Note that each deployment will have four different services. In this case, we have only one deployment, “west.”
Selecting the “west” deployment takes us to the deployment Information page to see the deployment variables. Please note that best practices suggest installing software in one file system and deploying files on a different file system. Here demonstrated by /u01 and /u02.
Step 3: Disable target constraints and truncate tables:
Unlike other methods like Datapump, GoldenGate’s initial load does not handle referential constraints rules while extracting and loading the data. Therefore, we need to disable the constraints before we start the process.
— Disable constraints before the initial load starts
alter table HR.COUNTRIES disable constraint COUNTR_REG_FK;
alter table HR.DEPARTMENTS disable constraint DEPT_LOC_FK;
alter table HR.EMPLOYEES disable constraint EMP_JOB_FK;
alter table HR.JOB_HISTORY disable constraint JHIST_JOB_FK;
alter table HR.DEPARTMENTS disable constraint DEPT_MGR_FK;
alter table HR.EMPLOYEES disable constraint EMP_MANAGER_FK;
alter table HR.JOB_HISTORY disable constraint JHIST_EMP_FK;
alter table HR.LOCATIONS disable constraint LOC_C_ID_FK;
alter table HR.EMPLOYEES disable constraint EMP_DEPT_FK;
alter table HR.JOB_HISTORY disable constraint JHIST_DEPT_FK;
— Truncate target tables
truncate table HR.JOB_HISTORY;
truncate table HR.EMPLOYEES;
truncate table HR.REGIONS;
truncate table HR.LOCATIONS;
truncate table HR.COUNTRIES;
truncate table HR.DEPARTMENTS;
truncate table HR.JOBS;
Step 4: Check the database connections in the OGG HUB
The connection to the database was pre-configured, and we will be using cdb_west to capture from the source database and pdb_east to apply to the target database.
Step 5: Create a change Extract
The extract is the process of capturing committed transactions from the database and writing them to the proprietary GoldenGate trail file. We first need to create a standard extract to capture delta changes while the initial instantiation is in progress.
Click on the +
Click on Integrated Extract and Next
Enter mandatory fields and next
Note: The “Register to PDB” files will only appear after entering the database credential information.
Check the pre-created parameter file and add the TABLE configuration, and press creates and run:
EXTRACT extw
USERIDALIAS cdb_west DOMAIN OracleGoldenGate
EXTFILE ea
TABLE WEST.HR.*;
Extract created
Step 6: Create the change Replicat but do not start
Replicat is the process that reads the trail file created by the extract and applies the transactions to the target database. In this case, we will create the replicat but will not start at this time. Once the Initial Load process finishes, we can start the change replicat to apply the delta changes.
Click on + to create the replicat
Select Nonintegrated Parallel Replicat
Enter mandatory fields; please note checkpoint table was pre-created.
Review the parameter file and press “Create.”
Note: DO NOT PRESS “CREATE and RUN.”
REPLICAT repw
USERIDALIAS pdb_east DOMAIN OracleGoldenGate
MAP WEST.HR.*, TARGET HR.*;
Replicat Created:
Step 7: Get the SCN
Before creating the initial load extract, we need to check for the SCN of the initial position of the open long transaction. If no existing transaction exists, we use the CURRENT SCN. Log in to the source database and run the following SQL:
-- Query for active transactions
--
Select T.START_SCN, T.STATUS TSTATUS, T.START_DATE,
S.SID, S.SERIAL#, S.INST_ID, S.USERNAME, S.OSUSER, S.STATUS SSTATUS, S.LOGON_TIME
From gv$transaction T
Inner
Join gv$session S
on S.SADDR = T.SES_ADDR
Union All
--
-- Query for current status
--
Select current_scn, 'CURRENT', CURRENT_DATE,
NULL, NULL, NULL, 'SYS', NULL, NULL, NULL
from v$database
Order by 1;
Note: Capture the ACTIVE SCN from the TSTATUS column. If there is no ACTIVE SCN in the results, capture the CURRENT SCN from TSTATUS.
We will use the ACTIVE START SCN 2898620
Step 8: Create the Initial Load Extract with SQLpredicate 2898620
Since there is an active open transaction, we will use the start SCN from that transaction.
Press the + to create the extract
Select Initial Load Extract and press next
Enter the name for the extract
Enter the details for the parameter file, note the SQLPREDICATE
EXTRACT EINIT
USERIDALIAS cdb_west DOMAIN OracleGoldenGate
EXTFILE ei MEGABYTES 250 PURGE
TABLEEXCLUDE WEST.HR.EMP_DETAILS_VIEW
TABLE WEST.HR.*; SQLPREDICATE "AS OF SCN 2898620";
Note: initial load extract stops once it finishes extracting all data from the tables in the HR schema and writes to the trail file. Since this is a very small data set, it finished fast. We can check the report file by clicking Actions -> Details -> Report.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2022-05-09 21:00:03 (activity since 2022-05-09 21:00:02)
Output to ei:
From table WEST.HR.COUNTRIES:
# inserts: 25
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table WEST.HR.DEPARTMENTS:
# inserts: 27
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table WEST.HR.EMPLOYEES:
# inserts: 107
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table WEST.HR.JOBS:
# inserts: 19
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table WEST.HR.JOB_HISTORY:
# inserts: 10
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table WEST.HR.LOCATIONS:
# inserts: 23
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table WEST.HR.REGIONS:
# inserts: 4
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
Bytes output 40774
Step 9: Create the initial load replicat
This step will create an initial load replicat to read from the trail files created in step 8 and load to the target database. There is nothing special about this replicat, nd it is the same procedures we did in Step 6 except that we will create and start at the end.
Press the + to create replicat
Select Integrated Replicat and Next
Enter name and connections
Check the parameter file and press create and run.
REPLICAT RINIT
USERIDALIAS pdb_east DOMAIN OracleGoldenGate
MAP WEST.HR.*, TARGET HR.*;
Initial load is created and running.
We can now check the statistics to verify the data load, as we can see below that all data was successfully loaded.
Now that the initial load is completed, we will re enable the constraints in the target that was disabled on SETP 3, before staring the change replicat.
-- Enable Contraints after the Initial Load is completed
alter table HR.COUNTRIES enable constraint COUNTR_REG_FK;
alter table HR.DEPARTMENTS enable constraint DEPT_LOC_FK;
alter table HR.EMPLOYEES enable constraint EMP_JOB_FK;
alter table HR.JOB_HISTORY enable constraint JHIST_JOB_FK;
alter table HR.DEPARTMENTS enable constraint DEPT_MGR_FK;
alter table HR.EMPLOYEES enable constraint EMP_MANAGER_FK;
alter table HR.JOB_HISTORY enable constraint JHIST_EMP_FK;
alter table HR.LOCATIONS enable constraint LOC_C_ID_FK;
alter table HR.EMPLOYEES enable constraint EMP_DEPT_FK;
alter table HR.JOB_HISTORY enable constraint JHIST_DEPT_FK;
Step 10: Start change replicat
There was a long transaction open when we started but not committed when we created the initial load. It means that
Click Action and Select with Options on the change replicat REPW
Select Start Point: At CSN, add the SCN captured on STEP 8 (2898620) and press Start
Checking the result, we will see REPW now running.
Step 11: Check the uncommitted transaction
Note: The transaction opened was a test insert I had without committing. Once the initial load was finished, I committed to the source, and the change replicat was replicated. Note below:
We can also see in the extract and replicat statistics:
This completes the Initial Load process with GoldenGate WebUI. Please note that this same process can be executed with scripts, obey files and REST API.
References:
alexlima.com 