This was first published on https://blog.dbi-services.com/12cr2-upgrade-by-unplugplug-in-the-oracle-cloud-service (2016-11-19)
Republishing here for new followers. The content is related to the the versions available at the publication date
[oracle@HP121A ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Nov 19 14:47:04 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO
I close the PDB1 and unplug it.
SQL> alter pluggable database PDB1 close; Pluggable database altered. SQL> alter pluggable database PDB1 unplug into '/tmp/PDB1.xml'; Pluggable database altered.
I’ve opened ssh between the two VMs and copy the xml file
[oracle@HP122A tmp]$ scp 141.144.32.166:/tmp/PDB1.xml . The authenticity of host '141.144.32.168 (141.144.32.168)' can't be established. RSA key fingerprint is 84:e4:e3:db:67:20:e8:e2:f7:ff:a6:4d:9e:ee:a4:08. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '141.144.32.168' (RSA) to the list of known hosts. PDB1.xml 100% 6118 6.0KB/s 00:00
From the xml file I see which files are referenced:
[oracle@HP121A ~]$ grep path /tmp/PDB1.xml <path>/u02/app/oracle/oradata/HP121A/41A8A48F54195236E0534EC5C40A569E/datafile/o1_mf_system_d30owr5v_.dbf</path> <path>/u02/app/oracle/oradata/HP121A/41A8A48F54195236E0534EC5C40A569E/datafile/o1_mf_sysaux_d30owr69_.dbf</path> <path>/u02/app/oracle/oradata/HP121A/41A8A48F54195236E0534EC5C40A569E/datafile/o1_mf_temp_d30owr6h_.dbf</path>
and copy them
[oracle@HP122A tmp]$ scp -r 141.144.32.168:/u02/app/oracle/oradata/HP121A/41A8A48F54195236E0534EC5C40A569E /u02/app/oracle/oradata/HP121A o1_mf_temp_d30owr6h_.dbf 100% 20MB 20.0MB/s 00:00 o1_mf_system_d30owr5v_.dbf 100% 270MB 135.0MB/s 00:02 o1_mf_sysaux_d30owr69_.dbf 100% 570MB 114.0MB/s 00:05
It’s only one command to plug it into the 12.2 CDB:
[oracle@HP122A tmp]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 19 14:50:26 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> create pluggable database PDB1 using '/tmp/PDB1.xml'; Pluggable database created.
At that time, you can drop it from the source but probably you will remove the service once you are sure the migration is ok.
That was easy, but that was only the transportation of the PDB to another CDB, but it cannot be opened so easily on a newer version CDB. When we open the PDB we get a warning:
SQL> alter pluggable database PDB1 open; Warning: PDB altered with errors.
and have to look at the PDB_PLUG_IN_VIOLATIONS
SQL> select MESSAGE from pdb_plug_in_violations order by time MESSAGE ----------------------------------------------------------------------------------------------------------- APEX mismatch: PDB installed version NULL CDB installed version 5.0.4.00.12 CDB is using local undo, but no undo tablespace found in the PDB. CDB parameter compatible mismatch: Previous '12.1.0.2.0' Current '12.2.0' Database option APS mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option CATALOG mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option CATJAVA mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option CATPROC mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option CONTEXT mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option DV mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option JAVAVM mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option OLS mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option ORDIM mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option OWM mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option SDO mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option XDB mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option XML mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. Database option XOQ mismatch: PDB installed version 12.1.0.2.0. CDB installed version 12.2.0.1.0. PDB's version does not match CDB's version: PDB's version 12.1.0.2.0. CDB's version 12.2.0.1.0.
Each component report a newer version. We have to upgrade them running catupgrd.sql. In 12.2 we have a new script that calls the catctl.pl and catupgrd.sql to make this easier. It is a shell script located in ORACLE_HOME/bin and is dbupgrade. As with catcon.pl we have the ‘-c’ argument to run it on PDB1:
[oracle@HP122A tmp]$ $ORACLE_HOME/bin/dbupgrade -c PDB1
How long does it take? Documentation says that: It is easier to apply a patch to one CDB than to multiple non-CDBs and to upgrade one CDB than to upgrade several non-CDBs. So this supposes that upgrade work is mostly done at CDB level. PDBs have only metadata links to them. It’s only a virtual dictionary.
More than 3 years after the multitenant architecture was released, there are big doubts about the time it takes to upgrade a PDB plugged from a previous version:
#dbupgrade @OracleDatabaseGuess how many minutes to catupgrd a 12.1 PDB plugged into an empty 12.2 CDB?[ doc says https://t.co/hQTknmKl1y
— Franck Pachot (@FranckPachot) November 19, 2016
So I keep the answer for the next blog post.
hi,Franck did you really upgrade 12.1.0.2 pdb to 12.2.0.2 on Oracle Public Cloud by unplug then plug, in my test, it does not fully work due to plug in violcatons after execute dbupgrade, now I can open plugged pdb just as restricted mode. what my miss ?
Hi Thomas, Yes I really did that on Oracle Public Cloud here for both source and destination. No hybrid cloud here What I didn’t detail here is the export/import of the TDE key to get this first post simple. I did it for remote clone: http://blog.dbi-services.com/12cr2-upgrade-by-remote-clone-with-tde-in-dbaas/ and it’s the same idea here. You also have to run dbupgrade, which is details in http://blog.dbi-services.com/12cr2-how-long-to-upgrade-a-pdb/ You may also have characterset mismatch if the target is not AL32UTF8 Can you share
? Thanks, Franck.franck, thanks for you replay. now i can open plugged pdb on read write normal
noteļ¼ pdb2 is new plugged from 12.1.0.2 but there are some issue still in the pdb_plug_in_violations. ===>also checked information as following: i can’t find the root cause, confused ^|^Hi Thomas,
What did you do, to open the database in un-restricted mode? Even I am able to open the database only in restricted mode after upgrade from 12.1 to 12.2.
Regards, Reshmi
Hi, As I suspected you need to import the TDE key and run dbupgrade. Refer to the blog post I’ve linked. Regards, Franck.
Nice work Franck, keep it going, your tests are helping alot even when I should already know the stuff you are doing.
Thanks a lot for your feedback.