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
Thanks for the interesting article. Would it work to clone a production Enterprise Edition PDB from a Enterprise Edition Source CDB to a Standard Edition 2 destination CDB for non-prod?
Best regards,
Martin
Martin,
yes, this would work. But the devil is in the detail.
See here:
https://mikedietrichde.com/2017/07/18/switching-from-se-to-ee-with-oracle-multitenant/
Cheers,
Mike
Mike is this still an issue with 19c?
Can we not upgrade from 12.1.0.2 (non CDB server A) to 19.x (CDB server B) directly? Is there a document you can point me to?
Hi Suman,
what exactly do you mean? The case is still the same. It is not an “issue” but happens by design.
Please read these blog posts which go way more into all the details:
https://mikedietrichde.com/2019/07/22/database-migration-from-non-cdb-to-pdb-overview/
This will answer all your questions.
For your particular one, this is the blog post matching your case:
https://mikedietrichde.com/2019/07/23/database-migration-from-non-cdb-to-pdb-upgrade-plug-in-convert/
Cheers,
Mike
About ORA-65353 : if your CDB database is on “LOCAL UNDO ENABLED” mode = TRUE you can plug a non-CDB database 12.1 on CDB 12.2+
Oups ! “LOCAL UNDO ENABLED” mode = FALSE, of course. Sorry …
Mike – can you comment on a workaround published here:
https://www.muniqsoft-training.de/oracle/f?p=SCHULUNG:TIPP-ANZEIGE::Upgrade_und_Migration_einer_Non-Container-DB__12.1.0.2__in_eine_Container-DB__12.2.0.1_:::P1001_TIPPAUSWAHL,P1001_CS:1703,962141538
Basically if target 19c CDB is altered with LOCAL UNDO off then remote clone from source 12.1 Non-CDB runs without error.
thanks
Albert
Hi Albert,
sorry, I can’t access the tip via the link you’ve pasted.
Are you referring to this error I describe at the end of my blog post:
https://mikedietrichde.com/2019/07/25/database-migration-from-non-cdb-to-pdb-clone-via-noncdb-upgrade-convert/
?
Cheers,
Mike