Cloning with Pluggable Databases in Oracle 18c

We had several discussions on the internal mailing lists the other day about cloning with Pluggable Databases in Oracle 18c (or 12.2.0.1). And I blogged about this topic a while ago but realized I may need to refresh this a bit.

My example from the old blog post still works fine. But I realized that I switched my database into read-only mode. Hence, I like to repeat it and check different options.

Cloning a 12.1 PDB into Oracle 18c

In the source CDB1 I create a fresh PDB first, then open it and create a cloning user with the necessary privileges.

create pluggable database pdb3 admin user pdb3 identified by pdb3
file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb3');

alter pluggable database pdb3 open;
alter session set container=PDB3;

create user hugo identified by hugo;
grant create session, create pluggable database to hugo;

exit

In the destination CDB2 I create a database link to the source PDB using the user’s context – and then I clone without setting the source into read-only mode. This can be done when my destination CDB2 has LOCAL UNDO on.

create database link sourcedblink connect to hugo identified by hugo using 'PDB3';

create pluggable database CDB2_PDB3 from pdb3@sourcedblink
file_name_convert=('/u02/oradata/CDB1/pdb3', '/u02/oradata/CDB2/pdb3');

alter pluggable database CDB2_PDB3 open;

As the CDB2_PDB3 opens with errors only I need to check PDB_PLUG_IN_VIOLATIONS:

column name format a9
column cause format a20
column message format a50
column action format a36
set line 200
set pages 1000

select name, cause, message, action from PDB_PLUG_IN_VIOLATIONS where status<>'RESOLVED' and CON_ID=4 order by time;

NAME	  CAUSE 	       MESSAGE						  ACTION
--------- -------------------- -------------------------------------------------- ------------------------------------
CDB2_PDB1 VSN not match        PDB's version does not match CDB's version: PDB's  Either upgrade the PDB or reload the
			       version 12.1.0.2.0. CDB's version 18.0.0.0.0.	  components in the PDB.

Obviously I will have to upgrade my PDB now.
You can find a “howto” here: Unplug a 12.2 PDB and plug into the 18c Cloud

Cloning a 12.1 non-CDB into Oracle 18c

In my DB12 non-CDB source environment I add a cloning user:

create user hugo identified by hugo;
grant create session, create pluggable database to hugo;

And then I clone the database directly into a PDB into CDB2. I don’t do the usual compatibility check as it has issues in Oracle 18c.

create database link sourcedblinkDB12 connect to hugo identified by hugo using 'DB12';

create pluggable database CDB2_PDB2 from NON$CDB@sourcedblinkDB12
file_name_convert=('/u02/oradata/DB12','/u02/oradata/CDB2/db12');

Unfortunately this raises an ORA-65353: The undo tablespace is missing from the XML metadata file error which I found documented nowhere except somebody asking the same question in the Oracle Community.

This is not a bug. The error code is just very misleading. Simple explanation:

You can either clone a PDB that needs to be upgraded or a non-cdb that doesn’t need to be upgraded.

The above steps will certainly work with a same-version non-CDB. I filed a bug to have the error message either changed or more explanatory. And also to have this requirement added to the docs.

Source database has to be:

• at least Oracle 12.1.0.2
• same Endianness
• at the same (equal) version as the container database (this part is not documented)
• and script noncdb_to_pdb.sql needs to be run

Further Information

• Upgrade Testing – Online Clone a PDB into Oracle 19c
• Cloning a PDB from Oracle 12.1 to Oracle 12.2
• Unplug a 12.2 PDB and plug into the Oracle 18c Cloud
• Oracle 18.3.0 Multitenant: Compatibility Check does not work
• Create a PDB directly from a non-CDB
• Bug 28502219 – CREATE PLUGGABLE DATABASE FROM NON$CDB: ORA-65353: THE UNDO TABLESPACE IS MISSIN

–Mike