1. Installation Guide
  2. Moving from Previous Versions of Oracle Database XE to Oracle Database Free

8 Moving from Previous Versions of Oracle Database XE to Oracle Database Free

This topic explains how to export and import data between Oracle Database 21c Express Edition (XE) and Oracle Database 23c Free.

Note these points before you start the export and import process:

  • You cannot use Oracle Database Upgrade Assistant (Oracle DBUA) to perform an upgrade.
  • You cannot use Oracle Database Configuration Assistant (Oracle DBCA) to plug PDBs of previous versions to Oracle Database Free.
  • Oracle Database 18c XE users must first move to 21c XE (See, Moving from Previous Versions of Oracle Database XE to XE 21c), and then export data from 21c XE to import them into Oracle Database 23c Free.

Exporting and Importing Data between Oracle Database 21c XE and Oracle Database 23c Free

Learn how to export and import data between Oracle Database 21c Express Edition (XE) and Oracle Database 23c Free.

Exporting Data

To export data from your 21c XE database:

  1. As the root user, create a /opt/dump directory on the local file system for the DUMP_DIR directory object. 

    mkdir /opt/dump
chown -R oracle:oinstall /opt/dump
chmod -R 760 /opt/dump

  2. Perform the following steps for each pluggable database (PDB). The steps in this section are for the PDB xepdb1.

    1. Set the ORACLE_HOME and ORACLE_SID environment variables.

      export ORACLE_SID=XE
export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE

    2. Connect to the 21c XE database as user SYS using the SYSDBA privilege as the oracle user, and switch the container to xepdb1.

    3. Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user. 

      /opt/oracle/product/21c/dbhomeXE/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=xepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    4. Export data from your 21c XE PDB xepdb1 to the dump folder. 

      /opt/oracle/product/21c/dbhomeXE/bin/expdp system/system_password@dbhost.example.com:1521/xepdb1 full=Y directory=DUMP_DIR dumpfile=expdb21c_xepdb1.dmp logfile=expdb21c_xepdb1.log

      Note:

      • Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
      • The default PDB name created is xepdb1 in 21c and freepdb1 in 23c.

  3. Deinstall Oracle Database 21c XE if you plan to install 23c Free on the same system. See Deinstalling Oracle Database XE for more information

  4. Install Oracle Database 23c Free.

Importing Data

To import data to your Oracle Database 23c Free, perform the following steps for each PDB. The steps in this section are for importing data from PDB xepdb1 to freepdb1.

  1. Set the ORACLE_HOME and ORACLE_SID environment variables.

    export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree

  2. Connect to the 23c Free database as user SYS using the SYSDBA privilege as the oracle user and switch the container to freepdb1.

  3. Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user. 

    /opt/oracle/product/23c/dbhomeFree/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=freepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

  4. Import data to the 23c Free PDB freepdb1 from the dump folder created during the export operation. 

    /opt/oracle/product/23c/dbhomeFree/bin/impdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR dumpfile=expdb21c_xepdb1.dmp  logfile=impdb23c_freepdb1.log

    Note:

    • Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
    • The default PDB name created is xepdb1 in 21c and freepdb1 in 23c.

Ignore the following errors:

  • ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
  • ORA-31684: Object type TABLESPACE:"TEMP" already exists
  • ORA-31684: Object type TABLESPACE:"USERS" already exists
  • ORA-31684: Object type USER:"PDBADMIN" already exists
  • ORA-39083: Object type PROC_SYSTEM_GRANT failed to create with error:
  • ORA-29393: user EM_EXPRESS_ALL does not exist or is not logged on
  • ORA-39083: Object type NETWORK_ACL:TABLE:NACL$_ACE_EXP failed to create with error:
  • ORA-01007: Reference to a variable not in SELECT clause.
  • ORA-39342: Data Pump did not import dependent objects for NETWORK_ACL due to the previous error
  • ORA-01007: Reference to a variable not in SELECT clause.

Exporting and Importing Data between Oracle Database 23c (23.2) Free and Oracle Database 23c (23.3) Free

Learn how to export and import data between Oracle Database 23c (23.2) Free and Oracle Database 23c (23.3) Free.

Exporting Data

To export data from Oracle Database 23.2 Free:

  1. As the root user, create a /opt/dump directory on the local file system for the DUMP_DIR directory object. 

    mkdir /opt/dump
chown -R oracle:oinstall /opt/dump
chmod -R 760 /opt/dump

  2. Perform the following steps for each pluggable database (PDB). The steps in this section are for the PDB freepdb1.

    1. Set the ORACLE_HOME and ORACLE_SID environment variables.

      export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree

    2. Connect to the 23.2 Free database as user SYS using the SYSDBA privilege as the oracle user, and switch the container to freepdb1.

    3. Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user. 

      /opt/oracle/product/23c/dbhomeFree/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=freepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    4. Export data from your 23.2 Free PDB freepdb1 to the dump folder. 

      /opt/oracle/product/23c/dbhomeFree/bin/expdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR dumpfile=expdb23c_freepdb1.dmp logfile=expdb23c_freepdb1.log

      Note:

      • Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
      • The default PDB name created is freepdb1 in 23.2 Free and 23.3 Free.

  3. Deinstall Oracle Database 23.2 Free if you plan to install 23.3 Free on the same system. See Deinstalling Oracle Database Free for more information

  4. Install Oracle Database 23c (23.3) Free.

Importing Data

To import data to your Oracle Database 23c (23.3) Free, perform the following steps for each PDB. The steps in this section are for importing data from 23.2 Free PDB freepdb1 and overwriting to the same PDB freepdb1 in 23.3 Free.

  1. Set the ORACLE_HOME and ORACLE_SID environment variables.

    export ORACLE_SID=FREE
export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree

  2. Connect to the 23.3 Free database as user SYS using the SYSDBA privilege as the oracle user and switch the container to freepdb1.

  3. Create the directory object DUMP_DIR and grant READ and WRITE privileges on the DUMP_DIR directory to the SYSTEM user. 

    /opt/oracle/product/23c/dbhomeFree/bin/sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=freepdb1;
SQL> CREATE DIRECTORY DUMP_DIR AS '/opt/dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

  4. Import data to the 23.3 Free PDB freepdb1 from the dump folder created during the export operation. 

    $ /opt/oracle/product/23c/dbhomeFree/bin/impdp system/system_password@dbhost.example.com:1521/freepdb1 full=Y directory=DUMP_DIR dumpfile=expdb23c_freepdb1.dmp  logfile=impdb23c_freepdb1.log

    Note:

    • Replace dbhost.example.com with your database host name. If required, replace 1521 with the port number the listener listens on.
    • The default PDB name created is freepdb1 in 23.2 Free and 23.3 Free.

Ignore the following errors:

  • ORA-39342: Data Pump did not import dependent objects for NETWORK_ACL due to the previous error
  • ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
  • ORA-31684: Object type TABLESPACE:"TEMP" already exists
  • ORA-31684: Object type TABLESPACE:"USERS" already exists
  • ORA-31684: Object type USER:"PDBADMIN" already exists
  • ORA-31684: Object type DIRECTORY:"DUMP_DIR" already exists