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
-
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 -
To export the data from your 11.2 XE database, perform the following steps:
-
Create a directory on the local file system for the
DUMP_DIR
directory object. -
Connect to the 11.2 XE database as user
SYS
using theSYSDBA
privilege. -
Create directory object
DUMP_DIR
and grantREAD
andWRITE
privileges on the directory to theSYSTEM
user.sqlplus "/ AS SYSDBA" SQL> CREATE DIRECTORY DUMP_DIR AS '/dump_folder'; SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;
-
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
-
-
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
-
Install Oracle Database XE 18c.
Importing Data
-
To import data to the 18c XE database, perform the following steps:
-
Connect to 18c XE database as user
SYS
using theSYSDBA
privilege. -
Create directory object
DUMP_DIR
and grantREAD
andWRITE
privileges on the directory to theSYSTEM
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;
-
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
-
- Run post database import scripts to configure Oracle Application Express (APEX).
-
Download https://www.oracle.com/technetwork/developer-tools/apex/application-express/apxfix-5137274.zip and extract the
apfix.sql
script on your server. -
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.
-
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 nameAPEX_050100
as the argument:sqlplus / AS SYSDBA SQL> ALTER SESSION SET CONTAINER=XEPDB1; SQL> @apxfix.sql APEX_050100 SQL> EXIT
-
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
-
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;
-
Connect to
CDB$ROOT
and unlock theANONYMOUS
user:SQL> ALTER SESSION SET CONTAINER=CDB$ROOT; SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK; SQL> EXIT
-