GoldenGate Microservices Initial Load Instantiation with WebUI

May 10, 2022 | 17 minute read
Alex Lima
Director of Product Management
Text Size 100%:

Every GoldenGate 21c 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 21c 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.

 

DiagramDescription automatically generated

 

Goldengate Step by Step Initial Load

Step 1: Log in to the GoldenGate WebUI

 

A picture containing rockDescription automatically generated

 

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

https://docs.oracle.com/en/middleware/goldengate/core/21.1/admin/loading-data-file-replicat-ma-19.1.html

 

Alex Lima

Director of Product Management

Alex is Director of Product Management in the GoldenGate group focusing on GoldenGate Core Product, GoldenGate for Oracle, and Goldengate Foundation Suite.  Alex has a 20-year extensive background in managing, integrating, and architecting Oracle database solutions to customers worldwide, specializing in process improvement, performance, tunning, and high availability.

Show more

Previous Post

Automating SDLC with OCI Data Integration

David Allan | 11 min read

Next Post


Enable Customer Managed Keys on ODI Marketplace

Carla Romano | 5 min read