How to Move Your APEX Applications to Autonomous Database

Oracle APEX is now available on Oracle Autonomous Database, and many customers will wish to move their existing on-premises APEX applications to the Oracle Cloud.  It's actually a straightforward process.  All you simply need to do is copy your data, your APEX applications, and your RESTful services into your new cloud environment.  The following blog post is a complete cookbook, with examples, that demonstrates how to accomplish this move.

High-Level Steps

You can follow the steps below to move your APEX applications, associated data schemas, and local RESTful services to Autonomous Database:

1. Archive your APEX applications - This step copies your APEX application definitions into your local schema
2. Export your data schema(s) - This will be used to move all of your exiting data objects and data
3. Stage your export file(s) - Copy the Data Pump export file into the Oracle Cloud Infrastructure Object Storage
4. Connect to Autonomous Database and Import File - Your data schema(s) will now be moved
5. Create an APEX Workspace - Define your new APEX workspace where your applications will be restored
6. Remove database objects and install APEX Application Archive App - Run a small SQL script to remove the objects which prevent the APEX Application Archive App from installing
7. Restore the APEX Applications - Using the APEX Application Archive productivity app, you can easily restore all of your APEX applications
8. Copy ORDS RESTful Services - If you have defined RESTful Services on-premises then use this step to copy them to the cloud
9. Complete post migration tasks in Autonomous Database, if applicable.

Once you have completed these steps you will be able to run your APEX applications on Autonomous Database. Please note that Autonomous Database is currently running Oracle APEX 19.1.  If you are running an earlier version of APEX on-premises, then your applications will be upgraded to APEX 19.1 at the end of this process.  Your APEX applications should run without issue, but we still recommend to thoroughly test them. Developers should also go into each application and review the Upgrade Application report, within Utilities, for any additional upgrades they may wish to implement which were not automatically performed as part of the upgrade.

Export Your Apps with the APEX Application Archive productivity app

1. Sign in to your on-premises workspace
2. From the App Builder home page, click Create and choose Productivity App
3. Choose APEX Application Archive and click Install App
4. Click Next and Install App
5. Click Run App icon
6. Accept the defaults and click Complete Setup
7. Click Archive Applications
   
    
8. Ensure all applications are in the right side of the shuttle, click Next and then Create Archive.
   

Export Your Workspace Schema

The next step is to migrate your workspace schema from your on-premises APEX instance to Autonomous Database. You use Data Pump to do the schema level export that will include the database user and the schema. Per the Autonomous Database documentation, the recommended parameters to use for expdp are as follows:

exclude=cluster,db_link
parallel=n
schemas=schema_name
dumpfile=export.dmp

The following example exports the PROJECTS schema which is associated with an on-premises workspace using the recommended Data Pump parameters from the Autonomous Database documentation. You should export the schema using a privileged user that can create a database user. Make sure the user doing the export is granted read and write on the default DATA_PUMP_DIR directory.

[oracle@localhost ~]$ sqlplus sys/oracle@localhost/apex1 as sysdba

SQL> grant read, write on directory data_pump_dir to SYSTEM;

Grant succeeded.

SQL> exit

[oracle@localhost ~]$ expdp SYSTEM/system_password@localhost/apex1 \
> exclude=cluster,db_link \
> parallel=2 \
> schemas=projects \
> dumpfile=projects.dmp \
> directory=data_pump_dir

Export: Release 19.0.0.0.0 - Production on Mon Jul 1 11:37:49 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "PROJECTS"."SYS_EXPORT_SCHEMA_01":  SYSTEM/********@localhost/apex1 exclude=cluster, db_link data_options=group_partition_table_data parallel=4 schemas=projects dumpfile=projects.dmp directory=data_pump_dir 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "PROJECTS"."APEX$ARCHIVE_CONTENTS"          1.013 MB       2 rows
. . exported "PROJECTS"."APEX$ARCHIVE_HEADER"            10.79 KB       1 rows
. . exported "PROJECTS"."APEX$ARCHIVE_HISTORY"           7.828 KB       2 rows
. . exported "PROJECTS"."APEX$ARCHIVE_LOG"               8.804 KB       2 rows
. . exported "PROJECTS"."APEX$ARCHIVE_PREF"              7.726 KB       1 rows
. . exported "PROJECTS"."DEPT"                           6.031 KB       4 rows
. . exported "PROJECTS"."EBA_ARCHIVE_ACCESS_LEVELS"      5.968 KB       3 rows
. . exported "PROJECTS"."EBA_ARCHIVE_ERRORS"                 0 KB       0 rows
. . exported "PROJECTS"."EBA_ARCHIVE_ERROR_LOOKUP"       5.992 KB       1 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
. . exported "PROJECTS"."EBA_ARCHIVE_NOTIFICATIONS"          0 KB       0 rows
. . exported "PROJECTS"."EBA_ARCHIVE_PREFERENCES"        7.953 KB       4 rows
. . exported "PROJECTS"."EBA_ARCHIVE_USERS"              8.554 KB       1 rows
. . exported "PROJECTS"."EMP"                            8.781 KB      14 rows
. . exported "PROJECTS"."SAMPLE$PROJECTS"                11.66 KB      12 rows
. . exported "PROJECTS"."SAMPLE$PROJECT_COMMENTS"        9.328 KB      11 rows
. . exported "PROJECTS"."SAMPLE$PROJECT_MILESTONES"      14.61 KB      30 rows
. . exported "PROJECTS"."SAMPLE$PROJECT_STATUS"          8.265 KB       3 rows
. . exported "PROJECTS"."SAMPLE$PROJECT_TASKS"           42.20 KB     125 rows
. . exported "PROJECTS"."SAMPLE$PROJECT_TASK_LINKS"      11.10 KB       7 rows
. . exported "PROJECTS"."SAMPLE$PROJECT_TASK_TODOS"      12.97 KB      16 rows
Master table "PROJECTS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PROJECTS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/orcl/dpdump/8C3CC5A5DCAC1E8BE055000000000001/projects.dmp
Job "PROJECTS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jul 1 11:38:26 2019 elapsed 0 00:00:36

Stage Dumpfile in Oracle Cloud Infrastructure Object Storage

1. Log in to OCI using your OCI account
2. Navigate to Object Storage by clicking the icon on the home page or using the upper left hamburger menu, go to Object Storage => Object Storage
3. Pick a compartment and the an existing bucket, or create a new bucket
4. Click on the bucket name and then click Upload Objects button
5. Choose export.dmp (or the name of your DataPump export file) and click Upload Objects
   
6. After the upload completes, click the icon with the three dots next to the file and choose View Object Details. Copy the address in URL Path (URI) field. You will need the address in the next step.
7. Obtain your OCI token by clicking the user icon in the upper right corner and choosing User Settings.
8. Under Resources, click Auth tokens. If you do not already have an Auth Token, click the Generate Token button.
   
9. Copy the token which you will use in the next step.

For more information on Oracle Cloud Infrastructure Object Storage please review the documentation.

Connect to Autonomous Database Using Client Credentials and Import Schema using Dump File

Please Note:  It is recommended to use the Instant Client downloaded from Autonomous Database service console (Development → Download Oracle Instant Client)

1. Log in to Autonomous Transaction Processing or Autonomous Data Warehouse 
2. Open the Service Console and Click Administration on the left side
3. Click Download Client Credentials (Wallet) and save the zip file
4. Unzip the file in $ORACLE_HOME/network/admin
5. Inspect tnsnames.ora file included in the zip to see the list of service names to connect to. The first service name should be annotated with _high, and we will use it to import data.
6. Use SQL*Plus to connect to your Autonomous Database as the ADMIN user using the service name you found in the previous step.
7. Run the following to create a credential using the DBMS_CLOUD package. Replace OCI_AUTH_TOKEN with the token you copied in the previous section and username with your actual OCI username:

   BEGIN
       DBMS_CLOUD.CREATE_CREDENTIAL(
           credential_name => 'DEF_CRED_NAME',
           username        => 'admin@example.com',
           password        => 'OCI_AUTH_TOKEN'
       );
   END;
   /
   

8. Before using impdp to import your projects.dmp file, you will need to determine the tablespace name which you will use to remap to the default DATA tablespace. Run the following query on the database instance you are migrating from. Replace 'PROJECTS' below with the schema associated with the workspace you are migrating.

   select default_tablespace from sys.dba_users where username = 'PROJECTS';
   

9. With the information above, you can now use impdp to import your data from the dump file. For example:

   impdp admin/XXXXXXXXX@pdb1_high credential=def_cred_name directory=data_pump_dir remap_tablespace=APEX_1422010366028782:DATA \
   dumpfile='https://console.us-phoenix-1.oraclecloud.com/object-storage/buckets/spartaprogrammanagement/project-move/projects.dmp' \
   parallel=1 partition_options=merge transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y \
   exclude=cluster,db_link
   

10. (Optional) Remove the credential for OCI object storage from the database:

    BEGIN
       dbms_cloud.drop_credential(credential_name => 'DEF_CRED_NAME');
    END;
    /
    

11. You will need to reset the password for the database user that was created as a result of the import. The password must conform to the Autonomous Database password complexity rules. Connect to your Autonomous Database instance and run the following, replacing PASSWORD with a proper password.

    sqlplus admin/XXXXXX@pdb1_high
    alter user projects identified by PASSWORD;
    

Create APEX Workspace on Autonomous Database Matching Name and ID for your on-premises Workspace

1. Sign in to your on-premises workspace
2. Click the ? icon in the upper right, choose About, and then copy your Workspace ID
   
3. Log in to your Autonomous Transaction Processing or Autonomous Data Warehouse service console
4. Click Development on the left side, then click the APEX link. The Application Express Administration Services sign-in page will appear. If you already created a workspace, the workspace sign-in page will appear instead. In this case, click the Administration Services link to proceed.
5. Enter the Autonomous Database administrator (ADMIN) password and click Sign In to Administration.
   
   
6. In Administration Services, click Create Workspace and select the Database User that you imported and make sure the Workspace Name matches your on-premises workspace. Expand the Advanced section and enter the same Workspace ID as you copied in Step 2. Click Create Workspace.
7. Click the link of the workspace name in the success message.  Alternatively, you can click the ADMIN username in the upper right and click Sign Out. Sign in to the workspace you just created.
8. Click Set APEX Account Password and enter the same password you just used to sign in to Oracle APEX.

Remove database objects and install APEX Application Archive App

Follow the steps below to remove database objects (tables, sequences and packages) related to APEX Application Archive App. Presence of these objects will prevent APEX Application Archive to be installed on Autonomous Database.

1. Sign in to your Oracle Autonomous Transaction Processing or Oracle Autonomous Data Warehouse service console.
2. Click Development on the left side, then click the APEX link.
3. Click Workspace Sign-in and enter your workspace name, apex account name and password.
4. Click SQL Workshop and click SQL scripts
5. Upload the following script and run it.

   drop package eba_archive;
   drop package eba_archive_fw;
    
   drop sequence eba_archive_seq;
    
   drop table eba_archive_users          cascade constraints;
   drop table eba_archive_errors         cascade constraints;
   drop table eba_archive_access_levels  cascade constraints;
   drop table eba_archive_notifications  cascade constraints;
    
   drop table eba_archive_error_lookup;
    
   drop table eba_archive_preferences;
   

6. Click App Builder, then click Install a Productivity or Sample App.
   
7. Find the APEX Application Archive app in the App Gallery list and click Install App, click Next, then click Install App.
8. Repeat this process for all workspaces you wish to import into your Autonomous Database.

For additional information, see:

• Create Oracle Application Express Workspaces in Autonomous Transaction Processing in Using Oracle Autonomous Transaction Processing
• Create Oracle Application Express Workspaces in Autonomous Data Warehouse in Using Oracle Autonomous Data Warehouse

Use APEX Application Archive App to Restore your Applications and Resources

1. Run the APEX Application Archive App
2. Click Archived Content and click Restore button associated with the application you want to restore. This will export the archive to Export Repository in your current workspace.
   
   
   
3. Go back to App Builder, click Workspace Utilities, click Export.
4. Click Export Repository under Tasks on the right side of the page.
   
5. Now you should see the application sql file. Click install and select "Reuse Application ID from Export file".
   

Move ORDS RESTful Services from on-premises to Autonomous Database

If you have RESTful services that you created in your on-premises workspace, you can easily export them and then import them into Autonomous Database. The following is just an example using the example RESTful service module. You do not need to move the example RESTful service module in practicality, you can just recreate it in your workspace on Autonomous Database.

1. Sign in to your on-premises workspace and click SQL Workshop, then RESTful Services
2. Click the Export button
   
3. Choose All Services from the RESTful Service select list, click Export and save the file
4. Sign in to the corresponding workspace you created in Autonomous Transaction Processing or Autonomous Data Warehouse
5. Click SQL Workshop and then RESTful Services
6. Click the Register Schema with ORDS button
7. Choose No for Install Sample Service and click the Save Schema Attributes button
   
8. Click the Import button, choose the file from step 3, and then click Import

Post Migration Tasks

You may need to complete additional tasks after migration to Autonomous Database before you can fully utilize your APEX applications and RESTful services.  Please review Restrictions and Limitations for Oracle Application Express with Autonomous Transaction Processing in Using Oracle Autonomous Transaction Processing.

Unsupported APEX Authentication Schemes

Oracle APEX in Autonomous Database does not support certain application authentication schemes. If your application in on-premises database uses an authentication scheme not available in Autonomous Database, you must switch to a different authentication scheme after importing this application into Autonomous Database.

To change authentication scheme for your APEX application in Autonomous Database:

1. Open your application in APEX App Builder.
2. Click Shared Components, then click Authentication Schemes in the Security section.
3. Click Create, click Next.
4. Enter the Name for your new authentication scheme and select a supported Scheme Type (for example, Application Express Accounts).
5. Click Create Authentication Scheme.
6. Run the application.

Applications which Send Email

If your applications in on-premises database send outbound email using APEX_MAIL or built-in Application Express dialogs, further configuration is required in Autonomous Database. See Send Email from Oracle Application Express in Using Oracle Autonomous Transaction Processing documentation to learn more.

Credits

Thanks go to Jason Straub, Vlad Uvarov, Shawn Zhang and David Peake for authoring and testing this cookbook.