noncdb_to_pdb.sql – why does it take so long?

What is noncdb_to_pdb.sql?

noncdb_to_pdb.sql - Why does it take so long?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.

More information?

Please find a complete upgrade followed by a conversion into a PDB here:

Upgrade to Oracle Database 12.2 and Converting it into an 12.2 PDB (plugin)

–Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.