Oracle by Example brandingUnplug, Plug, and Upgrade a PDB to a New CDB

section 0Before You Begin

This 15-minute tutorial shows you how to unplug, plug, and upgrade a pluggable database (PDB).

Background

Upgrading a container database (CDB) will also upgrade all of the associated PDBs. One way to upgrade PDBs individually is by using the unplug/plug method. In this method, first unplug the PDB from an earlier release CDB, plug it into the later release CDB, and then upgrade it.

Description of cdb-and-pdbs.png follows
Description of the illustration cdb-and-pdbs.png

What Do You Need?

  • You are using Oracle Managed Files.
  • You have installed the same release Oracle Database software on the target server, and it is updated to the same release update and release update revision.
  • The endian formats of the source CDB and the target CDB are identical.
  • You have installed the same set of Oracle Database features on the source CDB and the target CDB.
  • The source CDB and the target CDB have compatible character sets and national character sets.
  • You must be connected to a CDB, and the current container must be root (CDB$ROOT).
  • You must have the CREATE PLUGGABLE DATABASE system privilege.
  • The CDB in which the PDB is being created must be in read/write mode.
  • The source CDB and the target CDB can be on the same server hardware or on different server hardware.
  • You have backed up your PDB on the source CDB to the destination CDB, so that the backup is available on the destination CDB.

section 1Unplug the Earlier Release PDB from the Earlier Release CDB

  1. Run the Pre-Upgrade Information Tool on the PDB.

    For example, run the tool on the PDB salespdb in the CDB in $ORACLE_HOME_12.2:

    $ORACLE_HOME_12.2/jdk/bin/java -jar
$ORACLE_HOME_18/rdbms/admin/preupgrade.jar dir /tmp -c salespdb
  2. Run preupgrade_fixups.sql on the source database.

    For example:

    CONNECT / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=salespdb;
    SQL> SET ECHO ON
SQL> SPOOL /tmp/preupgrade_fixups_salespdb.log
SQL> @/tmp/preupgrade_fixups_salespdb.sql
  3. Follow all the recommendations listed in preupgrade.log.
  4. Close the PDB that you want to unplug.

    For example, to close the PDB salespdb: 

    SQL> ALTER PLUGGABLE DATABASE salespdb CLOSE;
  5. Log back in to CDB$ROOT: 
    CONNECT / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
  6. Unplug the earlier release PDB using the following SQL command: 
    SQL> ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/home/oracle/salespdb.xml';
Pluggable database altered

    Here, salespdb is the name of the PDB, and /home/oracle/salespdb.xml is the location of the PDB XML file.

  7. Drop the pluggable database salespdb, but keep the data files. 
    SQL> DROP PLUGGABLE DATABASE salespdb KEEP DATAFILES;

    Oracle recommends that you drop salespdb after this procedure to clean up leftover information in the CDB views, and to avoid future issues.

    As a best practice guideline, back up your PDB in the target CDB first, and then run the DROP command on the source CDB.

    Caution: After you drop the PDB from its original CDB, you cannot restore the PDB on the source CDB by using a backup you have taken previously, because the DROP command removes the backup files.

  8. Exit.

section 2Plug In the Earlier Release PDB to the Later Release CDB

  1. Connect to the later release CDB.
  2. Plug in the earlier release PDB using the following SQL command: 
    SQL> CREATE PLUGGABLE DATABASE salespdb USING '/home/oracle/salespdb.xml';
Pluggable database altered

    Note: When you plug in an earlier release PDB, the PDB is in RESTRICTED mode. You can open the PDB only for an upgrade.

section 3Upgrade the Earlier Release PDB to the Later Release

  1. If needed, switch to the PDB salespdb that you want to upgrade to: 
    SQL> ALTER SESSION SET CONTAINER=salespdb;
  2. Open the PDB in UPGRADE mode. 
    SQL> ALTER PLUGGABLE DATABASE OPEN UPGRADE;
  3. Upgrade the PDB using the Parallel Upgrade Utility command catctl.pl, or the shell utility dbupgrade.

    When you upgrade a PDB, use the commands that you normally use with the Parallel Upgrade Utility. However, also add the option -c PDBname to specify which PDB you are upgrading.

    Capitalize the name of your PDB as shown in the following example using the PDB named salespdb:

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d \
$ORACLE_HOME/rdbms/admin -c 'SALESPDB' -l $ORACLE_BASE catupgrd.sql
  4. Review the results.

    The default log file path is: $ORACLE_BASE/cfgtoollogs/dbname/upgradedatetime.

    where:

    dbname is the name of the database.

    upgradedatetime is the date and time for the upgrade.

    The date and time strings are in the character string format YYYYMMDDHHMMSC, in which YYYY designates the year, MM designates the month, DD designates the day, HH designates the hour, MM designates the minute, and SC designates the second.

    For example:

    $ORACLE_BASE/cfgtoollogs/salespdb/upgrade20180815120001/upg_summary.log
  5. Log in to SQL*Plus. Open the PDB to execute the post-upgrade fixup scripts and to recompile the INVALID objects in the database: 
    SQL> connect / as sysdba
SQL> ALTER SESSION SET CONTAINER=salespdb;
SQL> STARTUP;
  6. Run the postupgrade_fixups.sql script using the catcon.pl utility: 
    $ORACLE_HOME/perl/bin/perl catcon.pl –c 'salespdb' -n 1 -e -b postfixups -d '''.''' /tmp/cfgtoollogs/salespdb/preupgrade/postupgrade_fixups.sql
  7. Use the utility catcon.pl to run utlrp.sql from the $ORACLE_HOME/rdbms/admin directory: 
    $ORACLE_HOME/perl/bin/perl catcon.pl –c 'salespdb' -n 1 -e -b comp -d '''.''' utlrp.sql

    The utlrp.sql script recompiles INVALID objects in the database, and places a log file in the current directory with the name comp0.log.

more informationWant to Learn More?