Migrating Data and Applications

10 Migrating Data and Applications

You can migrate data and applications in and out of your XE database using various methods.

Oracle recommends you use 11g Release 2 and higher to export your data. 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.

Migrating from Oracle Database XE 21c to Oracle Database Enterprise Edition 21c is supported via the unplug, plug scenario only. You can unplug a PDB from XE 21c database and plug it into another Enterpise Edition 21c database of the same release. Migrating from Oracle Database XE 21c to any of the other Oracle Database 21c offerings is not supported.

You cannot directly upgrade from Oracle Database XE 11.2 to Oracle Database XE 21c. To upgrade to XE 21c from XE 11.2, first upgrade to XE 18c, then upgrade to 21c.

Procedure to Unplug a PDB from 21c XE and Plug into 21c EE

A Pluggable Database (PDB) created in Oracle Database XE 21c can be migrated to another Multitenant Container Database (CDB) Enterprise Edition (EE) 21c.

The standard plug compatibility requirements apply. See Oracle Multitenant Administrator’s Guide for more information about general prerequisites for PDB creation.

For example, you want to unplug your xepdb1 out of the XE 21c XE CDB and plug it in another 21c EE CDB.

Unplug xepdb1 from XE database and create the /tmp/xepdb1.xml metadata file using this command :
```
ALTER PLUGGABLE DATABASE xepdb1 UNPLUG INTO '/tmp/xepdb1.xml';
```

Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB.

SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
           pdb_descr_file => '/tmp/xepdb1.xml',
           pdb_name       => 'XEPDB1')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

If the PDB is compatible with the CDB, you can plug xepdb1 into the 21c EE database using this command.
```
CREATE PLUGGABLE DATABASE NEWXEPDB USING '/tmp/xepdb1.xml'
NOCOPY 
TEMPFILE REUSE;
```
This example assumes :
- The XML file accurately describes the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause or SOURCE_FILE_DIRECTORY clause is not required.
- The files are in the correct location. Therefore, NOCOPY is included.
- Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
- A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE REUSE clause is required.
See Oracle Multitenant Administrator’s Guide for more information about plugging in an unplugged PDB.

Run catalog, catproc and utrlp on the plugged in NEWXEPDB.

sqlplus / as sysdba

SQL> SHOW PDBS;
CON_ID CON_NAME         OPEN MODE  RESTRICTED
------ ---------------- ---------- ----------        
2      PDB$SEED         READ ONLY  NO         
3      ORCLPDB          READ WRITE NO         
4      NEWXEPDB         READ WRITE YES

SQL> ALTER SESSION SET CONTAINER=newxepdb;
SQL> SPOOL catalog.log
SQL> @$ORACLE_HOME/rdbms/admin/catalog
SQL> SPOOL OFF;

SQL> SPOOL catproc.log
SQL> @$ORACLE_HOME/rdbms/admin/catproc
SQL> SPOOL OFF;

SQL> SPOOL utlrp.log
SQL> @$ORACLE_HOME/rdbms/admin/utlrp
SQL> SPOOL OFF;

SELECT COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY;

COMP_NAME                           STATUS       VERSION
----------------------------------- ------------ ------------
Oracle Database Catalog Views       VALID        21.0.0.0.0
Oracle Database Packages and Types  VALID        21.0.0.0.0
Oracle Real Application Clusters    OPTION OFF   21.0.0.0.0
JServer JAVA Virtual Machine        VALID        21.0.0.0.0
Oracle XDK                          VALID        21.0.0.0.0
Oracle Database Java Packages       VALID        21.0.0.0.0
OLAP Analytic Workspace             VALID        21.0.0.0.0
Oracle XML Database                 VALID        21.0.0.0.0
Oracle Workspace Manager            VALID        21.0.0.0.0
Oracle Text                         VALID        21.0.0.0.0
Oracle Multimedia                   VALID        21.0.0.0.0
Spatial                             VALID        21.0.0.0.0
Oracle OLAP API                     VALID        21.0.0.0.0
Oracle Label Security               VALID        21.0.0.0.0
Oracle Database Vault               VALID        21.0.0.0.0

SQL> SELECT OWNER, OBJECT_NAME FROM DBA_INVALID_OBJECTS WHERE STATUS = 'INVALID';

no rows selected