E Maximizing Uptime During an Application Express Upgrade

Learn how to maximize uptime during an Application Express upgrade.

Previously, Oracle Application Express could only be upgraded by completely disabling application usage for an extended length of time. The following is an overview of the additional steps you can take to keep your applications usable for end users during most portions of an Oracle Application Express upgrade.

This advanced procedure is an alternative to the following “Installing Application Express” subsections of the Oracle Application Express Installation Guide:

  • Installing Application Express and Configuring Oracle REST Data Services

  • Installing Application Express and Configuring Embedded PL/SQL Gateway

  • Installing Application Express and Configuring Oracle HTTP Server

To upgrade the instance, administrators typically run these phases in one step by executing one of the following:
  • For full development environment:

    @apexins.sql tablespace_apex tablespace_files tablespace_temp images

  • For runtime-only environment:

    @apxrtins.sql tablespace_apex tablespace_files tablespace_temp images

Where:

  • tablespace_apex is the name of the tablespace for the Oracle Application Express application user.

  • tablespace_files is the name of the tablespace for the Oracle Application Express files user.

  • tablespace_temp is the name of the temporary tablespace or tablespace group.

  • images is the virtual directory for Oracle Application Express images.

The upgrade of an Application Express instance runs in four phases:

  1. Create database schemas and database objects (tables, packages).

  2. Migrate application metadata.

  3. Migrate data that runtime applications modify and switch to the new version.

  4. Migrate additional log and summary data.

Phases 1 and 4 do not disable end users using the instance. Phase 2 only affects developers who modify applications. Phase 3 affects all access to Application Express.

Oracle now also provides alternative upgrade scripts to run the phases independently. Administrators can use these scripts instead of apexins.sql and apxrtins.sql, to reduce the effective downtime of an Application Express instance from potentially hours to just a few minutes (depending on hardware performance).

Note:

This feature is not supported when Application Express is installed in CDB$ROOT.

Administrators must sequentially execute the following scripts to start phases 1, 2 and 3, respectively. At the end of phase 3, a scheduler job automatically starts to execute phase 4.

To reduce downtime during an Oracle Application Express upgrade:

  1. Execute phase 1 script: Development and runtime usage is not affected.

    • For full development environment:

      @apexins1.sql tablespace_apex tablespace_files tablespace_temp images

    • For runtime-only environment:

      @apxrtins1.sql tablespace_apex tablespace_files tablespace_temp images

    Example: @apexins1.sql sysaux sysaux temp /i/

  2. Execute phase 2 script: Development is disabled, but runtime usage is not affected.

    • For full development environment:

      @apexins2.sql tablespace_apex tablespace_files tablespace_temp images

    • For runtime-only environment:

      @apxrtins2.sql tablespace_apex tablespace_files tablespace_temp images

    Example: @apexins2.sql sysaux sysaux temp /i/

  3. Disable web access for the web listener (Oracle REST Data Services, Embedded PL/SQL Gateway, or Oracle HTTP Server).

  4. Execute phase 3 script: Application Express can not be used.

    • For full development environment:

      @apexins3.sql tablespace_apex tablespace_files tablespace_temp images

    • For runtime-only environment:

      @apxrtins3.sql tablespace_apex tablespace_files tablespace_temp images

    Example: @apexins3.sql sysaux sysaux temp /i/

  5. Install images of the new Application Express version in your web listener. Administrators can do this while phase 3 is running or even earlier, if the new version's images directory is different to the previous Application Express version's ( for example: /i51/ for the new version vs. /i50/ for the old version).

    For details refer to the installation instructions for Oracle REST Data Services, Embedded PL/SQL Gateway, or Oracle HTTP Server.

  6. Re-enable web access for the web listener (restart Oracle REST Data Services, Embedded PL/SQL Gateway, or Oracle HTTP Server).

After web access is restarted, developers and users can access the instance again, while phase 4 finishes in the background.