Keeping data synchronized is essential. Keeping database schemas synchronized as applications evolve shouldn’t be complicated. 

Modern applications are constantly changing. New business requirements introduce additional columns, existing data structures evolve, and database schemas must adapt to support new functionality.  For organizations replicating data across multiple databases, particularly those using different database technologies, even a simple schema change can introduce additional operational complexity. 

Consider a company replicating transactional data from PostgreSQL to Oracle Database. When an application introduces a new column in the PostgreSQL source database, the corresponding target table must also be updated to accommodate the new data.  Traditionally, this may require database administrators to coordinate schema changes, translate DDL statements between database platforms, validate data type compatibility, and ensure that replication continues operating correctly. 

What if Oracle GoldenGate could automatically propogate schema changes as part of the replication process?

With the introduction of Automatic Schema Evolution (AUTOSCHEMA) in Oracle GoldenGate 26ai (23.26.4), GoldenGate brings automated schema management to supported homogeneous and heterogeneous replication environments.  This capability helps organizations reduce manual schema maintenance, simplify database onboarding, and manage application-driven schema changes more efficiently. 

Introducing Automatic Schema Evolution 

Oracle GoldenGate has long enabled real-time data replication across heterogeneous database environments. However, keeping the source and target schemas aligned as applications evolve has traditionally required additional coordination, particularly when replicating between databases with different SQL dialects and data types. 

AUTOSCHEMA introduces a metadata-driven approach to managing supported target schema changes. 

Unlike traditional DDL replication, which captures and propagates source database DDL statements, AUTOSCHEMA leverages the built-in table –definition metadata available in GoldenGate trail files.  GoldenGate Replicat uses this metadata to identify supported differences between the incoming source table definition and the existing target database schema. 

It can then generate and execute the appropriate target-specific DDL statements using compatible data types and database syntax.  This is particularly valuable for heterogeneous replication, where a source database DDL statement may not be directly executable on the target database.  Rather than requiring identical DDL syntax across database platforms, AUTOSCHEMA uses the source metadata to determine how supported structural changes should be represented on the target. 

Why Does Automatic Schema Evolution Matter? 

As enterprises modernize their applications, migrate databases, and adopt heterogeneous data architectures, schema changes become an ongoing part of maintaining data replication pipelines. 

These changes can introduce several operational challenges: 

  • Manual schema synchronization: Database administrators must coordinate and implement compatible target-side schema changes. 
  • Heterogeneous database complexity: Source and target platforms may use different SQL syntax, data types, precision limits, and naming conventions. 
  • Operational overhead: Even relatively simple schema changes can require additional planning, validation, and intervention. 
  • Replication disruption risks: Missing or incompatible target schema changes can prevent new data from being applied successfully. 

AUTOSCHEMA helps address these challenges by making supported schema evolution part of the GoldenGate replication workflow. 

Advantages of GoldenGate Automatic Schema Evolution

1. Simplify Database Onboarding with Automatic Target Table Creation  

When setting up replication, corresponding target tables often need to be created before data can be applied.  With AUTOSCHEMA, GoldenGate Replicat can automatically generate and execute a CREATE TABLE statement when a mapped target table is missing. 

The generated table definition is based on source table metadata, supported key information, compatible target data types, and configured mapping preferences. This can simplify initial-load and ongoing CDC scenarios by reducing the need to manually provision corresponding target tables. 

2. Automatically Manage Supported Schema Changes 

Applications continuously evolve, and those changes frequently introduce modifications to database table structures.  AUTOSCHEMA supports target schema evolution operations such as adding, modifying, and dropping columns, subject to the capabilities and limitations of the supported database configuration. 

For example, when a new column is introduced into the source table, GoldenGate can identify the additional column from the incoming table metadata and generate a compatible ALTER TABLE statement for the target database.  This reduces the manual coordination that would otherwise be required to keep the target schema compatible with the source. 

3. Enable Schema Evolution Across Heterogeneous Databases 

One of the most significant benefits of AUTOSCHEMA is its ability to manage supported schema evolution across different database technologies.  Traditional DDL replication works by capturing and applying database DDL operations in supported replication environments. 

AUTOSCHEMA takes a different approach.  It uses source table metadata to generate DDL appropriate for the target database, helping address differences in database syntax and supported data types.  For organizations modernizing their applications, migrating between database platforms, or integrating heterogeneous databases, this provides a more automated way to manage compatible schema structures across replication environments. 

4. Intelligent Data Type Mapping with Greater Flexibility 

Different databases often represent similar data using different native data types. A numeric or character column defined in one database may not have an exact equivalent in another. Database platforms may also differ in the maximum supported length, precision, or scale of certain data types. 

AUTOSCHEMA uses database-specific default mappings to select a suitable target data type based on the source column metadata.  Where a compatible mapping is available, GoldenGate attempts to accommodate the source column’s data using an appropriate target representation. 

For application-specific requirements, administrators can use AUTOSCHEMAOPTIONS TYPEMAP to override default mappings and specify preferred target data types for selected source columns or data types. This gives organizations both automation and flexibility when replicating across heterogeneous databases. 

5. Flexibility and Control Over Schema Evolution 

Not every replication pipeline needs automatic schema evolution for every table.  AUTOSCHEMA can be enabled through the DDL AUTOSCHEMA parameter in GoldenGate Replicat, with inclusion and exclusion criteria to control which mapped objects participate. 

Administrators can also configure data type mapping preferences globally or at individual table-mapping levels. This enables organizations to adopt automatic schema evolution selectively while maintaining control over target schema definitions. 

How Does AUTOSCHEMA Work? 

Automatic Schema Evolution relies on the metadata already available in the Oracle GoldenGate trail files. 

At a high level, the workflow consists of four stages: 

1. Capture source changes and metadata  

GoldenGate Extract captures database changes and supplies table-definition metadata in the trail.  The required source-side configuration and schema-change tracking procedures must be followed to ensure updated table metadata is available to downstream replication. 

2. Identify schema differences 

As Replicat processes the incoming table-definition records, it resolves the corresponding target table definition and identifies supported differences, such as missing tables or columns. 

3. Generate compatible target DDL 

Based on the source metadata and target database capabilities, AUTOSCHEMA generates the required CREATE TABLE or ALTER TABLE statements using appropriate target database syntax and data types. 

4. Apply schema changes as part of replication 

GoldenGate executes the generated DDL according to the configured rules. Once the target schema change completes successfully, Replicat can continue processing compatible data changes against the updated schema. This metadata-driven approach reduces the need to manually translate and apply supported schema changes on heterogeneous target databases. 
Consider replicating data from PostgreSQL to Oracle Database. When an application introduces a new column in the PostgreSQL source table, GoldenGate can use the updated source metadata to generate and apply a compatible schema change on the Oracle target. This helps eliminate the need to manually translate and execute the corresponding target DDL for supported changes. 

Explore Automatic Schema Evolution 

Automatic Schema Evolution is available in Oracle GoldenGate 26ai for supported database configurations, helping organizations simplify schema management across heterogeneous replication environments. 

To explore its capabilities, supported configurations, and prerequisites, refer to the Oracle GoldenGate Automatic Schema Evolution documentation. 

Want to see AUTOSCHEMA in action? 

In our next blog, we’ll walk through a hands-on heterogeneous replication scenario, demonstrating automatic target table creation, schema evolution during ongoing replication, custom data type mappings, and key operational considerations.