Database Migration from non-CDB to PDB – Clone via NON$CDB, upgrade, convert

The third option for plugging in a non-CDB is called Database Migration from non-CDB to PDB – Clone via NON$CDB, upgrade, convert. As I showed already in this blog post a while ago, your source must be at least an Oracle 12.1 database. And this technique can be used also only for same-Endianness migrations but does not apply if you’d like to migrate from Big to Little Endian. If your source database matches already the receiving CDB, no upgrade is necessary.

Photo by Samuel Chan on Unsplash

High Level Overview

Database Migration from non-CDB to PDB – Clone via NON$CDB, upgrade, convert

Since Oracle 12.2.0.1 or higher (or with undo restrictions in 12.1.0.2 already), you have the freedom to plugin a non-CDB at first via a database link, and then upgrade and adjust it. I still prefer the other solution of upgrading first, then plugin as I describe in this article. There’s simple reason for me here: Proven fallback for the upgrade case whereas there’s no easy fallback once you plugged in. But I leave it to you to choose your preferred option.

I can’t do this exercise with my 11.2.0.4 databases, as DBMS_PDB.DESCRIBE does not exist in this release. You can see what happens if you attempt this solution with an Oracle 11.2.0.4 database in this blog post.

Why “upgrade”?

Your non-CDB has a data dictionary. And this data dictionary must be adjusted to match the dictionary version of the receiving CDB. Whether you do this at first or after plugin, is up to you. But here, I will plug in at first via a database link, then upgrade my 12.2.0.1 non-CDB databases from our Hands-On Lab.

Prerequesites

This technique requires that you fulfill some prerequisites outlined in the documentation:

• The current user must have the CREATE PLUGGABLE DATABASE system privilege in the root of the CDB that will contain the target PDB.
• The source and target platforms must meet the following requirements:
  • They must have the same endianness.
  • The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.
• The CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.
• The CDB and the non-CDB must be running the same Oracle Database release.
• The data block size of the newly created PDB must match the CDB.
• If the non-CDB is in NOARCHIVELOG mode, then it must be open in read-only mode. If the non-CDB is in ARCHIVELOG mode, then it can be open read-only or read/write.

I restriction marked in RED has been lifted (when the source is at least 12.2.0.1 AFAIK). Of course, you could do the upgrade before – which I’d seriously recommend. Hence, it is not a real issue to have it documented this way.

Preparation in Source

Before I can attempt this move, I need to do some simple prep steps in my source database. I will create a special user for this operation and grant the necessary privileges.

create user SOURCEDB_USER identified by oracle;
grant CONNECT, RESOURCE, CREATE PLUGGABLE DATABASE to SOURCEDB_USER;

As my databases are not in ARCHIVELOG mode, I can safely do all steps in read-only. This has another advantage: I can create the XML manifest file and check whether the non-CDB can be plugged in or if there are any issues I need to resolve at first.

shutdown immediate
startup open read only
exec DBMS_PDB.DESCRIBE('/home/oracle/DB12.xml');

Once I created the XML manifest file, I need to execute the recommended plugin-compatibility check in my CDB.

set serveroutput on

DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/home/oracle/DB12.xml', pdb_name => 'DB12') WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Is the future PDB compatible?  ==>  ' || compatible);
END;
/

A typical result is this here in the lab:

SQL> start db12_compatible.sql
Is the future PDB compatible?  ==>  NO
PL/SQL procedure successfully completed.

Why is it not compatible?

Let us check PDB_PLUG_IN_VIOLATIONS for the reason why DB12 is not compatible to be plugged into CDB2. I use this query to check what I need to fix:

set pages 2000

column message format a50
column status format a9
column type format a9
column con_id format 9
column name format a8

select con_id, name, type, message, status
  from PDB_PLUG_IN_VIOLATIONS
 where status<>'RESOLVED'
 order by name,time;

And the result is interesting:

CON_ID NAME	TYPE	  MESSAGE					     STATUS
------ -------- --------- -------------------------------------------------- ---------
     1 DB12	WARNING   PDB plugged in is a non-CDB, requires noncdb_to_pd PENDING
			  b.sql be run.

     1 DB12	ERROR	  PDB's version does not match CDB's version: PDB's  PENDING
			  version 12.2.0.1.0. CDB's version 19.0.0.0.0.

     1 DB12	WARNING   CDB parameter sga_target mismatch: Previous 1200M  PENDING
			  Current 1504M

     1 DB12	WARNING   CDB parameter compatible mismatch: Previous '12.2. PENDING
			  0' Current '19.0.0'

     1 DB12	WARNING   CDB parameter _fix_control mismatch: Previous '254 PENDING
			  76149:1', '23249829:1', '26019148:1', '26986173:1'
			  , '27466597:1', '20107874:1', '27321179:1', '25120
			  742:1', '26536320:1', '26423085:1', '28072567:1',
			  '25405100:1' Current NULL

     1 DB12	WARNING   CDB parameter pga_aggregate_target mismatch: Previ PENDING
			  ous 120M Current 200M

     1 DB12	ERROR	  DBRU bundle patch 190416 (DATABASE APR 2019 RELEAS PENDING
			  E UPDATE 12.2.0.1.190416): Not installed in the CD
			  B but installed in the PDB

     1 DB12	ERROR	  '19.3.0.0.0 Release_Update 1904101227' is installe PENDING
			  d in the CDB but no release updates are installed
			  in the PDB

The ERRORs are quite disturbing and some of them – the patch errors – make no real sense to me. Of course a 12.2.0.1 RU can’t be installed in a 19c CDB (hopefully!). Funny to read the next ERROR telling me now that the CDB has an RU (which is correct) but the future PDB has none?!?!

Well, let me ignore these – but focus on the first ERROR:

“PDB’s version does not match CDB’s version: PDB’s version 12.2.0.1.0. CDB’s version 19.0.0.0.0.”

It is misleading to log an error here – even though ERROR may be correct. But in fact the documentation promises that I can plugin this non-CDB – I just need to upgrade it afterwards.

Plugin Operation

I will simply ignore the above ERROR – and pretend that I know better. Let me see if I can plugin the non-CDB as a new PDB via the database link:

CREATE DATABASE LINK sourcedblink CONNECT TO sourcedb_user IDENTIFIED BY oracle using 'DB12';

create pluggable database DB12 FROM NON$CDB@sourcedblink file_name_convert=('DB12','CDB2/DB12');
Pluggable database created.

This looks good. And as I suspected, the above restriction from the documentation does not apply here.

From here on it works exactly the same way as in the previous scenario, Plug In, Upgrade, Convert.

Let me do a quick check:

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 DB12 			  MOUNTED

I will open the PDB but it will open RESTRICTED only of course as it needs to be upgraded and assimilated as a “real” PDB.

My source database has been copied over the database link. Hence, this process should work remotely as well. And the source remains untouched.

SQL> alter pluggable database all open;
Warning: PDB altered with errors.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 DB12 			  MIGRATE    YES

Please note that it automatically gets opened in MIGRATE mode – which is equivalent to STARTUP UPGRADE.

Can I run noncdb_to_pdb.sql in this stage already?

No, of course not. The new PDB hasn’t been upgraded. You will received this error – and the session gets terminated:

BEGIN
*
ERROR at line 1:
ORA-04023: Object SYS.STANDARD could not be validated or authorized

Upgrade the PDB after plug in

The new PDB needs to be upgraded at first. In this case I can’t use the new AutoUpgrade – it does currently not support upgrading a single PDB.Hence, I’m running my upgrade with the simple command:

dbupgrade -l /home/oracle/logs -c "DB12"

And 20 minutes later, my new PDB “DB12” is upgraded.

This is not the end of the story as the PDB needs to be recompiled with utlrp.sql.

Resistance is futile

And still, even thought the PDB is now upgraded to Oracle 19c, it can’t be used yet. The final step in this process is the execution of the noncdb_to_pdb.sql script. It “assimilates” (no relation or connection to The Borg) the PDB and makes it fully operational.

SQL> alter session set container=DB12;
Session altered.

SQL> set timing on
SQL> set serverout on
SQL> set echo on
SQL> set termout on
SQL> spool /home/oracle/logs/db12_noncdbtopdb.log
SQL> start ?/rdbms/admin/noncdb_to_pdb.sql

Once noncdb_to_pdb.sql has completed its work (in my case, 10 minutes later), I can restart the PDB to get it out of RESTRICTED mode.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 DB12                           READ WRITE YES
SQL> shutdown
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 DB12                           READ WRITE NO

Now my new PDB is fully upgraded and operational.

Let me drop the SOURCEDB user which I used only for this operation. There’s no need for it anymore.

alter session set container=DB12;
drop user sourcedb cascade;

Fallback?

An important topic you need to plan for in all your upgrade and migration tasks are the various fallback opportunities. In the above case, there is no direct fallback for the upgrade. Once you plugged in DB12, you can’t define a guaranteed restore point as the PDB isn’t a PDB yet. It results in:

SQL> create restore point PDB1_GRP1 guarantee flashback database;
create restore point PDB1_GRP1 guarantee flashback database
*
ERROR at line 1:
ORA-39893: PDB restore point could not be created

Makes sense – but isn’t nice as this means, your only fallback from now on is to drop the PDB and repeat the action.

Once the plug in, upgrade and convert all succeeded, then you have a fallback option again: AFTER upgrade fallback is an export and import with Oracle Data Pump. Transportable Tablespaces can’t be used when your process did include an upgrade as TTS works only within the same or to a higher version. If you seek for minimal downtime you can use Oracle GoldenGate on top. I’m not aware of any other fallback options once you started production on the plugged in PDB.

Important Remarks

For this specific case, I’ll have to note down a few important remarks.

First of all, this technique still requires to execute noncdb_to_pdb.sql when you have already the same database version in source. And I’d recommend to do a potentially necessary upgrade at first.

If your source database is a 12.1.0.2 non-CDB, then you must disable Local Undo in the receiving CDB. Otherwise the operation will fail with:

create pluggable database DB12 FROM NON$CDB@sourcedblink file_name_convert=('DB12','CDB2/DB12')
*
ERROR at line 1:
ORA-65353: The undo tablespace is missing from the XML metadata file.

You can disable local undo in the CDB by:

shutdown immediate
startup upgrade
alter database local undo off;
shutdown immediate
startup

You may enable local undo later on again.

Second, in my opinion, the only real benefit of this solution is that you can do it over a network connection when the TNS setup has been done. You don’t have to copy around the future PDB manually. But apart from this I don’t see a major benefit. It is another technique – but doesn’t lift any of the restrictions and does not make your non-CDB a PDB implicitly.

Instead there is one major flaw within this approach: When you migrate a 50TB database with this approach and something fails during the conversion – as it has happened to a customer of mine recently – then you are lost. You can repeat the exact same steps after you found out (hopefully) how to cure the issue. This is another reason why I prefer the Upgrade, plug in, convert approach a lot over any other technique.

More Issues

Just recently, this was brought to my attention – it applies to Oracle 18c and to Oracle 19c (but not in 12.2.0.1):

• Bug 31310564 – CLONE TDE-ENABLED NON-CDB TO PDB FAILS WITH ORA-01078

  SQL> create pluggable database SRCPDB1 from NON$CDB@DBLINK1
  create_file_dest='/scratch/u01/app/u01/oradata' keystore identified by
  Welcome123;
  
  ERROR at line 1:
  ORA-01078: failure in processing system parameters
  LRM-00111: no closing quote for value 'keystore_c'

   

Further Information and Links

• The new AutoUpgrade Blog Post Series
• Oracle Database 19c Hands-On Lab
• Hand-On Lab: Plugin UPGR into CDB2
• Plugin 18c – Compatibility Check does not work – August 14, 2018
• Cloning with Pluggable Database in 18c – August 16, 2018
• noncdb_to_pdb.sql – Why does it take so long – June 8, 2017
• Upgrade to Oracle Database 12.2 and plugin into a 12.2 CDB – March 8, 2017
• 9 Upgrade and Migration Scenarios – August 1, 2018
• Fallback Strategy: Flashback a Pluggable Database – August 30, 2017
• Oracle 19c Multitenant Admin Guide: Create a PDB by cloning a NON$CDB
• Creating a PDB directly from a stand-alone database (non-CDB)

Typical Plugin Issues and Workarounds

• Typical Plugin Issues and Workarounds
• The Compatible Pitfall
• The Time Zone Pitfall
• The Component Pitfall
• The Patch Level Pitfall
• Various Pitfalls
• The Fallback Challenge
• The Minimal Downtime Challenge

Related Posts

• Upgrade, plug in, convert (noncdb_to_pdb.sql) – 11.2.0.4 and higher
• Plugin, upgrade, convert (noncdb_to_pdb.sql) – 12.2.0.1 and higher
• Clone via NON$CDB, upgrade, convert – 12.1.0.2 and higher

–Mike