Loading Data from File to Replicat in Microservices Architecture

9 Loading Data from File to Replicat in Microservices Architecture

By following the steps provided in this topic, data can be precisely replicated from a source to a target database with zero data loss using a combination of file-based initial load and change data capture (CDC) processes.

In Loading Data from File to Replicat, the initial load process is implemented using files. However, Microservices Architecture uses a different approach. The process of creating and running a replication solution constitutes:

Initial Load: Used to copy the existing contents of one or more tables from the source to the target database.
Change Data Capture: Used to copy transactional changes from the source to the target database.

Note:

MA doesn’t support loading data with an Oracle GoldenGate direct load.

Process for Loading Data from File to Replicat in Microservices Architecture

File-based initial load process is the preferred method for performing data replication in MA. It’s key components are:

Initial Load Extract and Replicat: Replicates the existing content of the database tables.
Primary Extract and Replicat: Replicates change data from the database tables.
Distribution Paths: Transfers trail files to the target system.

Before you begin, make sure that the database credential alias is created.

Important:

This topic demonstrates the steps for initial load processing using the AdminClient. However, you can also use curl to perform these steps.

Step 1: Creating a Primary Extract

Precise instantiation is used to replicate database resources correctly from the source to the target database. The primary Extract is started first to initiate change data capture early. Precise instantiation is based on the following assumptions:

Note:

For precise instantiation to work, the instantiation SCN must come after the registration SCN.

The primary Extract is started. It is responsible for change data capture and noting it’s registration SCN.
The database is monitored. The database waits for the oldest open transaction’s SCN to come after the registration SCN. This is the instantiation SCN.
The instantiation SCN is used when creating the initial load Extract and Replicat processes.
The instantiation SCN is used to create the primary Replicat, once the initial load replication is complete.

To begin, create and start the primary Extract EXTPRIM from the AdminClient, as shown in the following example:

OGG (not connected) 1> connect https://phoenix.oggdevops.us:9100 as oggadmin password oggadmin !
Using default deployment 'Phoenix'

OGG (https://phoenix.oggdevops.us:9100 Phoenix) 2> dblogin useridalias oggadmin
Successfully logged into database.

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 3> add extract EXTPRIM integrated tranlog begin now
2018-03-16T13:37:07Z  INFO    OGG-08100  EXTRACT (Integrated) added.

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 4> register extract EXTPRIM database
2018-03-16T13:37:30Z  INFO    OGG-02003  Extract EXTPRIM successfully registered with database at SCN 1608891.

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 5> edit params EXTPRIM

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 6> view params EXTPRIM
--
--  E X T P R I M . p r m
--  Primary Extract Parameter File
--
Extract     EXTPRIM
UseridAlias oggadmin
ExtTrail    AA
Table       user01.*;

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 7> add exttrail AA extract EXTPRIM
2018-03-16T13:37:55Z  INFO    OGG-08100  EXTTRAIL added.

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 8> start extract EXTPRIM
2018-03-16T13:38:02Z  INFO    OGG-00975  EXTRACT EXTPRIM starting
2018-03-16T13:38:02Z  INFO    OGG-15426  EXTRACT EXTPRIM started

In this example, oggadmin is the database credential alias.

After creating the primary Extract, retrieve the SCN registration number. Run the REGISTER EXTRACT command in the AdminClient. The following example retrieves an SCN value of 1608891.

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 4> register extract EXTPRIM database
2018-03-16T13:37:30Z  INFO    OGG-02003  Extract EXTPRIM successfully registered with database at SCN 1608891.

Step 2: Determining the Instantiation SCN

In Oracle GoldenGate 19c (19.1.0) and later, the Administration Server provides an endpoint for retrieving information about open database transactions. This information can be used to identify the SCN to use when instantiating the initial load extract. In the following example, the instantiation SCN is 1609723, which is the oldest SCN of all open transactions that is also past the registration SCN of 1608891, identified in the previous step.

-- 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;

The results of this query can be used to determine the instantiation SCN. The results for this specific query are:

1538916  ACTIVE  2018-03-16 18:10:31.0  3865  9176  1  GGADMIN   oracle  INACTIVE  2018-03-16 18:10:26.0 1540555  CURRENT  2018-03-16 18:21:50.0    SYS

The SCN used to instantiate the initial load Extract is obtained using SQL*Plus. In the following example, the SQL query uses the instantiation SCN value as 1624963, which is the oldest SCN of all open transactions that are also past the registration SCN of 1608891.

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 14> shell echo 'Select MIN(START_SCN) From gv$transaction;' | ${ORACLE_HOME}/bin/sqlplus -S / as sysdba

MIN(START_SCN)
--------------
       1624963

If there are no open transactions, then this SQL query returns an empty result. A detailed query that takes into account the situation where there are no open transactions is:

Select MIN(SCN) as INSTANTIATION_SCN
  From (Select MIN(START_SCN) as SCN
          From gv$transaction
         Union All
        Select current_scn
          From gv$database);

Step 3: Creating and Starting the Initial Load Replicat

Before you begin this step, make sure that the checkpoint table oggadmin.checkpoints, already exists on the target system. The initial load Replicat is responsible for populating the target database. Run the following command on the AdminClient to create and start the initial load Replicat (REPINIT):

OGG (not connected) 1> connect https://dallas.oggdevops.us:9100 as oggadmin password oggadmin !
Using default deployment 'Dallas'

OGG (https://dallas.oggdevops.us:9100 Dallas) 2> dblogin useridalias oggadmin
Successfully logged into database.

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 3> add checkpointtable oggadmin.checkpoints
ADD "oggadmin.checkpoints" succeeded.

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 4> add replicat REPINIT exttrail DD checkpointtable oggadmin.checkpoints
2018-03-16T13:56:41Z  INFO    OGG-08100  REPLICAT added.

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 5> edit params REPINIT

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 6> view params REPINIT
--
--  R E P I N I T . p r m
--  File-Based Initial Load Replicat Parameter File
--
Replicat    REPINIT
UseridAlias oggadmin
Map         user01.*
  Target    user01.*;

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 7> start replicat REPINIT
2018-03-16T13:58:21Z  INFO    OGG-00975  REPLICAT REPINIT starting
2018-03-16T13:58:21Z  INFO    OGG-15426  REPLICAT REPINIT started

Step 4: Creating and starting the Initial Load Extract

Using the instantiation SCN that you retrieved (1624963), the initial load Extract is created to write contents of the database tables to the trail.

Create and start the initial load extract, EXTINIT.

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 15> add extract EXTINIT sourceistable
2018-03-16T14:08:38Z  INFO    OGG-08100  EXTRACT added.

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 16> edit params EXTINIT

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 17> view params EXTINIT
--
--  E X T I N I T . p r m
--  File-Based Initial Load Extract Parameter File
--
Extract     EXTINIT
UseridAlias oggadmin
ExtFile     CC Megabytes 2000 Purge
Table       user01.*, SQLPredicate "As Of SCN 1609723";

OGG (https://phoenix.oggdevops.us:9100 Phoenix as oggadmin) 18> start extract EXTINIT
2018-03-16T14:13:42Z  INFO    OGG-00975  EXTRACT EXTINIT starting
2018-03-16T14:13:42Z  INFO    OGG-15426  EXTRACT EXTINIT started

Step 5: Creating the Distribution Paths

Create two distribution paths (AABB and CCDD) for copying the local trails to the remote host from the AdminClient:

OGG (https://phoenix.oggdevops.us:9100 Phoenix) 15> add distpath AABB source trail://phoenix.oggdevops.us:9102/services/v2/sources?trail=AA target wss://dallas.oggdevops.us:9103/services/v2/targets?trail=BB
2018-03-16T17:28:27Z  INFO    OGG-08511  The path 'AABB' has been added.

OGG (https://phoenix.oggdevops.us:9100 Phoenix) 16> add distpath CCDD source trail://phoenix.oggdevops.us:9102/services/v2/sources?trail=CC target wss://dallas.oggdevops.us:9103/services/v2/targets?trail=DD
2018-03-16T17:28:35Z  INFO    OGG-08511  The path 'CCDD' has been added.

OGG (https://phoenix.oggdevops.us:9100 Phoenix) 17> start distpath AABB
2018-03-16T17:28:42Z  INFO    OGG-08513  The path 'AABB' has been started.

OGG (https://phoenix.oggdevops.us:9100 Phoenix) 18> start distpath CCDD
2018-03-16T17:28:47Z  INFO    OGG-08513  The path 'CCDD' has been started.

Step 6: Creating the Primary Replicat REPPRIM

Once the initial load Extract and Replicat complete, they can be deleted. Then, the primary Replicat process is created on the remote host for applying change data to the target database.

Use the AdminClient to create the primary Replicat process.

Note:

The primary Replicat is started at the instantiation SCN.

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 12> add replicat REPPRIM exttrail BB checkpointtable oggadmin.checkpoints
2018-03-16T17:37:46Z  INFO    OGG-08100  REPLICAT added.

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 13> edit params REPPRIM

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 14> view params REPPRIM
--
--  R E P P R I M . p r m
--  Replicat Parameter File
--
Replicat    REPPRIM
UseridAlias oggadmin
Map         user01.*
  Target    user01.*;

OGG (https://dallas.oggdevops.us:9100 Dallas as oggadmin) 15> start replicat REPPRIM atcsn 1624963
2018-03-16T17:38:10Z  INFO    OGG-00975  REPLICAT REPPRIM starting
2018-03-16T17:38:10Z  INFO    OGG-15426  REPLICAT REPPRIM started

Parent topic: Administering Oracle GoldenGate Microservices Architecture