Infrastructure at your Service

Oracle Team

12cR2: Upgrade by unplug/plug in the Oracle Cloud Service

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.

9 Comments

  • thomas says:

    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 select messages from pdb_plug_in_violations?
    Thanks,
    Franck.

    • thomas says:

      franck, thanks for you replay.
      now i can open plugged pdb on read write normal

      SQL> show pdbs
       
      CON_ID CON_NAME OPEN MODE RESTRICTED
      ---------- ------------------------------ ---------- ----------
      2 PDB$SEED READ ONLY NO
      3 PDB1 READ WRITE NO
      5 PDB2 READ WRITE NO
      SQL>

      note: pdb2 is new plugged from 12.1.0.2
      but there are some issue still in the pdb_plug_in_violations.

      SQL> select message from pdb_plug_in_violations order by time;
      MESSAGE
      --------------------------------------------------------------------------------
      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.
       
      Database option RAC mismatch: PDB installed version 12.2.0.1.0. CDB installed version NULL.
       
      APEX mismatch: PDB installed version 5.0.0.00.31 CDB installed version 5.0.4.00.12
       
      User tablespace USERS is not encrypted. Oracle Cloud mandates all user tablespaces must be encrypted.
       

      ===>also checked information as following:

      SQL> show pdbs
       
      CON_ID CON_NAME OPEN MODE RESTRICTED
      ---------- ------------------------------ ---------- ----------
      2 PDB$SEED READ ONLY NO
      3 PDB1 READ WRITE NO
      5 PDB2 READ WRITE NO
      SQL> show con_name
       
      CON_NAME
      ------------------------------
      CDB$ROOT
      SQL> select comp_id, version, status from dba_registry;
       
      COMP_ID VERSION STATUS
      ------------------------------ ------------------------------ ----------------
      CATALOG 12.2.0.1.0 VALID
      CATPROC 12.2.0.1.0 VALID
      JAVAVM 12.2.0.1.0 VALID
      XML 12.2.0.1.0 VALID
      CATJAVA 12.2.0.1.0 VALID
      APS 12.2.0.1.0 VALID
      RAC 12.2.0.1.0 OPTION OFF
      XDB 12.2.0.1.0 VALID
      OWM 12.2.0.1.0 VALID
      CONTEXT 12.2.0.1.0 VALID
      ORDIM 12.2.0.1.0 VALID
       
      COMP_ID VERSION STATUS
      ------------------------------ ------------------------------ ----------------
      SDO 12.2.0.1.0 VALID
      XOQ 12.2.0.1.0 VALID
      OLS 12.2.0.1.0 VALID
      APEX 5.0.4.00.12 VALID
      DV 12.2.0.1.0 VALID
       
      16 rows selected.
      SQL> select property_name, property_value from database_properties where property_name like '%CHARACTERS%';
       
      PROPERTY_NAME PROPERTY_VALUE
      ------------------------------ --------------------
      NLS_NUMERIC_CHARACTERS .,
      NLS_NCHAR_CHARACTERSET AL16UTF16
      NLS_CHARACTERSET AL32UTF8
       
      SQL>
       
      SQL> alter session set container=PDB2;
       
      Session altered.
       
      SQL> select comp_id, version, status from dba_registry;
       
      COMP_ID VERSION STATUS
      ------------------------------ ------------------------------ ----------------
      CATALOG 12.2.0.1.0 VALID
      CATPROC 12.2.0.1.0 VALID
      JAVAVM 12.2.0.1.0 VALID
      XML 12.2.0.1.0 VALID
      CATJAVA 12.2.0.1.0 VALID
      APS 12.2.0.1.0 VALID
      RAC 12.2.0.1.0 OPTION OFF
      XDB 12.2.0.1.0 VALID
      OWM 12.2.0.1.0 VALID
      CONTEXT 12.2.0.1.0 VALID
      ORDIM 12.2.0.1.0 VALID
       
      COMP_ID VERSION STATUS
      ------------------------------ ------------------------------ ----------------
      SDO 12.2.0.1.0 VALID
      XOQ 12.2.0.1.0 VALID
      OLS 12.2.0.1.0 VALID
      APEX 5.0.4.00.12 VALID
      DV 12.2.0.1.0 VALID
       
      16 rows selected.
       
      SQL> select tablespace_name,encrypted from dba_tablespaces;
       
      TABLESPACE_NAME ENC
      ------------------------------ ---
      SYSTEM NO
      SYSAUX NO
      TEMP NO
      USERS YES
       
      SQL>
      SQL> select property_name, property_value from database_properties where property_name like '%CHARACTERS%';
       
      PROPERTY_NAME PROPERTY_VALUE
      ------------------------------ --------------------
      NLS_NUMERIC_CHARACTERS .,
      NLS_NCHAR_CHARACTERSET AL16UTF16
      NLS_CHARACTERSET AL32UTF8
       
      SQL>
       

      i can’t find the root cause, confused ^|^

      • Reshmi says:

        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.

  • Shivam Grover says:

    Would this work from 12c to 19c

    SQL> create pluggable database PDB1 using ‘/u01/migration/PDB12C.xml’;
    create pluggable database PDB1 using ‘/u01/migration/PDB12C.xml’
    *
    ERROR at line 1:
    ORA-65346: The PDB version is lower and components (DV, OLS) are missing in
    CDB.

    How can we resolve this ?

  • Franck Pachot says:

    Hi Shivam,
    If your PDB uses DV and OLS then you need to have them installed in the CDB. Or remove them before the migration.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team