Navigating DBMS_AQMIGTOOL package in Oracle Database 23c: A Starter’s Guide

Published in

Oracle Developers

8 min readMar 24, 2024

Oracle’s latest 23c release introduces the DBMS_AQMIGTOOL package, offering a user-friendly interface for seamless migration from Oracle Advanced Queue (AQ) to Oracle Transactional Event Queue (TxEventQ). This blog explores the functionalities provided by the DBMS_AQMIGTOOL package, giving valuable insights into its procedures and best practices for their utilization.

Introduction

Oracle’s messaging system has evolved significantly over the years. From Oracle 8, there was a built-in message platform in the Oracle Database called Oracle Advanced Queue. However, with the introduction of Oracle Transactional Event Queues in Oracle 19c, a new messaging system was brought in to optimize performance, especially in Oracle Real Application Clusters (RAC) environments.

Users face challenges when migrating from AQ to TxEventQ due to the lack of available APIs. Manual migration requires users to possess knowledge of AQ metadata, including queue configurations, subscriber details, privileges, and notification registrations. This task is complex and time-consuming. Even if users manage to replicate the AQ setup in TxEventQ, they must decide when to transition, how to handle existing messages in AQ, and modify applications to accommodate the new queue name, potentially causing downtime.

Oracle addresses these challenges by offering straightforward solutions within the DBMS_AQMIGTOOL package, offering easy-to-use APIs that eliminate the need for extensive background knowledge of queue setup. The DBMS_AQMIGTOOL package ensures a smooth migration process without downtime or application changes, making migration effortless for all users.

Now that we’re all on the same page let’s dive into each procedure’s functionalities and determine when to utilize them effectively.

But before we dive into the technicalities, let’s set the stage: this blog focuses on the conceptual aspects of migrating from AQ to TxEventQ. For a practical demonstration, refer to the following article.

Streamlining Oracle Advanced Queue to Transactional Event Queue Migration

Oracle 23c: Seamlessly Migrate Advanced Queue to Transactional Event Queue with DBMS_AQMIGTOOL

medium.com

Ensuring Compatibility: Pre-Migration Check

Before initiating the migration process, conducting a compatibility check is essential to ensure that the new system offers all the features required by the user application. Although TxEventQ includes most of the commonly used features of AQ, there are some differences between the two systems. For instance, AQ offers functionalities like sequence deviation and transformation, whereas TxEventQ introduces a range of modern message queuing features such as Kafka interoperability and improved performance.

However, if the user does not utilize these AQ features, their absence may not be critical.

Pre-Migration Compatibility Check Flowchart

To assess whether the application relies on any features unsupported by TxEventQ, users can employ the CHECK_MIGRATION_TO_TXEVENTQ procedure. This procedure operates in two modes: CURRENT, which examines the queue’s current metadata and messages, and ENABLE_EVALUATION (default mode), which not only checks the current state but also monitors the queue for any unsupported features during runtime. In ENABLE_EVALUATION mode, as the workload on the queue increases, any encountered unsupported features are logged in an internal table. Users can access this information through the USER_TXEVENTQ_MIGRATION_STATUS view or by calling the CHECK_MIGRATION_TO_TXEVENTQ API again.

Once users are satisfied that the workload has adequately captured all commonly used features, they can utilize the DISABLE_MIGRATION_CHECK procedure to halt internal monitoring. Users can proceed with the migration process if no unsupported features are detected. However, if unsupported features are identified, users can choose to address them by implementing workarounds, which may involve modifying the queue or application. Afterward, users can clear the unsupported feature table using the CLEAR_UNSUPPORTED_FEATURE_TABLE procedure before attempting to retry the migration check with CHECK_MIGRATION_TO_TXEVENTQ. Alternatively, if the unsupported features pose significant challenges, users may opt not to proceed with the migration at that time.

Now, let’s delve into the migration flow.

Migration Process Overview

Instead of directly copying data/messages from AQ to TxEventQ, Oracle employs a novel approach that ensures zero downtime.

Upon initiating the INIT_MIGRATION procedure, the database creates a TxEventQ mirroring the queue configuration of AQ, including payload, subscribers (with rules or non-rules), privileges, and notification setup. This TxEventQ is named similarly to AQ, with the addition of a suffix, which can be specified by the user or defaults to M. For instance, if the AQ is named DEMO_QUEUE, querying the user_queues view will reveal a new TxEventQ named DEMO_QUEUE_M.

You might wonder how this resolves the issue. With both queues in the database, the concept is to route new enqueue requests to TxEventQ. When a dequeue request occurs, the system checks AQ first. If a message is present, it dequeues from AQ; otherwise, it dequeues from TxEventQ. The Oracle database handles this routing process internally, relieving the application from managing these complexities. There’s no need for changes in enqueue-dequeue calls or even the queue name; everything is managed seamlessly.

Now, once AQ is empty, users can utilize the COMMIT_MIGRATION API. This action drops AQ and renames TxEventQ to AQ’s name. Consequently, querying user_queues post COMMIT_MIGRATION execution reveals only one queue named DEMO_QUEUE, now of the type TxEventQ.

To ensure data integrity, a prerequisite check was added to the COMMIT_MIGRATION API. It verifies whether AQ is empty before proceeding with the drop operation. If AQ is not empty, an error is thrown, preventing message loss during migration.

Simply put, the procedures in the DBMS_AQMIGTOOL package handles all complexities, making things easier for users.

Additional APIs for Enhanced Migration Experience:

Why do we have extra APIs, you might wonder? Well, it’s all about making life easier for users and ensuring a smoother transition to a modern queuing system. We don’t want migration to be a scary or daunting task. That’s why the DBMS_AQMIGTOOL package goes the extra mile to provide additional APIs. So, let’s dive in and explore each one!

CHECK_MIGRATED_MESSAGES:

Imagine you’re a user wanting to keep track of how many messages are waiting to be processed by consumers in both AQ and TxEventQ. Well, that’s where the CHECK_MIGRATED_MESSAGES API comes into play. This handy tool allows you to see the remaining messages in AQ before you’re ready to use the COMMIT_MIGRATION API. It’s a useful way to stay informed and ensure a smooth migration process.

PURGE_QUEUE_MESSAGES:

Picture this scenario: You’re in the final stages of your migration process, but a hefty backlog of messages clogs up AQ. You need to clear the way for a smooth COMMIT_MIGRATION, but you don’t want to wait for consumers to dequeue all those messages. Enter PURGE_QUEUE_MESSAGES API! With this nifty tool, you can specify whether you want to purge messages from AQ, TxEventQ, or both. Plus, purging from AQ is the default option, making user life much easier.

CHECK_STATUS:

Imagine this: The user has initiated the migration process with INIT_MIGRATION, but suddenly, an unexpected request comes in from your user application utilizing a feature not supported by TxEventQ. Don’t panic! The database has your back. It quietly takes note of these unsupported features and stores them in an internal table. Now, the user has two options: The user can either query the USER_TXEVENTQ_MIGRATION_STATUS view to retrieve the details of unsupported features or use the CHECK_STATUS procedure to examine the migration process’s status. Whichever route the user chooses, if any unsupported features are detected, the user will receive all the necessary details to address the situation effectively.

CANCEL_MIGRATION:

Let’s say a user has started the migration process but suddenly realizes they want to keep a feature that TxEventQ does not support, and they want to keep their application the same. No worries! With the CANCEL_MIGRATION procedure, the migration can be rolled back. This procedure drops the TxEventQ, but what about the messages in TxEventQ? Are they lost forever? Not at all! When using CANCEL_MIGRATION, there’s the option to restore the messages. If this option is chosen, the messages are copied back to AQ, along with their message state information. This way, if the user changes their mind in the future, they can attempt the migration process again by calling INIT_MIGRATION. It’s like hitting the rewind button on the migration journey, giving the user the flexibility to make changes as needed.

RECOVER_MIGRATION:

In real-time scenarios, errors can occur during the execution of INIT_MIGRATION or COMMIT_MIGRATION due to unexpected failures or instance crashes. These incidents can leave our queuing system in an inconsistent state. To restore it to a consistent state, users can utilize the RECOVER_MIGRATION API. Users will receive recommended actions through the recovery_message parameter detailed in the official documentation. Sometimes, unexpected issues may arise during migration, causing the entire system to halt. This API provides users with peace of mind, knowing that if such a situation occurs during queue migration, they can use this API to reach the nearest feasible and consistent point. It’s all about empowering users to handle any challenges that may arise during the migration process.

That concludes our overview of the procedures offered in the DBMS_AQMIGTOOL package and when to use them effectively.

But wait, there’s more!

Captured from https://memes.getyarn.io/yarn-clip/1840d1c3-dc92-4320-8c24-cc618017642a/gif

Different Ways to configure the migration:

Let’s explore the different ways to configure the migration.

The DBMS_AQMIGTOOL package offers four distinct modes to configure the migration, each tailored to meet various user requirements. Users can specify the mig_mode parameter when calling the INIT_MIGRATION procedure. Understanding each mode before starting the migration can help users choose the best option for their needs.

AUTOMATIC: This mode allows both enqueue and dequeue operations while a background job manages the migration process. It ensures completion once all messages in AQ are processed and no unsupported features are detected. It is ideal for users seeking a seamless migration process without manual intervention. Simply initiating INIT_MIGRATION is all needed, as the background process handles the rest.
INTERACTIVE (Default): Users control enqueue and dequeue operations in this mode. They can decide when to complete or cancel the migration.
OFFLINE: This mode limits operations to dequeue only, reducing workload by preventing new enqueue operations.
ONLY_DEFINITION: Unlike other modes, this one creates a separate TxEventQ with the same configuration as AQ, completing the migration process. Both AQ and TxEventQ remain in the system. It’s useful for users who want to test TxEventQ manually on the same queue configuration as AQ before proceeding with a separate migration call. As per the official documentation, it’s important to note that INIT_MIGRATION won’t copy messages existing in AQ to the newly created TxEventQ.

In conclusion, the DBMS_AQMIGTOOL package simplifies the migration process from Oracle AQ to TxEventQ, offering a smooth transition with minimal downtime and enhanced user experience.