Oracle GoldenGate Microservices Migration

You can migrate data from a non-distributed database to an Oracle 23ai distributed database using Oracle GoldenGate Microservices.

Prerequisites

  • The tables to be migrated from the non-distributed database must be classified into sharded tables and duplicated tables

  • The sharding keys for all of the tables to be migrated to sharded tables must be identified.

  • The target distributed database shard catalog must be created with the system-managed data distribution method.

  • Sharded tables and duplicated tables must be pre-created in the target distributed database. This can be done by extracting DDLs from the source database and modifying them to create corresponding sharded and duplicated table statements.

  • Install Oracle GoldenGate Hub software using the OCI Marketplace image - Oracle GoldenGate 23ai for Oracle Database (23.7.2.25.03). See Using Oracle GoldenGate on Oracle Cloud Marketplace for instructions.

Migrating Data from a Non-Distributed Database to a Distributed Database

Example Environment

The examples in the steps below use the following topology.

System/Object Source Environment Target Environment
Database type Non-distributed database Oracle Globally Distributed Database
Oracle Database release 23ai (Multitenant) 23ai (Multitenant)
Oracle GoldenGate release 23ai (Microservices Architecture) 23ai (Microservices Architecture)

CDB name

nshdcdb

sdbcdb

PDB names

nshdpdb

Shards: sdbpdb1,sdbpdb2,sdbpdb3

Shard catalog: scpdb

Application schema

app_schema

app_schema

Sharded tables

Customers, Orders, LineItems

Customers, Orders, LineItems

Duplicated tables

Products

Products

High Level Steps

At a high level, migrating data from a non-distributed database to a distributed database using Oracle GoldenGate Microservices is done in two phases:

  1. Extraction on source database: All the tables from source database are extracted using single Extract process on the source database.

  2. Replication on target database: The data replication into the sharded tables is done on the shard databases and the data replication into the duplicate table is done on the shard catalog.

Configure Source and Target Databases

Configure source and target databases to enable GoldenGate replication.

Source Database:
 
CDB Level.
 
1. Enable FORCE LOGGING on database.
 
SQL> ALTER DATABASE FORCE LOGGING;
 
2. Enable minimum database-level supplemental logging.
 
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 
3. Enable GoldenGate Replication.
 
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
 
PDB level.
 
4. Create user and grant privileges on the source PDB.
 
SQL> alter session set container=GGPDB;
SQL> create user ggadmin identified by <password>;
SQL> GRANT CONNECT, RESOURCE to ggadmin;
SQL> alter user ggadmin QUOTA UNLIMITED on users;
SQL> GRANT OGG_CAPTURE to ggadmin;
 
 
Target Databases: All Shard Databases
 
CDB Level.
 
1. Enable GoldenGate replication.
 
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
 
PDB Level. 
 
2. Create users and privileges. (local user, NOT a sharded user)
 
create user ggadmin identified by <password>;
alter user ggadmin QUOTA UNLIMITED on users;
GRANT CONNECT, RESOURCE to ggadmin;
GRANT OGG_APPLY to ggadmin;
GRANT SELECT, INSERT, UPDATE, DELETE on APP_SCHEMA.CUSTOMERS to ggadmin;
GRANT SELECT, INSERT, UPDATE, DELETE on APP_SCHEMA.ORDERS to ggadmin;
GRANT SELECT, INSERT, UPDATE, DELETE on APP_SCHEMA.LINEITEMS to ggadmin;
 
 
Catalog Database:
 
CDB Level.
 
1. Enable GoldenGate replication.
 
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
 
PDB Level. 
 
2. create users and privileges. (local user, NOT a sharded user)
 
create user ggadmin identified by <password>;
alter user ggadmin QUOTA UNLIMITED on users;
GRANT CONNECT, RESOURCE to ggadmin;
GRANT OGG_APPLY to ggadmin;
GRANT SELECT, INSERT, UPDATE, DELETE on APP_SCHEMA.PRODUCTS to ggadmin;

Add Credentials

Log in to OCI GoldenGate instance (hub) and create source and target user credentials.

The oggadmin password can be found in the GoldenGate Hub node (installed using Marketplace) location, /home/opc/ogg-credentials.json. 

export PATH=$PATH:/u01/app/ogg/bin/
 
[opc@ogg23aiora ~]$ adminclient
 
OGG (not connected) 1>
 
OGG (not connected) 6> connect http://localhost:9000 as oggadmin password <password>
Using default deployment 'GG23Test'
 
--Add credentials for the source database:
 
ALTER CREDENTIALSTORE ADD USER
 ggadmin@gg23ai.dbsubnet.shardvcn.oraclevcn.com:1521/ggpdb.dbsubnet.shardvcn.oraclevcn.com
 ALIAS ggadmin_src DOMAIN OracleGoldenGate PASSWORD <password>
 
--Add credentials for the downstream database:
 
ALTER CREDENTIALSTORE ADD USER
 ggadmin@devshdphx10.dbsubnet.shardvcn.oraclevcn.com:1521/shdpdb.dbsubnet.shardvcn.oraclevcn.com
 ALIAS ggadmin_shd1 DOMAIN OracleGoldenGate PASSWORD <password>
ALTER CREDENTIALSTORE ADD USER
 ggadmin@devshdphx11.dbsubnet.shardvcn.oraclevcn.com:1521/shdpdb.dbsubnet.shardvcn.oraclevcn.com
 ALIAS ggadmin_shd2 DOMAIN OracleGoldenGate PASSWORD <password>
ALTER CREDENTIALSTORE ADD USER
 ggadmin@devshdphx12.dbsubnet.shardvcn.oraclevcn.com:1521/shdpdb.dbsubnet.shardvcn.oraclevcn.com
 ALIAS ggadmin_shd3 DOMAIN OracleGoldenGate PASSWORD <password>
ALTER CREDENTIALSTORE ADD USER
 ggadmin@devcatphx10.dbsubnet.shardvcn.oraclevcn.com:1521/catpdb.dbsubnet.shardvcn.oraclevcn.com ALIAS
 ggadmin_cat DOMAIN OracleGoldenGate PASSWORD <password>

Source (Non-Distributed) Database Configuration

  1. Enable SCHEMATRANDATA and create an Extract on source database to capture transactions from source tables, and start it. 

    $ adminclient

OGG (not connected) 6> connect http://localhost:9000 as oggadmin password <password>
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 > DBLOGIN USERIDALIAS ggadmin_src

--Enable SCHEMATRANDATA:
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 3> add schematrandata app_schema
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 7> info schematrandata app_schema
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 6> info trandata app_schema.*

--Add Extract
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 > ADD EXTRACT extnshd INTEGRATED TRANLOG BEGIN NOW
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 > REGISTER EXTRACT extnshd DATABASE
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 > ADD EXTTRAIL extnshd/et EXTRACT extnshd 
  
Add the following parameters in extract parameter file
 
OGG (http://localhost:9000 GG23Test) 3> edit params extnshd
EXTRACT EXTNSHD
USERIDALIAS ggadmin_src DOMAIN OracleGoldenGate
EXTTRAIL extnshd/et
Table app_schema.customers;
Table app_schema.orders;
Table app_schema.lineitems;
Table app_schema.products;
 
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 > START EXTRACT extnshd

  2. Capture data from the source database for initial load using expdp.

    Note:

    The flashback_scn option is not required because SCHEMATRANDATA (enabled on the source) and DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameters on the Replicats take care of SCNs. 
    $ expdp app_schema/<password>@NSHDPDB directory=DATA_PUMP_DIR
 dumpfile=app_schema_exp.dmp logfile=app_schema_exp.log

Target (Distributed) Database Configuration

  1. Perform initial load on the target distributed databases and catalog using impdp. 

    Import into shards:
$ impdp app_schema/<password>@SDBPDB1 directory=DATA_PUMP_DIR dumpfile=app_schema_exp.dmp
 logfile=app_schema_imp.log tables=CUSTOMERS,ORDERS,LINEITEMS, CONTENT=DATA_ONLY
$ impdp app_schema/xxxxx@SDBPDB2 directory=DATA_PUMP_DIR dumpfile=app_schema_exp.dmp
 logfile=app_schema_imp.log tables=CUSTOMERS,ORDERS,LINEITEMS, CONTENT=DATA_ONLY
$ impdp app_schema/xxxxx@SDBPDB3 directory=DATA_PUMP_DIR dumpfile=app_schema_exp.dmp
 logfile=app_schema_imp.log tables=CUSTOMERS,ORDERS,LINEITEMS, CONTENT=DATA_ONLY
 
Import into shard catalog:
$ impdp app_schema/<password>@SCPDB directory=DATA_PUMP_DIR dumpfile=app_schema_exp.dmp
 logfile=app_schema_imp.log tables=PRODUCTS CONTENT=DATA_ONLY

  2. Create 3 Replicats (same as the number of shards) on target databases.

    Replicat for sharded tables on Shard 1
======================================
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 34> DBLOGIN USERIDALIAS ggadmin_shd1

OGG (http://localhost:9000 GG23Test as ggadmin_shd1@SHDCDB)
 42> ADD CHECKPOINTTABLE ggadmin.GGCHKPT

OGG (http://localhost:9000 GG23Test as ggadmin_shd2@SHDCDB)
 51> ADD REPLICAT REP1,PARALLEL INTEGRATED, EXTTRAIL extnshd/et
 CHECKPOINTTABLE ggadmin.GGCHKPT

Add the following parameters in replicat for shard1

  
OGG (http://localhost:9000 GG23Test) 4> view params REP1
  
REPLICAT rep1
USERIDALIAS ggadmin_shd1 DOMAIN OracleGoldenGate
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
MAP_PARALLELISM 3
MIN_APPLY_PARALLELISM 2
MAX_APPLY_PARALLELISM 10
MAP app_schema.customers, TARGET app_schema.customers;
MAP APP_SCHEMA.orders, target APP_SCHEMA.orders;
MAP APP_SCHEMA.lineitems, target APP_SCHEMA.lineitems;
 

Replicat for sharded tables on Shard 2
======================================
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 34> DBLOGIN USERIDALIAS ggadmin_shd2

OGG (http://localhost:9000 GG23Test as ggadmin_shd1@SHDCDB)
 42> ADD CHECKPOINTTABLE ggadmin.GGCHKPT

OGG (http://localhost:9000 GG23Test as ggadmin_shd2@SHDCDB)
 51> ADD REPLICAT REP2,PARALLEL INTEGRATED, EXTTRAIL extnshd/et
 CHECKPOINTTABLE ggadmin.GGCHKPT

Add the following parameters in replicat for shard2

  
OGG (http://localhost:9000 GG23Test) 4> view params REP2
  
REPLICAT rep2
USERIDALIAS ggadmin_shd2 DOMAIN OracleGoldenGate
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
MAP_PARALLELISM 3
MIN_APPLY_PARALLELISM 2
MAX_APPLY_PARALLELISM 10
MAP app_schema.customers, TARGET app_schema.customers;
MAP APP_SCHEMA.orders, target APP_SCHEMA.orders;
MAP APP_SCHEMA.lineitems, target APP_SCHEMA.lineitems;


Replicat for sharded tables on Shard 3
======================================
OGG (http://localhost:9000 GG23Test as ggadmin_src@GGDB23)
 34> DBLOGIN USERIDALIAS ggadmin_shd3

OGG (http://localhost:9000 GG23Test as ggadmin_shd1@SHDCDB)
 42> ADD CHECKPOINTTABLE ggadmin.GGCHKPT

OGG (http://localhost:9000 GG23Test as ggadmin_shd2@SHDCDB)
 51>  ADD REPLICAT REP3,PARALLEL INTEGRATED, EXTTRAIL extnshd/et
 CHECKPOINTTABLE ggadmin.GGCHKPT

Add the following parameters in replicat for shard3

  
OGG (http://localhost:9000 GG23Test) 4> view params REP3
  
REPLICAT rep3
USERIDALIAS ggadmin_shd3 DOMAIN OracleGoldenGate
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
MAP_PARALLELISM 3
MIN_APPLY_PARALLELISM 2
MAX_APPLY_PARALLELISM 10
MAP app_schema.customers, TARGET app_schema.customers;
MAP APP_SCHEMA.orders, target APP_SCHEMA.orders;
MAP APP_SCHEMA.lineitems, target APP_SCHEMA.lineitems;


#### NOTE ####

You can remove DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter
 when Replicat has processed all transactions beyond the instantiation SCN.



 
Replicat for duplicate tables on Catalog
========================================
 
OGG (http://localhost:9000 GG23Test as ggadmin_shd3@SHDCDB)
 67> DBLOGIN USERIDALIAS ggadmin_cat

OGG (http://localhost:9000 GG23Test as ggadmin_cat@CATCDB)
 68> ADD CHECKPOINTTABLE ggadmin.GGCHKPT

OGG (http://localhost:9000 GG23Test as ggadmin_cat@CATCDB)
 69> ADD REPLICAT REPC, PARALLEL INTEGRATED, EXTTRAIL extnshd/et
 CHECKPOINTTABLE ggadmin.GGCHKPT

Add the following parameters in replicat for catalog 

OGG (http://localhost:9000 GG23Test as ggadmin_cat@CATCDB) > edit params repcat
  
REPLICAT repc
USERIDALIAS ggadmin_cat DOMAIN OracleGoldenGate
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
MAP_PARALLELISM 3
MIN_APPLY_PARALLELISM 2
MAX_APPLY_PARALLELISM 10
MAP APP_SCHEMA.products, target APP_SCHEMA.products;

  3. Start Replicats on the target shards using AFTERCSN. 

    OGG (http://localhost:9000 GG23Test as ggadmin_shd3@SHDCDB)
 64> start replicat rep1
OGG (http://localhost:9000 GG23Test as ggadmin_shd3@SHDCDB) 64> start replicat rep2
OGG (http://localhost:9000 GG23Test as ggadmin_shd3@SHDCDB) 64> start replicat rep3
OGG (http://localhost:9000 GG23Test as ggadmin_shd3@SHDCDB) 64> start replicat repc  

OGG (http://localhost:9000 GG23Test as ggadmin_cat@CATCDB) 17> info all
Program     Status      Group       Type            Lag at Chkpt    Time Since Chkpt

ADMINSRVR   RUNNING   
DISTSRVR    RUNNING   
PMSRVR      RUNNING   
RECVSRVR    RUNNING   
EXTRACT     RUNNING     EXTNSHD     INTEGRATED      00:00:00        00:00:08    
REPLICAT    RUNNING     REP1        PARALLEL INT    00:00:00        00:00:09    
REPLICAT    RUNNING     REP2        PARALLEL INT    00:00:00        00:00:00    
REPLICAT    RUNNING     REP3        PARALLEL INT    00:00:00        00:00:03    
REPLICAT    RUNNING     REPC        PARALLEL INT    00:00:00        00:00:00

Validate

Validate that rows are replicated from the non-sharded tables to the shards. For example, if you have 9000 rows in the source table, and three target shards, about 3000 rows should be distributed to each shard.

Creating a Testing Environment

How to create a source non distributed database environment for testing

Login to Database
 
Create tablespace
SQL> create tablespace customers_tsp datafile size 100m autoextend on;
 
Create app_schema
SQL> create user app_schema identified by <password>;
SQL> grant connect, resource, alter session to app_schema;
SQL> grant create view, create database link, alter database link, create materialized view, create tablespace to app_schema;
SQL> grant unlimited tablespace to app_schema;
 
Create tables
SQL> CREATE TABLE Customers
(
  CustId      VARCHAR2(60) NOT NULL,
  FirstName   VARCHAR2(60),
  LastName    VARCHAR2(60),
  Class       VARCHAR2(10),
  Geo         VARCHAR2(8),
  CustProfile VARCHAR2(4000),
 CONSTRAINT pk1_customers PRIMARY KEY (CustId)
) TABLESPACE customers_tsp;
 
 
SQL> CREATE TABLE Orders
(
  OrderId     INTEGER NOT NULL,
  CustId      VARCHAR2(60) NOT NULL,
  OrderDate   TIMESTAMP NOT NULL,
  SumTotal    NUMBER(19,4),
  Status      CHAR(4),
  constraint  pk_orders primary key (CustId, OrderId)
 ) TABLESPACE customers_tsp;
 
 
SQL> CREATE TABLE LineItems
(
  OrderId     INTEGER NOT NULL,
  CustId      VARCHAR2(60) NOT NULL,
  ProductId   INTEGER NOT NULL,
  Price       NUMBER(19,4),
  Qty         NUMBER,
  constraint  pk_items primary key (CustId, OrderId, ProductId)
) TABLESPACE customers_tsp;
 

SQL> CREATE TABLE "PRODUCTS"
   (    "PRODUCTID" NUMBER(*,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE,
    "NAME" VARCHAR2(128),
    "DESCRURI" VARCHAR2(128),
    "LASTPRICE" NUMBER(19,4),
     PRIMARY KEY ("PRODUCTID")
)  TABLESPACE USERS;

How to create a target distributed database environment for testing

create tablespace set tsp_set_1 using template (datafile size 100m autoextend on next 10M maxsize unlimited extent
management local segment space management auto) in shardspace shardspaceora;
 
create tablespace products_tsp datafile size 100m autoextend on;
 
create user app_schema identified by xxxxxx;
grant connect, resource, alter session to app_schema;
 
grant create view, create database link, alter database link, create materialized view, create tablespace to app_schema;
 
grant unlimited tablespace to app_schema;
 
Tables creation:
 
sqlplus app_schema/<password>@SCPDB
 
alter session enable shard ddl;
 
CREATE SHARDED TABLE Customers
(
  CustId      VARCHAR2(60) NOT NULL,
  FirstName   VARCHAR2(60),
  LastName    VARCHAR2(60),
  Class       VARCHAR2(10),
  Geo         VARCHAR2(8),
  CustProfile VARCHAR2(4000),
  CONSTRAINT pk1_customers PRIMARY KEY (CustId)
) TABLESPACE SET tsp_set_1
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
 
CREATE SHARDED TABLE Orders
(
  OrderId     INTEGER NOT NULL,
  CustId      VARCHAR2(60) NOT NULL,
  OrderDate   TIMESTAMP NOT NULL,
  SumTotal    NUMBER(19,4),
  Status      CHAR(4),
  constraint  pk_orders primary key (CustId, OrderId),
  constraint  fk_orders_parent foreign key (CustId) 
    references Customers on delete cascade
) partition by reference (fk_orders_parent);
 
 
CREATE SHARDED TABLE LineItems
(
  OrderId     INTEGER NOT NULL,
  CustId      VARCHAR2(60) NOT NULL,
  ProductId   INTEGER NOT NULL,
  Price       NUMBER(19,4),
  Qty         NUMBER,
  constraint  pk_items primary key (CustId, OrderId, ProductId),
  constraint  fk_items_parent foreign key (CustId, OrderId)
    references Orders on delete cascade
) partition by reference (fk_items_parent);
 
CREATE DUPLICATED TABLE Products
(
  ProductId  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  Name       VARCHAR2(128),
  DescrUri   VARCHAR2(128),
  LastPrice  NUMBER(19,4)
) TABLESPACE PRODUCTS_TSP;