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.
High Level Overview
|Endianness change possible:
|Source database versions:||Oracle 220.127.116.11 (without local undo) or 18.104.22.168 or newer|
|Characteristic:||Plugin a non-CDB via a database link|
||Yes, if source version <> target CDB|
||Upgrade, pluigin, copy (optional) and
|Minimal downtime option(s):||Oracle GoldenGate|
|Process overview:||At first plugin a non-CDB via a database link as PDB, then upgrade and finally convert it to a PDB|
|Fallback after plugin:
||Data Pump – optional: Oracle GoldenGate|
Database Migration from non-CDB to PDB – Clone via NON$CDB, upgrade, convert
Since Oracle 22.214.171.124 or higher (or with undo restrictions in 126.96.36.199 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 188.8.131.52 databases, as
DBMS_PDB.DESCRIBE does not exist in this release. You can see what happens if you attempt this solution with an Oracle 184.108.40.206 database in this blog post.
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 220.127.116.11 non-CDB databases from our Hands-On Lab.
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 (18.104.22.168) 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. 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'); shutdown immediate
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 22.214.171.124.0. CDB's version 126.96.36.199.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 188.8.131.52.190416): Not installed in the CD B but installed in the PDB 1 DB12 ERROR '184.108.40.206.0 Release_Update 1904101227' is installe PENDING d in the CDB but no release updates are installed in the PDB
ERRORs are quite disturbing and some of them – the patch errors – make no real sense to me. Of course a 220.127.116.11 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
“PDB’s version does not match CDB’s version: PDB’s version 18.104.22.168.0. CDB’s version 22.214.171.124.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.
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
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 – and
postupgrade_fixups.sql need to be run as well when I don’t upgrade with AutoUpgrade.
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
noncdb_to_pdb.sql has completed its work (in my case, 10 minutes later), I can restart the PDB to get it out of
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;
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.
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 126.96.36.199 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.
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
- Upgrade, plug in, convert (noncdb_to_pdb.sql) – 188.8.131.52 and higher
- Plugin, upgrade, convert (noncdb_to_pdb.sql) – 184.108.40.206 and higher
- Clone via NON$CDB, upgrade, convert – 220.127.116.11 and higher