Upgrading APEX is very simple. You just have to run the apexins.sql script and voila.
So the question is , what to you do if this script fails.
Well, first of all, you have to take backups before any upgrades like any other database upgrades. Better safe than sorry. Full rman database backups are better, rebuilding from a full export is a bit more tricky.
There is also a java class that comes with APEX that allows you to schedule backups of your applications. APEXExport does an export of your applications by workspace or for the full instance. You can run that java class in a script file. You will find it in the APEX zip under utilities / oracle / apex .
Back to my original story :). I ran the apexins.sql script to upgrade from APEX 3.1 to APEX 4.1 and got the ORA-06502 error.
I searched on google to find out that this is a bug with 10.2.0.3 :
9930553 - FAILED INSTALLATION ON 10.2.0.3
Application Express 4.0 cannot be installed on Database version 10.2.0.3 on Linux and potentially other platforms as well. The installation will end with "ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parameters".
Solution: Apply the patch for bug 5890966 (related bug) or apply the 10.2.0.3 Patch 6 on Windows Platforms (if on Windows Platforms) or apply the 10.2.0.4 database patch set or upgrade to 11gR1 or later.
I was upgrading a production environment and at this point my options were :
- restore database
- rollback to previous version of APEX
- apply the database patch and rerun the Upgrade
Option 2, well, if you read the APEX documentation, it is usually easy to perform. (BTW I think that there is an error in the documentation , you probably have to run
('APEX_040100','FLOWS_030100');Instead of APEX_030200 ...) .
When you upgrade an APEX instance, it does not really upgrade. It creates a new schema called APEX_040100 (where 040100 is the version of APEX) and upgrades / copies the applications from the previous APEX schema. To rollback, you simply run a script that recreates APEX synonyms to point back to the previous version (wwv_flow_upgrade.switch_schemas). In that specific case, the procedure to switch back was a bit more complicated. Don't ask me why, it just is. And at the end, APEX would not be upgraded to 4.1 but back to 3.1.
Option 3, this really depended on the impact of the patch. Patching a production environment usually need some testing and planning. Fortunately, in our case, this patch was a minor patch. I contacted the DBA in charge and he agreed to apply it.
The question was: If I re-run the apexins.sql script, will it know that the last valid install was APEX 3.1 and not the failed APEX 4.1 ? Can I just drop the APEX_040100 schema and re-run the apexins.sql script or do I have to rollback to the previous release before re-upgrading ?
The answer is : apexins.sql will check the latest version of APEX before upgrading. So you can drop the failed install schema (in this case APEX_040100) and re-run the upgrade without any worries.
I did just that and Bingo ! everything worked like a charm.
Lesson learned :
- When you upgrade APEX, make sure that you have good backups. Even if the upgrade is a very simple process and almost never fails. Sometimes it can fail due to Database issues like bugs or tablespaces being full etc.
- If the install/upgrade fails, you can drop the APEX schema and rerun the script.
- Better run APEX on 10.2.0.4 and up :)