How to Work with Queue Propagation in Oracle

In the performance-tuning module you learned how to create queues, build a PL/SQL application that uses Advanced Queues (AQ), and run that application inside a single Oracle database. This lesson moves to the network administration perspective: you will see how messages can travel from a queue in one database to a queue in another database using queue propagation.

Queue propagation allows you to keep processing local while sharing information globally. Branch systems can enqueue work locally and have selected messages forwarded to a central database, a reporting system, or a partner site—without rewriting the application to connect directly to every destination.

The figure shows the typical propagation layout:

• A local database that owns the application and a source queue.
• A remote database that owns a destination queue.
• Oracle Net connectivity between the databases (listeners, services, and either Easy Connect Plus strings or tnsnames.ora entries).
• A database link defined on the local database that points to the remote database.
• A propagation schedule that tells Oracle when and how to forward messages from the source queue to the destination queue.

Your goal in this lesson is to configure those pieces so that messages placed on the local queue are automatically propagated to the remote queue.

There are two main ways to send messages from one database to another:

• Remote enqueue – Your PL/SQL code calls DBMS_AQ.ENQUEUE across a database link (for example, DBMS_AQ.ENQUEUE@remotedb_link) directly into the remote queue.
• Queue propagation – Your application enqueues messages into a local source queue. A background process—configured with DBMS_AQADM—reads from the source queue and forwards messages to the remote destination queue according to a schedule and optional rules.

Remote enqueue is simple but tightly couples your application to the remote database. Queue propagation keeps the application logic local and lets DBAs manage routing, schedules, and destinations centrally. This lesson focuses on propagation.

Before you can configure propagation, make sure the following prerequisites are satisfied:

1. Network connectivity
   Configure Oracle Net so that the local database can reach the remote database. For modern environments, an Easy Connect Plus string is often sufficient:

   
   -- Example Easy Connect Plus service name (used by DB link)
   remotedb_high = 
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = remote-host.example.com)(PORT = 1521))
       (CONNECT_DATA = (SERVICE_NAME = remotedb_high.example.com))
     )
         

   Verify connectivity with tnsping or a test connection.
2. Database link from local to remote
   On the local database, create a database link that uses the service name or Easy Connect string:

   
   CREATE DATABASE LINK remotedb_link
     CONNECT TO remote_user IDENTIFIED BY remote_password
     USING 'remotedb_high';
         

   The user remote_user must exist on the remote database and have access to the destination queue.
3. Queues on both databases
   Each database must have a queue table and queue defined. The payload types for the source and destination queues must be compatible. Typically you:
   • Create a queue table and queue on the local database for the application.
   • Create a corresponding queue table and queue on the remote database.
4. Privileges
   The owner of the queue and the user referenced in the database link need appropriate privileges:
   • EXECUTE on DBMS_AQ and DBMS_AQADM.
   • ENQUEUE on the source queue and ENQUEUE/DEQUEUE on the destination queue, as appropriate.

On the remote database, create the queue table and queue that will receive propagated messages. This example uses a JMS text message payload type for flexibility:

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'REMOTE_QTAB',
    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
    multiple_consumers => FALSE
  );

  DBMS_AQADM.CREATE_QUEUE(
    queue_name  => 'REMOTE_QUEUE',
    queue_table => 'REMOTE_QTAB'
  );

  DBMS_AQADM.START_QUEUE(
    queue_name => 'REMOTE_QUEUE'
  );
END;
/
  

Grant privileges on this queue to the user referenced in the database link:

BEGIN
  DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(
    privilege   => 'ENQUEUE',
    queue_name  => 'REMOTE_QUEUE',
    grantee     => 'REMOTE_USER',
    grant_option => FALSE
  );
END;
/

GRANT EXECUTE ON SYS.AQ$_JMS_TEXT_MESSAGE TO REMOTE_USER;
  

On the local database, you already have an application queue from the performance-tuning module. If not, create a compatible queue table and queue:

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'LOCAL_QTAB',
    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE'
  );

  DBMS_AQADM.CREATE_QUEUE(
    queue_name  => 'LOCAL_QUEUE',
    queue_table => 'LOCAL_QTAB'
  );

  DBMS_AQADM.START_QUEUE(
    queue_name => 'LOCAL_QUEUE'
  );
END;
/
  

Next, create a propagation schedule that forwards messages from LOCAL_QUEUE to REMOTE_QUEUE using the database link REMOTEDB_LINK:

BEGIN
  DBMS_AQADM.SCHEDULE_PROPAGATION(
    queue_name            => 'LOCAL_QUEUE',
    destination           => 'REMOTEDB_LINK',
    destination_queue     => 'REMOTE_QUEUE',
    start_time            => SYSTIMESTAMP,
    propagation_window    => NULL,     -- continuous
    next_time             => NULL,
    latency               => 0
  );
END;
/
  

This schedule tells Oracle to start propagating messages from the local queue to the remote queue as soon as they are ready. You can limit propagation to certain windows or specify a minimum latency if you want to batch messages.

With both queues defined and the propagation schedule in place, test the configuration by enqueuing a message into the local queue and confirming that it appears in the remote queue.

6.1 Enqueue a message locally

DECLARE
  l_enq_opts    DBMS_AQ.ENQUEUE_OPTIONS_T;
  l_msg_props   DBMS_AQ.MESSAGE_PROPERTIES_T;
  l_msg_handle  RAW(16);
  l_message     SYS.AQ$_JMS_TEXT_MESSAGE;
BEGIN
  l_message := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
  l_message.set_text('Order 12345 from branch database');

  DBMS_AQ.ENQUEUE(
    queue_name         => 'LOCAL_QUEUE',
    enqueue_options    => l_enq_opts,
    message_properties => l_msg_props,
    payload            => l_message,
    msgid              => l_msg_handle
  );

  COMMIT;
END;
/
  

6.2 Monitor propagation

On the local database, you can monitor the propagation schedule:

SELECT destination, schedule_disabled, last_error_msg
  FROM   user_queue_schedules
  WHERE  queue_name = 'LOCAL_QUEUE';
  

On the remote database, confirm that the message has arrived:

SELECT msgid, user_data
  FROM   REMOTE_QTAB;
  

If the message appears in REMOTE_QTAB, propagation is working correctly. If not, check the schedule status, database link, and network connectivity.

• Security
  Restrict who can create database links and who can enqueue or dequeue from queues. Consider using Oracle Wallet or Secure External Password Store to avoid embedding passwords in the database link definition.
• Performance
  Use propagation windows and latency controls to balance timeliness and workload. High-volume systems may benefit from sharded queues or separate queues for different message types.
• Monitoring
  Use AQ data dictionary views (DBA_QUEUES, DBA_QUEUE_SCHEDULES, DBA_QUEUE_TABLES) and database alerting to detect failures, backlogs, or disabled schedules.
• Error handling
  Configure exception queues or alert mechanisms so that messages that cannot be delivered are not silently lost. Review error messages in the schedule views and database alert logs.

Queue propagation extends the Advanced Queuing techniques you used in the performance-tuning module into a distributed environment. Instead of teaching the basics of queues or showing how to run the entire AQ application, this lesson focused on:

• Understanding the architecture of local and remote queues linked by Oracle Net.
• Configuring network connectivity and database links for propagation.
• Creating compatible queues on local and remote databases.
• Defining a propagation schedule with DBMS_AQADM.SCHEDULE_PROPAGATION.
• Testing and monitoring message flow between databases.

With these skills, you can design Oracle-based systems where each site processes its own workload locally while still sharing key events and transactions through reliable, database-integrated messaging.

Advanced Queuing (AQ): A database-integrated messaging feature that stores messages in queues, supports transactional enqueue/dequeue operations, and can propagate messages between databases over Oracle Net.

[1]Advanced Queuing (AQ): A utility to store transactions in a queue, distribute the queue to other databases, and then retrieve and run the queued transactions.