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.
You can follow the steps below to move your APEX applications, associated data schemas, and local RESTful services to Autonomous Database:
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.
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
For more information on Oracle Cloud Infrastructure Object Storage please review the documentation.
Please Note: It is recommended to use the Instant Client downloaded from Autonomous Database service console (Development → Download Oracle Instant Client)
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'DEF_CRED_NAME', username => 'admin@example.com', password => 'OCI_AUTH_TOKEN' ); END; /
select default_tablespace from sys.dba_users where username = 'PROJECTS';
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
BEGIN dbms_cloud.drop_credential(credential_name => 'DEF_CRED_NAME'); END; /
sqlplus admin/XXXXXX@pdb1_high alter user projects identified by PASSWORD;
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.
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;
For additional information, see:
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.
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.
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:
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.
Thanks go to Jason Straub, Vlad Uvarov, Shawn Zhang and David Peake for authoring and testing this cookbook.