What is noncdb_to_pdb.sql?
noncdb_to_pdb.sql gets run only once in the life span of your database. It gets executed when a regular Oracle database (non-CDB) gets move to become part of a Single-/Multitenant deployment as a pluggable database (PDB). But why does it take so long?
You will never run this script again for this database. The script is a sanity script to clean up and change things in a non-CDB, and unite it finally and irreversible with the CDB.
How long does it take to complete?
Runtime varies a lot. It depends mainly on the number of objects it has to adjust and map to the CDB’s dictionary. And on the time it takes for recompilation as it does forced recompilations.
16:21:48 SQL> DECLARE 16:21:48 2 threads pls_integer := &&1; 16:21:48 3 BEGIN 16:21:48 4 utl_recomp.recomp_parallel(threads); 16:21:48 5 END; 16:21:48 6 / PL/SQL procedure successfully completed. Elapsed: 00:10:12.67 16:32:01 SQL> 16:32:01 SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual; TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2017-06-08 16:32:01 1 row selected.
Can you speed it up? Can it be restarted?
Based on my experience you can’t speed it up except for adding CPU power for the recompilation. Therefore test the runtime upfront if you depend on certain downtime restrictions and rules.
In Oracle Database 188.8.131.52 the script can’t be restarted or resumed. Once it failed the entire setup had to be restored and the process need to be restarted from the beginning. That’s why a backup is absolutely essential.
Since Oracle Database 184.108.40.206 the script is supposed to be resumable – and I did check back with my Support colleagues in Romania who tested this already with positive results. Bu the overall run time hasn’t been improved.
One known issue I came across (Thanks to Franky Weber Faust!):
noncdb_to_pdb.sql will fail when Concurrent Statistics Gathering is on.
15:35:52 SQL> DECLARE 15:35:52 2 threads pls_integer := &&1; 15:35:52 3 BEGIN 15:35:52 4 utl_recomp.recomp_parallel(threads); 15:35:52 5 END; 15:35:52 6 / DECLARE * ERROR at line 1: ORA-20000: Unable to gather statistics concurrently: Resource manager plan is not active or is not managing CPU usage ORA-06512: at "SYS.UTL_RECOMP", line 899 ORA-06512: at "SYS.DBMS_STATS", line 36873 ORA-06512: at "SYS.DBMS_STATS", line 4321 ORA-06512: at "SYS.DBMS_STATS", line 36652 ORA-06512: at "SYS.UTL_RECOMP", line 260 ORA-06512: at "SYS.UTL_RECOMP", line 803 ORA-06512: at line 4
This can be solved simply:
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
Furthermore the issue gets tracked and solved under bug 26434999.
Important Addition – Oracle 19.9.0 – May 5, 2021
Since Oracle 19.9.0 you will find an important improvement with noncdb_to_pdb,sql. We added the fix for bug 25809128 into this Release Update. And since RUs are cumulative, it is in 19.10.0, 19.11.0 etc as well of course.
This fix leads to less objects requiring recompilation. Especially with Oracle EBS database where you have a huge dictionary with so many objects the migration to Multitenant in 19c caused extremely long runtimes, in some cases several hours. With this fix there are significantly less objects to recompile. And hence, the script completes faster.
You find the fix listed in MOS Note: 2523220.1 – Database 19 Release Updates and Revisions Bugs Fixed Lists as well.
Please find a complete upgrade followed by a conversion into a PDB here:
- Oracle Database Upgrade Guide 12.2:
Upgrade Scenarios for Non-CDB database to a PDB on a CDB
- MOS Note: 2000882.1
ORA-01405 while running “noncdb_to_pdb.sql”
- MOS Note: 2523220.1 – Database 19 Release Updates and Revisions Bugs Fixed Lists
- Bug 25809128