For as long as I have used GoldenGate, an initial load had one prerequisite: the target tables must already exist. Data Pump takes care of it between two Oracle databases. For anything else, you write the CREATE TABLE statements yourself, with the datatypes you choose, or choose another initial load method. The Automatic Schema Evolution feature launched in GoldenGate 26ai promises to “automatically detect and propagate supported schema changes as part of the replication flow”. I wanted to see it work, so I tried it from an Oracle 19c PDB to a PostgreSQL database.

And it works. The replicat creates the missing table itself, with a default datatype mapping, and loads the rows in the target.

Automatic schema evolution is a preview feature in GoldenGate 26ai: its syntax, messages and behavior can change before it becomes generally available, so the results below apply to the 23.26.3 release I tested.

How it works

The feature replicates table structure changes between databases of the same or of different types. The exception is Oracle to Oracle, where regular DDL replication is the solution. It also creates tables during an initial load. The replicat must be 26ai or higher, but the extract writing the trail can be 19c or higher. On a live trail, the replicat also adds a column that appears on the source. The documentation lists ADD COLUMN as the one supported ALTER TABLE. But an initial load never alters an existing table: it leaves it alone or, with OPTYPE RECREATE, drops and recreates it.

The replicat does not need anything from the source database. It builds the CREATE TABLE statement from the Table Definition Record (TDR) that GoldenGate already writes in the trail file.

AUTOSCHEMA is a replicat-only option of DDL: the DDL parameter of the extract does not accept it. I put DDL AUTOSCHEMA INCLUDE ALL in the extract’s parameter file. The extract abends at startup with the same parsing error as an unrecognized keyword:

2026-09-26T05:54:27Z  ERROR   OGG-10151  (EXTPGL.prm) line 3: Parsing error, parameter [DDL] has unrecognized keyword or extra value "AUTOSCHEMA".

That makes sense given what the feature does. It creates missing tables on the target, and the extract never touches the target database.

The setup

  • Source: Oracle 19c, PDB1, schema AUTOSCH, table AUTOSCH.T1 with 5 rows, managed by the 26ai deployment ogg_test_01 in GoldenGate 23.26.3.0.0.
  • Target: PostgreSQL 16, database pgstore, an empty schema autosch, managed by a separate deployment ogg_pg_01 from GoldenGate for PostgreSQL 23.26.3.0.1.

The source table:

CREATE TABLE autosch.t1 (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    amount NUMBER(10,2),
    created DATE DEFAULT SYSDATE
);

For the initial load, on the ogg_test_01 deployment, EXTPGL is added as a SOURCEISTABLE extract.

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

OGG (http://localhost:7810 ogg_test_01 as pdb1@CDB01/PDB1) 3> ADD EXTRACT EXTPGL, SOURCEISTABLE
2026-09-26T05:53:26Z  INFO    OGG-08100  Extract added.

OGG (http://localhost:7810 ogg_test_01 as pdb1@CDB01/PDB1) 4> EDIT PARAMS EXTPGL
2026-09-26T05:53:26Z  INFO    OGG-10183  Parameter file EXTPGL.prm passed validity check.

OGG (http://localhost:7810 ogg_test_01 as pdb1@CDB01/PDB1) 5> VIEW PARAMS EXTPGL
EXTRACT EXTPGL
USERIDALIAS pdb1 DOMAIN OracleGoldenGate
EXTFILE p1 MEGABYTES 100 PURGE
TABLE AUTOSCH.T1;

OGG (http://localhost:7810 ogg_test_01 as pdb1@CDB01/PDB1) 6> START EXTRACT EXTPGL
2026-09-26T05:53:26Z  INFO    OGG-00975  Extract group EXTPGL starting.
2026-09-26T05:53:26Z  INFO    OGG-15426  Extract group EXTPGL started.

The extract report confirms the 5 rows went out to the file:

Report at 2026-09-26 05:53:26 (activity since 2026-09-26 05:53:26)

Output to p1:

From table AUTOSCH.T1:
       #                   inserts:         5
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0

The target is a separate deployment, ogg_pg_01, from the Oracle GoldenGate for PostgreSQL binaries. A distribution path delivers the p1 trail from ogg_test_01 to ogg_pg_01. The replicat there, REPPGL, is added straight against the trail name, with EXTSEQNO 0 telling it where to start reading. The checkpoint table is oggadmin.checkpoint:

OGG (http://localhost:7859 ogg_pg_01) 2> DBLOGIN USERIDALIAS pgalias DOMAIN OracleGoldenGate
Successfully logged into database.

OGG (http://localhost:7859 ogg_pg_01 as pgalias@pgstore) 3> ADD REPLICAT REPPGL, EXTFILE p1, EXTSEQNO 0, CHECKPOINTTABLE oggadmin.checkpoint
2026-09-26T09:14:42Z  INFO    OGG-30509  Connected to database server using ODBC DSN: ogg_pg_01 with SSLMODE 'disable'
2026-09-26T09:14:42Z  INFO    OGG-08100  Replicat added.

Baseline: without the feature

REPPGL’s parameter file starts with just a MAP, no DDL clause at all:

REPLICAT REPPGL
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
MAP AUTOSCH.*, TARGET AUTOSCH.*;

With this replicat, the initial load stops on the first table, as it always did:

2026-09-26 05:52:11  WARNING OGG-00869  Could not retrieve definition for table AUTOSCH.T1.
2026-09-26 05:52:11  ERROR   OGG-00199  Table AUTOSCH.T1 does not exist in target database.

Enabling automatic schema evolution

The feature is called AUTOSCHEMA, but as a parameter on a line by itself, it is rejected:

2026-09-26T05:52:30Z  ERROR   OGG-10141  (REPPGL.prm) line 3 column 1: Parsing error, value "AUTOSCHEMA" syntax error.
2026-09-26T05:52:30Z  ERROR   OGG-10184  Parameter file REPPGL.prm failed validity check.

AUTOSCHEMA is an option of the DDL parameter, followed by the usual INCLUDE and EXCLUDE clauses:

REPLICAT REPPGL
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
DDL AUTOSCHEMA INCLUDE ALL
MAP AUTOSCH.*, TARGET AUTOSCH.*;

The replicat starts and announces the feature, then finds the table missing and creates it:

2026-09-26 05:52:41  INFO    OGG-30684  The Automatic Schema Evolution feature is enabled. Please refer to the GoldenGate documentation for the supported operations, the datatype mappings, and the corresponding parameter options to enable/disable/override the functionality.
2026-09-26 05:52:41  WARNING OGG-00869  Could not retrieve definition for table AUTOSCH.T1.
2026-09-26 05:52:41  WARNING OGG-30633  Table AUTOSCH.T1 does not exist in target database, AutoSchema will attempt to create the missing table.
2026-09-26 05:52:41  INFO    OGG-02756  The definition for table AUTOSCH.T1 is obtained from the trail file.

The same warning OGG-00869 as in the baseline is still there. But it is followed by OGG-30633 and by OGG-02756, which shows that the definition comes from the trail file. In PostgreSQL:

pgstore=# \d autosch.t1
                            Table "autosch.t1"
 Column  |              Type              | Collation | Nullable | Default
---------+--------------------------------+-----------+----------+---------
 id      | character varying(50)          |           | not null |
 name    | character varying(50)          |           |          |
 amount  | numeric(10,2)                  |           |          |
 created | timestamp(0) without time zone |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)

pgstore=# select * from autosch.t1 order by 1;
 id | name  | amount |       created
----+-------+--------+---------------------
 1  | row 1 |  10.25 | 2026-09-26 05:43:53
 2  | row 2 |  20.50 | 2026-09-26 05:43:53
 3  | row 3 |  30.75 | 2026-09-26 05:43:53
 4  | row 4 |  41.00 | 2026-09-26 05:43:53
 5  | row 5 |  51.25 | 2026-09-26 05:43:53
(5 rows)

The table was created, the primary key too, and the 5 rows were loaded. This is the default datatype mapping at work:

Oracle columnPostgreSQL column
ID NUMBER (primary key)character varying(50)
NAME VARCHAR2(50)character varying(50)
AMOUNT NUMBER(10,2)numeric(10,2)
CREATED DATEtimestamp(0) without time zone

The primary key is the surprise: a NUMBER without precision becomes a varchar on PostgreSQL. Check the mapping before you let a replicat create tables that an application will query. It can be overridden, which is the subject of another post.

What happens when the table already exists

Between two loads, I added a column to the source table (ALTER TABLE autosch.t1 ADD (note VARCHAR2(20) DEFAULT 'added')). I also inserted a stray row in the PostgreSQL table. Then I reran EXTPGL and pointed the same REPPGL, still with DDL AUTOSCHEMA INCLUDE ALL, at the new file. It did not change the existing table:

2026-09-26 05:53:26  INFO    OGG-06511  Using following columns in default map by name: id, name, amount, created.
2026-09-26 05:53:26  WARNING OGG-01004  Canceled grouped transaction on table autosch.t1. Database error 3505685, (SQLState = 23000 SQLError = 3,505,685 SQLErrorHex = 00357e15 SQLErrorText = [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; duplicate key value violates unique constraint "t1_pkey"(Detail Key (id)=(1) already exists.; sautosch; tt1; nt1_pkey; File nbtinsert.c; Line 666; Routine _bt_check_unique; )).
2026-09-26 05:53:26  ERROR   OGG-01296  Error mapping from AUTOSCH.T1 to autosch.t1.

The new source column NOTE is silently left out of the column mapping: an initial load does not alter an existing table. The load then abends on duplicate key value violates unique constraint "t1_pkey", as the rows are already there. So DDL AUTOSCHEMA INCLUDE ALL creates missing tables only.

The documentation says that for an initial load trail, DDL AUTOSCHEMA “automatically creates or replaces target tables”. It does not say how to get the replacement. The replicat binary has a RECREATE token, and it is a DDL operation type, so it goes in the INCLUDE filter:

DDL AUTOSCHEMA INCLUDE ALL OPTYPE RECREATE

Testing RECREATE

To see what RECREATE does, I prepare a PostgreSQL table that already exists. It has a column that is not on the source and a row that is not in the load:

CREATE TABLE autosch.t1 (
  id varchar(50) PRIMARY KEY,
  name varchar(50),
  amount numeric(10,2),
  created timestamp(0),
  legacy text
);
INSERT INTO autosch.t1 (id, name, legacy)
VALUES ('99', 'stray row', 'pre-existing');

The initial load is delivered the way the documentation describes it:

  • A SOURCEISTABLE extract writes the 5 rows of AUTOSCH.T1 to the extract file ci.
  • A distribution path sends ci to the receiver of the PostgreSQL deployment, as the trail di.
  • A replicat reads the trail di with DDLOPTIONS REPORT and an explicit MAP AUTOSCH.T1, TARGET AUTOSCH.T1;.

With DDL AUTOSCHEMA INCLUDE ALL, the replicat recognizes the initial load by itself. It builds a RECREATE operation from the table definition record, then excludes it: INCLUDE ALL does not cover that operation type.

2026-10-03 13:11:47  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [ /* AutoSchema */ create table "autosch"."t1" (ID varchar(50) not null, NAME varchar(50), AMOUNT decimal(10,2), CREATED timestamp(0), primary key(ID)) (size 150)].
2026-10-03 13:11:47  INFO    OGG-00488  DDL operation excluded [not included by any filter], optype [RECREATE], objtype [TABLE], objowner "autosch", objname "t1".

The 5 rows are loaded into the existing table, next to the stray row, and the legacy column is still there:

 id |   name    | amount |       created       |    legacy
----+-----------+--------+---------------------+--------------
 1  | row 1     |  10.25 | 2026-09-26 09:11:40 |
 2  | row 2     |  20.50 | 2026-09-26 09:11:40 |
 3  | row 3     |  30.75 | 2026-09-26 09:11:40 |
 4  | row 4     |  41.00 | 2026-09-26 09:11:40 |
 5  | row 5     |  51.25 | 2026-09-26 09:11:40 |
 99 | stray row |        |                     | pre-existing
(6 rows)

Second run with the RECREATE filter

The parameter file for this second run only differs from the previous one by the OPTYPE RECREATE filter:

REPLICAT REPPGL
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
DDLOPTIONS REPORT
DDL AUTOSCHEMA INCLUDE ALL OPTYPE RECREATE
MAP AUTOSCH.T1, TARGET AUTOSCH.T1;

With this file and the same table reset, the replicat warns about what it is going to do, then drops the table:

2026-10-03 13:12:34  WARNING OGG-30687  The OPTYPE 'RECREATE' is specified with DDL AUTOSCHEMA option. Please note that the corresponding pre-existing tables will be attempted to be dropped and re-created, only when the REPLICAT is applying initial load trail files. In case of CDC trails, REPLICAT would attempt to create the tables, only when the corresponding tables do not exist already.
2026-10-03 13:12:34  INFO    OGG-00487  DDL operation included [INCLUDE ALL OPTYPE RECREATE], optype [RECREATE], objtype [TABLE], objowner "autosch", objname "t1".
2026-10-03 13:12:34  INFO    OGG-30640  Table autosch.t1 is dropped successfully.
2026-10-03 13:12:34  INFO    OGG-00484  Executing DDL operation.
2026-10-03 13:12:34  INFO    OGG-00483  DDL operation successful.

The table is recreated from the table definition record and loaded, and the legacy column and the stray row are gone.

                            Table "autosch.t1"
 Column  |              Type              | Collation | Nullable | Default
---------+--------------------------------+-----------+----------+---------
 id      | character varying(50)          |           | not null |
 name    | character varying(50)          |           |          |
 amount  | numeric(10,2)                  |           |          |
 created | timestamp(0) without time zone |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)

 id | name  | amount |       created
----+-------+--------+---------------------
 1  | row 1 |  10.25 | 2026-09-26 09:11:40
 2  | row 2 |  20.50 | 2026-09-26 09:11:40
 3  | row 3 |  30.75 | 2026-09-26 09:11:40
 4  | row 4 |  41.00 | 2026-09-26 09:11:40
 5  | row 5 |  51.25 | 2026-09-26 09:11:40
(5 rows)

No parameter tells the replicat that it applies an initial load: a trail written by a SOURCEISTABLE extract is enough. I got the same drop and recreate in three setups. The replicat was added on the trail with EXTTRAIL, added with EXTFILE, or reading a copy of the raw extract file. On a CDC trail, RECREATE does not drop anything, as the warning says.

A wildcard MAP breaks RECREATE on PostgreSQL

The MAP of the previous test was explicit (MAP AUTOSCH.T1, TARGET AUTOSCH.T1;). The baseline of this post used MAP AUTOSCH.*, TARGET AUTOSCH.*;. I replaced it with the wildcard on the same table and the same trail, and nothing else changed:

REPLICAT REPPGL
TARGETDB ogg_pg_01 USERIDALIAS pgalias DOMAIN OracleGoldenGate
DDLOPTIONS REPORT
DDL AUTOSCHEMA INCLUDE ALL OPTYPE RECREATE
MAP AUTOSCH.*, TARGET AUTOSCH.*;

This time, the replicat never drops the table. There is no OGG-30640 in the report, and the CREATE TABLE that follows abends because the table is still there:

2026-10-03 13:17:02  INFO    OGG-06506  Wildcard MAP resolved (entry AUTOSCH.*): MAP "AUTOSCH"."T1", TARGET AUTOSCH."T1".
2026-10-03 13:17:02  INFO    OGG-00487  DDL operation included [INCLUDE ALL OPTYPE RECREATE], optype [RECREATE], objtype [TABLE], objowner "autosch", objname "T1".
2026-10-03 13:17:02  INFO    OGG-00484  Executing DDL operation.
2026-10-03 13:17:02  ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [6844183], [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; relation "t1" already exists(File heap.c; Line 1167; Routine heap_create_with_catalog; )], no error handler present.
2026-10-03 13:17:02  ERROR   OGG-30538  Fatal error executing DDL statement [ /* AutoSchema */ create table "autosch"."t1" (ID varchar(50) not null, NAME varchar(50), AMOUNT decimal(10,2), CREATED timestamp(0), primary key(ID))].

The first line of the report explains it. The wildcard is resolved from the table name in the trail, so the target becomes AUTOSCH."T1": the case of the source (uppercase on Oracle), and quoted. PostgreSQL stores an unquoted name in lowercase, so my existing table is t1, not "T1". The replicat looks for "T1" to drop it, finds nothing, and the CREATE TABLE that follows hits the real t1. To check this, I created the existing table in PostgreSQL as "T1" (quoted, so uppercase). Then I ran the same wildcard replicat: this time the drop works.

2026-10-03 13:27:14  INFO    OGG-06506  Wildcard MAP resolved (entry AUTOSCH.*): MAP "AUTOSCH"."T1", TARGET AUTOSCH."T1".
2026-10-03 13:27:14  INFO    OGG-00487  DDL operation included [INCLUDE ALL OPTYPE RECREATE], optype [RECREATE], objtype [TABLE], objowner "autosch", objname "T1".
2026-10-03 13:27:14  INFO    OGG-30640  Table autosch.T1 is dropped successfully.
2026-10-03 13:27:14  INFO    OGG-00483  DDL operation successful.

The names are matched exactly. With an explicit MAP AUTOSCH.T1, TARGET AUTOSCH."T1"; on a lowercase t1, the replicat created a second table "T1" and left t1 alone. Without quotes, the target of an explicit MAP is lowercased for PostgreSQL, which is why the first test dropped t1. So with RECREATE on PostgreSQL, the target name in the MAP must be spelled exactly like the existing table. Write one explicit MAP per table, unquoted for a lowercase table. The RECREATE parameter file with the explicit MAP above does exactly that.

Limitations

  • AUTOSCHEMA is not for Oracle-to-Oracle replication. Pointing the same DDL AUTOSCHEMA INCLUDE ALL replicat at an Oracle target instead of PostgreSQL abends immediately:

    2026-09-26 05:54:11  ERROR   OGG-30624  AUTOSCHEMA is not allowed in an Oracle to Oracle replication scenario. Please use the DDL replication to control schema evolution.

    Between two Oracle databases, regular DDL replication is still the standard tool for schema evolution, and a GoldenGate initial load still has no automatic way to create the target table.

  • The trail must be format 19.1 or higher with Table Definition Records.

  • UNMAPPED and OTHER scope are not valid with AUTOSCHEMA. Adding INCLUDE UNMAPPED (or INCLUDE OTHER) next to INCLUDE ALL parses fine, but the replicat rejects it at startup:

    2026-09-26 07:12:06  ERROR   OGG-30689  The 'UNMAPPED' is not a valid DDL SCOPE to be used with Automatic Schema Evolution.

To summarize

  • Automatic schema evolution is enabled on the replicat with DDL AUTOSCHEMA INCLUDE ALL.
  • During an initial load from Oracle to PostgreSQL, it creates the missing table and its primary key from the Table Definition Record of the trail (OGG-30633). Then it loads the rows.
  • The datatypes come from a default mapping: check it first, as a NUMBER primary key became a varchar(50).
  • An initial load leaves an existing table alone. A column added on the source is not added on the target, and the rows are loaded into it.
  • DDL AUTOSCHEMA INCLUDE ALL OPTYPE RECREATE drops and recreates an existing table, only when the trail is an initial load trail (OGG-30640). INCLUDE ALL alone excludes that operation (OGG-00488).
  • Use one explicit MAP per table with RECREATE on PostgreSQL. A wildcard MAP looks for the table under the source name in quotes and misses a lowercase table. The CREATE TABLE then abends with relation "t1" already exists.
  • It is not available between two Oracle databases. Pointing it at an Oracle target from an Oracle source abends with OGG-30624. An Oracle-to-Oracle initial load still needs the target table to already exist, exactly as before.
Post Views: 48
Thumbnail [60x60]
by
Julien Delattre