dbi Blog

When migrating a large database with GoldenGate, most of the volume is sometimes in old partitions which no longer change. A common shortcut is to move them first with expdp/impdp, even before GoldenGate is set up. Everything else, the active partitions and the non-partitioned tables, is exported once the extract is running, with PREPARECSN set. Instantiation filtering then makes the replicat skip what Data Pump already loaded.

However, there is an important aspect here: dba_apply_instantiated_objects has one row per table. Every impdp overwrites it, and there is no way for GoldenGate or Data Pump to know which import should be considered the correct one for populating the dba_apply_instantiated_objects view. The old partitions and the live data of a table share that row, so in a multi-import initial load the last import always wins.

Standard scenario: importing old partitions first

The old partitions of all partitioned tables are moved first, in one dump, before GoldenGate is involved. Since no GoldenGate extract exists yet, PREPARECSN has not run either:

$ expdp sales/***@PDB1 dumpfile=old_part.dmp tables=SALES.ORDERS_PART:P_OLD,SALES.ITEMS_PART:P_OLD
. . exported "SALES"."ITEMS_PART":"P_OLD"                 5.523 KB       5 rows
. . exported "SALES"."ORDERS_PART":"P_OLD"                5.523 KB       5 rows
Job "SALES"."SYS_EXPORT_TABLE_01" successfully completed at Sun Oct 11 13:52:08 2026 elapsed 0 00:00:10

$ impdp sales/***@PDB2 dumpfile=old_part.dmp
. . imported "SALES"."ITEMS_PART":"P_OLD"                 5.523 KB       5 rows
. . imported "SALES"."ORDERS_PART":"P_OLD"                5.523 KB       5 rows
Job "SALES"."SYS_IMPORT_FULL_01" successfully completed at Sun Oct 11 13:52:12 2026 elapsed 0 00:00:02

Querying the instantiation view on the target returns nothing, as expected:

SQL> SELECT source_database, source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
WHERE source_object_owner = 'SALES';

no rows selected

Data Pump only populates this view when the table was prepared with PREPARECSN before the export. These first imports are invisible to GoldenGate.

Starting the extract

The extract connects to PDB1 and captures the SALES schema into the trail pt. It is added, registered and started in the adminclient, logged in to PDB1. The registration SCN is where capture begins:

OGG (http://localhost:7810 ogg_test_01) 2> DBLOGIN USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate
Successfully logged into database PDB1.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 3> ADD EXTRACT EXTPT, INTEGRATED TRANLOG, BEGIN NOW
2026-10-11T13:52:13Z  INFO    OGG-08100  Integrated Extract added.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 4> REGISTER EXTRACT EXTPT DATABASE
2026-10-11T13:52:14Z  INFO    OGG-02003  Extract group EXTPT successfully registered with database at SCN 189554050.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 5> ADD EXTTRAIL pt, EXTRACT EXTPT
2026-10-11T13:52:14Z  INFO    OGG-08100  EXTTRAIL added.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 6> VIEW PARAMS EXTPT
EXTRACT EXTPT
USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate
EXTTRAIL pt
TABLE SALES.*;

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 7> START EXTRACT EXTPT
2026-10-11T13:52:14Z  INFO    OGG-00975  Extract group EXTPT starting.
2026-10-11T13:52:14Z  INFO    OGG-15426  Extract group EXTPT started.

Some workload follows on the live data, before PREPARECSN:

SQL> INSERT INTO sales.orders_part VALUES (1001, 'ACTIVE');

1 row created.

SQL> INSERT INTO sales.orders_part VALUES (1002, 'ACTIVE');

1 row created.

SQL> INSERT INTO sales.items_part VALUES (1001, 'ACTIVE');

1 row created.

SQL> INSERT INTO sales.items_part VALUES (1002, 'ACTIVE');

1 row created.

SQL> INSERT INTO sales.customers VALUES (4, 'LIVE');

1 row created.

SQL> COMMIT;

Commit complete.

Then the schema is prepared in the adminclient. ADD SCHEMATRANDATA ... ALLCOLS sets PREPARECSN on the schema in NOWAIT mode:

OGG (http://localhost:7810 ogg_test_01) 2> DBLOGIN USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate
Successfully logged into database PDB1.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 3> ADD SCHEMATRANDATA SALES ALLCOLS
2026-10-11T13:52:15Z  INFO    OGG-01788  SCHEMATRANDATA has been added on schema "SALES".
2026-10-11T13:52:15Z  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema "SALES".
2026-10-11T13:52:15Z  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema "SALES".
2026-10-11T13:52:15Z  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema "SALES"

Exporting and importing the live data

The active partitions are exported next, in a single expdp, deliberately without FLASHBACK_SCN. Data Pump chooses a consistent SCN itself, for each table:

$ expdp sales/***@PDB1 dumpfile=active_part.dmp tables=SALES.ORDERS_PART:P_ACTIVE,SALES.ITEMS_PART:P_ACTIVE
FLASHBACK automatically enabled to preserve database integrity.
. . exported "SALES"."ITEMS_PART":"P_ACTIVE"                5.5 KB       2 rows
. . exported "SALES"."ORDERS_PART":"P_ACTIVE"               5.5 KB       2 rows
Job "SALES"."SYS_EXPORT_TABLE_01" successfully completed at Sun Oct 11 13:52:29 2026 elapsed 0 00:00:12

Live inserts after that export are therefore not in the dump:

SQL> INSERT INTO sales.orders_part VALUES (1003, 'ACTIVE');

1 row created.

SQL> INSERT INTO sales.items_part VALUES (1003, 'ACTIVE');

1 row created.

SQL> COMMIT;

Commit complete.

Every other table goes in a third export, at the schema level, excluding the partitioned tables. Any number of normal tables fits in this one export, and a new partitioned table is added to the exclusion:

$ cat rest.par
dumpfile=rest.dmp
schemas=SALES
exclude=TABLE:"IN ('ORDERS_PART','ITEMS_PART')"

$ expdp sales/***@PDB1 parfile=rest.par
FLASHBACK automatically enabled to preserve database integrity.
. . exported "SALES"."CUSTOMERS"                          5.515 KB       4 rows
Job "SALES"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Oct 11 13:52:55 2026 elapsed 0 00:00:24

A live insert after this export is not in the dump either:

SQL> INSERT INTO sales.customers VALUES (5, 'LIVE');

1 row created.

SQL> COMMIT;

Commit complete.

The partitioned tables already exist on the target, so the active partitions are appended. A regular import creates the normal tables:

$ impdp sales/***@PDB2 dumpfile=active_part.dmp table_exists_action=append
Table "SALES"."ORDERS_PART" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "SALES"."ITEMS_PART" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
. . imported "SALES"."ITEMS_PART":"P_ACTIVE"                5.5 KB       2 rows
. . imported "SALES"."ORDERS_PART":"P_ACTIVE"               5.5 KB       2 rows
Job "SALES"."SYS_IMPORT_FULL_01" successfully completed at Sun Oct 11 13:53:00 2026 elapsed 0 00:00:03

$ impdp sales/***@PDB2 dumpfile=rest.dmp
. . imported "SALES"."CUSTOMERS"                          5.515 KB       4 rows
Job "SALES"."SYS_IMPORT_FULL_01" successfully completed at Sun Oct 11 13:53:04 2026 elapsed 0 00:00:02

The instantiation view now has one row per table. The partitioned tables have SCNs from the second export, while CUSTOMERS has one from the third export:

SQL> SELECT source_database, source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
WHERE source_object_owner = 'SALES'
ORDER BY source_object_name;

SOURCE_DATABASE SOURCE_OBJECT_NAME INSTANTIATION_SCN
--------------- ------------------ -----------------
PDB1            CUSTOMERS                  189585331
PDB1            ITEMS_PART                 189572011
PDB1            ORDERS_PART                189572008

3 rows selected.

Replicat with instantiation filtering

Each instantiation SCN applies to the whole table, and we use instantiation filtering in the replicat. It is added and started in the adminclient:

OGG (http://localhost:7810 ogg_test_01) 2> DBLOGIN USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate
Successfully logged into database PDB2.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02/PDB2) 3> ADD REPLICAT REPPT, INTEGRATED, EXTTRAIL pt, CHECKPOINTTABLE OGGADMIN.CHECKPOINT
2026-10-11T13:53:05Z  INFO    OGG-08100  Integrated Replicat added.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02/PDB2) 4> VIEW PARAMS REPPT
REPLICAT REPPT
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate
MAP SALES.*, TARGET SALES.*;

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02/PDB2) 5> START REPLICAT REPPT
2026-10-11T13:53:05Z  INFO    OGG-00975  Replicat group REPPT starting.
2026-10-11T13:53:05Z  INFO    OGG-15445  Replicat group REPPT started.

Once the replicat has caught up, the target tables have the same number of rows as the source:

SQL> SELECT COUNT(*) FROM sales.orders_part;

COUNT(*)
--------
       8

1 row selected.

SQL> SELECT COUNT(*) FROM sales.items_part;

COUNT(*)
--------
       8

1 row selected.

SQL> SELECT COUNT(*) FROM sales.customers;

COUNT(*)
--------
       5

1 row selected.

In the adminclient, we can see the transaction done after creating the extract, on each table:

OGG (http://localhost:7810 ogg_test_01) 2> STATS REPLICAT REPPT, TOTAL, TABLE SALES.*

Sending STATS TOTAL TABLE SALES.* request to Replicat group REPPT ...


Start of statistics at 2026-10-11 13:53:18.


Integrated Replicat statistics:

        Total transactions                                 2.00
        Redirected                                         0.00
        Replicated procedures                              0.00
        DDL operations                                     0.00
        Stored procedures                                  0.00
        Datatype functionality                             0.00
        Operation type functionality                       0.00
        Event actions                                      0.00
        Database compatibility                             0.00
        Direct transactions ratio                          0.00%

Replicating from SALES.ORDERS_PART to PDB2.SALES.ORDERS_PART:

*** Total statistics since 2026-10-11 13:53:07 ***
    Total inserts                              1.00
    Total updates                              0.00
    Total deletes                              0.00
    Total upserts                              0.00
    Total discards                             0.00
    Total operations                           1.00

Replicating from SALES.ITEMS_PART to PDB2.SALES.ITEMS_PART:

*** Total statistics since 2026-10-11 13:53:07 ***
    Total inserts                              1.00
    Total updates                              0.00
    Total deletes                              0.00
    Total upserts                              0.00
    Total discards                             0.00
    Total operations                           1.00

Replicating from SALES.CUSTOMERS to PDB2.SALES.CUSTOMERS:

*** Total statistics since 2026-10-11 13:53:07 ***
    Total inserts                              1.00
    Total updates                              0.00
    Total deletes                              0.00
    Total upserts                              0.00
    Total discards                             0.00
    Total operations                           1.00

End of statistics.

The only inserts are the three rows added after the exports, one per table. There is no ORA-00001: the rows inserted before the exports were replayed from the trail and skipped by the instantiation filtering. Without ENABLE_INSTANTIATION_FILTERING they would have hit the primary key.

FLASHBACK_SCN is not needed here. The replicat compares commit CSNs against instantiation_scn, not against the export’s flashback point. You only need PREPARECSN to have run before the export of the live data.

Foreign keys between tables that are in different exports are something to keep in mind.

Wrong order: importing old partitions after

What happens if, for some reason, you run the initial load of the active partitions first, with PREPARECSN and instantiation filtering, and then migrate the old partitions? Let’s try this, by first adding trandata:

OGG (http://localhost:7810 ogg_test_01) 2> DBLOGIN USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate
Successfully logged into database PDB1.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 3> ADD TRANDATA SALES.ORDERS_PART PREPARECSN NOWAIT
2026-10-11T13:53:42Z  INFO    OGG-15132  Logging of supplemental redo data enabled for table SALES.ORDERS_PART.
2026-10-11T13:53:42Z  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table SALES.ORDERS_PART.
2026-10-11T13:53:42Z  INFO    OGG-15438  Enabling logical replication (with all keys) on auto capture table SALES.ORDERS_PART.
2026-10-11T13:53:42Z  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SALES.ORDERS_PART.

The old partitions are exported before the extract exists:

$ expdp sales/***@PDB1 dumpfile=b_old.dmp tables=SALES.ORDERS_PART:P_OLD
FLASHBACK automatically enabled to preserve database integrity.
. . exported "SALES"."ORDERS_PART":"P_OLD"                5.523 KB       5 rows
Job "SALES"."SYS_EXPORT_TABLE_01" successfully completed at Sun Oct 11 13:53:55 2026 elapsed 0 00:00:11

The extract is then registered and started, as in the first scenario:

OGG (http://localhost:7810 ogg_test_01) 2> DBLOGIN USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate
Successfully logged into database PDB1.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 3> ADD EXTRACT EXTPT, INTEGRATED TRANLOG, BEGIN NOW
2026-10-11T13:53:57Z  INFO    OGG-08100  Integrated Extract added.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 4> REGISTER EXTRACT EXTPT DATABASE
2026-10-11T13:54:00Z  INFO    OGG-02003  Extract group EXTPT successfully registered with database at SCN 189596053.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 5> ADD EXTTRAIL pt, EXTRACT EXTPT
2026-10-11T13:54:00Z  INFO    OGG-08100  EXTTRAIL added.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01/PDB1) 6> START EXTRACT EXTPT
2026-10-11T13:54:00Z  INFO    OGG-00975  Extract group EXTPT starting.
2026-10-11T13:54:00Z  INFO    OGG-15426  Extract group EXTPT started.

Rows 1001 and 1002 are inserted into P_ACTIVE, and the live data is exported. Row 1003 is inserted after the export:

$ expdp sales/***@PDB1 dumpfile=b_active.dmp tables=SALES.ORDERS_PART:P_ACTIVE
FLASHBACK automatically enabled to preserve database integrity.
. . exported "SALES"."ORDERS_PART":"P_ACTIVE"               5.5 KB       2 rows
Job "SALES"."SYS_EXPORT_TABLE_01" successfully completed at Sun Oct 11 13:54:09 2026 elapsed 0 00:00:08

This time, on the target, the live data is imported first:

$ impdp sales/***@PDB2 dumpfile=b_active.dmp
. . imported "SALES"."ORDERS_PART":"P_ACTIVE"               5.5 KB       2 rows
Job "SALES"."SYS_IMPORT_FULL_01" successfully completed at Sun Oct 11 13:54:14 2026 elapsed 0 00:00:02

For now, the boundary is above the registration SCN 189596053:

SQL> SELECT source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
WHERE source_object_owner = 'SALES'
AND source_object_name = 'ORDERS_PART';

SOURCE_OBJECT_NAME INSTANTIATION_SCN
------------------ -----------------
ORDERS_PART                189610870

1 row selected.

The old partitions are then imported:

$ impdp sales/***@PDB2 dumpfile=b_old.dmp table_exists_action=append
Table "SALES"."ORDERS_PART" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
. . imported "SALES"."ORDERS_PART":"P_OLD"                5.523 KB       5 rows
Job "SALES"."SYS_IMPORT_FULL_01" successfully completed at Sun Oct 11 13:54:17 2026 elapsed 0 00:00:01

If we check the table again, the boundary is now below the registration SCN, and more importantly below the instantiation SCN of the live data !

SQL> SELECT source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
WHERE source_object_owner = 'SALES'
AND source_object_name = 'ORDERS_PART';

SOURCE_OBJECT_NAME INSTANTIATION_SCN
------------------ -----------------
ORDERS_PART                189594418

1 row selected.

This means that a replicat using instantiation filtering will not filter the transactions which happened before the first import. If we add the replicat in the adminclient, and start it:

OGG (http://localhost:7810 ogg_test_01) 2> DBLOGIN USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate
Successfully logged into database PDB2.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02/PDB2) 3> ADD REPLICAT REPPT, INTEGRATED, EXTTRAIL pt, CHECKPOINTTABLE OGGADMIN.CHECKPOINT
2026-10-11T13:54:19Z  INFO    OGG-08100  Integrated Replicat added.

OGG (http://localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02/PDB2) 4> START REPLICAT REPPT
2026-10-11T13:54:19Z  INFO    OGG-00975  Replicat group REPPT starting.
2026-10-11T13:54:19Z  INFO    OGG-15445  Replicat group REPPT started.

The replicat reads the instantiation SCN 189594418 from the target. It is now older than the live data export, so the transactions that export already contains are no longer filtered, and the replicat fails on the first trail record:

OGG (http://localhost:7810 ogg_test_01) 2> INFO REPLICAT REPPT

Replicat   REPPT     Last Started 2026-10-11 13:54   Status ABENDED
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:00:19 ago)
Log Read Checkpoint  File pt000000000
                     First Record  RBA 0
Encryption Profile   LocalWallet

The replicat report shows the boundary and the error:

2026-10-11 13:54:25  INFO    OGG-10155  Instantiation CSN filtering is enabled on table SALES.ORDERS_PART at CSN 189,594,418.
2026-10-11 13:54:31  WARNING OGG-02544  Unhandled error (ORA-00001: unique constraint (.) violated
ORA-00001: unique constraint (SALES.SYS_C0010450) violated) while processing the record at SEQNO 0, RBA 1793 in Integrated mode. REPLICAT will retry in Direct mode.

1001 and 1002 were already on the target from the live-data import, but their commit CSNs are now above the boundary.

Conclusion

instantiation_scn is per table, not per partition. The last impdp will decide the boundary being used by instantiation filtering.
impdp overwrites it unconditionally, with no warning.
When using instantiation filtering, always import the dump which should contain the boundary last.
Keep in mind that, depending on the context of your real replication, another instantiation method might be needed. Foreign keys, DDL replication, or many aspects a GoldenGate replication might need to address, were not covered in this blog.

Import order matters for GoldenGate instantiation filtering

Standard scenario: importing old partitions first

Starting the extract

Exporting and importing the live data

Replicat with instantiation filtering

Wrong order: importing old partitions after

Conclusion

Julien Delattre

Leave a Reply:

Related blog articles

Standard scenario: importing old partitions first

Starting the extract

Exporting and importing the live data

Replicat with instantiation filtering

Wrong order: importing old partitions after

Conclusion

Julien Delattre

Leave a Reply:

Related blog articles

Removing instantiation filtering from a GoldenGate replicat

GoldenGate 26ai AutoSchema TYPEMAP: overriding the default datatype mapping

Archiving 5 TB from Oracle into SQL Server: views, indexes and compression (part 2)

GoldenGate AI Service bug confirmed in the Maximum Input Characters setting