Troubleshooting Multitenant PDB Upgrade

by Brian Peasland

I am working on an upgrade of a Multitenant database from Oracle 12.1.0.2 to the newly-released Oracle 19c version. To perform my upgrade, I want to unplug a pluggable database (PDB) from the old version and plug into a container database (CDB) that I created with the new version. To perform this upgrade, I am going to follow the instructions in the 19c documentation here. 

The upgrade steps sound easy enough but as you will see, I ran into a few issues.

After preparing the PDB, I unplugged it from the old CDB, all according to the instructions in the documentation. Upon plugging the PDB into the new version CDB, I received the following error:

          _{SQL> create pluggable database UPGRADE_PDB using
               2  '/tmp/UPGRADE_PDB.xml' NOCOPY;
          create pluggable database UPGRADE_PDB using
          *
          ERROR at line 1:
          ORA-65346: The PDB version is lower and components (CONTEXT, ORDIM, SDO) are
          missing in CDB.}

The first part of the ORA-65346 error message makes sense. I know this PDB is a lower version. I’m trying to upgrade the PDB. But the fact that the PDB is a lower version should not be a problem. The second part of the error message about missing components does make some sense. When I created the new version CDB, I did not install any of these missing options as our applications do not use them. But they were installed in the source database in the past. The documentation did not cover this issue. A search on My Oracle Support yielded no information. Where do I go next? A Google search of the ORA-65346 error message led me to this blog post by an Oracle employee. To add the components to my CDB, I simply launched the Database Configuration Assistance (DBCA) and selected the option to “Configure an existing database”. On the Database Options screen, I selected the missing options, shown in the screen capture below. I was curious about the column titled “Include in PDBs” and the blog post I linked earlier answered my questions. I let the DBCA work its magic and get these options installed.

After the options were now in place, it was time to see if I could plug in the PDB.  One more attempt to run the command gave me a different error.

          _{SQL> create pluggable database UPGRADE_PDB using
               2  '/tmp/UPGRADE_PDB.xml' nocopy;
          create pluggable database UPGRADE_PDB using
          *
          ERROR at line 1:
          ORA-27038: created file already exists
          ORA-01119: error in creating database file
          '/u01/app/oracle/oradata/data01/temp_01.dbf'}

Well this is progress. I am no longer receiving an error about the older version or missing components. But the ORA-27038 error is telling me the file already exists on the file system. I hope so as I am copying the entire PDB to plug into this CDB. I should have all of the files.

After closer inspection, and another Google search for the ORA-27038 error, I determined the problem was that I did not tell Oracle to reuse any existing temp files. The _{TEMPFILE REUSE} clause does exactly that. Including it lets me create the PDB.

          _{SQL> create pluggable database UPGRADE_PDB using '/tmp/UPGRADE_PDB.xml' 
               2  nocopy tempfile reuse;
          Pluggable database created.}

Now that the PDB has been plugged into this new version CDB, I can proceed with the rest of the upgrade.

Everything I have used in to get this PDB plugged into the new version has been discussed in my new book, Oracle DBA Mentor.

I started off with the Oracle documentation and when I ran into an error, looked towards My Oracle Support for assistance. When I did not find the help I needed, I performed a Google search. Oracle DBA Mentor provides information on how to use each of these resources to assist in solving problems just like this one.

About the Author

Brian Peasland is a seasoned Oracle database administrator with well over 20 years working with all Oracle database versions since Oracle 7.1 was released. He is a constant contributor to the My Oracle Support and Oracle Developer communities as well as other forums. Brian has been writing for SearchOracle.com since 2001. He has BS and MS degrees in computer science. Brian is the author of Oracle RAC Performance Tuning. 

This article was contributed by Brian Peasland, author of Oracle DBA Mentor.