Propagating Table Evolution Changes from Source to Target

Oracle GoldenGate replicates the table changes including the missing table events. When AUTOSCHEMA is enabled, the missing table event is detected during table mapping, more specifically, while processing the Table Definition Record (TDR) from the trail file.

The following steps explain this workflow:

  1. Replicat attempts to resolve the target table’s metadata. If the metadata resolution fails with the object not found error, it indicates that the target table does not exist on the target database.

  2. If the AUTOSCHEMA functionality is enabled, then the CREATE TABLE statement, for the missing target table, is generated based on the source metadata in the TDR record.

    1. The default data type mapping template, as per the target database type, is used while choosing suitable data types for the columns of the target table.

    2. The CREATE TABLE statement is generated based on the SQL syntax of the target database, considering the user preferences, if specified. You can specify the following preferences:

      1. Target table name

      2. By default, the names for the target table columns are the same as the source table columns, however, you can choose different names using COLMAP.

      3. You can choose custom data type names (available on the target database) for specific source data type names (or source columns) using TYPEMAP. Suitable in cases, where the supported data types (and their limits) can differ between source and target databases.

  3. The creation of new tables using the generated CREATE TABLE statements is handled automatically by the replication process, provided the operation meets the configured filtering criteria (such as INCLUDE/EXCLUDE rules). If a temporary issue occurs, the system attempts to apply the change several times depending on the configured retry settings. If the change still cannot be applied after these retries, replication will stop, and an error is reported.

Propagating Column Evolution in Target Tables

The missing column in the target table event is detected during the table mapping. More specifically, while processing the table definition record (TDR).

  1. Replicat attempts to resolve the target table’s metadata. In this case, as the target table exists, the metadata resolution does not fail but a valid target table definition structure is returned.

  2. If AUTOSCHEMA is enabled, then the metadata in the target table definition is compared against the source table’s metadata in the incoming TDR record. This is to detect any additional columns in the source table metadata in the TDR record that are not available in the target table definition, that were retrieved in the previous step.

    1. The default data type mapping template as per the target database type, is used while choosing the most suitable data types for the new columns of the target table.

    2. The ALTER TABLE ADD COLUMN statement is generated, based on the SQL syntax of the target database.

      You can choose custom data type names (available on the target database) for specific source data type names (or source columns). Suitable in cases, where the supported data types (and their limits) can differ between source and target databases.

  3. If there are any such additional columns identified, then the ALTER TABLE ADD COLUMN statement, for the existing target table, is generated based on the source metadata in the TDR record (more specifically the additional column(s) in the TDR record).

The execution of the generated ALTER TABLE DDL is performed by the existing DDL replication infrastructure, only when the corresponding DDL passes the DDL filetring criteria (DDL INCLUDE/EXCLUDE criteria with OBJTYPE/OPTYPE/OBJNAME options). As the DDL replication infrastructure is used for AUTOSCHEMA as well, the retry logic on non-fatal error codes also gets used. The retry logic also has a configurable number of retries and a delay between retries. If the generated DDL execution fails even after the retries, then Replicat abends.