Messaging Gateway – Upgrade can’t find mgwu122.sql

Thanks to Christian Ballweg from Optiz Consulting who brought this issue to my attention. I haven’t seen it before since I haven’t installed it but you may encounter this if you have the Messaging Gateway – Upgrade can’t find mgwu122.sql.

Photo by Olav Ahrens Røtne on Unsplash

What is the Messaging Gateway?

Actually I steal this from the documentation:

Messaging Gateway enables communication between applications based on non-Oracle messaging systems and Oracle Database Advanced Queuing.

Oracle Database Advanced Queuing provides propagation between two Oracle Database Advanced Queuing queues to enable e-business (HTTP through IDAP). Messaging Gateway extends this to applications based on non-Oracle messaging systems.

Because Messaging Gateway is integrated with Oracle Database Advanced Queuing and Oracle Database, it offers reliable message delivery. Messaging Gateway guarantees that messages are delivered once and only once between Oracle Database Advanced Queuing and non-Oracle messaging systems that support persistence. The PL/SQL interface provides an easy-to-learn administrative API, especially for developers already proficient in using Oracle Database Advanced Queuing.

In DBA_REGISTRY (or CDB_REGISTRY) you will find then a component called “MGW“. This means, it will be upgraded, and upgrade scripts need to be run.

What is the problem?

Christian now faced the following problem. He is using AutoUpgrade to upgrade to Oracle 19.10.0. And close to the end, approximately 3/4 into the upgrade process, the upgrade fails.

SP2-0310: unable to open file “$ORACLE_HOME/mgw/admin/mgwu122.sql”

is the error message.

Good news – we documented this issue in MOS Note: 2587948.1 – Upgrade to RDBMS 18c or 19c from 12c fails with SP2-0310 for files mgwu121.sql and mgwu122.sql.

Which releases are affected?

As you can read in MOS Note: 2587948.1, the missing files do not exist in the 18c and 19c Oracle Homes. The issues have been found and tagged as Bug 28785273 and Bug 30418926.

But checking the patch download on MOS, you will find out that there is not always a one-off available.

and

But what do you do when you’d like to have this fix on 19.10.0 or 19.11.0, the most recent RUs?

Actually, then you would take the generic version of the patch, the one which doesn’t has the “DBRU” postfix.

What is in this patch?

Now the fun part begins. And this is one of the reasons for this blog post, and also the reason why Christian mailed me about this issue.

So let us check the single file in this patch, mgwu122.sql:

Rem
Rem $Header: mgw/dist/oracle.mgw.common/generic/mgw/admin/mgwu122.sql apfwkr_blr_backport_30418926_19.0.0.0.0/1 2019/12/18 21:12:01 apfwkr Exp $
Rem
Rem mgwu122.sql
Rem
Rem Copyright (c) 2017, 2019, Oracle and/or its affiliates. 
Rem All rights reserved.
Rem
Rem    NAME
Rem      mgwu122.sql - MGW component upgrade script from 12.2.0 (to 18.1)
Rem
Rem    DESCRIPTION
Rem      This script must be run AS SYSDBA.
Rem
Rem    This script is intended to be run during component upgrade process;
Rem    typically from mgwdbmig.sql. It should only be called when upgrading
Rem    a 12.2.0 database in which the MGW component is currently loaded.
Rem
Rem    Messaging Gateway agent must be shut down before running this script.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    srikrkri    11/26/19 - BUG 30464030 - BLR BACKPORT OF BUG 30418926 ON
Rem                           TOP OF 18.0.0.0.0 (BLR #8429071)
Rem    alwjosep    07/28/17 - Created
Rem

Rem ========================================================================
Rem No database changes for MGW component for 18.1 release.
Rem No upgrade actions needed for MGW other than registry.
Rem ========================================================================

Rem ========================================================================
Rem Chain to component's next "u" script
Rem ========================================================================

Rem For next release, we will need to chain to mgwuNNN+1 script
rem @@mgwu181

Rem === End file: mgwu122.sql ================================================

Do you see why I copied the ENTIRE script here?

It is empty. Everything is commented out. This is basically an empty script. It does nothing. It is just placeholder.

The solution

You don’t even need a patch.

You just create an empty file named mgwu122.sql in directory $ORACLE_HOME/mgw/admin.

That’s it.

In case you are upgrading from 12.1.0.2 or before, please add a 2nd empty file and name it mgwu121.sql. This one is included in the first patch 28785273 but not in 30418926. But there is basically no need to download this patch and run through all the steps to apply it.

Thanks to Christian again for bringing this to my attention.

Further Links and Information

• Messaging Gateway in the Oracle 19c documentation
• Patching all my environment with the Janaury 2021 Patch Bundles – Oracle 19.10.0
• MOS Note: 2587948.1 – Upgrade to RDBMS 18c or 19c from 12c fails with SP2-0310 for files mgwu121.sql and mgwu122.sql
• Patch 30418926
• Patch 28785273

–Mike