Instantiate an Initial Load Extract Using SOURCEISTABLE

Use this procedure for an initial load Extract with the SOURCEISTABLE parameter. This procedure is not meant for instantiation using Oracle Data Pump or any precise instantiation method. This procedure uses HANDLECOLLISIONS instead of precise positioning so that the source database can remain active while the initial data is loaded.

The examples use an Oracle source and target, the HR schema, and these trail names:

Note: Replace the example aliases, URLs, hosts, ports, schema names, and trail names with values from your environment.

Step 1: Create and start the primary TRANLOG Extract

Connect to the source deployment. For an Oracle source, enable supplemental logging, create and register the integrated primary Extract, and start it before beginning the initial load. Record the registration SCN returned by REGISTER EXTRACT; do not rerun the command only to retrieve that value.

ADMINCLIENT: Source deployment
OGG (not connected) 1> CONNECT https://<source-admin-url> AS <deployment-user> PASSWORD "<deployment-password>"
OGG (https://<source-admin-url> source_deployment) > DBLOGIN USERIDALIAS <source-db-alias>
OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > ADD SCHEMATRANDATA hr

If logging is required for selected tables instead of the entire schema, use ADD TRANDATA for those tables, for example:

ADD TRANDATA hr.*

Create and register the primary Extract, then create its parameter file:

OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > ADD EXTRACT extprim INTEGRATED TRANLOG BEGIN NOW
OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > REGISTER EXTRACT extprim DATABASE
OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > EDIT PARAMS extprim

Primary Extract parameter file (EXTPRIM):

EXTRACT extprim
USERIDALIAS <source-db-alias>
EXTTRAIL AA
TABLE hr.*;

Create the local source trail and start the primary Extract:

OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > ADD EXTTRAIL AA EXTRACT extprim MEGABYTES 2000
OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > START EXTRACT extprim

For a container database, use the appropriate three-part object names or a valid SOURCECATALOG statement. For other source database types, use the database-specific registration, logging, and start-position syntax.

Step 2: Create and start the initial-load Extract

The initial-load Extract is a task Extract. It reads the source tables and writes the records to the local initial-load trail CC. The Oracle-specific SQLPREDICATE "AS OF SCN" clause selects the table contents at the chosen consistent point.

Before starting the initial-load Replicat, make sure that all transactions that were open when the primary TRANLOG Extract was started have committed. Transactions that remain open are not captured by either the initial-load Extract or the primary TRANLOG Extract and can be lost.

Create the initial-load Extract and its parameter file:

ADMINCLIENT: Source deployment
OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > ADD EXTRACT extinit SOURCEISTABLE
OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > EDIT PARAMS extinit

Initial-load Extract parameter file (EXTINIT):

EXTRACT extinit
USERIDALIAS <source-db-alias>
EXTFILE CC MEGABYTES 2000 PURGE
TABLE hr.*, SQLPREDICATE "AS OF SCN <instantiation-scn>";

Starting with Oracle GoldenGate 26ai, a wildcarded TABLE statement in an initial-load Extract does not resolve views. To include a view, specify it separately without a wildcard, for example:

TABLE hr.employees_view;

Start and monitor the initial-load Extract:

OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > START EXTRACT extinit
OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > INFO EXTRACT extinit DETAIL
OGG (https://<source-admin-url> source_deployment as <source-db-alias>) > VIEW REPORT extinit

Step 3: Create and start the Distribution Service paths

Create the paths from the source deployment. The SOURCE URI identifies the source Distribution Service and local trail. The TARGET URI identifies the target Receiver Service and target trail. The source and target trail names must match the trails created for the corresponding stream.

Create the path for ongoing change data (AA to BB):

ADMINCLIENT: Source deployment
OGG (https://<source-admin-url> source_deployment) > ADD DISTPATH aabb SOURCE trail://<source-dist-host>:<source-dist-port>/services/v2/sources?trail=AA TARGET wss://<target-recv-host>:<target-recv-port>/services/v2/targets?trail=BB
OGG (https://<source-admin-url> source_deployment) > START DISTPATH aabb
OGG (https://<source-admin-url> source_deployment) > INFO DISTPATH aabb DETAIL

Create the path for initial-load data (CC to DD):

OGG (https://<source-admin-url> source_deployment) > ADD DISTPATH ccdd SOURCE trail://<source-dist-host>:<source-dist-port>/services/v2/sources?trail=CC TARGET wss://<target-recv-host>:<target-recv-port>/services/v2/targets?trail=DD
OGG (https://<source-admin-url> source_deployment) > START DISTPATH ccdd
OGG (https://<source-admin-url> source_deployment) > INFO DISTPATH ccdd DETAIL

For a secured target that requires path authentication, append the following clause to each ADD DISTPATH command:

AUTHENTICATION USERIDALIAS <target-path-alias> DOMAIN <target-path-domain>

Omit DOMAIN when the alias is in the default domain. The alias and domain must be configured for target-path authentication in the source deployment.

If you use the legacy ogg protocol instead of ws or wss, also specify the appropriate TARGETTYPE option on the ADD DISTPATH command.

Step 4: Create and start the initial-load Replicat

Connect to the target deployment, create the checkpoint table, and add a non-integrated initial-load Replicat that reads the target trail DD. The Replicat can be started before the initial-load trail arrives; it waits for data from DD after the Distribution Path is running.

ADMINCLIENT: Target deployment
OGG (not connected) 1> CONNECT https://<target-admin-url> AS <deployment-user> PASSWORD "<deployment-password>"
OGG (https://<target-admin-url> target_deployment) > DBLOGIN USERIDALIAS <target-db-alias>
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > ADD CHECKPOINTTABLE ggadmin.ggcheckpoint
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > ADD REPLICAT repinit EXTTRAIL DD CHECKPOINTTABLE ggadmin.ggcheckpoint
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > EDIT PARAMS repinit

Initial-load Replicat parameter file (REPINIT):

REPLICAT repinit
USERIDALIAS <target-db-alias>
MAP hr.*, TARGET hr.*;

Start and monitor the initial-load Replicat:

OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > START REPLICAT repinit
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > INFO REPLICAT repinit DETAIL

Step 5: Complete the initial load and remove one-time objects

Wait until the initial-load Extract has reached end of input, the CC to DD path has delivered all records to DD, and REPINIT has applied them. Use the following checks:

ADMINCLIENT: Source deployment
OGG (https://<source-admin-url> source_deployment) > INFO EXTRACT extinit DETAIL
OGG (https://<source-admin-url> source_deployment) > INFO DISTPATH ccdd DETAIL
ADMINCLIENT: Target deployment
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > INFO REPLICAT repinit DETAIL
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > SEND REPLICAT repinit, LOGEND

After validating the initial-load results, remove only the one-time path and process objects. Keep the primary Extract EXTPRIM and primary path AABB.

From the source deployment:

STOP DISTPATH ccdd
DELETE DISTPATH ccdd
STOP EXTRACT extinit
DELETE EXTRACT extinit

From the target deployment, stop REPINIT if it is still running, then delete it:

STOP REPLICAT repinit
DELETE REPLICAT repinit

DELETE EXTRACT requires the appropriate source database login for Oracle, and DELETE REPLICAT requires the target database login. Deleting a process group does not necessarily delete its parameter file or trail files; manage retained artifacts through the deployment’s supported administration operations.

Step 6: Create and start the primary Replicat with HANDLECOLLISIONS

Create the primary Replicat on the target. This procedure uses HANDLECOLLISIONS while the primary Replicat applies changes that may overlap with the initial-load data.

For a HANDLECOLLISIONS workflow, use a non-integrated primary Replicat where supported. Do not use HANDLECOLLISIONS with an integrated Oracle Replicat; use a precise instantiation method when an integrated Replicat is required.

ADMINCLIENT: Target deployment
OGG (https://<target-admin-url> target_deployment) > DBLOGIN USERIDALIAS <target-db-alias>
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > ADD REPLICAT repprim EXTTRAIL BB CHECKPOINTTABLE ggadmin.ggcheckpoint
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > EDIT PARAMS repprim

Primary Replicat parameter file (REPPRIM):

REPLICAT repprim
USERIDALIAS <target-db-alias>
HANDLECOLLISIONS
MAP hr.*, TARGET hr.*;

Start and monitor the primary Replicat:

OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > START REPLICAT repprim
OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > INFO REPLICAT repprim DETAIL
OGG (https://<source-admin-url> source_deployment) > INFO DISTPATH aabb DETAIL

After the primary Replicat has caught up and processed the changes queued during the initial load, disable HANDLECOLLISIONS:

OGG (https://<target-admin-url> target_deployment as <target-db-alias>) > SEND REPLICAT repprim, NOHANDLECOLLISIONS

SEND REPLICAT ... NOHANDLECOLLISIONS disables collision handling for the current run but does not remove HANDLECOLLISIONS from the parameter file. Remove HANDLECOLLISIONS from the parameter file before the next restart, or add NOHANDLECOLLISIONS before the affected MAP statements.