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.

Upgrading Oracle Database 18c XE PDB to 21c XE

Unplug the PDB from Oracle Database 18c XE

Upgrade Instructions

Note:

You cannot use Oracle Database Upgrade Assistant (Oracle DBUA) to perform the upgrade.
You cannot use Oracle Database Configuration Assistant (Oracle DBCA) to plug 18c XE to 21c XE database.

Install Oracle Database 18c XE.

As the root user, create a directory to place the 18c XE oradata files.

# mkdir /opt/xepdbxml
# chown -R oracle:oinstall /opt/xepdbxml
# chmod -R 777 /opt/xepdbxml

From Oracle Database 18c XE, connect to the database as SYSDBA.
```
SQL> CONNECT / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
```
The session is altered.
Close the PDB you want to upgrade.

For example, use the following command to close the PDB XEPDB1.
```
SQL> ALTER PLUGGABLE DATABASE XEPDB1 CLOSE;
```
The pluggable database is closed.
Unplug the Oracle Database 18c XE PDB using the following SQL command syntax:
```
SQL> ALTER PLUGGABLE DATABASE XEPDB1 UNPLUG INTO '/opt/xepdbxml/XEPDB1.xml';
```
In the above example, the pdb name is XEPDB1 and the location of the PDB XML file is /opt/xepdbxml/XEPDB1.xml.

The Pluggable database altered response is displayed when the command completes.

Drop the closed PDB and keep the data files.

SQL> DROP PLUGGABLE DATABASE XEPDB1 KEEP DATAFILES;

Copy all the 18c XE XEPDB1 oradata files from /opt/oracle/oradata/XE/XEPDB1 to /opt/xepdbxml:

$ cd /opt/oracle/oradata/XE/XEPDB1
$ ls
sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

$ cp *.dbf /opt/xepdbxml/
$ cd /opt/xepdbxml/
$ ls
sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

Update all the dbf files in the /opt/xepdbxml/XEPDB1.xml file with the new oradata file paths.

For example, in the XEPDB1.xml file, replace /opt/oracle/oradata/XE/XEPDB1/system01.dbf with /opt/xepdbxml/system01.dbf. Similarly, update the remaining .dbf files.
Deinstall Oracle Database 18c XE using the command: /etc/init.d/oracle-xe-18c delete.

Remove the 18c XE package:

$ rpm -e oracle-database-xe-18c-1.0-1.x86_64

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

Install the latest version of Oracle Database 21c XE.

For more information, see Installing Oracle Database XE.
Connect to the 21c XE CDB as SYSDBA, and plug in the PDB from the 18c XE CDB to the 21c XE CDB using the following command:
```
SQL> CONNECT / AS SYSDBA
SQL> CREATE PLUGGABLE DATABASE XEPDB2 USING '/opt/xepdbxml/XEPDB1.xml' NOCOPY TEMPFILE REUSE;
```
A new pluggable database, XEPDB2 is created.

Open the PDB in UPGRADE mode.

SQL> ALTER PLUGGABLE DATABASE XEPDB2 OPEN UPGRADE;

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 $ORACLE_BASE -t catupgrd.sql
```
You can view the upgrade summary report at /opt/oracle/upg_summary.log. The PDB is now in the MOUNTED state.
Open the PDB to execute post-upgrade fixups.
```
SQL> ALTER PLUGGABLE DATABASE XEPDB2 OPEN;
```
As the oracle user, create the utlrp directory under /opt/xepdbxml. Use the catcon.pl utility to run utlrp.sql from the $ORACLE_HOME/rdbms/admin directory.
```
$ mkdir /opt/xepdbxml/utlrp
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -n 1 -l /opt/xepdbxml/utlrp -e -b utlrp -c 'XEPDB2' utlrp.sql
```
The script recompiles INVALID objects in the database.

Performing a Sanity for the New PDB

SQL> alter session set container=XEPDB2;
Session altered.

SQL> show con_name;
CON_NAME
------------------------------
XEPDB2
SQL> select count(*) , ' Invalid Objects' from all_objects where status = 'INVALID';
  COUNT(*) 'INVALIDOBJECTS'
---------- ----------------
         0  Invalid Objects

Related Topics

Upgrade instructions for APEX users moving from 18c XE to 21c XE