How to Manually Upgrade Oracle APEX from 4.2 to 20.2 and configure

Manually Upgrade APEX from oracle apex 4.2 to oracle apex 20.2

To upgrade to latest Application Express, download the latest version from the oracle metalink.

Upgrading APEX

1. Check the current APEX Version: (Existing or Installed Version)

SQL> Select Comp_name, status, Version From Dba_Registry where comp_id='APEX';

COMP_NAME                                STATUS       VERSION

---------------------------------------- ------------ ------------------------------

Oracle Application Express               VALID        4.2.3.00.08

SQL>

2. From the directory which holds the APEX unzipped software, connect to sqlplus as SYS user and run apexins.sql

$ cd $ORACLE_HOME/apex

SQL> sqlplus /as sysdba

SQL> @apexins.sql APEX_SPACE APEX_SPACE TEMP /i/

3. Set Admin Password 

Now run the change password script for the ADMIN user as following and the password must follow the complexity rules, for example, I provided the password as Apx#123.

SQL> @apxchpwd.sql

4. Unlock the APEX_PUBLIC_USER  and other users

Unlock the APEX_PUBLIC_USER account and specify the password. Don’t forget to connect to Oracle using SQL*PLUS if disconnected. 

SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER APEX_LISTENER ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER APEX_INSTANCE_ADMIN_USER ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER APEX_200200 ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

SQL> ALTER USER ORDSYS ACCOUNT UNLOCK IDENTIFIED BY Apx#123;

5. Run Configure Apex RESTful Service Script 

SQL> @apex_rest_config.sql

This will ask you to give APEX_LISTENER and APEX_PUBLIC_USER password to set which will be used later for the ORDS setup.

6. Check the upgraded APEX Version:

Select Comp_name, status, Version From Dba_Registry where comp_id='APEX';

COMP_NAME                                STATUS       VERSION

---------------------------------------- ------------ ------------------------------

Oracle Application Express               VALID        20.2.0.00.20

SQL>

APEX has been successfully upgraded to version 20.2.0.00.20…

7. Configuration Oracle Application Express with ORDS:

Making required directory structure where ORDS server installation.

Here are the steps on Linux

ORDS Server

$ mkdir -p /cloaneappl/ords

$ mkdir -p /cloaneappl/ords/conf

$ cd /cloaneappl

$ unzip ords.3.0.12.263.15.32.zip -d /cloaneappl/ords

Note: Copy the images from where oracle apex(Database Installed) installed and past where ORDS Server.

In my case oracle apex and ORDS server are seprate.

Database Server

$ cd $ORACLE_HOME/apex (10.24.248.40 database server)

$ scp -r images applmgr@10.24.248.39:/cloaneappl/ords/

ORDS Server

$ java -jar apex.war configdir /cloaneappl/ords/conf

$ java -jar apex.war static /cloaneappl/ords/images

$ java -jar apex.war standalone(Provide images location first time)

$ nohup java -jar apex.war > startApex_15MAR23.log &

8. Deinstallation of ORDS

1. Deinstall the database schemas using

$ java –jar ords.war uninstall

In effect this removes the 2 schemas from the database

2. Optionally remove the ORDS installation directories

3. Optionally remove the ORDS tablespace from the database