Using Oracle Data Pump for Automatic Per Table Instantiation

Oracle GoldenGate uses the Oracle Data Pump utility to create new instances of a schema, table, user for the Oracle database. It works by unloading data and metadata into a set of operating system files that are called a dump file set.

This method uses the Oracle Data Pump utility to establish the target data. To run this method, you start Extract, the data pumps, and replicates at the SCN at which the copy stopped. Transactions that were included in the copy are skipped to avoid collisions from integrity violations. From the process start point, Oracle GoldenGate maintains data synchronization.

No initial-load Oracle GoldenGate processes are required for this method.

You can automatically instantiate per table CSN filtering for Oracle Database with Oracle data pump, which avoids having all of your tables at the same SCN.

On the Source Database

Use ADD TRANDATA and ADD SCHEMATRANDATA. ADD TRANDATA/SCHEMATRANDATA.PREPARECSN automatically prepares the tables at the source so the Oracle data pump export dump file includes instantiation CSNs. Replicat uses the per table instantiation CSN set by the Oracle data pump (on import) to filter out trail records.
Use INFO TRANDATA to make sure that your table is prepared for instantiation and at what point it was done. Here's a sample of the report file:
```
2025-09-29 15:30:00 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema
      hr
```
Stop Replicat on the target database.
Start Extract with the correct TABLE statement.

The EXPORT datapump option FLASHBACK_SCN is not needed as the tables have been prepared earlier.

On the Target Database

Import your exported tables using Oracle data pump, which populates system tables and views with instantiation SCNs, as well as the specified table data.
Start Replicat using one of the following:

Set the DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter in the Replicat parameter file to enable table-level instantiation filtering.

You can remove this parameter when replicat has processed all transactions beyond the instantiation SCN.

For all other Replicats, set the DBOPTIONS source_dbase_name global_name parameter in the Replicat parameter file where global_name is the global name of the Oracle source database that the trail is coming from.

Note:

When the source has no DOMAIN, do not specify a DOMAIN for the downstream database.
Replicat queries the instantiation SCN on any new mapping and filter records accordingly. For example, see the following report file output:
```
2025-06-29 17:12:39 INFO OGG-10155 Oracle GoldenGate Delivery for Oracle, r1.prm:
Instantiation CSN filtering is enabled on table HR.EMPLOYEES at CSN 1,851,797.
```

You can use other methods for instantiation instead of using the data pump to export and import tables also. One such method is using the create table as a select command or RMAN. It's steps are:

Use create table with an at SCN of parameter, using the following command:

SET_INSTANTIATION_CSN SCN for object from global_name
For example:
```
SET_INSTANTIATION_CSN 1 FOR u1.t1 FROM DBS1.REGRESS.RDBMS.DEV.US.ORACLE.COM
```
If you want to remove the manual setting of the instantiation CSN later, you can use the following command:
```
CLEAR_INSTANTIATION_CSN for object from global_name
```