APEX 404 Not Found after ORDS upgrade to 23.1 or later when SYS shows NO in column COMMON of DBA_USERS in non-cdb

When upgrading Oracle REST Data Services (ORDS) to version 23.1 or later, it completes with warnings as following

WARNING: Procedure APEX_ADMIN could not be added to allow list.
 The function or procedure is a common object.
 You must execute the following as a common user: 
 begin APEX_240100.wwv_flow_listener.sync_ords_gateway_allow_list; end;
WARNING: ORDS migrated 0 entry points from APEX to ORDS PL/SQL
 Procedure Gateway Allow List.

Now accessing APEX will return 404 error as following

                   Not Found
               HTTP Status Code: 404
       Request ID: EHFC-h-DNoA9A0Qo-_W
     Request Timestamp: 2024-12-17T13:20:38.684973Z
               Error Code: ORDS-2201
The procedure named apex could not be accessed, it may not be declared, or the user 
executing this request may not have been granted execute privilege on the procedure, or a 
function specified by security.requestValidationFunction configuration property has prevented 
access. Check the spelling of the procedure, check that the execute privilege has been 
granted to the caller and check the configured security.requestValidationFunction function. If 
using the PL/SQL Gateway Procedure Allow List, check that the procedure has been allowed 
via ords_admin.add_plsql_gateway_procedure.

CAUSE

Since pluggable database was introduced in Oracle database from 12.1, Oracle supplied users (such as SYS, SYSTEM, DBSNMP, etc) are set to YES for new colume COMMON (added from 12.1) of view DBA_USERS no matter whether the database is CDB or non-CDB.

However, if the database was upgraded from 11g or earlier to 12.1, the upgrade may not set COMMON properly because of bug

  25117045 - 12C UPGRADE DOES NOT SET THE COMMON-USER FLAG FOR ORACLE-SUPPLIED USERS

For example, I have a database which upgrade history as following

SQL> select version,comments from dba_registry_history where action='UPGRADE' order by action_time;

VERSION       COMMENTS
_____________ _________________________________________
10.2.0.3.0    Upgraded from 9.2.0.6.0
10.2.0.4.0    Upgraded from 10.2.0.3.0
10.2.0.5.0    Upgraded from 10.2.0.4.0
11.2.0.3.0    Upgraded from 10.2.0.5.0
12.1.0.2.0    Upgraded from 11.2.0.3.0
12.2.0.1.0    Upgraded from 12.1.0.2.0
12.2.0.1.0    Upgraded from 12.1.0.2.0
19.0.0.0.0    Upgraded from 12.2.0.1.0 to 19.7.0.0.0

The COMMON of Oracle supplied users as following

SQL> select username,common from dba_users where oracle_maintained='Y';

USERNAME      COMMON
_____________ _________
SYSTEM                    NO
SYS                       NO
SYSDG                     YES
SYSKM                     YES
SYSBACKUP                 YES
SYSRAC                    YES
DBSNMP                    NO
    ... ...
FLOWS_FILES               YES
APEX_PUBLIC_ROUTER        YES
APEX_240100               YES

It shows that, SYS is not common user, but APEX_240100 is common user. The procedure APEX_ADMIN is owned by COMMON user APEX_240100, which cannot be processed by local (non-common) user SYS. Therefore, ORDS upgrade returned the warnings.

WORKAROUND

The APEX 404 error can be fixed as a workaround by changing the pool.xml of ORDS databse pool.

Remove following line from the file

<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>

Add following ling to the file

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

Restart ORDS application.

SOLUTION

Since the root cause is the COMMON of SYS user, we can update the COMMON to YES for Oracle supplied user (including SYS, SYSTEM). Then re-run the ORDS upgrade.

To update COMMON, following the steps,

1. Stop applications which are accessing database

If this is a RAC database, shutdown all instances except one. The remaining steps will be performed on the running instance. Ensure that no application  sessions are connected. Optionally, put the system into restricted session mode so that non-DBA connections cannot be made (alter system enable restricted session).

2. Backup the database

Make a backup of the database, or create a backup of table user$ with CREATE TABLE SELECT FROM 

connect / as sysdba
create table sys.backup_of_user$
   as select * from sys.user$;
create table sys.backup_of_user$_updated
   as select * from sys.user$ where bitand(spare1, 256) = 256;

3. Update the data dictionary using following commands

connect / as sysdba
update user$
 set spare1 = spare1 - bitand(spare1, 128) + 128
 where bitand(spare1, 256) = 256;
commit;

4. Ensure that stale user$ information is removed from the cache

If possible, shutdown and restart the instance. If that is not possible, try to flush the shared pool three times with commands

alter system flush shared_pool;

If RAC, start all other instances. If the system was previously put into restricted session mode, that can now be disabled (alter system disable restricted session).

5. Verify that the users (SYS, SYSTEM, etc) are now common users

SQL> select username,common from dba_users where oracle_maintained='Y';

USERNAME      COMMON
_____________ _________
SYSTEM                    YES
SYS                       YES
SYSDG                     YES
SYSKM                     YES
SYSBACKUP                 YES
SYSRAC                    YES
DBSNMP                    YES
    ... ...
FLOWS_FILES               YES
APEX_PUBLIC_ROUTER        YES
APEX_240100               YES