Target DDL Notification

November 19, 2024 | 5 minute read
Volker Kuhr
Senior Principal Product Manager
Text Size 100%:

Oracle Database 23ai introduces an exciting new feature for DDL Notification, and Oracle GoldenGate 23ai leverages this innovation to enhance Replicat at the target database.

Ever considered what happens if the target objects change while Replicat is running? With Oracle Database and GoldenGate 23ai, Replicat is automatically notified and promptly revalidates the SQL statement used in GoldenGate to apply the changes. This new feature not only enhances the robustness of Oracle GoldenGate but also prevents data inconsistencies that could arise if changes at the target database are not carefully managed.

In the following sections, I will drill down into the database feature first, before focusing on Oracle GoldenGate and providing a negative test case – showing the benefit why to use GoldenGate 23ai.

Oracle Database

Oracle Database has a long history of leveraging Database Change Notifications, and this technology has been continuously improved with each new release. The key innovation lies in the client application subscribing to the database, allowing the database instance to push the requested notifications directly to the application. This approach is highly efficient, reducing network round trips since clients no longer need to poll the database repeatedly. As soon as a database change occurs, the notification is immediately pushed to the client. In Oracle 23c, applications that need to access table metadata can now take advantage of Table DDL Change Notifications. This feature is particularly useful for database clients that cache table metadata in the middle tier. Clients can register to receive notifications for DDL changes on any of the following:

  • list of tables
  • list of schemas
  • all database tables.

If you're interested in learning more about this database feature, be sure to check out the Application Developer Guide (link). It includes source code examples using the Oracle Call Interface, showing how applications can take advantage of this new feature.

Oracle GoldenGate

GoldenGate 23ai takes full advantage of the DDL Notification Database feature at the target system within the Replicat. You might have observed the following information within the GoldenGate report file:

 2024-10-19 01:56:56  INFO    OGG-30192  Target Table DDL Notification turned ON.


By default, Target DDL Notification is enabled. If you dislike, you could disable target DDL NOTIFICATION with the Replicat parameter setting:

 NOTARGETDDLNOTIFY                                                                                  

When DDL operations occur on replicated tables within the target database, Replicat is notified of the change, causing it to invalidate the cached SQL statements for that table within GoldenGate. Replicat will then treat the table as if it has never encountered a change before and will generate a new DML statement based on the updated structure of the target table.

(Negative) TestCase

I want to highlight the benefits of this new feature by walking through a negative test case, where Target DDL Notification is not enabled (as was the case in previous GoldenGate releases). Before beginning the demonstration, I want to emphasize that it has always been the responsibility of the developer or administrator to carefully manage database changes in a replication environment. While GoldenGate can handle tables with different shapes and structures at the source and target (such as through column mapping), it remains the responsibility of the project manager to perform these operations appropriately.
Before starting with the demonstration, I would like to emphasis that it has always been in the responsibility of the engineer/administrator to manage database changes to a replication environment carefully. GoldenGate is able to manage tables at source and target with different shapes (using column mapping as one example). However,  it is the responsibility of the developer/application tp perform those operations projected.

  1. Let’s assume there is a unidirectional GoldenGate replication and add the table U1.T01 to the system.
    CREATE TABLE u1.t01(a INT PRIMARY KEY, b VARCHAR2(30), c DATE);
    If DDL Replication is enabled, the table will be created at the target database.
  2. If an INSERT operations populates all 3 columns at he source database, all 3 columns will be populated at the target database:
    INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "U1"."T01" ("A","B","C") VALUES (:a0,:a1,:a2);
    This INSERT operation in this example is simplified for educational purpose, I want to highlight that bind variables are in use. Whenever a similar INSERT happens at the source database, the same SQL statement will be used at the target database.
  3. While Replicat is running at the target system, I am dropping a column from the target table:
    ALTER TABLE u1.t01 DROP COLUMN c;
  4. If an INSERT operation from the source system comes across that INSERTS all 3 columns again, Replicat still tries the old SQL statement and abends as the target table only has 2 columns now.
  5. If you are restarting Replicat manually, a new SQL statement  will be generated. As this is the 1st statement for this table in the new run, GoldenGate automatically manages the condition of having a different table shape and will construct a correct SQL statement so that the change gets applied at the target system:
    INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "U1"."T01" ("A","B") VALUES (:a0,:a1);

In the past, this caused some confusion, as engineers wondered why Replicat was applying changes even though no modifications had been made to the Replicat parameter settings. The situation became even more confusing when using the Managed Process Profile with the AUTORESTART option. In this case, the ggserror.log and report files would show Replicat abending, restarting, and re-processing without any issues.
In contrast, with GoldenGate 23ai and Target DDL Notification enabled, Replicat would seamlessly continue processing without disruption.

I would like to emphasize the negative test case a bit more:

  1. Let’s assume that the administrator from the target database adds (and repopulates) the earlier dropped column C back to the system:
     ALTER TABLE u1.t01 ADD s DATE default to_date('01-01-2000','MM-DD-YYYY');
  2. As the statement used by  GoldenGate is not touched, the previously new generated INSERT operation only using the 2 columns A and B would be in use:
    INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "U1"."T01" ("A","B") VALUES (:a0,:a1);
    Data divergence between source and target database might occur!

In contrast to this negative test case, GoldenGate 23ai with Target DDL Notification enabled, the new feature provides resilience and data consistency. with GoldenGate 23ai and Target DDL Notification enabled.

One additional note as we combine a Database and GoldenGate enhancement:
As the Database notification is passed to GoldenGate with the listener, you have to pay attention to the listener configuration. If you are using the default Listener on port 1521, there is no need to change. If your database system is using a non-default listener (not using the default port), you explicitly have to set the LOCAL_LISTENER parameter within the database:
ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS=(PROTOCOl=TCP)(HOST=<server>)(PORT=<port>))';

In this blog post, I discuss the new Target DDL Notification feature introduced in Database 23ai and GoldenGate 23ai. I explain how this feature addresses challenges from earlier versions, where DDL changes to the target database were not always handled effectively. With Target DDL Notification now enabled by default, it improves system resilience and helps prevent accidental data inconsistencies.

Volker Kuhr

Senior Principal Product Manager

Volker is a Senior Principal Product Manager working in the GoldenGate Development group.
His primary focus is on the GoldenGate Core Product, mainly GoldenGate for Oracle. Key topics are Performance, High Availability, Security, and Resilience.
Volker has worked for more than 20 years in the field of database technology and data replication.
He has supported customers worldwide in different industries to develop & manage distributed database systems/applications and build Data Integration Solutions.

Show more

Previous Post

Oracle GoldenGate 23ai now supports Open Mirroring in Microsoft Fabric

Shrinidhi Kulkarni | 9 min read

Next Post


Oracle GoldenGate Veridata 12.2.1.4.241210 is now available!

Oracle Chatbot
Disconnected