Moving APEX Applications Repository with APEXExport

Most likely you’ve landed here because you need to migrate APEX applications/workspaces from one database to another. If so, you’re in the right place!

We’ll use APEXExport to accomplish this.

I’m providing a quick summary of the steps for using the tool, assuming:

• The source APEX version is at least 4.2.4.
• The target version is also be 4.2.4 or higher.

Be aware that none of the APEX installation schemas on the database (the APEX_% and FLOWS_FILES schemas) can be exported in this manner or in any other manner.

The APEX must be installed on the target database, meaning this process is about migrating the workspaces and applications from one installation to another.

To Export:

1. Migrate the application schemas (non-APEX) via traditional methods.  You can use the database export utilities (Data Pump or legacy Export, just be aware of the limitations of each) to generate a dump file with all DB objects and data that your APEX applications need to run. This refers to the objects in the schemas that your APEX applications are dependent on.

expdp matheusdba schemas=MY_APP_SCHEMA directory=DIR_BKP dumpfile=APEX_APP_SCHEMA.dmp logfile=APEX_APP_SCHEMA.log

2. The APEXExport tool is available along with the APEX Software, which can be downloaded separately here: https://www.oracle.com/tools/downloads/apex-v191-downloads.html.

It can be even unzipped at the /tmp (as long it’s not mounted with noexec parameter on OS):

cd /tmp
unzip apex_20.1_en.zip

Note1: You may also want to use ojdbc8, which you can download separately and use on the JAVA_% environment variables paths.

In this case, you also may copy ojdbc8.jar to /tmp/apex/utilities, assuming the example above.

3. Run the APEXExport as follows:

3.1 First run it using “-expWorkspace” to export all workspaces (This will generate a w*.sql script for each workspace).

java oracle.apex.APEXExport -db localhost:1521:MYDB -user system -password systems_password -expWorkspace

3.2 Now run it using “-instance” which will generate a f*.sql script for every application and shared component.

java oracle.apex.APEXExport -db localhost:1521:MYDB -user system -password systems_password -instance

Note2: This workspace export should export all of the shared components from the workspaces.
Note3: This doesn’t mention RESTful services but if using the APEXExport from 4.2.4 or higher, they will be included.
Note4: The password can be removed from the command line to be requested during the execution time.

To Import:

As a reminder, the target version of APEX doesn’t need to be the same as the source version. But APEX does need to be fully installed and configured into the target database prior to importing.

1. Import the dump file generated for the regular database schemas your APEX Application uses.
2. Import the workspaces via SQL*Plus as per:

2.1 Connect to SQL*Plus:

connect sys / as sysdba

2.2 Alter session to the APEX schema:

alter session set current_schema = APEX_040200;

Note5: The target schema might be different if importing into a different version of APEX. For example, APEX_200200 or similar.

2.3 Run the scripts to create the workspaces.

SQL> @<script_generated>.sql

This will create the workspaces with the same workspace IDs as the source DB.

This also prevents the need to modify the workspace ID contained in each of the application exports.

3. From the same session as above, accomplish the import of each of the application exports.

SQL> @<app1_script>.sql
SQL> @<app2_script>.sql
[...]
SQL> @<appN_script>.sql

4. Once done, all your APEX applications should have been migrated to your destination environment.

However, as usual, test and validate this procedure as much as possible to eliminate untested situations like blocked ports on the new server and so on.

I hope this helps!

For additional reference, please look into the Oracle Documentation.

As always, if you have questions or thoughts, please feel free to leave them in the comments!

To read more on this topic, please visit my post, Automating APEX Applications Backup.