By Franck Pachot

.

    12.2 is available in the Oracle Public Cloud DBaaS. If you have a 12.1 DBaaS service, there’s no button to upgrade it. I’ll describe all the possible upgrade procedures and the first one, and the most simple, is to create a new DBaaS service in 12.2 and unplug/plug the PDBs to it.

    Here is my DBaaS in 12.1

    [[email protected] ~]$ 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

    Unplug

    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.

    Copy files

    I’ve opened ssh between the two VMs and copy the xml file

    [[email protected] 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:

    [[email protected] ~]$ 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

    [[email protected] 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

    Plug

    It’s only one command to plug it into the 12.2 CDB:

    [[email protected] 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.

    Upgrade

    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:

    [[email protected] 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:

    So I keep the answer for the next blog post.