Manage Schema Evolution

Managing schemas requires tracking and synchronizing structural changes such as tables, columns, indexes, and data types between source and target systems. An effective schema management technique ensures that replication continues smoothly when applications evolve or database changes are deployed. It also helps prevent failures caused by mismatched structures, unsupported data types, or missing objects.

Oracle GoldenGate provides techniques to manage schemas for homogeneous or like to like data replication and heterogeneous or cross-platform replication. For homegeneous replication, administrators can use the existing DDL Replication functionality, which is available with Oracle to Oracle and MySQL to MySQL databases.

For heterogeneous replication, administrators can use Automatic Schema Evolution functionality. Automatic schema evolution in Oracle GoldenGate provides schema validation, change detection, automated mapping, and controlled rollout of schema updates. Introduced with the Oracle GoldenGate 26ai (23.26.3.0.1) release, this functionality allows replication between a wide range of heterogeneous source and target database schemas. The supported list of source and target database schemas for Automatic Schema Evolution includes (but is not limited to) Oracle, MySQL, PostgreSQL, SQL Server, DB2LUW, DB2400, DB2z/OS, Teradata, Timesten databases along with DAA targets like ADW, AIDP, BigQuery, Databricks, Iceberg, Redshift, Snowflake, Synapse.

Differences between Automatic Schema Evolution and DDL Replication

Here is a comparison between Automatic schema evolution and DDL replication-based schema management:

Automatic Schema Evolution	DDL Replication
Automatically detects schema differences and updates the target schema as needed.	Replicates actual DDL statements, such as ALTER TABLE, CREATE TABLE, or DROP COLUMN, from source to target using DDL and DML operations.
Easier for users because many schema changes are handled without manual intervention.	Requires the source DDL to be supported and correctly interpreted by the replication system.
Works at a logical or metadata level	Works by capturing and applying database schema-change commands.
Support only selected schema changes, such as adding columns or changing compatible data types.	Can preserve the exact intent of source-side schema changes.
Useful when source and target systems are different platforms or cloud services.	Best suited when source and target databases have similar DDL syntax and object behavior.