Table with Trigger for AQ Event Notification, Notification Fails if Table in Replication with GoldenGate Replicat (Doc ID 2827972.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

In an Oracle GoldenGate (OGG) replication environment with OGG 19c and target Oracle 12.2 database with replicat, a target table in replication has a TRIGGER for AFTER INSERT, UPDATE , DELETE that enqueues a message into a queue configured for Advanced Queuing (AQ) event notification.

If replicated DML on the target table is processed by OGG replicat i.e. executed by replicat, the trigger fires and a message is enqueued into the queue as expected; however, the message remains in READY state. AQ EMON (event monitor) does not acknowledge the presence of the message in the queue to initiate the event notification process.

If DML is executed directly on the target table in the target database via SQLPlus, the trigger fires and a message is enqueued into the queue as expected.  In this case, though, EMON acknowledges the message and subsequently enqueues a message in the notification queue SYS.AQ_SRVNTFN_TABLE_Q_1.  This initiates the event notification process whereby a Scheduler job is created to execute the callback procedure for event notification.

For example, the following is observed:

-- <USER> and <USER_QUEUE_TABLE> are substitutions for the queue table configured for AQ PL/SQL event notification. The target table has a trigger that fires on DML to enqueue a message into the queue of this queue table.

SQL> select count(*), msg_state from <USER>.<USER_QUEUE_TABLE> group  by msg_state;

  COUNT(*) MSG_STATE
---------- ----------------
         1 PROCESSED      -- DML executed directly on target table e.g. from SQL*Plus
         1 READY              -- DML executed by OGG replicat

Changes

 The target table was configured for OGG replication, and DML executed at the source was replicated to the target database for processing by replicat.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.