{"id":47906,"date":"2026-10-15T17:37:47","date_gmt":"2026-10-15T15:37:47","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=47906"},"modified":"2026-10-15T17:37:49","modified_gmt":"2026-10-15T15:37:49","slug":"import-order-matters-for-goldengate-instantiation-filtering","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/","title":{"rendered":"Import order matters for GoldenGate instantiation filtering"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">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 <code>expdp<\/code>\/<code>impdp<\/code>, even before GoldenGate is set up. Everything else, the active partitions and the non-partitioned tables, is exported once the extract is running, with <code>PREPARECSN<\/code> set. <a href=\"https:\/\/www.dbi-services.com\/blog\/goldengate-instantiation-csn-explained\/\" target=\"_blank\" rel=\"noopener noreferrer\">Instantiation filtering<\/a> then makes the replicat skip what Data Pump already loaded.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, there is an important aspect here: <code>dba_apply_instantiated_objects<\/code> has <strong>one row per table<\/strong>. Every <code>impdp<\/code> 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 <code>dba_apply_instantiated_objects<\/code> 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.<\/p>\n\n\n\n<h2 id=\"standard-scenario-importing-old-partitions-first\" class=\"wp-block-heading\">Standard scenario: importing old partitions first<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The old partitions of all partitioned tables are moved first, in one dump, before GoldenGate is involved. Since no GoldenGate extract exists yet, <code>PREPARECSN<\/code> has not run either:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ expdp sales\/***@PDB1 dumpfile=old_part.dmp tables=SALES.ORDERS_PART:P_OLD,SALES.ITEMS_PART:P_OLD\n. . exported \"SALES\".\"ITEMS_PART\":\"P_OLD\"                 5.523 KB       5 rows\n. . exported \"SALES\".\"ORDERS_PART\":\"P_OLD\"                5.523 KB       5 rows\nJob \"SALES\".\"SYS_EXPORT_TABLE_01\" successfully completed at Sun Oct 11 13:52:08 2026 elapsed 0 00:00:10\n\n$ impdp sales\/***@PDB2 dumpfile=old_part.dmp\n. . imported \"SALES\".\"ITEMS_PART\":\"P_OLD\"                 5.523 KB       5 rows\n. . imported \"SALES\".\"ORDERS_PART\":\"P_OLD\"                5.523 KB       5 rows\nJob \"SALES\".\"SYS_IMPORT_FULL_01\" successfully completed at Sun Oct 11 13:52:12 2026 elapsed 0 00:00:02<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Querying the instantiation view on the target returns nothing, as expected:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT source_database, source_object_name, instantiation_scn\nFROM dba_apply_instantiated_objects\nWHERE source_object_owner = 'SALES';\n\nno rows selected<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Data Pump only populates this view when the table was prepared with <code>PREPARECSN<\/code> before the export. These first imports are invisible to GoldenGate.<\/p>\n\n\n\n<h3 id=\"starting-the-extract\" class=\"wp-block-heading\">Starting the extract<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The extract connects to <code>PDB1<\/code> and captures the <code>SALES<\/code> schema into the trail <code>pt<\/code>. It is added, registered and started in the <code>adminclient<\/code>, logged in to <code>PDB1<\/code>. The registration SCN is where capture begins:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>OGG (http:\/\/localhost:7810 ogg_test_01) 2&gt; DBLOGIN USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate\nSuccessfully logged into database PDB1.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 3&gt; ADD EXTRACT EXTPT, INTEGRATED TRANLOG, BEGIN NOW\n2026-10-11T13:52:13Z  INFO    OGG-08100  Integrated Extract added.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 4&gt; REGISTER EXTRACT EXTPT DATABASE\n2026-10-11T13:52:14Z  INFO    OGG-02003  Extract group EXTPT successfully registered with database at SCN 189554050.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 5&gt; ADD EXTTRAIL pt, EXTRACT EXTPT\n2026-10-11T13:52:14Z  INFO    OGG-08100  EXTTRAIL added.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 6&gt; VIEW PARAMS EXTPT\nEXTRACT EXTPT\nUSERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate\nEXTTRAIL pt\nTABLE SALES.*;\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 7&gt; START EXTRACT EXTPT\n2026-10-11T13:52:14Z  INFO    OGG-00975  Extract group EXTPT starting.\n2026-10-11T13:52:14Z  INFO    OGG-15426  Extract group EXTPT started.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Some workload follows on the live data, before <code>PREPARECSN<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; INSERT INTO sales.orders_part VALUES (1001, 'ACTIVE');\n\n1 row created.\n\nSQL&gt; INSERT INTO sales.orders_part VALUES (1002, 'ACTIVE');\n\n1 row created.\n\nSQL&gt; INSERT INTO sales.items_part VALUES (1001, 'ACTIVE');\n\n1 row created.\n\nSQL&gt; INSERT INTO sales.items_part VALUES (1002, 'ACTIVE');\n\n1 row created.\n\nSQL&gt; INSERT INTO sales.customers VALUES (4, 'LIVE');\n\n1 row created.\n\nSQL&gt; COMMIT;\n\nCommit complete.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Then the schema is prepared in the <code>adminclient<\/code>. <code>ADD SCHEMATRANDATA ... ALLCOLS<\/code> sets <code>PREPARECSN<\/code> on the schema in <code>NOWAIT<\/code> mode:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>OGG (http:\/\/localhost:7810 ogg_test_01) 2&gt; DBLOGIN USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate\nSuccessfully logged into database PDB1.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 3&gt; ADD SCHEMATRANDATA SALES ALLCOLS\n2026-10-11T13:52:15Z  INFO    OGG-01788  SCHEMATRANDATA has been added on schema \"SALES\".\n2026-10-11T13:52:15Z  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema \"SALES\".\n2026-10-11T13:52:15Z  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema \"SALES\".\n2026-10-11T13:52:15Z  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema \"SALES\"<\/code><\/pre>\n\n\n\n<h3 id=\"exporting-and-importing-the-live-data\" class=\"wp-block-heading\">Exporting and importing the live data<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The active partitions are exported next, in a single <code>expdp<\/code>, deliberately <strong>without<\/strong> <code>FLASHBACK_SCN<\/code>. Data Pump chooses a consistent SCN itself, for each table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ expdp sales\/***@PDB1 dumpfile=active_part.dmp tables=SALES.ORDERS_PART:P_ACTIVE,SALES.ITEMS_PART:P_ACTIVE\nFLASHBACK automatically enabled to preserve database integrity.\n. . exported \"SALES\".\"ITEMS_PART\":\"P_ACTIVE\"                5.5 KB       2 rows\n. . exported \"SALES\".\"ORDERS_PART\":\"P_ACTIVE\"               5.5 KB       2 rows\nJob \"SALES\".\"SYS_EXPORT_TABLE_01\" successfully completed at Sun Oct 11 13:52:29 2026 elapsed 0 00:00:12<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Live inserts after that export are therefore not in the dump:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; INSERT INTO sales.orders_part VALUES (1003, 'ACTIVE');\n\n1 row created.\n\nSQL&gt; INSERT INTO sales.items_part VALUES (1003, 'ACTIVE');\n\n1 row created.\n\nSQL&gt; COMMIT;\n\nCommit complete.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">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:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ cat rest.par\ndumpfile=rest.dmp\nschemas=SALES\nexclude=TABLE:\"IN ('ORDERS_PART','ITEMS_PART')\"<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>$ expdp sales\/***@PDB1 parfile=rest.par\nFLASHBACK automatically enabled to preserve database integrity.\n. . exported \"SALES\".\"CUSTOMERS\"                          5.515 KB       4 rows\nJob \"SALES\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at Sun Oct 11 13:52:55 2026 elapsed 0 00:00:24<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">A live insert after this export is not in the dump either:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; INSERT INTO sales.customers VALUES (5, 'LIVE');\n\n1 row created.\n\nSQL&gt; COMMIT;\n\nCommit complete.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The partitioned tables already exist on the target, so the active partitions are appended. A regular import creates the normal tables:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ impdp sales\/***@PDB2 dumpfile=active_part.dmp table_exists_action=append\nTable \"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\nTable \"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\n. . imported \"SALES\".\"ITEMS_PART\":\"P_ACTIVE\"                5.5 KB       2 rows\n. . imported \"SALES\".\"ORDERS_PART\":\"P_ACTIVE\"               5.5 KB       2 rows\nJob \"SALES\".\"SYS_IMPORT_FULL_01\" successfully completed at Sun Oct 11 13:53:00 2026 elapsed 0 00:00:03\n\n$ impdp sales\/***@PDB2 dumpfile=rest.dmp\n. . imported \"SALES\".\"CUSTOMERS\"                          5.515 KB       4 rows\nJob \"SALES\".\"SYS_IMPORT_FULL_01\" successfully completed at Sun Oct 11 13:53:04 2026 elapsed 0 00:00:02<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The instantiation view now has one row per table. The partitioned tables have SCNs from the second export, while <code>CUSTOMERS<\/code> has one from the third export:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT source_database, source_object_name, instantiation_scn\nFROM dba_apply_instantiated_objects\nWHERE source_object_owner = 'SALES'\nORDER BY source_object_name;\n\nSOURCE_DATABASE SOURCE_OBJECT_NAME INSTANTIATION_SCN\n--------------- ------------------ -----------------\nPDB1            CUSTOMERS                  189585331\nPDB1            ITEMS_PART                 189572011\nPDB1            ORDERS_PART                189572008\n\n3 rows selected.<\/code><\/pre>\n\n\n\n<h3 id=\"replicat-with-instantiation-filtering\" class=\"wp-block-heading\">Replicat with instantiation filtering<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Each instantiation SCN applies to the whole table, and we use instantiation filtering in the replicat. It is added and started in the <code>adminclient<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>OGG (http:\/\/localhost:7810 ogg_test_01) 2&gt; DBLOGIN USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate\nSuccessfully logged into database PDB2.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02\/PDB2) 3&gt; ADD REPLICAT REPPT, INTEGRATED, EXTTRAIL pt, CHECKPOINTTABLE OGGADMIN.CHECKPOINT\n2026-10-11T13:53:05Z  INFO    OGG-08100  Integrated Replicat added.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02\/PDB2) 4&gt; VIEW PARAMS REPPT\nREPLICAT REPPT\nDBOPTIONS ENABLE_INSTANTIATION_FILTERING\nUSERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate\nMAP SALES.*, TARGET SALES.*;\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02\/PDB2) 5&gt; START REPLICAT REPPT\n2026-10-11T13:53:05Z  INFO    OGG-00975  Replicat group REPPT starting.\n2026-10-11T13:53:05Z  INFO    OGG-15445  Replicat group REPPT started.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Once the replicat has caught up, the target tables have the same number of rows as the source:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT COUNT(*) FROM sales.orders_part;\n\nCOUNT(*)\n--------\n       8\n\n1 row selected.\n\nSQL&gt; SELECT COUNT(*) FROM sales.items_part;\n\nCOUNT(*)\n--------\n       8\n\n1 row selected.\n\nSQL&gt; SELECT COUNT(*) FROM sales.customers;\n\nCOUNT(*)\n--------\n       5\n\n1 row selected.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In the <code>adminclient<\/code>, we can see the transaction done after creating the extract, on each table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>OGG (http:\/\/localhost:7810 ogg_test_01) 2&gt; STATS REPLICAT REPPT, TOTAL, TABLE SALES.*\n\nSending STATS TOTAL TABLE SALES.* request to Replicat group REPPT ...\n\n\nStart of statistics at 2026-10-11 13:53:18.\n\n\nIntegrated Replicat statistics:\n\n        Total transactions                                 2.00\n        Redirected                                         0.00\n        Replicated procedures                              0.00\n        DDL operations                                     0.00\n        Stored procedures                                  0.00\n        Datatype functionality                             0.00\n        Operation type functionality                       0.00\n        Event actions                                      0.00\n        Database compatibility                             0.00\n        Direct transactions ratio                          0.00%\n\nReplicating from SALES.ORDERS_PART to PDB2.SALES.ORDERS_PART:\n\n*** Total statistics since 2026-10-11 13:53:07 ***\n    Total inserts                              1.00\n    Total updates                              0.00\n    Total deletes                              0.00\n    Total upserts                              0.00\n    Total discards                             0.00\n    Total operations                           1.00\n\nReplicating from SALES.ITEMS_PART to PDB2.SALES.ITEMS_PART:\n\n*** Total statistics since 2026-10-11 13:53:07 ***\n    Total inserts                              1.00\n    Total updates                              0.00\n    Total deletes                              0.00\n    Total upserts                              0.00\n    Total discards                             0.00\n    Total operations                           1.00\n\nReplicating from SALES.CUSTOMERS to PDB2.SALES.CUSTOMERS:\n\n*** Total statistics since 2026-10-11 13:53:07 ***\n    Total inserts                              1.00\n    Total updates                              0.00\n    Total deletes                              0.00\n    Total upserts                              0.00\n    Total discards                             0.00\n    Total operations                           1.00\n\nEnd of statistics.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The only inserts are the three rows added after the exports, one per table. There is no <code>ORA-00001<\/code>: the rows inserted before the exports were replayed from the trail and skipped by the instantiation filtering. Without <code>ENABLE_INSTANTIATION_FILTERING<\/code> they would have hit the primary key.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>FLASHBACK_SCN<\/code> is not needed here. The replicat compares commit CSNs against <code>instantiation_scn<\/code>, not against the export\u2019s flashback point. You only need <code>PREPARECSN<\/code> to have run before the export of the live data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Foreign keys between tables that are in different exports are something to keep in mind.<\/p>\n\n\n\n<h2 id=\"wrong-order-importing-old-partitions-after\" class=\"wp-block-heading\">Wrong order: importing old partitions after<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">What happens if, for some reason, you run the initial load of the active partitions first, with <code>PREPARECSN<\/code> and instantiation filtering, and then migrate the old partitions? Let\u2019s try this, by first adding <code>trandata<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>OGG (http:\/\/localhost:7810 ogg_test_01) 2&gt; DBLOGIN USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate\nSuccessfully logged into database PDB1.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 3&gt; ADD TRANDATA SALES.ORDERS_PART PREPARECSN NOWAIT\n2026-10-11T13:53:42Z  INFO    OGG-15132  Logging of supplemental redo data enabled for table SALES.ORDERS_PART.\n2026-10-11T13:53:42Z  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table SALES.ORDERS_PART.\n2026-10-11T13:53:42Z  INFO    OGG-15438  Enabling logical replication (with all keys) on auto capture table SALES.ORDERS_PART.\n2026-10-11T13:53:42Z  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SALES.ORDERS_PART.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The old partitions are exported before the extract exists:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ expdp sales\/***@PDB1 dumpfile=b_old.dmp tables=SALES.ORDERS_PART:P_OLD\nFLASHBACK automatically enabled to preserve database integrity.\n. . exported \"SALES\".\"ORDERS_PART\":\"P_OLD\"                5.523 KB       5 rows\nJob \"SALES\".\"SYS_EXPORT_TABLE_01\" successfully completed at Sun Oct 11 13:53:55 2026 elapsed 0 00:00:11<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The extract is then registered and started, as in the first scenario:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>OGG (http:\/\/localhost:7810 ogg_test_01) 2&gt; DBLOGIN USERIDALIAS oggadmin_pdb1 DOMAIN OracleGoldenGate\nSuccessfully logged into database PDB1.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 3&gt; ADD EXTRACT EXTPT, INTEGRATED TRANLOG, BEGIN NOW\n2026-10-11T13:53:57Z  INFO    OGG-08100  Integrated Extract added.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 4&gt; REGISTER EXTRACT EXTPT DATABASE\n2026-10-11T13:54:00Z  INFO    OGG-02003  Extract group EXTPT successfully registered with database at SCN 189596053.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 5&gt; ADD EXTTRAIL pt, EXTRACT EXTPT\n2026-10-11T13:54:00Z  INFO    OGG-08100  EXTTRAIL added.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb1@CDB01\/PDB1) 6&gt; START EXTRACT EXTPT\n2026-10-11T13:54:00Z  INFO    OGG-00975  Extract group EXTPT starting.\n2026-10-11T13:54:00Z  INFO    OGG-15426  Extract group EXTPT started.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Rows <code>1001<\/code> and <code>1002<\/code> are inserted into <code>P_ACTIVE<\/code>, and the live data is exported. Row <code>1003<\/code> is inserted after the export:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ expdp sales\/***@PDB1 dumpfile=b_active.dmp tables=SALES.ORDERS_PART:P_ACTIVE\nFLASHBACK automatically enabled to preserve database integrity.\n. . exported \"SALES\".\"ORDERS_PART\":\"P_ACTIVE\"               5.5 KB       2 rows\nJob \"SALES\".\"SYS_EXPORT_TABLE_01\" successfully completed at Sun Oct 11 13:54:09 2026 elapsed 0 00:00:08<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This time, on the target, <strong>the live data is imported first<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ impdp sales\/***@PDB2 dumpfile=b_active.dmp\n. . imported \"SALES\".\"ORDERS_PART\":\"P_ACTIVE\"               5.5 KB       2 rows\nJob \"SALES\".\"SYS_IMPORT_FULL_01\" successfully completed at Sun Oct 11 13:54:14 2026 elapsed 0 00:00:02<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">For now, the boundary is above the registration SCN <code>189596053<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT source_object_name, instantiation_scn\nFROM dba_apply_instantiated_objects\nWHERE source_object_owner = 'SALES'\nAND source_object_name = 'ORDERS_PART';\n\nSOURCE_OBJECT_NAME INSTANTIATION_SCN\n------------------ -----------------\nORDERS_PART                189610870\n\n1 row selected.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The old partitions are then imported:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ impdp sales\/***@PDB2 dumpfile=b_old.dmp table_exists_action=append\nTable \"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\n. . imported \"SALES\".\"ORDERS_PART\":\"P_OLD\"                5.523 KB       5 rows\nJob \"SALES\".\"SYS_IMPORT_FULL_01\" successfully completed at Sun Oct 11 13:54:17 2026 elapsed 0 00:00:01<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">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 !<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT source_object_name, instantiation_scn\nFROM dba_apply_instantiated_objects\nWHERE source_object_owner = 'SALES'\nAND source_object_name = 'ORDERS_PART';\n\nSOURCE_OBJECT_NAME INSTANTIATION_SCN\n------------------ -----------------\nORDERS_PART                189594418\n\n1 row selected.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">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 <code>adminclient<\/code>, and start it:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>OGG (http:\/\/localhost:7810 ogg_test_01) 2&gt; DBLOGIN USERIDALIAS oggadmin_pdb2 DOMAIN OracleGoldenGate\nSuccessfully logged into database PDB2.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02\/PDB2) 3&gt; ADD REPLICAT REPPT, INTEGRATED, EXTTRAIL pt, CHECKPOINTTABLE OGGADMIN.CHECKPOINT\n2026-10-11T13:54:19Z  INFO    OGG-08100  Integrated Replicat added.\n\nOGG (http:\/\/localhost:7810 ogg_test_01 as oggadmin_pdb2@CDB02\/PDB2) 4&gt; START REPLICAT REPPT\n2026-10-11T13:54:19Z  INFO    OGG-00975  Replicat group REPPT starting.\n2026-10-11T13:54:19Z  INFO    OGG-15445  Replicat group REPPT started.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The replicat reads the instantiation SCN <code>189594418<\/code> 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:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>OGG (http:\/\/localhost:7810 ogg_test_01) 2&gt; INFO REPLICAT REPPT\n\nReplicat   REPPT     Last Started 2026-10-11 13:54   Status ABENDED\nINTEGRATED\nCheckpoint Lag       00:00:00 (updated 00:00:19 ago)\nLog Read Checkpoint  File pt000000000\n                     First Record  RBA 0\nEncryption Profile   LocalWallet<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The replicat report shows the boundary and the error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>2026-10-11 13:54:25  INFO    OGG-10155  Instantiation CSN filtering is enabled on table SALES.ORDERS_PART at CSN 189,594,418.\n2026-10-11 13:54:31  WARNING OGG-02544  Unhandled error (ORA-00001: unique constraint (.) violated\nORA-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.<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>1001<\/code> and <code>1002<\/code> were already on the target from the live-data import, but their commit CSNs are now above the boundary.<\/p>\n\n\n\n<h2 id=\"conclusion\" class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>instantiation_scn<\/code> is <strong>per table<\/strong>, not per partition. The last <code>impdp<\/code> will decide the boundary being used by instantiation filtering.<\/li>\n\n\n\n<li><code>impdp<\/code> overwrites it <strong>unconditionally<\/strong>, with no warning.<\/li>\n\n\n\n<li>When using instantiation filtering, always import the dump which should contain the boundary <strong>last<\/strong>.<\/li>\n\n\n\n<li>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.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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. [&hellip;]<\/p>\n","protected":false},"author":152,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":"","_members_access_role":[],"_members_access_error":""},"categories":[3787,59],"tags":[62,734,1837,3781,328,1403,2522,4236,2562,3730,96,366,3976,4240],"type_dbi":[4026,4259,4283,3785,3740,4282,4265,4258,3786,3881,2728,4284,3977],"class_list":["post-47906","post","type-post","status-publish","format-standard","hentry","category-goldengate","category-oracle","tag-19c","tag-datapump","tag-expdp","tag-extract","tag-goldengate","tag-impdp","tag-initial-load","tag-instantiation","tag-migration-2","tag-ogg","tag-oracle","tag-partitioning","tag-replicat","tag-scn","type-19c","type-datapump","type-expdp","type-extract","type-goldengate","type-impdp","type-initial-load","type-instantiation","type-migration","type-ogg","type-oracle","type-partitioning","type-replicat"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.6 (Yoast SEO v28.7) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Import order matters for GoldenGate instantiation filtering - dbi Blog<\/title>\n<meta name=\"description\" content=\"Import order matters for GoldenGate instantiation filtering. Here is what the replicat does when old partitions are imported last.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Import order matters for GoldenGate instantiation filtering\" \/>\n<meta property=\"og:description\" content=\"Import order matters for GoldenGate instantiation filtering. Here is what the replicat does when old partitions are imported last.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-10-15T15:37:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-10-15T15:37:49+00:00\" \/>\n<meta name=\"author\" content=\"Julien Delattre\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Julien Delattre\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/\"},\"author\":{\"name\":\"Julien Delattre\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/764ab019cc9dec42655b4c6b9b8e474e\"},\"headline\":\"Import order matters for GoldenGate instantiation filtering\",\"datePublished\":\"2026-10-15T15:37:47+00:00\",\"dateModified\":\"2026-10-15T15:37:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/\"},\"wordCount\":832,\"commentCount\":0,\"keywords\":[\"19c\",\"DataPump\",\"expdp\",\"extract\",\"GoldenGate\",\"impdp\",\"Initial load\",\"instantiation\",\"migration\",\"ogg\",\"Oracle\",\"Partitioning\",\"replicat\",\"scn\"],\"articleSection\":[\"GoldenGate\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/\",\"name\":\"Import order matters for GoldenGate instantiation filtering - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2026-10-15T15:37:47+00:00\",\"dateModified\":\"2026-10-15T15:37:49+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/764ab019cc9dec42655b4c6b9b8e474e\"},\"description\":\"Import order matters for GoldenGate instantiation filtering. Here is what the replicat does when old partitions are imported last.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/import-order-matters-for-goldengate-instantiation-filtering\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Import order matters for GoldenGate instantiation filtering\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/764ab019cc9dec42655b4c6b9b8e474e\",\"name\":\"Julien Delattre\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97d00e680bbf237126e24b65281cbcb66cd20bd1ed2d14bf928991b2bf68eb5?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97d00e680bbf237126e24b65281cbcb66cd20bd1ed2d14bf928991b2bf68eb5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97d00e680bbf237126e24b65281cbcb66cd20bd1ed2d14bf928991b2bf68eb5?s=96&d=mm&r=g\",\"caption\":\"Julien Delattre\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/juliendelattre\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Import order matters for GoldenGate instantiation filtering - dbi Blog","description":"Import order matters for GoldenGate instantiation filtering. Here is what the replicat does when old partitions are imported last.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/","og_locale":"en_US","og_type":"article","og_title":"Import order matters for GoldenGate instantiation filtering","og_description":"Import order matters for GoldenGate instantiation filtering. Here is what the replicat does when old partitions are imported last.","og_url":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/","og_site_name":"dbi Blog","article_published_time":"2026-10-15T15:37:47+00:00","article_modified_time":"2026-10-15T15:37:49+00:00","author":"Julien Delattre","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Julien Delattre","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/"},"author":{"name":"Julien Delattre","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/764ab019cc9dec42655b4c6b9b8e474e"},"headline":"Import order matters for GoldenGate instantiation filtering","datePublished":"2026-10-15T15:37:47+00:00","dateModified":"2026-10-15T15:37:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/"},"wordCount":832,"commentCount":0,"keywords":["19c","DataPump","expdp","extract","GoldenGate","impdp","Initial load","instantiation","migration","ogg","Oracle","Partitioning","replicat","scn"],"articleSection":["GoldenGate","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/","url":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/","name":"Import order matters for GoldenGate instantiation filtering - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2026-10-15T15:37:47+00:00","dateModified":"2026-10-15T15:37:49+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/764ab019cc9dec42655b4c6b9b8e474e"},"description":"Import order matters for GoldenGate instantiation filtering. Here is what the replicat does when old partitions are imported last.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/import-order-matters-for-goldengate-instantiation-filtering\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Import order matters for GoldenGate instantiation filtering"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/764ab019cc9dec42655b4c6b9b8e474e","name":"Julien Delattre","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97d00e680bbf237126e24b65281cbcb66cd20bd1ed2d14bf928991b2bf68eb5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97d00e680bbf237126e24b65281cbcb66cd20bd1ed2d14bf928991b2bf68eb5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97d00e680bbf237126e24b65281cbcb66cd20bd1ed2d14bf928991b2bf68eb5?s=96&d=mm&r=g","caption":"Julien Delattre"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/juliendelattre\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/47906","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/152"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=47906"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/47906\/revisions"}],"predecessor-version":[{"id":47912,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/47906\/revisions\/47912"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=47906"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=47906"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=47906"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=47906"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}