Multitenant: Pluggable database migration
Table of contents
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
And the newly created pluggable database can be seen:

References
- EM Express FAQ (Doc ID 1575988.1)
- Upgrading a Pluggable Database (PDB)





