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.
-
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';
-
Login 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 using this command.This example assumes :
CREATE PLUGGABLE DATABASE NEWXEPDB USING '/tmp/xepdb1.xml' NOCOPY TEMPFILE REUSE;
-
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