How to upgrade a PDB on MS Windows from Oracle Database 12.1.0.2 to 12.2.0.1 by unplug / plug in


As single-tenant is the new default for all Oracle databases 12c R2 (12.2.0.1) I was happy that I already had a multi-tenant database in my Oracle 12.1.0.2 home on my laptop (running Windows 10). To test some stuff I need to prepare for my presentation at DOAG Conference 2017 (e.g. new cross-endian functionality of RMAN), I installed the fresh 12.2.0.1 database software to my oracle product folder.


Within the installer I liked to create a container database, but without a PDB – my goal was to unplug one of my existing (Spatial) PDBs from 12.1 and plug it into the 12.2 database on the same machine - but the installer does not allow to create a container database without a PDB, so I created also a PDB called jso2pdb.

(For your understanding: When I refer to CDB121 it’s the 12.1.0.2 container database I am working with, when I refer to CDB122 it’s the 12.2.0.1.)

After the software installation and the new CDB122 was created, I connected as sys to CDB122 and dropped the new pluggable database (which I never wanted to exist):

SQL> drop pluggable database jso2pdb including datafiles;

SQL> select pdb_id, pdb_name from dba_pdbs;

    PDB_ID PDB_NAME

---------- --------------------------------------------------------------------------------

         2 PDB$SEED


Next to that, I recreated the listener service from the new home using the old *.ora files and added my new stuff to tnsnames.ora. Everything is prepared now, so I started with the work on the CDB121 as a SYS User connected to the CDB:

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 JSOPDB1                        MOUNTED

         4 SPATEST                        READ WRITE

The PDB SPATEST is the one I want to unplug, plug and upgrade, but it is still in READ WRITE mode, so first we need to put it in the mount state by closing it. Don’t forget to close always PDBs immediate – otherwise they are shut down with the normal mode.

SQL> alter pluggable database SPATEST close immediate;

Done


SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 JSOPDB1                        MOUNTED

         4 SPATEST                        MOUNTED


Now I unplug the PDB SPATEST by creating the XML File which stores the metadata of the PDB:

SQL> ALTER PLUGGABLE DATABASE SPATEST

  2    UNPLUG INTO 'C:\app\oracle\admin\orcljso2\temp\SPATEST.xml';

     

Done

Selecting from v$pdbs does still show my PDB as mounted, but at dba_pdbs we can see it’s unplugged.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 JSOPDB1                        MOUNTED

         4 SPATEST                        MOUNTED


SQL> select PDB_ID, PDB_NAME, STATUS from dba_pdbs;

    PDB_ID PDB_NAME                                                                         STATUS

---------- -------------------------------------------------------------------------------- ---------

         2 PDB$SEED                                                                         NORMAL

         3 JSOPDB1                                                                          NORMAL

         4 SPATEST                                                                          UNPLUGGED

Do not drop the PDB from the old CDB121 NOW.  

When I open the XML file with an editor, I can see that my file paths still refer to the old location:

      <path>C:\DATA\ORACLEDB\ORADATA\DATAFILES\ORCLJSO\SPATEST\SYSTEM01.DBF</path>


Don’t change the entries in the XML file by your own, we can do this later…

We now test the compatibility of the SPATEST PDB with the new CDB122 by running the following PL/SQL block on CDB122:

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2    compatible CONSTANT VARCHAR2(3) :=

  3      CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

  4             pdb_descr_file => 'C:\app\oracle\admin\orcljso2\temp\SPATEST.xml',

  5             pdb_name       => 'SPATEST')

  6      WHEN TRUE THEN 'YES'

  7      ELSE 'NO'

  8  END;

  9  BEGIN

 10    DBMS_OUTPUT.PUT_LINE(compatible);

 11  END;

 12  /

NO

PL/SQL procedure successfully completed

Unfortunately, it is not compatible, so we need to check, why:

SQL> select cause, type, line, message from pdb_plug_in_violations where name='SPATEST';

CAUSE                                                            TYPE            LINE MESSAGE

---------------------------------------------------------------- --------- ---------- --------------------------------------------------------------------------------

VSN not match                                                    ERROR              1 PDB's version does not match CDB's version: PDB's version 12.1.0.2.0. CDB's vers

OPTION                                                           ERROR              1 PDB's version is lower than CDB and PDB has more components than the CDB, plug i

Parameter                                                        WARNING            1 CDB parameter processes mismatch: Previous 300 Current 320

APEX                                                             ERROR              2 APEX mismatch: PDB has installed common APEX. CDB has not installed APEX.

Parameter                                                        WARNING            2 CDB parameter pga_aggregate_limit mismatch: Previous 10G Current 3980M

Parameter                                                        WARNING            3 CDB parameter memory_target mismatch: Previous 4080M Current 0

CDB undo mode                                                    WARNING            1 Undo mode mismatch: PDB using SHARED undo.  CDB using LOCAL undo.

Parameter                                                        WARNING            4 CDB parameter memory_max_target mismatch: Previous 6096M Current 0

Parameter                                                        WARNING            5 CDB parameter compatible mismatch: Previous '12.1.0.2.0' Current '12.2.0'

Parameter                                                        WARNING            6 CDB parameter job_queue_processes mismatch: Previous 200 Current 4000

10 rows selected


OK, let’s sort it out:

  • VSN not match: Yes, we want to upgrade the pdb after plugging it into CDB122.
  • Option and APEX: Ok – some options are missing in my CDB122, we should fix that first
  • Parameter: Ok, nothing that makes any trouble while plugging the database in
  • CDB undo mode: Yes, 12.2 has local undo, we need to change that after the PDB is plugged into CDB122.
  • There is no warning/error about a mismatch in endian format or character set.

To fix the only real error I have remembered that the database CDB122 was created by the software installation process while the CDB121 I had created some months ago using DBCA. And it’s true, the DBCA shows that Oracle Application Express is not installed in CDB122 (JVM, Text, Multimedia, OLAP, Spatial, Label Security, Database Vault and Database Extensions for .NET are all installed by default), so I added it to the installed components list and finished the DBCA. After that, I still got an error about APEX: The Apex version in the CDB121 is 4.x, the Apex installed in CDB122 is 5.x, but I still can proceed with the plugin of the PDB.

So I plugin the SPATEST into CDB122 (which is running some seconds):

SQL> create pluggable database SPATEST

  2  using  'C:\app\oracle\admin\orcljso2\temp\SPATEST.xml'

  3  move file_name_convert=

  4  ('C:\data\OracleDB\oradata\Datafiles\ORCLJSO\SPATEST','C:\data\OracleDB\oradata\Datafiles\ORCLJSO2\SPATEST');

     

Done

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                                                                             OPEN_MODE

---------- -------------------------------------------------------------------------------- ----------

         2 PDB$SEED                                                                         READ ONLY

         3 SPATEST                                                                          MOUNTED


My database is now plugged into the CDB122 and stays in mount state. With Windows Explorer I can see all datafiles do exist in the new location.

In Oracle DB 12.2 we can upgrade PDBs using priority lists, but as we do need to upgrade only one, we can specify the upgrade direct with the –c switch.

First I prepare the SPATEST PDB:

SQL> alter pluggable database SPATEST open upgrade;

Done

Next I run the new parallel upgrade utility on SPATEST by using the dbupgrade.cmd in Windows. The parameters are the same as for catctl.pl (-n= 4 parallel sessions, -c= List of PDBs  to upgrade)

C:\>%ORACLE_HOME%\bin\dbupgrade -c 'SPATEST' –n 4


On my laptop, this was running now for roundabout 45 minutes. After this has finished, the PDB can now be opened on CDB122:

SQL> alter session set container=CDB$ROOT;

Session altered


SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                                                                             OPEN_MODE

---------- -------------------------------------------------------------------------------- ----------

         2 PDB$SEED                                                                         READ ONLY

         3 SPATEST                                                                          MOUNTED


SQL> alter pluggable database SPATEST open;

Done


SQL> col message for a35 word_wrapped

SQL> select cause, type, line, message from pdb_plug_in_violations where name='SPATEST' and status <> 'RESOLVED';

CAUSE                                                            TYPE            LINE MESSAGE

---------------------------------------------------------------- --------- ---------- -----------------------------------

OPTION                                                           WARNING           15 Database option RAC mismatch: PDB i

                                                                                      nstalled version 12.2.0.1.0. CDB in

                                                                                      stalled version NULL.


Still one warning – There is a RAC option enabled in the PDB (CDB121 wasn’t a RAC?), so we need to switch this option off. Checking the CDB122 first:

SQL> select COMP_ID, COMP_NAME, STATUS from dba_registry;

COMP_ID                        COMP_NAME                                                                        STATUS

------------------------------ -------------------------------------------------------------------------------- --------------------------------------------

CATALOG                        Oracle Database Catalog Views                                                    VALID

CATPROC                        Oracle Database Packages and Types                                               VALID

JAVAVM                         JServer JAVA Virtual Machine                                                     VALID

XML                            Oracle XDK                                                                       VALID

CATJAVA                        Oracle Database Java Packages                                                    VALID

APS                            OLAP Analytic Workspace                                                          VALID

RAC                            Oracle Real Application Clusters                                                 OPTION OFF

XDB                            Oracle XML Database                                                              VALID

OWM                            Oracle Workspace Manager                                                         VALID

CONTEXT                        Oracle Text                                                                      VALID

ORDIM                          Oracle Multimedia                                                                VALID

SDO                            Spatial                                                                          VALID

XOQ                            Oracle OLAP API                                                                  VALID

OLS                            Oracle Label Security                                                            VALID

APEX                           Oracle Application Express                                                       VALID

DV                             Oracle Database Vault                                                            VALID

16 rows selected


Checking the DBA_REGISTRY of the PDB shows that RAC was upgraded:

SQL> alter session set container=SPATEST;

Session altered


SQL> select COMP_ID, COMP_NAME, STATUS from dba_registry;

COMP_ID                        COMP_NAME                                                                        STATUS

------------------------------ -------------------------------------------------------------------------------- --------------------------------------------

CATALOG                        Oracle Database Catalog Views                                                    UPGRADED

CATPROC                        Oracle Database Packages and Types                                               UPGRADED

JAVAVM                         JServer JAVA Virtual Machine                                                     UPGRADED

XML                            Oracle XDK                                                                       UPGRADED

CATJAVA                        Oracle Database Java Packages                                                    UPGRADED

APS                            OLAP Analytic Workspace                                                          UPGRADED

RAC                            Oracle Real Application Clusters                                                 UPGRADED

XDB                            Oracle XML Database                                                              UPGRADED

OWM                            Oracle Workspace Manager                                                         UPGRADED

CONTEXT                        Oracle Text                                                                      UPGRADED

ORDIM                          Oracle Multimedia                                                                UPGRADED

SDO                            Spatial                                                                          UPGRADED

XOQ                            Oracle OLAP API                                                                  UPGRADED

OLS                            Oracle Label Security                                                            UPGRADED

APEX                           Oracle Application Express                                                       UPGRADED

DV                             Oracle Database Vault                                                            UPGRADED

16 rows selected

This seems to be a bug, because in the CDB121 RAC also has the status “OPTION OFF”. Now it’s easy to set the RAC option of on the PDB level.

SQL> exec dbms_registry.OPTION_OFF('RAC');

PL/SQL procedure successfully completed


SQL> select COMP_ID, COMP_NAME, STATUS from dba_registry;

COMP_ID                        COMP_NAME                                                                        STATUS

------------------------------ -------------------------------------------------------------------------------- --------------------------------------------

CATALOG                        Oracle Database Catalog Views                                                    UPGRADED

CATPROC                        Oracle Database Packages and Types                                               UPGRADED

JAVAVM                         JServer JAVA Virtual Machine                                                     UPGRADED

XML                            Oracle XDK                                                                       UPGRADED

CATJAVA                        Oracle Database Java Packages                                                    UPGRADED

APS                            OLAP Analytic Workspace                                                          UPGRADED

RAC                            Oracle Real Application Clusters                                                 OPTION OFF

XDB                            Oracle XML Database                                                              UPGRADED

OWM                            Oracle Workspace Manager                                                         UPGRADED

CONTEXT                        Oracle Text                                                                      UPGRADED

ORDIM                          Oracle Multimedia                                                                UPGRADED

SDO                            Spatial                                                                          UPGRADED

XOQ                            Oracle OLAP API                                                                  UPGRADED

OLS                            Oracle Label Security                                                            UPGRADED

APEX                           Oracle Application Express                                                       UPGRADED

DV                             Oracle Database Vault                                                            UPGRADED

16 rows selected


Oracle checks the violations when we open a PDB, so I do a last restart of my pdb and check the status again:


SQL> alter pluggable database close immediate;

Done


SQL> alter pluggable database open;

Done


SQL> select cause, type, line, message from pdb_plug_in_violations where name='SPATEST' and status <> 'RESOLVED';

CAUSE                                                            TYPE            LINE MESSAGE

---------------------------------------------------------------- --------- ---------- -----------------------------------


SQL>

All violations are gone. Now it’s time to make a backup of the new database environment. After this has finished the pluggable database can safely be dropped from CDB121 (but be aware of losing backup metadata https://www.pythian.com/blog/rman-12c-say-goodbye-to-your-backup-when-dropping-your-pdb/). I needed to remove the SYSAUX and SYSTEM datafiles manually from the disk stored in the OLD PDB folder.

At this step I was wondering about my undo. Because in 12.1 there is one shared undo for the CDB and all PDBs and in 12.2 every PDB has it’s local Undo. And I never have changed something in here, but the upgrade procedure did it by itself and created a local undo tablespace.

SQL> select t.name,d.name, d.status, to_char(d.creation_time,'DD.MM.YYYY HH24:MI:SS') from v$tablespace t, v$datafile d where d.ts#=t.ts# and t.name like '%UNDO%' ;

NAME                           NAME                                                                             STATUS  TO_CHAR(D.CREATION_TIME,'DD.MM

------------------------------ -------------------------------------------------------------------------------- ------- ------------------------------

UNDO_1                         C:\DATA\ORACLEDB\ORADATA\DATAFILES\ORCLJSO2\SPATEST\SYSTEM01_I1_UNDO.DBF         ONLINE  26.07.2017 13:25:26


I can live with that, so I didn’t change the default undo to another name and another file.

Lessions learned:
- Upgrading a PDB from 12.1 to 12.2 by unplug / plugin on Windows is running roundabout 1h
- Seems there is a bug while upgrading the options, so RAC Option is enabled/upgraded by default, even it is off
- Never touch the XML File created, everything like moving datafiles etc. can be done at the plugin phase
- I thought I have to deal with the creation of a local undo, but it’s migrated by default
- Even if the test says, the PDB is NOT compatible with the CDB I want to plug it in – check the errors and proceed

1 comment:

Your comment will be published soon...