NON-CDB to PDB Migration Horror Mustafa, 2023-04-27 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: https://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: Oracle PL/SQL Error painting error page: ORA-04063: package body APEX_220200.HTMLDB_UTIL has errors ORA-06508: PL/SQL: could not find program unit being called: APEX_220200.HTMLDB_UTIL ORA-06512: at APEX_220200.WWV_FLOW, line 347 ORA-06512: at APEX_220200.WWV_FLOW_PAGE, line 4200524400 ORA-06512: at APEX_220200.WWV_FLOW_PAGE, line 3419 ------------------------------------------------------ ORA-06508: PL/SQL: could not find program unit being called: APEX_220200.HTMLDB_UTIL ORA-06512: at APEX_220200.WWV_FLOW, line 347 ORA-06512: at APEX_220200.WWV_FLOW_PAGE, line 4200524400 ORA-06512: at APEX_220200.WWV_FLOW_PAGE, line 3157 ------------------------------------------------------ - error_backtrace: ORA-06512: at APEX_220200.WWV_FLOW, line 3312 ORA-06512: at APEX_220200.WWV_FLOW_LANG, line 1654 ORA-06512: at APEX_220200.WWV_FLOW_ERROR, line 1509 ORA-06512: at APEX_220200.WWV_FLOW, line 277 ORA-06512: at APEX_220200.WWV_FLOW, line 214 ORA-06512: at APEX_220200.WWV_FLOW, line 2129 ORA-06512: at APEX_220200.APEX, line 52 123456789101112131415161718 Error painting error page: ORA-04063: package body APEX_220200.HTMLDB_UTIL has errorsORA-06508: PL/SQL: could not find program unit being called: APEX_220200.HTMLDB_UTILORA-06512: at APEX_220200.WWV_FLOW, line 347ORA-06512: at APEX_220200.WWV_FLOW_PAGE, line 4200524400ORA-06512: at APEX_220200.WWV_FLOW_PAGE, line 3419------------------------------------------------------ORA-06508: PL/SQL: could not find program unit being called: APEX_220200.HTMLDB_UTILORA-06512: at APEX_220200.WWV_FLOW, line 347ORA-06512: at APEX_220200.WWV_FLOW_PAGE, line 4200524400ORA-06512: at APEX_220200.WWV_FLOW_PAGE, line 3157------------------------------------------------------- error_backtrace: ORA-06512: at APEX_220200.WWV_FLOW, line 3312ORA-06512: at APEX_220200.WWV_FLOW_LANG, line 1654ORA-06512: at APEX_220200.WWV_FLOW_ERROR, line 1509ORA-06512: at APEX_220200.WWV_FLOW, line 277ORA-06512: at APEX_220200.WWV_FLOW, line 214ORA-06512: at APEX_220200.WWV_FLOW, line 2129ORA-06512: at APEX_220200.APEX, line 52 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: Oracle PL/SQL Error: PLS-00907: cannot load library unit APEX_220200.WWV_FLOW_SECURITY (referenced by APEX_220200.WWV_FLOW_PAGE) 1 Error: PLS-00907: cannot load library unit APEX_220200.WWV_FLOW_SECURITY (referenced by APEX_220200.WWV_FLOW_PAGE) 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: Oracle PL/SQL alter pluggable database pdb close immediate; alter pluggable database pdb open upgrade; @?/rdbms/admin/utlirp.sql 123 alter pluggable database pdb close immediate;alter pluggable database pdb open upgrade;@?/rdbms/admin/utlirp.sql now everything is invalidated, so recompile them: Oracle PL/SQL alter pluggable database pdb close immediate; alter pluggable database pdb open; @?/rdbms/admin/utlrp.sql 123 alter pluggable database pdb close immediate;alter pluggable database pdb open;@?/rdbms/admin/utlrp.sql 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. 19c 23c Administration Multi-tenant apexcannot load library unit APEXHTMLDB_UTILmigrationnon-cdb to pdbpdb migrationruntime invalidationWWV_FLOW_SECURITY
Hi Mustafa, Just wanted to drop a quick comment and say how much I appreciate the tips you shared in this article! Your advice really helped us out when we were migrating from Oracle 12c non-CDB to 19.18 PDB with APEX 19.2. Everything’s been going smoothly so far. Thanks a bunch for making our day, Cheers. Reply
Hi Brian, Thank you very much. this is one of the most important motivation for me and your comment made my day. I am so glad to help. Cheers. Reply