Add Initial Load Extract Using the Admin Client
Learn about adding the 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.
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