Lesson 3	Queue propagation
Objective	Work with Queue Propagation.

Work with Queue Propagation

Prior to Oracle 8, you could queue messages for later retrieval and execution within a single database instance. The Oracle queue functionality did not work across a database link to a distributed database. With Oracle 8i, you could use Advanced Queuing (AQ)^[1] message propagation to enqueue to a remote queue at a remote database. In addition, you could use the scheduling feature to specify the start time and the propagation window. To enable users and a local database to enqueue messages to a remote queue at a remote database using Oracle 23c Advanced Queuing (AQ), you must configure both database-level and network-level components. The process involves setting up database links, queue ownership and grants, and ensuring that the queue table and queue on the remote side are correctly configured.

Preliminary Steps to Enable Remote Enqueueing in Oracle 23c

Configure Oracle Net Services

Ensure the TNS entry for the remote database is defined in the tnsnames.ora file on the local (enqueueing) database:

          REMOTEDB =
            (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = remote-host)(PORT = 1521))
              (CONNECT_DATA =
                (SERVICE_NAME = remotedb_service)
              )
            )

Create a Database Link (DB Link)
- On the local database, create a public or private database link to the remote database where the queue resides:
```
  CREATE DATABASE LINK remotedb_link
	CONNECT TO remote_user IDENTIFIED BY password
	USING 'REMOTEDB';
```
  - remote_user must have appropriate privileges on the remote queue.
  - Ensure the remote user can access the queue and has ENQUEUE privileges.

Set Up the Remote Queue (on Remote DB)

On the remote database, create the queue table and queue if they do not already exist:

  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 Permissions to Remote User
- Still on the remote database, grant enqueue privileges:
```
  BEGIN
	DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(
	  privilege => 'ENQUEUE',
	  queue_name => 'remote_queue',
	  grantee => 'remote_user',
	  grant_option => FALSE
	);
  END;
  /
```
  You may also need to grant EXECUTE on message types (like SYS.AQ$_JMS_TEXT_MESSAGE) and SELECT on the queue table, if applicable.

Enable Remote Enqueueing from Local DB

Back on the local database, use the database link to enqueue remotely:

  DECLARE
	enqueue_options     DBMS_AQ.ENQUEUE_OPTIONS_T;
	message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
	message_handle      RAW(16);
	message             SYS.AQ$_JMS_TEXT_MESSAGE;
  BEGIN
	message := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
	message.set_text('Hello from Oracle 23c!');

	DBMS_AQ.ENQUEUE@remotedb_link (
	  queue_name          => 'remote_queue',
	  enqueue_options     => enqueue_options,
	  message_properties  => message_properties,
	  payload             => message,
	  msgid               => message_handle
	);
  END;
  /

Verify AQ Setup
- Use the following query on the remote DB to confirm the message was enqueued:
```
  SELECT msgid, user_data FROM remote_qtab;
```

🛡️ Security and Access Notes

Ensure SQL*Net connectivity from local to remote works (via tnsping or sqlplus).
If using Oracle wallets or Secure External Password Store (SEPS), configure accordingly for passwordless links.
Check firewall rules, listener configuration, and network ACLs if required.

Here is a full "Oracle 23c PL/SQL + SQL script" to automate the setup required for remote enqueuing. This script is written from the local database perspective (enqueue initiator), but includes instructions for the remote database as well. It assumes both databases are properly connected via Oracle Net.
🔧 Step-by-Step Oracle 23c AQ Remote Enqueue Setup Script
📝 Note: Run the REMOTE sections on the remote database and the LOCAL sections on the local database.
🔹 [REMOTE] Step 1 – Create Queue Table and Queue

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;
/

🔹 [REMOTE] Step 2 – Grant Permissions to Remote User

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;

Replace `REMOTE_USER` with the user account used in the DB link. --- 🔹 [LOCAL] Step 3 – Create Database Link to Remote

CREATE DATABASE LINK remotedb_link
  CONNECT TO REMOTE_USER IDENTIFIED BY remote_password
  USING 'REMOTEDB';  -- entry in tnsnames.ora

🔹 [LOCAL] Step 4 – Enqueue Message to Remote Queue

DECLARE
  enqueue_options    DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_handle     RAW(16);
  message            SYS.AQ$_JMS_TEXT_MESSAGE;
BEGIN
  message := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
  message.set_text('Hello from Oracle 23c automated script!');

  DBMS_AQ.ENQUEUE@remotedb_link (
    queue_name         => 'remote_queue',
    enqueue_options    => enqueue_options,
    message_properties => message_properties,
    payload            => message,
    msgid              => message_handle
  );
END;
/

🛠️ Optional – Verify on Remote Side

SELECT msgid, user_data FROM remote_qtab;

📌 Important Assumptions and Tips

SYS.AQ$_JMS_TEXT_MESSAGE is a predefined Oracle AQ message type.
Ensure REMOTEDB TNS entry exists and is reachable (tnsping REMOTEDB).
REMOTE_USER must be a valid user with required permissions.
Run this script in SQL*Plus or SQL Developer as a DBA or privileged AQ user.

Preparing to use Advanced Queuing in Oracle

The following series of images describes how this works.

1) The first step in preparing to use advanced queueing is to adjust the parameter of the database — 1) "The first step in preparing to use advanced queueing is to adjust the database’s parameter. The initialization parameter called JOB_QUEUE_PROCESSES defaults to zero. To use advanced queueing, you must set this parameter to at least 2 in all the databases that are to use advanced queuing."

2) You must login as SYS or SYSTEM and grant a privilege to the user — 2) Next, you must log in as SYS or SYSTEM and grant a privilege to the user who will create a queue. A user that creates his or her own queues must have the EXECUTE privilege on DBMS_AQADM. This user can enqueue and dequeue messages in his or her own queues as well as create, drop, or monitor his or her own queues. A user who creates, drops, and monitors other user’s queues must have a role called AQ_ADMINISTRATOR_ROLE granted to him or her. Both SYS and SYSTEM may grant this role to other users."**

3) After creating a queue, the owner of the queue can allow other users to submit messages to the queue — 3) "After creating a queue, the owner of the queue can allow other users to submit messages to the queue. To do this, the owner of the queue must grant other users these two privileges: (1) the ENQUEUE ANY QUEUE system privilege (or the ENQUEUE object privilege on a specific queue), and (2) the DEQUEUE ANY QUEUE system privilege (or the DEQUEUE object privilege on a specific queue)."

All the 8.1 privileges needed for working with queues must be granted by an AQ administrator using the DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE package. SYS and SYSTEM are initially given the AQ ADMINISTRATOR role and can grant that role to other users. The AQ_USER_ROLE is only for 8.0 compatible queues. This role is ignored by 8.1 compatible queues. The next lesson discusses connection pooling.

Steps For Setting Up Advanced Queuing

The following section discusses advanced queuing in Oracle.

Changes in Oracle Database Advanced Queuing 12c Release 1 (12.1.0.2)
The following are changes in Oracle Database Advanced Queuing User's Guide for Oracle Database 12c Release 1 (12.1.0.2).
The following feature is new in this release:
JMS Streaming
In Oracle Database 12c Release 1 (12.1.0.2), Advanced Queuing introduces JMS Streaming with enqueue and dequeue for sharded queues through AQjmsBytesMessage and AQjmsStreamMessage, for the applications interested in sending and receiving large message data or payload.
Changes in Oracle Database Advanced Queuing 12c Release 1 (12.1)
The following are changes in Oracle Database Advanced Queuing User's Guide for Oracle Database 12c Release 1 (12.1).
JMS Sharded Queues:
In Oracle Database 12c Release 1 (12.1), Advanced Queuing introduces high performing and scalable sharded JMS Queues. A sharded queue is a single logical queue that is divided into multiple, independent, physical queues through system-maintained partitioning. A sharded queue increases enqueue-dequeue throughput, especially across Oracle RAC instances, because ordering between two messages on different queue shards is best effort. Each shard is ordered based on enqueue time within a session. Sharded queues automatically manage table partitions so that enqueuers and dequeuers do not contend among themselves. In addition, sharded queues use an in-memory message cache to optimize performance and reduce the disk and CPU overhead of AQ-JMS enqueues and dequeues. Sharded queues are the preferred JMS queues for queues used across Oracle RAC instances, for queues with high enqueue or dequeue rates, or for queues with many subscribers.

Result Cache Enhancement

In Oracle Database 12c Release 1 (12.1), the Rules Engine introduces a result cache to improve the performance of many commonly used rules. The result cache will bypass the evaluation phase if an expression with the same attributes has already been evaluated earlier. Not all rule results are cached, such as when rule results are potentially non-deterministic or when not all rules are evaluated or when attributes include non-scalar data types. For Advanced Queues, the cache is most useful when subscriptions and their dequeue sessions are long-lived.

Steps to take before setting up Advanced Queuing
1. Set JOB_QUEUE_PROCESSES to at least 2.
2. Grant EXECUTE on DBMS_AQADM to a user.
3. Create a new queue in the database.
4. Grant ENQUEUE ANY QUEUE and DEQUEUE ANY QUEUE to other users.

[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.