Add Initial Load Extract Using the Admin Client

Step 1: Create 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.
For MySQL, precise instantiation is applicable only for MySQL source and target databases, and is implemented using the Dump utility of the MySQL shell. For more information on the Dump utility, see MySQL Dump Utility.

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

Command:

OGG (not connected) 1> CONNECT https://oggdep.example.com:9100 as oggadmin password oggadmin !

Output:

Using default deployment 'OGGDEP'

Command:

OGG (https://oggdep.example.com:9100 OGGDEP) 2> DBLOGIN USERIDALIAS oggadmin

Output:

Successfully logged into database.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP) 3> ADD EXTRACT extprim INTEGRATED TRANLOG BEGIN NOW

Output:

2018-03-16T13:37:07Z INFO OGG-08100 EXTRACT (Integrated) added.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 4> REGISTER EXTRACT extprim DATABASE

Output:

2018-03-16T13:37:30Z INFO OGG-02003 Extract EXTPRIM successfully registered with database at SCN 1608891.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 5> EDIT PARAMS extprim

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 6> VIEW PARAMS extprim

Output:

--
-- E X T P R I M . p r m
-- Primary Extract Parameter File
--
Extract EXTPRIM
UseridAlias oggadmin
ExtTrail AA
Table user01.*;

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 7> ADD EXTTRAIL aa EXTRACT extprim

Output:

2018-03-16T13:37:55Z INFO OGG-08100 EXTTRAIL added.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 8> START EXTRACT extprim

Output:

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://oggdep.example.com:9100 OGGDEP as oggadmin) 4> REGISTER EXTRACT extprim DATABASE

Output:

2018-03-16T13:37:30Z INFO OGG-02003 Extract EXTPRIM successfully registered with database at SCN 1608891.

Step 2: Determine the Instantiation SCN

The Administration Service in Oracle GoldenGate Microservices Architecture, 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 OGGADMIN 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://oggdep.example.com:9100 OGGDEP 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: Create and Start 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):

Command:

OGG (not connected) 1> CONNECT https://oggdep.example.com:9100 as oggadmin password oggadmin !

Output:

Using default deployment 'OGGDEP'

Command:

OGG (https://oggdep.example.com:9100 OGGDEP) 2> DBLOGIN USERIDALIAS oggadmin

Output:

Successfully logged into database.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 3> ADD CHECKPOINTTABLE oggadmin.checkpoints

Output:

ADD "oggadmin.checkpoints" succeeded.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 4> ADD REPLICAT repinit EXTTRAIL dd CHECKPOINTTABLE oggadmin.checkpoints

Output:

2018-03-16T13:56:41Z INFO OGG-08100 REPLICAT added.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 5> EDIT PARAMS repinit

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 6> VIEW PARAMS repinit

Output:

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

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 7> START REPLICAT repinit

Output:

2018-03-16T13:58:21Z  INFO    OGG-00975  REPLICAT REPINIT starting
2018-03-16T13:58:21Z  INFO    OGG-15426  REPLICAT REPINIT started

Step 4: Create and start 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.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 15> ADD EXTRACT extinit SOURCEISTABLE sourceistable

Output:

2018-03-16T14:08:38Z INFO OGG-08100 EXTRACT added.

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 16> EDIT PARAMS extinit

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 17> VIEW PARAMS extinit

Output:

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

Command:

OGG (https://oggdep.example.com:9100 OGGDEP as oggadmin) 18> START EXTRACT extinit

Output:

2018-03-16T14:13:42Z  INFO    OGG-00975  EXTRACT EXTINIT starting
2018-03-16T14:13:42Z  INFO    OGG-15426  EXTRACT EXTINIT started

Step 5: Create the Distribution Paths

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

Command:

OGG (https://oggdep.example.com:9100 oggdep) 15> ADD DISTPATH aabb SOURCE TRAIL://oggdep.example.com:9102/services/v2/sources?trail=AA target wss://dallas.oggdevops.us:9103/services/v2/targets?trail=BB

Output:

2018-03-16T17:28:27Z INFO OGG-08511 The path 'AABB' has been added.

Command:

OGG (https://oggdep.oggdevops.us:9100 oggdep) 16> ADD DISTPATH ccdd SOURCE TRAIL://oggdep.example.com:9102/services/v2/sources?trail=CC target wss://dallas.oggdevops.us:9103/services/v2/targets?trail=DD

Output:

2018-03-16T17:28:35Z INFO OGG-08511 The path 'CCDD' has been added.

Command:

OGG (https://oggdep.example:9100 oggdep) 17> START DISTPATH aabb

Output:

2018-03-16T17:28:42Z INFO OGG-08513 The path 'AABB' has been started.

Command:

OGG (https://oggdep.example.com:9100 oggdep) 18> START DISTPATH ccdd

Output:

2018-03-16T17:28:47Z INFO OGG-08513 The path 'CCDD' has been started.

If you use the ogg protocol instead of wss, then you must use the TARGETTYPE option. The syntax in that case would be:

ADD DISTPATH path-name SOURCE source-uri TARGET target-uri [ TARGETTYPE ( MANAGER | COLLECTOR | RECVSRVR ) ]

TARGETTYPE specifies the target type in case the distribution path uses the legacy protocol. This argument is only valid if the target URI schema is ogg.

Step 6: Create the Primary Replicat

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.

Command:

OGG (https://oggdep.example.com:9100 oggdep as oggadmin) 12> ADD REPLICAT repprim EXTTRAIL bb CHECKPOINTTABLE oggadmin.checkpoints

Output:

2018-03-16T17:37:46Z INFO OGG-08100 REPLICAT added.

Command: EDIT PARAMS

OGG (https://oggdep.example.com:9100 oggdep as oggadmin) 13> EDIT PARAMS repprim

Command:

OGG (https://oggdep.example.com:9100 oggdep as oggadmin) 14> VIEW PARAMS repprim

Output:

--
--  R E P P R I M . p r m
--  Replicat Parameter File
--
Replicat    REPPRIM
USERIDALIAS oggadmin
Map         user01.*
  Target    user01.*;

Command:

OGG (https://oggdep.example.com:9100 oggdep as oggadmin) 15> START REPLICAT repprim ATCSN 1624963

Output:

2018-03-16T17:38:10Z  INFO    OGG-00975  REPLICAT REPPRIM starting
2018-03-16T17:38:10Z  INFO    OGG-15426  REPLICAT REPPRIM started