Pitfall: Upgrade to 21c fails when ORDIM is present but no SDO

You may have read my previous blog post about Upgrading a non-CDB to Oracle Database 21c. And I referred to a potential Pitfall: Upgrade to 21c fails when ORDIM is present but no SDO. In this blog post I would like to explain how to avoid this pitfall beforehand and explain what needs to be done.

Photo by Sara Codair on Unsplash

What’s the problem?

In my previous blog post I showed you the ideal and normal way. But during my tests I came across an issue I was not aware of – and I simply had no chance to be aware of it beforehand. Several issues come together. And while I write this, there is no fix available.

But no problem, I will describe the workarounds.

If your non-CDB (and potentially your PDB in case you attempt unplug/plug/upgrade) has the following component setup in DBA_REGISTRY:

• No SDO (Spatial Data Option)
• ORDIM (Multimedia) with user MDSYS

then you need to take action.

This situation leads to the fact that in Oracle 21c an Oracle Locator (LCTR) component gets created. Regardless whether you ever used the Locator or not. But some essential steps are missing or not happening.

In an upgrade like I showed it in Upgrading a non-CDB to Oracle Database 21c this will lead to:

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+---------------+
| 101|   DB12|DBUPGRADE|EXECUTING|RUNNING|21/08/24 10:53|10:56:43|0%Upgraded DB12|
+----+-------+---------+---------+-------+--------------+--------+---------------+
Total jobs 1

upg> 
-------------------------------------------------
Errors in database [DB12]
Stage     [NONCDBTOPDBXY]
Operation [STOPPED]
Status    [ERROR]
Info    [
Error: UPG-3009
There are plugin violations for pdb DB12 on CDB3
Cause: After running noncdb_to_pdb.sql, the pdb was closed or was opened in restricted mode
For further details, see the log file located at /home/oracle/logs/DB12/101/autoupgrade_20210824_user.log]

-------------------------------------------------
Logs: [/home/oracle/logs/DB12/101/autoupgrade_20210824_user.log]
-------------------------------------------------

What went wrong here?

Let me check the logfile at first.

2021-08-24 10:56:38.885 INFO Begin Upgrade on Database [DB12-DB12]
2021-08-24 10:56:43.369 INFO 0%Upgraded DB12
2021-08-24 10:59:43.653 INFO 22%Upgraded DB12
2021-08-24 11:02:43.855 INFO 22%Upgraded DB12
2021-08-24 11:05:44.255 INFO 52%Upgraded DB12
2021-08-24 11:08:44.734 INFO 67%Upgraded DB12
2021-08-24 11:09:15.995 INFO SUCCESSFULLY UPGRADED [DB12-DB12]
2021-08-24 11:09:15.995 INFO End Upgrade on Database [DB12-DB12]
2021-08-24 11:09:19.984 INFO DB12 has been successfully upgraded skipping the resume
2021-08-24 11:09:19.985 INFO SUCCESSFULLY UPGRADED [DB12]
2021-08-24 11:09:19.985 INFO End Upgrade on Database [DB12]
2021-08-24 11:09:19.986 INFO SUCCESSFULLY UPGRADED [DB12]
2021-08-24 11:09:20.008 INFO db12 Return status is SUCCESS
2021-08-24 11:18:24.533 WARNING There are plugin violations for pdb DB12 on CDB3
DB12 3 ERROR PENDING Database option LCTR mismatch: PDB installed version 12.2.0.1.0. CDB installed version NULL.
2021-08-24 11:18:24.551 ERROR NonCDBToPDB failed: oracle.upgrade.autoupgrade.utils.errors.AutoUpgException: AutoUpgException [UPG-3009#There are plugin violations for pdb DB12 on CDB3
DB12 3 ERROR PENDING Database option LCTR mismatch: PDB installed version 12.2.0.1.0. CDB installed version NULL.]
2021-08-24 11:18:24.551 ERROR Dispatcher failed: AutoUpgException [UPG-3009#There are plugin violations for pdb DB12 on CDB3
DB12 3 ERROR PENDING Database option LCTR mismatch: PDB installed version 12.2.0.1.0. CDB installed version NULL.]
2021-08-24 11:18:24.554 INFO Starting error management routine
2021-08-24 11:18:24.554 INFO Ended error management routine
2021-08-24 11:18:24.556 ERROR Error running dispatcher for job 101
Cause: After running noncdb_to_pdb.sql, the pdb was closed or was opened in restricted mode
2021-08-24 11:18:24.557 ERROR Dispatcher failed:
Error: UPG-3009
There are plugin violations for pdb DB12 on CDB3
Cause: After running noncdb_to_pdb.sql, the pdb was closed or was opened in restricted mode

So the upgrade of my new PDB went fine.

That’s good news.

But the PDB does not open unrestricted because of a plugin violation. There was an issue with noncdb_to_pdb.sql.

Violations are bad

Well, checking the log above tells me already what the problem is. But let me check as well my most-favorite view with this strange name: PDB_PLUG_IN_VIOLATIONS:

column message format a50
column status format a9
column type format a9
column con_id format 9

select con_id, type, message, status
  from PDB_PLUG_IN_VIOLATIONS
 where status<>'RESOLVED'
 order by time;

It gives me the following output:

CON_ID TYPE	 MESSAGE					    STATUS
------ --------- -------------------------------------------------- ---------
     2 WARNING	 Database option OLS mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 21.0.0.0.0.

     2 WARNING	 Database option ORDIM mismatch: PDB installed vers PENDING
		 ion NULL. CDB installed version 21.0.0.0.0.

     3 ERROR	 Database option LCTR mismatch: PDB installed versi PENDING
		 on 12.2.0.1.0. CDB installed version NULL.

While the first two WARNINGs make no sense to me (but I ranted about this many times before) the ERROR is the reason why my PDB does not open unrestricted.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 DB12 			  READ WRITE YES

My DB12 won’t open unless it gets special treatment now. It misses the Locator component.

Database Component LCTR?

LCTR stands for Locator. And the Locator historically made sense for customers who had no intend to purchase a Spatial Data Option (SDO) license. But SDO became extra-license-free in December 2019. Now we could easily argue that all of you could install SDO safely. But I know the counter arguments such as “upgrades will take longer”, “maintenance”, “security and patching” etc. Hence, I won’t go there.

But the problem in my case is happening since:

1. I don’t have SDO installed
2. I have ORDIM installed
3. I have the MDSYS user since there is ORDIM

These conditions trigger the plugin to expect the Locator – even though I never used the Locator (at least not in this database).

When you open DBCA in 21c, there is no such component. You can choose ORDIM and/or SDO but not LCTR. The component doesn’t seem to have made it into DBCA on time. And even if it would be there, I wouldn’t have chosen it since I didn’t use the Locator before.

But if I’d install the Locator (LCTR) component before in CDB$ROOT, then my PDB would have had opened flawless.

So here we have hit the classical “Component Pitfall”. A component must be present in CDB$ROOT when you plugin a non-CDB with this component. Otherwise there will be a plugin violation, and your new PDB does never open unrestricted.

Workarounds

So you may ask yourself: What are the workarounds?

At first, please apply these – whatever workaround you choose – before you upgrade. This is really important.

1. You install the Locator – that’s the best deal
2. You install Spatial Data Option – if you want SDO, that’s not bad either but requires more tasks
3. You removed ORDIM before plugin/upgrade – if you don’t use it, you may remove it

Workaround a. – Install the Locator in CDB$ROOT

This is the easiest workaround.

But – you need to install it before you start the plugin and upgrade. And there is more to do as you will find out below.

These are the required tasks you need to do before you attempt the upgrade to 21c:

1. Create the Locator component in your 21c CDB$ROOT manually
   .
   Logon to your CDB$ROOT as SYS user and execute:

   ?/md/admin/mdinstlctr.sql

   This will create the Locator (LCTR) component with all objects in your 21c CDB.

2. Adjust the ?/rdbms/admin/cmpupsdo.sql script in your 21c home
   .
   This script has a flaw as far as I can see. It executes the Locator upgrade only in case the database identifies itself as an XE database. But it will skip this part of your database is not an XE. So will do an edit until there is a fixed version of the script available.Currently the script looks like this:

   Rem =========================================================================
   Rem Exit immediately if there are errors in the initial checks
   Rem =========================================================================
   
   Rem Setup component script filename variables
   COLUMN dbmig_name NEW_VALUE dbmig_file NOPRINT;
   
   set serveroutput off
   
   Rem First check if SDO is not loaded and if an XE database
   Rem where the MDSYS schema exists.
   Rem If all these are true, then call locdbmig.sql
   Rem to invoke locator upgrade script
   VARIABLE loc_name VARCHAR2(30);
   DECLARE
      p_name VARCHAR(128);
      p_edition VARCHAR2 (128);
   BEGIN
      :loc_name := '@nothing.sql';
      IF dbms_registry.is_loaded('SDO') IS NOT NULL THEN
          NULL;  -- Loaded already just fall through and execute nothing.sql
      ELSE
          EXECUTE IMMEDIATE
             'SELECT edition FROM registry$ WHERE cid=''CATPROC'''
          INTO p_edition;
   
         IF p_edition = 'XE' THEN
            BEGIN  -- is XE, check for MDSYS schema
               SELECT name INTO p_name FROM user$ WHERE name='MDSYS';
                :loc_name := '?/md/admin/locdbmig.sql';
               EXCEPTION WHEN NO_DATA_FOUND THEN NULL;  -- no MDSYS with XE;
            END;
         END IF;
      END IF;
   -- Exception handler for all other cases
   --   dbms_registry.is_loaded
   --   selecting edition column
   --   selecting name where error is not NO_DATA_FOUND
   EXCEPTION WHEN OTHERS THEN NULL;
   END;
   /
   
   Rem No timestamps for locator, but errors
   Rem will be associated with SDO
   SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'SDO';
   
   SELECT :loc_name AS dbmig_name FROM DUAL;
   @&dbmig_file
   

   And I change only 1 line to:

   Rem =========================================================================
   Rem Exit immediately if there are errors in the initial checks
   Rem =========================================================================
   
   Rem Setup component script filename variables
   COLUMN dbmig_name NEW_VALUE dbmig_file NOPRINT;
   
   set serveroutput off
   
   Rem First check if SDO is not loaded and if an XE database
   Rem where the MDSYS schema exists.
   Rem If all these are true, then call locdbmig.sql
   Rem to invoke locator upgrade script
   VARIABLE loc_name VARCHAR2(30);
   DECLARE
      p_name VARCHAR(128);
      p_edition VARCHAR2 (128);
   BEGIN
      :loc_name := '@nothing.sql';
      IF dbms_registry.is_loaded('SDO') IS NOT NULL THEN
          NULL;  -- Loaded already just fall through and execute nothing.sql
      ELSE
          EXECUTE IMMEDIATE
             'SELECT edition FROM registry$ WHERE cid=''CATPROC'''
          INTO p_edition;
   
         IF p_edition IS NOT NULL THEN
            BEGIN  -- is XE, check for MDSYS schema
               SELECT name INTO p_name FROM user$ WHERE name='MDSYS';
                :loc_name := '?/md/admin/locdbmig.sql';
               EXCEPTION WHEN NO_DATA_FOUND THEN NULL;  -- no MDSYS with XE;
            END;
         END IF;
      END IF;
   -- Exception handler for all other cases
   --   dbms_registry.is_loaded
   --   selecting edition column
   --   selecting name where error is not NO_DATA_FOUND
   EXCEPTION WHEN OTHERS THEN NULL;
   END;
   /
   
   Rem No timestamps for locator, but errors
   Rem will be associated with SDO
   SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'SDO';
   
   SELECT :loc_name AS dbmig_name FROM DUAL;
   @&dbmig_file
   

   Did you recognize it? No??
   This is the change I was doing:

   old:
        IF p_edition = 'XE' THEN
   new:
        IF p_edition IS NOT NULL THEN
   

   So now the locator upgrade script locdbmig.sql will be executed in case SDO is not there but the MDSYS user is. This is what I want. And no dependency on XE anymore.

3. Call AutoUpgrade and enforce classic mode
   .
   I haven’t blogged about it yet but by default in 21c the Capture/Replay upgrade will be used. When I have more time, I will blog about it in more detail. This means a “replay” table is in the database which is at the stage of “release”. And of course, my change to the upgrade script I did above won’t be picked up by capture/replay upgrade. Hence, when you go forward with capture/replay upgrade, this will still fail.So we need to switch to “classic” AutoUpgrade where the scripts get executed and everything gets logged as you and we are used to.One addition is necessary in your AutoUpgrade config file to trigger Classic Upgrade:

   upg1.catctl_options=-t

   I included this already in my config file in my Upgrading a non-CDB to Oracle Database 21c blog post.

No you can upgrade safely, even with no SDO and ORDIM and MDSYS user present in your database, regardless whether you ever used the Locator or not.

Workaround b. – Install Spatial Data Option in CDB$ROOT

Again, also this workaround has to happen before you upgrade to 21c in your target CDB you are plugging into. Keep in mind that Spatial, Graph and Machine Learning now license free even for 11.2.0.4 databases.

1. Install SDO with DBCA in your 21c CDB
   .
   Now SDO exists in CDB$ROOT. There is no need to create it in PDB$SEED (which would happen if you tick the “Include in PDBs” box).
   .
2. Kick off AutoUpgradeI

   $ java -jar /u01/app/oracle/product/21/rdbms/admin/autoupgrade.jar -config DB12.cfg -mode deploy
   AutoUpgrade 21.2.210721 launched with default options
   Processing config file ...
   +--------------------------------+
   | Starting AutoUpgrade execution |
   +--------------------------------+
   1 databases will be processed
   Type 'help' to list console commands
   upg> lsj
   +----+-------+---------+---------+-------+--------------+--------+-------------+
   |Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|      MESSAGE|
   +----+-------+---------+---------+-------+--------------+--------+-------------+
   | 100|   DB12|PREFIXUPS|EXECUTING|RUNNING|21/08/24 20:08|20:08:22|Remaining 3/3|
   +----+-------+---------+---------+-------+--------------+--------+-------------+
   Total jobs 1

   STOP!!!

   This won’t work. You will need the same treatment from the above solution:
   Adjust the ?/rdbms/admin/cmpupsdo.sql script in your 21c home
   Call AutoUpgrade and enforce classic mode

   So you won’t win anything by installing SDO.

   If you fail to do so, your conversion to PDB will hang forever:

   upg> lsj
   +----+-------+-------------+---------+-------+--------------+--------+-------------------+
   |Job#|DB_NAME|        STAGE|OPERATION| STATUS|    START_TIME| UPDATED|            MESSAGE|
   +----+-------+-------------+---------+-------+--------------+--------+-------------------+
   | 100|   DB12|NONCDBTOPDBXY|EXECUTING|RUNNING|21/08/24 20:08|20:23:21|noncdb_to_pdb - 64%|
   +----+-------+-------------+---------+-------+--------------+--------+-------------------+
   Total jobs 1
   

   and you will see the reason when you check the component’s versions in your new PDB:

   SQL> select comp_id, version from dba_registry;
   
   COMP_ID 		       VERSION
   ------------------------------ ------------------------------
   CATALOG 		       21.0.0.0.0
   CATPROC 		       21.0.0.0.0
   JAVAVM			       21.0.0.0.0
   XML			       21.0.0.0.0
   CATJAVA 		       21.0.0.0.0
   RAC			       21.0.0.0.0
   XDB			       21.0.0.0.0
   OWM			       21.0.0.0.0
   ORDIM			       21.0.0.0.0
   LCTR			       12.2.0.1.0
   OLS			       21.0.0.0.0
   
   11 rows selected.
   

   The Locator hasn’t been upgraded. Maybe it would have helped to install SDO not only in my CDB$ROOT but also in my non-CDB beforehand. But I didn’t try this out.

Workaround c. – Remove ORDIM

ORDIM (Oracle Multimedia) is quite a strange component. We announced that it gets removed in Oracle Database 19c, but actually only the API with all functionality got removed while the component still stayed. And as you can see, it exists even in Oracle 21c. Let’s see what happens in 23c then.

Anyhow, I wrote up some instructions to remove ORDIM a long while ago. And I’d like to do this here, too.

1. Remove ORDIM before upgrade
   .
   At first I remove ORDIM before upgrade from my 12.2.0.1 non-CDB as I explained in instructions to remove ORDIM. But let me check whether ORDIM is present:

   QL> select comp_id, version from dba_registry;
   
   COMP_ID 		       VERSION
   ------------------------------ ------------------------------
   CATALOG 		       12.2.0.1.0
   CATPROC 		       12.2.0.1.0
   JAVAVM			       12.2.0.1.0
   XML			       12.2.0.1.0
   CATJAVA 		       12.2.0.1.0
   XDB			       12.2.0.1.0
   OWM			       12.2.0.1.0
   ORDIM			       12.2.0.1.0
   OLS			       12.2.0.1.0
   
   9 rows selected.
   

   It is.

   I will invoke:

   @?/rdbms/admin/catcmprm.sql ORDIM

   and answer the question whether I’d like to remove ORDIM with “Y”.

2. Start AutoUpgrade
   .
   Now AutoUpgrade can upgrade the database, either in classic mode (upg1.catctl_options=-t) or with capture/replay upgrade.

   $ java -jar /u01/app/oracle/product/21/rdbms/admin/autoupgrade.jar -config X.cfg -mode deploy
   AutoUpgrade 21.2.210721 launched with default options
   Processing config file ...
   +--------------------------------+
   | Starting AutoUpgrade execution |
   +--------------------------------+
   1 databases will be processed
   Type 'help' to list console commands
   upg> Job 100 completed
   ------------------- Final Summary --------------------
   Number of databases            [ 1 ]
   
   Jobs finished                  [1]
   Jobs failed                    [0]
   Jobs pending                   [0]
   
   
   
   Please check the summary report at: 
   /home/oracle/logs/cfgtoollogs/upgrade/auto/status/status.html
   /home/oracle/logs/cfgtoollogs/upgrade/auto/status/status.log

   And 20 minutes later my non-CDB has been plugged in and is fully upgraded and assimilated as PDB.

   SQL*Plus: Release 21.0.0.0.0 - Production on Tue Aug 24 21:19:57 2021
   Version 21.3.0.0.0
   
   Copyright (c) 1982, 2021, Oracle.  All rights reserved.
   
   
   Connected to:
   Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
   Version 21.3.0.0.0
   
   SQL> show pdbs
   
       CON_ID CON_NAME			  OPEN MODE  RESTRICTED
   ---------- ------------------------------ ---------- ----------
   	 2 PDB$SEED			  READ ONLY  NO
   	 3 DB12 			  READ WRITE NO
   

Summary

This issue cost me many hours of testing and emailing the past days. I filed Bug# 33234414 – DBCA DOES NOT CREATE THE LOCATOR (LCTR) COMPONENT IN 21.3 – BUT PLUGIN/UPGRADE/CONVERT WILL CREATE IT, AND PDBS CAN’T OPEN for this issue. But currently there is no fix for it. This is why I put the workarounds together.

For me, the third workaround, the removal of ORDIM, makes the most sense since it is easy to do and does not require any changes to the upgrade scripts. But of course, this workaround only makes sense for you in case you don’t use the Locator.

In case you use the Locator, the first workaround may be the best option – you create the Locator in the CDB, you adjust the line in the script – and you invoke AutoUpgrade in classic mode. Of course, you could do the same exercise by installing SDO beforehand. But it needs to be installed in the receiving CDB and in the non-CDB (or PDB) beforehand. Thus, this is more work with the same result.

This blog post became much longer than I initially thought.

Just keep in mind: Oracle 19c is the Long Term Support release you will upgrade to right now. Oracle 21c is an Innovation Release only.

Further Links and Information

• Migration from non-CDB to PDB: Typical plugin violations and workarounds
• Migration from non-CDB to PDB: The Component Pitfall
• License News: Spatial, Graph and Machine Learning now license free
• Upgrading a non-CDB to Oracle Database 21c
• Oracle Multimedia gets removed in Oracle Database 19c
• Clean up ORDIM

–Mike