9.2 Exporting and Importing Data for Oracle Application Express (APEX) 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 '/dump_folder';
      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 Oracle Database XE for more information

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 '/dump_folder';
      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='/u01/app/oracle/oradata/XE/':'/opt/oracle/oradata/XE/XEPDB1' 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:

      sqlplus / AS SYSDBA
      SQL> ALTER SESSION SET CONTAINER=XEPDB1;
      SQL> @apex_epg_config.sql /tmp
      

   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