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 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.
-
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 21c 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;
This example assumes the following:
-
The XML file accurately describes the current locations of the files. Therefore, the
SOURCE_FILE_NAME_CONVERT
clause or theSOURCE_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.
-
-
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;
-
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
Note:
- See Plugging an Unplugged Pluggable Database for more information about plugging in an unplugged pluggable database.
- See Oracle Multitenant Administrator’s Guide for more information about plugging in an unplugged PDB.