noncdb_to_pdb.sql – why does it take so long?

What is noncdb_to_pdb.sql?

The script 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 12.1.0.2 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 12.2.0.1 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.

Known issue

One known issue I came across (Thanks to Franky Weber Faust!):
noncdb_to_pdb.sql will fail when Concurrent Statistics Gathering is on.

Error pattern:

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.

More information?

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

–Mike