Multitenant: Pluggable database migration

Preamble

As we have seen in previous post I have migrated my standalone 11gR2 database to a pluggable database (PDB). Pluggable database obviously running in a container database (CDB).

I have chosen to upgrade first in 12.1.0.1 to be able to test the unplug/plug procedure to upgrade to 12.1.0.2. In below my first container cdb1 is in 12.1.0.1.4 with /u01/app/oracle/product/12.1.0/dbhome_1 as Oracle home. My second container is cdb2 in 12.1.0.2.0 with /u01/app/oracle/product/12.1.0/dbhome_2 as Oracle home.

By referring to Oracle marketing slides I was expecting a no-work migration by just unplugging and plugging in new container my PDB and, yes, maybe a synchronize procedure to update the PDB dictionary. In real life I have been disappointed as you still have to execute the upgrade script. Oracle anyway claim that the upgrade is simplified…

My test server is a virtual machine running under Oracle Linux Server release 6.5.

Backup pluggable database

As usual one good advice is to start with a cold backup, for this I configure few basic options:

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/%U';

Then after to backup the container and all pluggable databases (including root one):

BACKUP DATABASE

Or simply to backup the pluggable database I’m planning to upgrade (pdb1):

BACKUP PLUGGABLE DATABASE pdb1;

Pluggable database migration

Let’s issue pre-upgrade script (preupgrd.sql) on PDB I plan to upgrade:

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> ALTER SESSION SET container=pdb1;
 
SESSION altered.
 
SQL> @/u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/preupgrd.SQL
 
Loading Pre-Upgrade PACKAGE...
 
 
***************************************************************************
Executing Pre-Upgrade Checks IN PDB1...
***************************************************************************
 
 
      ************************************************************
 
                   ====>> ERRORS FOUND FOR PDB1 <<====
 
 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    PRIOR TO attempting your upgrade.
            Failure TO DO so will result IN a failed upgrade.
 
           You MUST resolve the above errors PRIOR TO upgrade
 
      ************************************************************
 
      ************************************************************
 
               ====>> PRE-UPGRADE RESULTS FOR PDB1 <<====
 
ACTIONS REQUIRED:
 
1. Review results OF the pre-upgrade checks:
 /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade.LOG
 
2. EXECUTE IN the SOURCE environment BEFORE upgrade:
 /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade_fixups.SQL
 
3. EXECUTE IN the NEW environment AFTER upgrade:
 /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/postupgrade_fixups.SQL
 
      ************************************************************
 
***************************************************************************
Pre-Upgrade Checks IN PDB1 Completed.
***************************************************************************
 
***************************************************************************
***************************************************************************

I had nothing special to do except below standard command:

SQL> EXECUTE dbms_stats.gather_dictionary_stats;
 
PL/SQL PROCEDURE successfully completed.

Now let’s create the pluggable database manifest and unplug the pluggable database:

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> ALTER pluggable DATABASE pdb1 CLOSE;
 
Pluggable DATABASE altered.
 
SQL> ALTER pluggable DATABASE pdb1 unplug INTO '/backup/pdb1.xml';
 
Pluggable DATABASE altered.

On my 12.1.0.2 container (cdb2):

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

As I wanted afterwards to remove completely the +DATA/CDB1 directory I have chosen to copy the pdb1 pluggable database datafiles to new directory. As I’m using Oracle Managed Files (OMF) I have just specified COPY keyword, in a more standard environment using file systems you can also use FILE_NAME_CONVERT. In real life if you have a multi-terabytes pluggable database you may not want to copy them, so consider using NOCOPY:

SQL> CREATE pluggable DATABASE PDB1 USING '/backup/pdb1.xml' copy;
 
Pluggable DATABASE created.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
 
SQL> ALTER pluggable DATABASE pdb1 OPEN upgrade;
 
Warning: PDB altered WITH errors.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MIGRATE    YES

Here we are, in some Oracle slides you just see the unplug and plug operations (with a nice animation). But this is a bit more time consuming in real world, means that the upgrade script (catupgrd.sql) must be issued. To upgrade your PDB use below new upgrade parallel method (catctl.pl Perl script), pluggable database name(s) must be in capital letters:

[oracle@server1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@server1 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB1" -l /tmp catupgrd.sql

Once done you can finally open the pluggable database and compile invalid objects if any:

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
SQL> ALTER pluggable DATABASE pdb1 OPEN;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> ALTER SESSION SET container=PDB1;
 
SESSION altered.
 
SQL> @?/rdbms/admin/utlrp

Then executed /u01/app/oracle/cfgtoollogs/cdb1/preupgrade/postupgrade_fixups.sql script. The script is only suggesting to execute:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

That’s it, PDB upgraded in 12.1.0.2:

SQL> ALTER SESSION SET container=pdb1;
 
SESSION altered.
 
SQL> SELECT file_name FROM dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB2/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/SYSTEM.286.859639681
+DATA/CDB2/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/sysaux.287.859639681
+DATA/CDB2/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/users.288.859639679

Original pluggable database

As I have copied the original datafiles why not trying to reopen the pluggable database on its initial container (cdb1):

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
SQL> ALTER pluggable DATABASE pdb1 OPEN;
ALTER pluggable DATABASE pdb1 OPEN
*
ERROR AT line 1:
ORA-65086: cannot OPEN/CLOSE the pluggable DATABASE

It is not possible to open again the pluggable database… The official documentation claims:

After a PDB is unplugged, it remains in the CDB with an open mode of MOUNTED and a status of UNPLUGGED. The only operation you can perform on an unplugged PDB is DROP PLUGGABLE DATABASE, which will remove it from the CDB. You must drop the PDB before you can plug it into the same CDB or another CDB.

The open mode is displayed with show pdbs command while I had to use DBA_PDBS to have the status:

SQL> SELECT pdb_id,pdb_name,status FROM dba_pdbs;
 
    PDB_ID PDB_NAME   STATUS
---------- ---------- -------------
         2 PDB$SEED   NORMAL
         3 PDB1       UNPLUGGED

So following official documentation recommendations:

SQL> DROP pluggable DATABASE pdb1;
 
Pluggable DATABASE dropped.
 
SQL> CREATE pluggable DATABASE pdb1 USING '/backup/pdb1.xml' NOCOPY;
 
Pluggable DATABASE created.
 
SQL> ALTER pluggable DATABASE pdb1 OPEN;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

Enterprise Manager Express

Then on my 12.1.0.2 container (cdb2) database I have configured EM Express with:

SQL> EXEC DBMS_XDB_CONFIG.SETHTTPSPORT(5502);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT dbms_xdb_config.gethttpsport() FROM dual;
 
DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                          5502