GoldenGate Instantiation CSN Explained

For more than ten years (GoldenGate 12.2, released in 2015), GoldenGate has provided a way of performing initial load of tables with a per-table instantiation CSN.

But when migrating a database or just performing the initial load of a single table to prepare a GoldenGate replication, the known procedure is very often still the old one: start an extract, note down an SCN, run expdp with FLASHBACK_SCN=<that SCN>, import the data on the target, and finally start a replicat with the AFTERCSN clause.

It takes a long time to get used to new habits, so let’s refresh your memory, explaining how instantiation CSN work in GoldenGate.

Historic FLASHBACK_SCN + AFTERCSN initial load

Historically, precise instantiation of a table worked like this:

• START EXTRACT. It is the first step, to ensure that no transaction is lost.
• Wait for long running transactions to be committed.
• Select an SCN, which should not be prior to the registration SCN of the extract.
• Export the data with expdp ... FLASHBACK_SCN=<scn> so the dump is consistent.
• Import the data with impdp on the target.
• Start the replicat with the AFTERCSN <csn> clause, so the replicat does not replicate anything committed at or before the instantiation CSN.

While still perfectly valid, and even necessary under certain conditions, this method becomes hard to operate on complex replications or when the size of the database increases:

• UNDO retention: very often, using the FLASHBACK_SCN clause will generate ORA-01555: snapshot too old errors.
• Splitting the initial load is not possible, since the AFTERCSN clause will apply on the whole replicat.

Instantiation CSN in practice

GoldenGate instantiation CSN provides a simple solution: a per table instantiation CSN, on the target database, instead of a single one applied on the replicat.

This instantiation CSN is stored on the target database in the DBA_APPLY_INSTANTIATED_OBJECTS table, with one row per source object.

SET PAGES 100 LINES 200
COL source_object_owner FORMAT A20
COL source_object_name FORMAT A30
COL instantiation_scn FORMAT 999999999999999
SELECT source_object_owner, source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
ORDER BY 1, 2;

This table is populated by Data Pump, and is then read by GoldenGate. When applying changes, a replicat will compare the commit CSN of a record to the table’s instantiation CSN. If the commit CSN is higher, the transaction will be discarded.

Because the comparison is done for every table, there is no need for them to share one instantiation CSN using FLASHBACK_SCN.

Why FLASHBACK_SCN is no longer required

The instantiation CSN is the read-consistent SCN as of which Data Pump reads the table during export. It is stored in the dump and imported on the target when using impdp. It does not come from when the source table is prepared with PREPARECSN. PREPARECSN is what makes the table eligible for instantiation CSN.

Let’s see the difference with three different scenarios:

• Standard initial load without FLASHBACK_SCN
• Historic method with FLASHBACK_SCN
• Instantiation with PREPARECSN NONE

I will use the same two tables, HR.EMPLOYEES and HR.DEPARTMENTS.

ENABLE_INSTANTIATION_FILTERING vs ATCSN/AFTERCSN

Before running the three scenarios, here is what the instantiation filtering replaces:

1. Prepare the tables on the source

Using ADD SCHEMATRANDATA, we will start by preparing the table on the source. The full clause is ADD SCHEMATRANDATA <schema_name> PREPARECSN {WAIT | LOCK | NOWAIT | NONE}, and NOWAIT is the default. In other words, ADD SCHEMATRANDATA prepares tables for instantiation whether you include PREPARECSN or not.

OGG> DBLOGIN USERIDALIAS gg_source DOMAIN OracleGoldenGate
OGG> ADD SCHEMATRANDATA HR
2026-07-26 09:41:07  INFO    OGG-01788  SCHEMATRANDATA has been added on schema HR.
2026-07-26 09:41:07  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema HR.

If you do not want instantiation CSN filtering for a schema, you can opt out explicitly with ADD SCHEMATRANDATA HR PREPARECSN NONE. It is the only way to get a table with zero rows in dba_apply_instantiated_objects after a load. Everything else on this page happens by default.

The full clause, PREPARECSN {WAIT | LOCK | NOWAIT | NONE}, controls how invasive the preparation is on a live table:

For an OLTP table with a lot of concurrent DML, WAIT or LOCK can stall application sessions while GoldenGate waits for or takes the lock. NOWAIT is almost always the correct option.

Confirm both tables are prepared:

OGG> INFO SCHEMATRANDATA HR
Schema HR has 2 prepared tables for instantiation.

2. Start the extract

Capture has to be running before the export, so that any change committed during and after the dump is safely in the trail:

OGG> START EXTRACT EXTA

3. Wait for long running transactions to finish

A transaction that began before the extract could be missed, so wait for every long running transaction to finish before exporting. See Checking Long Running Transactions in GoldenGate for how to list them with the adminclient and the REST API.

4. Export without FLASHBACK_SCN

expdp hr/*** dumpfile=hr.dmp schemas=HR

Without the FLASHBACK_SCN clause, each table’s boundary will be the SCN as of which Data Pump reads it during the export, recorded automatically because the table was prepared with PREPARECSN.

5. Import on the target

impdp gg_target/*** dumpfile=hr.dmp full=y

As part of this import, Data Pump populates dba_apply_instantiated_objects for every prepared table it loads. No extra option is needed.

6. Retrieve the instantiation CSN for each table

Let’s query the table that replaces the SCN you used to write down by hand:

SELECT source_object_owner, source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
WHERE source_object_owner = 'HR'
ORDER BY 1, 2;

SOURCE_OBJECT_OWNER  SOURCE_OBJECT_NAME  INSTANTIATION_SCN
-------------------- ------------------- -----------------
HR                   DEPARTMENTS                  17662585
HR                   EMPLOYEES                    17662582

The two tables come from the same export, but with two different INSTANTIATION_SCN. Without FLASHBACK_SCN on the export, each table was read (and stamped) at its own point as the dump progressed. That per-table granularity is the entire point, and it is exactly what a single AFTERCSN could never express.

7. Configure the replicat and start it, without AFTERCSN

REPLICAT REPA
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
USERIDALIAS gg_target DOMAIN OracleGoldenGate
MAP HR.*, TARGET HR.*;

OGG> START REPLICAT REPA

DBOPTIONS ENABLE_INSTANTIATION_FILTERING tells the replicat to look each table’s boundary in dba_apply_instantiated_objects instead of expecting an AFTERCSN on the start command. The replicat discards trail records committed at or before its own boundary and applies everything after it. Both tables are in sync with the source, with no flashback SCN ever chosen.

Same initial load with FLASHBACK_SCN

Let’s run the same initial load again for a second schema, but add FLASHBACK_SCN to the export and watch what changes in the instantiation table. Pick an SCN and pass it to expdp:

expdp system/*** schemas=HR2 flashback_scn=17663448 dumpfile=hr2.dmp
impdp system/*** dumpfile=hr2.dmp full=y

Then the same step 6 query against the target:

SELECT source_object_owner, source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
WHERE source_object_owner = 'HR2'
ORDER BY 1, 2;

SOURCE_OBJECT_OWNER  SOURCE_OBJECT_NAME  INSTANTIATION_SCN
-------------------- ------------------- -----------------
HR2                  DEPARTMENTS                  17663448
HR2                  EMPLOYEES                    17663448

This time, both tables carry the same instantiation CSN, and it is exactly the FLASHBACK_SCN (17663448). FLASHBACK_SCN pins the whole export to one consistent point in time, so Data Pump stamps every table in the dump with that single SCN. Please note that FLASHBACK_SCN is still not required for the instantiation mechanism to work. It only gives a consistent export.

Same initial load with PREPARECSN NONE

Let’s see what happens when we disable instantiation, by preparing a third schema with PREPARECSN NONE and run the same export and import:

OGG> ADD SCHEMATRANDATA HR3 ALLCOLS, PREPARECSN NONE
INFO OGG-10154  Schema level PREPARECSN set to mode NONE on schema "HR3"
OGG> INFO SCHEMATRANDATA HR3
Schema "HR3" has 0 prepared tables for instantiation

expdp system/*** schemas=HR3 dumpfile=hr3.dmp
impdp system/*** dumpfile=hr3.dmp full=y

The import succeeds and the rows land on the target as normal, but the instantiation table stays empty for this schema:

SELECT source_object_owner, source_object_name, instantiation_scn
FROM dba_apply_instantiated_objects
WHERE source_object_owner = 'HR3';

no rows selected

In that case, a replicat running with DBOPTIONS ENABLE_INSTANTIATION_FILTERING would find no boundary for these tables and therefore would not filter them. It would apply every trail record it sees, including changes the dump already contains.

The boundary outlives the schema

dba_apply_instantiated_objects is independent from the schema it describes. Dropping the target schema with DROP USER ... CASCADE and recreating it does not clear its row:

SQL> DROP USER hr4 CASCADE;

User dropped.

SQL> CREATE USER hr4 IDENTIFIED BY Welcome1;

User created.

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

SOURCE_OBJECT_OWNER  SOURCE_OBJECT_NAME  INSTANTIATION_SCN
-------------------- ------------------- -----------------
HR4                  EMPLOYEES                    52356545

A stale boundary from a previous initial load can silently attach itself to the next import of an object with the same name. The same table, re-imported a second time from a dump whose source was never prepared with PREPARECSN, does not delete the row from the first prepared initial load.

A replicat with ENABLE_INSTANTIATION_FILTERING reading this table afterward would filter every record against a boundary that has no relationship to what the last import actually put on the target.

A command exists in the adminclient to clear the instantiation table:

OGG> DBLOGIN USERIDALIAS gg_target DOMAIN OracleGoldenGate
OGG> CLEAR INSTANTIATION CSN FOR hr.employees FROM PDB_SOURCE
INFO OGG-10464  Instantiation CSN has been cleared successfully.

SQL> SELECT source_object_owner, source_object_name, instantiation_scn
     FROM dba_apply_instantiated_objects WHERE source_object_owner='HR';
no rows selected

Remember to check the dba_apply_instantiated_objects view before reusing a target schema name for an initial load.

When should I still set instantiation SCN by hand ?

There are multiple scenarios where automatic instantiation filtering does not work.

• If your initial load is not done by Data Pump. GoldenGate initial extract, a manual INSERT ... SELECT over a database link, transportable tablespaces, do not alter the dba_apply_instantiated_objects view.
• If the workload during the initial load is not just DML. Instantiation filtering does not work on DDL, for instance.

In that case, you can either go back to the FLASHBACK_SCN method, or set the instantiation CSN by hand in the adminclient:

OGG> DBLOGIN USERIDALIAS gg_target DOMAIN OracleGoldenGate
OGG> SET INSTANTIATION CSN FOR hr.legacy_orders CSN 12345678 FROM PDB_SOURCE

Common questions

A table added after PREPARECSN never gets a boundary

If a table is created on the source after you ran ADD SCHEMATRANDATA HR, it is not part of that preparation. Exporting it later with expdp means Data Pump has no CSN bookkeeping to carry into the dump, and dba_apply_instantiated_objects is not populated during import. The fix is to run ADD TRANDATA ... before exporting it.

Removing the parameter once instantiation is complete

DBOPTIONS ENABLE_INSTANTIATION_FILTERING can be removed from the replicat parameter file once it has processed every transaction past the instantiation CSN of every table it maps (there is no harm in leaving it in indefinitely).

Summary

• The instantiation CSN is a per-table replication boundary, stored in dba_apply_instantiated_objects on the target, compared against each record’s commit CSN.
• Data Pump stamps it automatically on impdp, because ADD SCHEMATRANDATA/ADD TRANDATA prepare tables for instantiation by default (PREPARECSN NOWAIT). Use PREPARECSN NONE to not use instantiation SCN.
• Because that boundary is per table and set by Data Pump, FLASHBACK_SCN and AFTERCSN are no longer needed just to define where the dump ends and replication begins. Retrieve the boundary with a query instead of tracking an SCN by hand.
• FLASHBACK_SCN still has a use (making one large export internally consistent), but it is no longer needed just to drive the replication boundary. Data Pump records that boundary either way.
• The boundary outlives the schema it describes. Dropping and recreating a target schema does not clear its row in dba_apply_instantiated_objects. Check the view, and use CLEAR INSTANTIATION CSN if you see stale rows.