NON-CDB to PDB Migration Horror

Hello everyone,

Lately I migrated a non-cdb to pdb for a client database and it almost become a horror movie for over 2 weeks to me. before I start, if you want to know how to migrate from a non-cdb to pdb you can check one of my previous post: http://mustafakalayci.me/2022/03/25/3-ways-to-migrate-a-non-cdb-database-to-a-pdb/

Basically this is not a complicated or hard task. Especially, if you are using autoupgrade tool, it does everything for you. So, why did this task become a horror movie? First of all I am not an APEX guy (which I should be) and since there is an unknown component in this problem to me, I led to the wrong direction. What happened? I created a new container database on the server and then I used autoupgrade to migrate that non-cdb to pdb. Pretty straight and easy, then developer team told me that they cannot login to ords! here it is started. When they try to connect the ords url it just says “Contact to your application administratior. Details about this incident are available via debug id” and ends with some number. after a quick search I found these tables that debug messages are recorded: WWV_FLOW_DEBUG_MESSAGES and WWV_FLOW_DEBUG_MESSAGES2 and checked the tables for error messages. almost constantly new rows are inserted (since there are some integrations) and mostly error messages like this:

these are different error messages in same debug id (page_view_id). Errors are pointing HTMLDB_UTIL package but this package name has changed from time to time. same errors are raised for WWV_APEX_JSON and many others too.

So, first things first, I checked invalid objects and there weren’t any. I actually find the target procedure in HTMLDB_UTIL which is raised the error and when I run it in sql developer, it works properly. I also saw few “ORA-04068: existing state of packages has been discarded” error too. Well this should lead me to solution but…

From that moment, I thought something is wrong in apex since these errors are only raising for apex codes and also one of the developer team leader had told me that ords must be reinstalled if dbid has changed! That was a problem because during the non-cdb to pdb, new pdb database has a new db id and (as far as I know) it cannot be changed. What did I do? I started to learn how to reinstall ords! It was not that complicated and got it. I reinstall the ords but error kept coming.

Before migrations like this, I always test it on virtual machines by using same environments as much as possible. In that case, I didn’t have a RedHat and I did the tests on Oracle linux. as you can guess there were no error at all.

in one of my tries, I recompiled HTMLDB_UTIL package by “alter package … compile” (and body as well) but compilation is completed with some “warnings”.  I opened the source code and re-run the code (create or replace ….) and that cause a brand new error which I didn’t see before:

I checked libraries if this is an existing library but it wasn’t. I  thought there might be some problems about WWV_FLOW_PAGE package and then I tried to recompile it with alter package command but it raises similar error for some other packages and this goes on an on. Also, whenever I compiled a package in APEX schema, some other apex packages become invalid too. Since I was frustrated and really furious, I started to open every package in error messages and re run their source code one by one. Finally, it worked! all packages become VALID and ords url was working. I was able to login apex builders etc.

I thought that I solved it but it didn’t last long. after making a change in an application package which uses some apex packages, suddenly all the errors I fought with come back. run time errors, invalid objects etc.

by the way I also tried  dblink and dbms_pdb methods for migration instead of autoupgrade and nothing worked.

Then, this run time errors got my attention and search for them and there are some couple of MOS documents. to recompile invalid objects we use utlrp.sql file as you know but there is also another file that does the opposite, utlirp.sql (there is an I letter after utl). this package invalidates all objects in the database. so what the heck, I give it a try:

first close pdb and open in migrate mode:

now everything is invalidated, so recompile them:

and yes, that’s it. That solved the problem. if I focused on run time errors I would find the solution days earlier but not knowing APEX make me look into it.

By the way, those errors sometimes comes after hours later than pdb migration. so, I think I will use utlirp and then utlrp for every non-cdb to pdb migration anymore.

I hope this helps if you encounter such problems and you don’t suffer as I do. thanks for reading.

wish you all healthy and beautiful days.