Advanced Queuing

Date: 16/May/2002

Automatic Message Propagation in Intranet applications

Introduction

Message Propagation is a feature in Advanced Queuing that helps to propagate messages across queues. It offers guaranteed, automatic, and tunable propagation from a queue in an Oracle database to the queues that are present in other Oracle databases across the Internet. It also has the advance feature of exception handling that tells if the messages are propagated or not in case of an exception.

The way messages are propagated between two different databases depends on the location where the databases are located. Message Propagation in Internet is not covered here. The initial steps involved for propagation in an intranet are:

•   The value of the system parameter Job_queue_processes must be at least 2 and aq_tm_process must be at least 1, for propagation to take place.
•   Grant the permissions EXECUTE ON DBMS_AQ and EXECUTE ON DBMS_AQADM for the database users between which the propagation has to take place.
•   Create the queue table and the queue.
•   Start the queues.

Description

In intranet propagation messages are propagated by using Oracle Net Services (formerly Net8). Assume that the database user that is enqueuing the message is called producer and the user which is dequeuing the message is consumer.   Adminproductqueue be the name of the queue in the producer and retailproductqueue in the consumer. The steps involved to propagate messages depends on the location of the databases. The possible cases involved and the steps need to be taken in each case are explained below:

1) When both users are located in the same database

If the producer and the consumer are two different users present in the same database, the dblink need not be created to propagate the messages. The steps involved here after creating the queue table and queue are:

Step 1: Add consumer as a subscriber in producer.

BEGIN
dbms_aqadm.add_subscriber (
    queue_name => 'producer.adminproductqueue',
    subscriber => sys.aq$_agent('Consumer','consumer.retailproductqueue',null)
);
END;
/

where  Consumer is the name of the agent. This is the name of the agent that has to be specified while dequeuing.

Step 2: Schedule the propagation in the producer.

BEGIN
  dbms_aqadm.schedule_propagation(
  queue_name => 'producer.adminproductqueue',
  latency => 0);
END;
/

2) When both users are located in different databases within the firewall

Even in the case where the users are present in two different databases located within the firewall the messages are propagated using Oracle Net Services. But a database link needs to be created for that. The steps involved in this case are:

Step 1: Create a database link between the producer and consumer in the producer.

CREATE DATABASE LINK loopback CONNECT TO consumer IDENTIFIED BY consumer
		USING 'incq226b.idc.oracle.com';
  

where loopback is the name of the database link
      consumer is the username and password of the database where messages can be dequeued and
      incq226b.idc.oracle.com is the address where the consumer is located.

Step 2: Add consumer as a subscriber in producer

BEGIN
DBMS_AQADM.ADD_SUBSCRIBER (
    queue_name => 'producer.adminproductqueue',
    subscriber => sys.aq$_agent('Consumer','consumer.retailproductqueue@loopback',null)
);
END;

/

Step 3: Schedule the propagation in the producer.

BEGIN
  dbms_aqadm.schedule_propagation(
  queue_name => 'producer.adminproductqueue',
  destination => 'loopback',
  latency => 0);
END;
/

where destination specifies the location of the database to which the messages should be dequeued. The database link name must be specified in the procedure as the destination.

After performing the preceding steps messages can be enqueue from producer and dequeued from consumer.

Resources  

•   OTN - Message Propagation.
•    Advanced Queuing Developers Guide
•    Sample on Advanced Queuing