1. Installation Guide
  2. 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.

You can export tables and data from one XE installation and import them into another. Oracle recommends you use 11g Release 2 and higher to export your data.

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

Upgrade from Oracle Database XE 11.2 to Oracle Database XE 18c is not supported.

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

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

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 18c XE CDB and plug it in another 18c EE CDB.

  • Use SQL*Plus to close the PDBs before they can be unplugged.

    Note:

    The pdb database may not have been opened, so you may receive an error that the PDB is already closed. 
    sqlplus / as sysdba
ALTER PLUGGABLE DATABASE xepdb1 CLOSE IMMEDIATE;
  • Unplug the closed PDB and specify the path and name of the XML file.
    ALTER PLUGGABLE DATABASE xepdb1 UNPLUG INTO '\tmp\xepdb1.xml';
  • Drop the closed PDB and keep the data files.
    DROP PLUGGABLE DATABASE xepdb1 KEEP DATAFILES;

  • Log in to your EE environment. The xepdb1 metadata file should be available from that system.

  • 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 18c EE database.

    Use the data files of the unplugged PDB to plug the PDB into another CDB without any copy.
    CREATE PLUGGABLE DATABASE newxepdb USING '\tmp\xepdb1.xml' NOCOPY TEMPFILE REUSE;
  • Verify the status and open mode of the plugged PDB.
    SELECT pdb_name, STATUS FROM cdb_pdbs WHERE pdb_name='NEWXEPDB';
SELECT OPEN_MODE FROM V$PDBS WHERE NAME='NEWXEPDB';
  • If the PDB is in MOUNTED state, then open the PDB.
    ALTER PLUGGABLE DATABASE newxepdb OPEN;
  • 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.

  • 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        18.0.0.0.0
Oracle Database Packages and Types  VALID        18.0.0.0.0
Oracle Real Application Clusters    OPTION OFF   18.0.0.0.0
JServer JAVA Virtual Machine        VALID        18.0.0.0.0
Oracle XDK                          VALID        18.0.0.0.0
Oracle Database Java Packages       VALID        18.0.0.0.0
OLAP Analytic Workspace             VALID        18.0.0.0.0
Oracle XML Database                 VALID        18.0.0.0.0
Oracle Workspace Manager            VALID        18.0.0.0.0
Oracle Text                         VALID        18.0.0.0.0
Oracle Multimedia                   VALID        18.0.0.0.0
Spatial                             VALID        18.0.0.0.0
Oracle OLAP API                     VALID        18.0.0.0.0
Oracle Label Security               VALID        18.0.0.0.0
Oracle Database Vault               VALID        18.0.0.0.0
    SQL> SELECT OWNER, OBJECT_NAME FROM DBA_INVALID_OBJECTS WHERE STATUS = 'INVALID';

no rows selected

Note: