Friday, October 28, 2016

"ORA-20001: Application ID and current security group ID are not consistent" encountered during APEX Upgrade from 4.x to 5.x

Recently, our QA team at Oracle was testing database upgrades, and the following error appeared in the database upgrade log while the APEX component was being upgraded:

...Installing Theme 1

API Last Extended:20130101
Your Current Version:20130101
This import is compatible with version: 20130101
COMPATIBLE (You should be able to run this import without issues.)
--application/set_environment
APPLICATION 108801 - Theme: Simple Red
--application/delete_application
begin
*
ERROR at line 1:
ORA-20001: Application ID and current security group ID are not consistent.
ORA-06512: at "APEX_050000.WWV_FLOW_API", line 1419
ORA-06512: at "APEX_050000.WWV_FLOW_API", line 2106
ORA-06512: at line 2


A day later, our support organization reported that a customer ran into exactly the same error when they attempted an APEX upgrade from 4.2.5 to 5.0.4. I tried to reproduce the issue myself, but was unable to in a database I created using DBCA. I then created a database in our RDBMS development environment, and was able to reproduce this issue upgrading 4.2.5 to 5.0.4.

I noticed in the log of my failed installation a message that errors exist in the upgrade progress table.

SQL> select upgrade_error from apex_050000.wwv_flow_upgrade_progress where upgrade_error is not null;

UPGRADE_ERROR
--------------------------------------------------------------------------------
ORA-01000: maximum open cursors exceeded


I checked the open_cursors database parameter of the database where the upgrade failed, and found it was set to 50. I then checked the parameter in the database I created using DBCA, where the APEX upgrade was successful, and noted that it was set to 300. I restored the database of the failed upgrade, and issued the following command:

SQL> alter system set open_cursors=300 scope=both;

I retried the APEX upgrade to 5.0.4 and it completed successfully without errors. If you encounter the above error while doing an APEX upgrade, check the open_cursors parameter, and set it to at least 300.