Oracle Database 19c APEX Upgrade: 19.2 to 24.1.7

Upgrade Oracle Application Express (APEX) Version in Oracle Database

What is APEX ?

Oracle APEX (Application Express) is a low-code development platform for building web applications using Oracle Database. It enables developers to create applications quickly, with SQL for data interaction. APEX is designed to simplify development, automate processes and provide powerful tools for reporting, dashboards, and data management while tightly integrating with Oracle’s database ecosystem.

APEX Requirements :

The Oracle APEX upgrade process is independent of the database engine version, meaning you can upgrade APEX without needing to upgrade the Oracle Database itself. This is because APEX is installed as a set of database objects within the Oracle Database but is managed as a separate component. However, there are database version compatibility requirements. For example, each version of APEX requires a minimum Oracle Database version to function properly. You can upgrade APEX independently as long as the database meets those version requirements.

Oracle APEX release 24.1 requires an Oracle Database release 19c or later. APEX runs on all database editions, including Enterprise Edition (EE), Standard Edition (SE), and Oracle Database Free. APEX can be installed in single-instance database and in Oracle Real Application Clusters (Oracle RAC) database. APEX disk space requirements are as follows:

• Free space in APEX tablespace for each additional language (other than English) installed: 60 MB
• Free space for APEX software files on the file system: 599 MB if using English only download (apex_24.1_en.zip) and 983 MB if using full download (apex_24.1.zip).
• Free space in APEX tablespace: 190 MB
• Free space in SYSTEM tablespace: 125 MB

Upgrading from a Previous APEX Release :

Upgrading APEX is no different from the initial installation; there is no need to uninstall anything…

The upgrade process creates new database objects in a new schema and migrates the application metadata to the new release.

I will provide the steps. Currently, I have version 19.2 installed in my database with very minimal usage and a dedicated tablespace (APEX19). Using a dedicated tablespace is considered a best practice because it keeps APEX application objects (tables, views, procedures) isolated from other database objects. This approach also allows for the possibility of dropping the old, unused tablespace after a successful migration.

Step 1: Download the latest major version of Oracle APEX that is compatible with your database release.

For installations where the development will be in English only, download the apex_24.1_en.zip file from the APEX download page. If the development will include languages other than English, download apex_24.1.zip from the APEX download page.

Step 2: Unzip the downloaded file.

[oracle@blt01 ~]$ cd /home/oracle/apex
[oracle@blt01 apex]$ unzip apex_24.1_en.zip
[oracle@blt01 ~]$ cd /home/oracle/apex/apex

Step 3: Create a dedicated tablespace for the new version – APEX24

SYS@bltdb1> CREATE TABLESPACE APEX24 DATAFILE '+DATA'  SIZE 300 M AUTOEXTEND ON NEXT 1 M MAXSIZE 1 G;

Step 4: Execute the installation script

Connect to the database that will be upgraded. Select the appropriate installation option according to your needs. Refer to the Installation Guide for APEX Release 24.1 for more information about the available installation options. I will proceed with the Full Development Environment option, which is the most common choice.

SYS@bltdb1> @apexins.sql APEX24 APEX24 TEMP /i/
. ORACLE
.
. Oracle APEX Installation.
..........................................
.
...set_appun.sql
... Checking prerequisites (MANUAL)

... Prerequisite checks passed.
...
Thank you for installing Oracle APEX 24.1.0

Oracle APEX is installed in the APEX_240100 schema.
...

timing for: Complete Installation
Elapsed:   13.85 

Check for any invalid objects owned by APEX_240100 user in dba_objects and Check the APEX component from dba_registry whether is valid and its version is updated to the 24.1.

SYS@bltdb1> SELECT COMP_ID,COMP_NAME,VERSION,STATUS,SCHEMA FROM DBA_REGISTRY WHERE COMP_ID='APEX';

COMP_ID  COMP_NAME    VERSION  STATUS  SCHEMA
-------  ----------   -------  ------  ----------
APEX     Oracle APEX  24.1.0   VALID   APEX_240100

Step 5: Modify allowed procedures to be invoked in HTTP requests (ORA-20000)

Each time APEX is installed or upgraded, the wwv_flow_epg_include_mod_local function is created under the new schema. This function is invoked by Oracle Application Express’s request validation mechanism, which is called by the embedded PL/SQL gateway or mod_plsql.

The APEX request validation function first evaluates the incoming request. Based on the procedure name, it can approve, reject, or delegate the request to the wwv_flow_epg_include_mod_local function. This local function allows for custom request evaluation using user-defined rules.

By default, it returns FALSE for all procedures. In this case, the following error is encountered for custom procedures:

ORA-20000: Procedure call forbidden by request validation function (wwv_flow_epg_include_modules.authorize).

In short, this function adds an additional layer of security by controlling which procedures can be invoked in HTTP requests. I will add HR.SAMPLE_FUNCTION to the allowed list as an example.

CREATE OR REPLACE function APEX_240100.wwv_flow_epg_include_mod_local(procedure_name in varchar2) 
return boolean is begin -- ORA-20000: Procedure call forbidden by request validation function (wwv_flow_epg_include_modules.authorize)
-- return true;
-- remove this statement when you modify this function
--
-- Administrator note: the procedure_name input parameter may be in the format:
--
--    procedure
--    schema.procedure
--    package.procedure
--    schema.package.procedure
--
-- If the expected input parameter is a procedure name only, the IN list code shown below
-- can be modified to itemize the expected procedure names. Otherwise you must parse the
-- procedure_name parameter and replace the simple code below with code that will evaluate
-- all of the cases listed above.
--
if upper(procedure_name) in ('HR.SAMPLE_FUNCTION') 
then return TRUE;
else return FALSE;
end if;
end wwv_flow_epg_include_mod_local;
/

More information is available on Oracle Application Express Administration Guide and HTTP-403 Forbidden Error While Trying To Access The DB Procedure From The URL (Doc ID 783646.1).

Starting with APEX 20.2, APEX_INSTANCE_ADMIN.ADD_WEB_ENTRY_POINT procedure can also be used.

BEGIN
    apex_instance_admin.add_web_entry_point (
        p_name    => 'HR.SAMPLE_FUNCTION',
        p_methods => 'GET,POST' );
    commit;
END;

Step 6: Clean up – Drop old user (APEX_190200) and drop old tablespace (APEX19)

Now we may proceed with dropping old user “APEX_190200” and old tablespace(APEX19) for me. But first we will make sure that old tablespace is not used by any other database users and other APEX schemas.

SYS@bltdb1> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='APEX19';

OWNER        SEGMENT_NAME                SEGMENT_TYPE 
-------      -----------------           -------  
FLOWS_FILES  SYS_C001064077              INDEX  
FLOWS_FILES  SYS_IL0003685572C00017$$    LOBINDEX  
FLOWS_FILES  WWV_FLOW_FILES_FILE_IDX     INDEX  
FLOWS_FILES  WWV_FLOW_FILES_SESSION_IDX  INDEX   
FLOWS_FILES  WWV_FLOW_FILES_USER_IDX     INDEX  
FLOWS_FILES  WWV_FLOW_FILE_OBJECTS$      TABLE
FLOWS_FILES  WWV_FLOW_FILE_OBJ_PK        INDEX   

I need to move 5 indexes, 1 LOB index, and 1 table belonging to the FLOWS_FILES user from the APEX19 (old) tablespace to the new tablespace APEX24.

The process is straightforward: these objects will be moved to the new tablespace. Although How to Move APEX Schemas to a New Tablespace During APEX Upgrade (Doc ID 760404.1) was originally written for APEX version 3, it remains valid for the current versions. We will follow a similar approach as outlined in that document.

-- Define quota to FLOWS_FILES user on new tablespace
SYS@bltdb1> ALTER USER FLOWS_FILES QUOTA UNLIMITED ON APEX24;

-- Move index segments to the new tablespace.
SYS@bltdb1> ALTER INDEX FLOWS_FILES.WWV_FLOW_FILES_FILE_IDX REBUILD TABLESPACE APEX24 ONLINE;
SYS@bltdb1> ALTER INDEX FLOWS_FILES.WWV_FLOW_FILES_SESSION_IDX REBUILD TABLESPACE APEX24 ONLINE;
SYS@bltdb1> ALTER INDEX FLOWS_FILES.WWV_FLOW_FILES_USER_IDX REBUILD TABLESPACE APEX24 ONLINE;
SYS@bltdb1> ALTER INDEX FLOWS_FILES.WWV_FLOW_FILE_OBJ_PK REBUILD TABLESPACE APEX24 ONLINE;

-- This can be different on other environments.
SYS@bltdb1> ALTER INDEX FLOWS_FILES.SYS_C00369434 REBUILD TABLESPACE APEX24 ONLINE;

-- Move table and lob segment to the new tablespace
SYS@bltdb1> ALTER TABLE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ MOVE TABLESPACE APEX24 
LOB (BLOB_CONTENT) STORE AS (TABLESPACE APEX24) ONLINE;

-- Limit quota of FLOWS_FILES user on old tablespace
SYS@bltdb1> ALTER USER FLOWS_FILES QUOTA 0 ON APEX;

Now drop old user.

SYS@bltdb1> DROP USER APEX_190200 CASCADE;

In our environment, a package named WWV_DBMS_SQL_APEX_190200 with an invalid status remained under the SYS schema. This package was related to the APEX 19.2 version. After the APEX_190200 schema was dropped, we also removed this invalid package.

SYS@bltdb1> DROP PACKAGE SYS.WWV_DBMS_SQL_APEX_190200;

Drop old tablespace.

SYS@bltdb1> DROP TABLESPACE APEX19 INCLUDING CONTENTS AND DATAFILES;

The APEX major version upgrade steps have been successfully completed. Optionally, you may choose to apply the latest cumulative patch to ensure optimal performance and stability, especially if the APEX component is actively utilized..

Step 7: (Optional) Apply the Latest Cumulative APEX Patch (Patch 36695709)

Patch Set Bundle 7 for Oracle APEX 24.1 was released on the 9th December 2024. I will apply it. (Patch 36695709). It is also available in APEX download page. Download and unzip it.

[oracle@blt01 ~]$ export NLS_LANG=American_America.AL32UTF8
[oracle@blt01 ~]$ cd 36695709

SYS@bltdb1> @catpatch.sql

. ORACLE
.
. Oracle APEX 24.1.%
. Patch Set Exception 36695709
........................................
Elapsed: 00:00:00.21
...Validating APEX
...(21:44:01) Starting validate_apex for APEX_240100
...(21:44:01) Checking missing privileges for APEX_240100
...(21:44:02) Checking missing privileges for APEX_GRANTS_FOR_NEW_USERS_ROLE
...(21:44:02) Re-generating APEX_240100.wwv_flow_db_version
... wwv_flow_db_version is up to date
...(21:44:03) Checking for sys.wwv_flow_cu_constraints
... sys.wwv_flow_cu_constraints is still referenced by APEX_190200
...(21:44:03) Checking invalid public synonyms
...(21:44:03) alter public synonym "APEX_PURGEABLE_TASK_HISTORY" compile
...(21:44:03) Key object existence check
...(21:44:03) Post-ORDS updates
...(21:44:03) Calling wwv_flow_upgrade.post_ords_upgrade
...(21:44:03) Setting DBMS Registry for APEX to valid
...(21:44:03) Exiting validate_apex

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.32
...Recompiling invalid public synonyms

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
timing for: Complete Patch 36695709

Run utlrp for invalid objects.

 SYS@bltdb1> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Check the patch from dictionary. When this patch is applied, the APEX product version will be updated to 24.1.7 in dba_registry

SYS@bltdb1> select patch_version, installed_on from apex_patches where patch_number = 36695709;

Hope it helps.