Documentation Enhacement - Upgrade APEX v5.1.4 on OXAR installation to v18.2 up to v20.1

I propose a new document/section on the github page of OXAR that has specific information about upgrading to Oracle APEX (release 18.2 in this case). Note: Upgrade to APEX 19.1 is exactly the same, this guide works fine. I actually used it upgrade to 19.1 of APEX.

This is not using patches that requires paid support, this is by downloading the current version publicly available for download.

Before you start, make sure you increase the MAXSIZE limit of the datafile for SYSTEM:

SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
SQL> alter database datafile '/u01/app/oracle/oradata/XE/system.dbf' AUTOEXTEND ON MAXSIZE 800M;

These are the steps I followed and have upgraded my OXAR servers running 5.1.x to 18.2 No issues whatsoever. The only requirement from OXAR perspective is that ORDS 3.0.12 is the minimum for APEX 18.3 (See other enhancement request)

Note: In the notes below I use dropbox but you can use whatever you want

Note: All commands must be run as root

systemctl stop tomcat@oxar
cd ~
wget https://www.dropbox.com/s/{unique_link}/apex_18.2.zip
unzip apex_18.2.zip
cd apex

Now using SQLPlus connect as sys as sysdba and run the following:

SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
SQL> @apex_epg_config.sql /root
SQL> exit

SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
SQL> @apxchpwd.sql
SQL> exit

If your OXAR installation enabled the ACL (via OOS_ORACLE_ACL_APEX_ALL_YN = Y in the config.properties of your oxar installation) Note: set to Y by default then run the following

SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_180200
  -- the "connect" privilege if APEX_180200 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_180200',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_180200', TRUE, 'connect');
  END IF;

EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_180200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/

COMMIT;

As root run the following commands:

cd /ords/apex_images
unalias cp
cp -r /root/apex/images/* .
systemctl start tomcat@oxar
pm2 start node4ords

Note: Seems ORDS have issues after the Upgrade of APEX, the tomcat Catalina log shows errors like this:

WARNING: ORA-04063: view "ORDS_METADATA.UNIFIED_POOL_CONFIG" has errors
java.sql.SQLException: ORA-04063: view "ORDS_METADATA.UNIFIED_POOL_CONFIG" has errors

Check the views owned by ORDS_METADATA schema and if there are some views with errors, validate to have them fixed. For me I just run the following and works/fix the issue always:

cd /var/lib/tomcat/webapps
java -jar ords.war validate --database apex

Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.

Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.

Oracle REST Data Services will be validated.
Validating Oracle REST Data Services schema version 18.3.0.r2701456
... Log file written to /root/ords_validate_core_2019-04-26_203740_00175.log
Completed validating Oracle REST Data Services version 18.3.0.r2701456.  Elapsed time: 00:00:08.26

OraOpenSource / OXAR

Documentation Enhacement - Upgrade APEX v5.1.4 on OXAR installation to v18.2 up to v20.1 #264