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.
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?
But checking the patch download on MOS, you will find out that there is not always a one-off available.
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_188.8.131.52.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 184.108.40.206.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.
You don’t even need a patch.
You just create an empty file named mgwu122.sql in directory $ORACLE_HOME/mgw/admin.
In case you are upgrading from 220.127.116.11 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