1. Installation Guide
  2. Exporting and Importing Data between Oracle Database XE 11.2 and 18c

9 Exporting and Importing Data between Oracle Database XE 11.2 and 18c

This section explains how to export and import data between Oracle Database XE 11g Release 2 (11.2) and XE 18c. Depending if Oracle Application Express (APEX) was used or not in your 11.2 database, follow one of these procedures:

9.1 Exporting and Importing Data for non-APEX Users

Enter a short description of your topic here (optional).

This topic describes how to export and import data between your Oracle Database XE 11.2 and XE 18c databases when Oracle Application Express (APEX) was not used in your 11.2 XE database.

Exporting Data

  1. To export data from your 11.2 XE database, perform the following steps:

    1. Create a directory \dump_folder on the local file system for the DUMP_DIR directory object.

    2. Connect to the 11.2 XE database as user SYS using the SYSDBA privilege.

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

      sqlplus / AS SYSDBA
SQL> CREATE DIRECTORY DUMP_DIR AS 'C:\temp\dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    4. To create a dump folder, run the following command from your Windows command prompt: 

      mkdir C:\temp\dump

    5. Export data from your 11.2 XE database to the dump folder.

      expdp system/system_password full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=DUMP_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

  2. Deinstall Oracle Database XE 11.2 if installation of 18c XE is planned on the same system.

    See Deinstalling the Oracle Database XE Software in Oracle Database Express Edition Installation Guide 11g Release 2 (11.2) for Microsoft Windows.

  3. Install Oracle Database XE 18c.

Importing Data

  1. To import data in your 18c XE database, perform the following steps:

    1. Connect to the 18c XE database as user SYS using the SYSDBA privilege.

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

      sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=XEPDB1;
SQL> CREATE DIRECTORY DUMP_DIR AS '\dump_folder';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    3. Import data to the 18c XE database from the dump folder.

      impdp system/system_password@localhost:listnerport/xepdb1 full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=DUMP_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log
    You can ignore the following errors:

    • ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error

    • ORA-31685: Object type USER:"SYS" failed due to insufficient privileges

    • ORA-39083: Object type PROCACT_SYSTEM failed to create with error

    • ORA-01917: user or role 'APEX_040000' does not exist

    • ORA-31684 "already exists" errors

9.2 Exporting and Importing Data for Oracle Application Express Users

This topic describes how to export and import data between Oracle Database XE 11.2 and XE 18c for Oracle Application Express (APEX) users.

Exporting Data

  1. Upgrade Oracle Application Express in your 11.2 XE database to at least APEX 5.1.4 which is the minimum supported version in database 18c if you have not already done so. You can download APEX distributions at http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html.

    See Also:

    Application Express Installation Guide for more information about Upgrading Oracle Application Express (APEX) within Oracle Database Express Edition

  2. To export the data from your 11.2 XE database, perform the following steps:

    1. Create a directory on the local file system for the DUMP_DIR directory object.

    2. Connect to the 11.2 XE database as user SYS using the SYSDBA privilege.

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

      sqlplus / AS SYSDBA
SQL> CREATE DIRECTORY DUMP_DIR AS 'C:\temp\dump';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    4. Export data from the 11.2 XE database in the DUMP_DIR directory. 

      expdp system/system_password full=Y directory=DUMP_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

  3. Deinstall Oracle Database XE 11.2 if installation of Oracle Database XE 18c is planned on the same system.

    See Deinstalling the Oracle Database XE Software in Oracle Database Express Edition Installation Guide 11g Release 2 (11.2) for Microsoft Windows.

  4. Install Oracle Database XE 18c.

Importing Data

  1. To import data to the 18c XE database, perform the following steps:

    1. Connect to 18c XE database as user SYS using the SYSDBA privilege.

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

      sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=XEPDB1;
SQL> CREATE DIRECTORY DUMP_DIR AS 'C:\temp\dump';;
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

    3. Import data to your 18c XE database from the dump folder.

      impdp system/system_password@localhost/xepdb1 full=Y REMAP_DIRECTORY='C:\app\usernamne\install_dir\oradata\XE\':'\install_dir\oradata\XE\' directory=DUMP_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log

      Remapping the directory is necessary when you use different directory file naming conventions. The first argument of the REMAP_DIRECTORY parameter is the location of your 11.2 XE data files (the source) and the second argument is the location of the 18c XE data files (target).

      See Oracle Database Utilities for more information about impdp REMAP_DIRECTORY parameter syntax

    You can ignore the following errors:

    • ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error

    • ORA-31685: Object type USER:"SYS" failed due to insufficient privileges

    • ORA-39083: Object type PROCACT_SYSTEM failed to create with error

    • ORA-01917: user or role 'APEX_040000' does not exist

    • ORA-31684 "already exists" errors

  2. Run post database import scripts to configure Oracle Application Express (APEX).

    1. Download https://www.oracle.com/technetwork/developer-tools/apex/application-express/apxfix-5137274.zip and extract the apfix.sql script on your server.

    2. Copy the file apxfix.sql into the top level directory of the APEX source you used to upgrade APEX in your 11.2 XE database. Change your working directory to that source.

    3. Run apxfix.sql passing the schema name that owns the APEX software. For example, if you upgraded 11.2 XE to APEX 5.1.4 prior to exporting the data, provide the schema name APEX_050100 as the argument: 

      sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=XEPDB1;
SQL> @apxfix.sql APEX_050100
SQL> EXIT

    4. Configure the embedded PL/SQL gateway. Run the apex_epg_config.sql script passing the file system path to the Oracle Application Express (APEX) software. For example, if you unzipped the APEX software in \tmp: 

      SQL> @apex_epg_config.sql SYSTEM_DRIVE:\TEMP

    5. Set the HTTP port for the embedded PL/SQL gateway. For example, to set the HTTP port to 8080:

      SQL> ALTER SESSION SET CONTAINER=XEPDB1;
SQL> EXEC XDB.DBMS_XDB.SETHTTPPORT(8080);
SQL> COMMIT;

    6. Connect to CDB$ROOT and unlock the ANONYMOUS user: 

      SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL> EXIT