Jan 24, 2012

Upgrading to APEX 4.1 on Oracle - ORA-06502

I have been upgrading a lot (and I mean a lot) of APEX environments to 4.1 and never had any issues until ... well until I encontered the infamous error : "ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parameters".

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 :
Application Express 4.0 cannot be installed on Database version 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 Patch 6 on Windows Platforms (if on Windows Platforms) or apply the database patch set or upgrade to 11gR1 or later.

I was upgrading a production environment and at this point my options were :
  1. restore database
  2. rollback to previous version of APEX
  3. apply the database patch and rerun the Upgrade
Option 1 was a last resort option. This would affect too many other systems, some not related to APEX. And it would take too much time.

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
exec flows_030100.wwv_flow_upgrade.switch_schemas
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 and up  :)



Roel Hartman said...

Another lesson: Test your upgrade on an identical environment before running it on prod... Then you would have encountered the issue in a less critical moment. ;-)

Francis said...

I did ! I did ! :)
But turns out that this bug does not always happen. So the upgrade went fine in DEV but not in prod.

Roel Hartman said...

Ouch. That sounds nasty - and unreliable ;-)
(and I assumed you did, but that didn't appear in your post)
CU in Vegas!

Michelle Skamene said...

I can confirm that we tested the upgrade in DEV, and all went very smoothly. Identical environments! Not fun, but Francis did a great job and all issues were resolved in the end. Phew!

Panos Zarafidis said...

Thanks for the info. For future reference, after a failed upgrade from 4.0.1 to 4.2.1 we followed the exact steps of the oracle documentation and reverted successfully to 4.0.1.