1. Installation Guide
  2. Moving from Previous Versions of Oracle Database XE to XE 21c

9 Moving from Previous Versions of Oracle Database XE to XE 21c

This section explains how to export and import data between Oracle Database XE 18c and XE 21c.

Oracle Database XE 11g Release 2 (11.2) users need to first upgrade to XE 18c and then upgrade from XE 18c to XE 21c using the information provided in this section.

For information on upgrading from Oracle Database XE 11g Release 2 (11.2) to XE 18c, see Exporting and Importing Data between Oracle Database XE 11.2 and 18c .

9.1 Upgrading Oracle Database XE 18c PDB to XE 21c

Unplug the PDB from Oracle Database XE 18c

Note:

  • Database Upgrade Assistant (DBUA) can not be used to perform the upgrade.

  • Database Configuration Assistant (DBCA) can not be used to plug 18c XE to 21c XE database.

  1. Install Oracle Database XE 18c.

  2. From Oracle Database XE 18c, connect to the database as root. 

    SQL> CONNECT / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

    Output: Session altered.

  3. Close the PDB you want to unplug where XEPDB1 is the name of the PDB you want to unplug: 

    SQL> ALTER PLUGGABLE DATABASE XEPDB1 CLOSE;

    Output: Pluggable database altered.

  4. Unplug the 18c XE PDB using the following SQL command syntax, where XEPDB1 is the name of the PDB, and '\tmp\xepdb1.xml' is the location of the PDB XML file: 

    SQL> ALTER PLUGGABLE DATABASE XEPDB1 UNPLUG INTO '/tmp/xepdb1.xml';

    Output: Pluggable database altered.

  5. Drop the closed PDB and keep the data files.

    SQL> DROP PLUGGABLE DATABASE XEPDB1 KEEP DATAFILES;

  6. Copy the 18.4 oradatafiles of PDB XEPDB1 (typically under <INSTALL_DIR_18cXE>\oradata\XE\XEPDB1) to another location like C:\oldxepdb1\XEPDB1. 

    C:\copy <INSTALL_DIR_18cXE>\oradata\XE\XEPDB1\*.* c:\oldxepdb1\XEPDB1;
    C:\oldxepdb1\XEPDB1>dir
SYSAUX01.DBF   SYSTEM01.DBF   TEMP01.DBF     UNDOTBS01.DBF  USERS01.DBF

  7. Update /tmp/xepdb1.xml file with the new XEPDB1 oradata file paths.

  8. Use Add or Remove Programs to deinstall Oracle Database XE: In the Windows Control Panel, select Add or Remove Programs. Select Oracle Database 18c Express Edition. Click Uninstall.

Plugging in the Oracle Database XE 18c PDB to XE 21c CDB

  1. Install the latest version of Oracle Database XE 21c.

  2. Use the command CREATE PLUGGABLE DATABASE to plug the PDB from the 18c XE CDB to the 21c XE CDB.

    In the example, XEPDB2 is the new pdb: 

    SQL> CONNECT / AS SYSDBA
SQL> CREATE PLUGGABLE DATABASE XEPDB2 USING '/tmp/xepdb1.xml' NOCOPY TEMPFILE REUSE;

    Output: Pluggable database created.

  3. Upgrading the PDB from an Earlier Release to a Later Release

    Open the PDB in UPGRADE mode: 

    SQL> ALTER PLUGGABLE DATABASE XEPDB2 OPEN UPGRADE;

    Output: Pluggable database altered.

  4. Upgrade the PDB using the Parallel Upgrade Utility command: 

    %ORACLE_HOME%\perl\bin\perl  %ORACLE_HOME%\rdbms\admin\catctl.pl -u sys -d %ORACLE_HOME%\rdbms\admin -c XEPDB2 -l C:\utility -t catupgrd.sql

    The upgrade summary report is located in: C:\utility\upg_summary.log. The PDB will be in MOUNTED state.

  5. Open the PDB to execute post-upgrade fixups:

    SQL> ALTER PLUGGABLE DATABASE XEPDB2 OPEN;

  6. Use the utility catcon.pl to run utlrp.sql from the directory $ORACLE_HOME/rdbms/admin. The script recompiles INVALID objects in the database. 

    %ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -d %ORACLE_HOME%\rdbms\admin -n 1 -l C:\utlrp -e -b utlrp -c 'XEPDB2' utlrp.sql

    Output:catcon.pl: completed successfully.

Performing a Sanity Check for the new PDB

SQL> CONNECT / AS SYSDBA
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
         5 XEPDB2                         READ WRITE NO
SQL> ALTER SESSION SET CONTAINER=XEPDB2;
Session altered.
SQL> SELECT COUNT(*), 'Invalid Objects' FROM ALL_OBJECTS WHERE STATUS status = 'INVALID';
  COUNT(*) 'INVALIDOBJECTS'
---------- ----------------
         0  Invalid Objects

Note: