Upgrading both Oracle and Apex

We need to move from Oracle 11 to Oracle 19. Our Oracle 11 includes Apex 5.0.4 version with applications which need to be moved as well. Point is for Oracle Database 19c the minimum APEX version is 18.2, so we need to also upgrade Apex, and, as far as we understand, Oracled 19c included natively Apex 18.2.

We only found news where it's suggested to apply the script of the latest Apex version in order to directly upgrade Apex content and metadata, but this affects scenarios where Oracle DB should not be moved.

We were thinking to remove Apex 18.2 from Oracle installation (by using using apxremov.sql script available in Apex zipped installation scripts), copy Apex 5.0.4 schemas from Oracle 11 to Oracle 19 and then, applying Oracle 18.2 scripts which should upgrade version.

I'm wondering which is the better way to move applications to Apex 5.0.4 to Apex 18.2 (or even some later versions) in our scenario.

We really appreciate your thoughts and pov.

Thanks! Stefano

Solution

This is my suggestion; you don't have to accept it if you don't like it. Wait for someone else's opinion.

Current situation:

Oracle 11g database, Apex 5.0.4

Target:

Oracle 19c, Apex 18.2

My suggestion:

upgrade Apex in your current database from 5.0.4 to 18.2
that's straightforward, nothing special about it - follow installation guide
upgrade applications to 18.2
- that won't be that simple because certain functionalities have changed - the largest of them is the fact that interactive grid is now used instead of tabular forms. Upgrade won't do much good, so you'll have to do most of the job manually; if you have many tabular forms, it'll take time. True - you can keep applications in compatibility mode that lets applications work OK up to version 5, but - that's not what you want to achieve at the end, that's the purpose of upgrade - to keep up; sooner or later you will have to get rid of tabular forms
install new 19c database; if it already includes Apex 18.2, then you don't have to install anything
- export workspace-by-workspace from Oracle 11g and import them one-by-one into 19c
- alternatively, export only applications that are still being used. That's a good chance to get rid of garbage applications
once you're done, all applications should work just fine
then, upgrade Apex to the latest version (currently, 23.2). That step - from 18.2 to 23.2 shouldn't be too difficult

Approach I wouldn't want to take is what you said:

... remove Apex 18.2 from Oracle installation (...), copy Apex 5.0.4 schemas from Oracle 11 to Oracle 19 and then, applying Oracle 18.2 scripts which should upgrade version.