Error Processing Request. - Oracle APEX Post upgrade (Invalid WWV_FLOW_SESSION_RAS)

There would be occasions where you would upgrade your oracle database which is hosting your APEX and post upgrade you get error while opening APEX URL "Error Processing Request Contact your application administrator"

This looks very strange as you have just upgraded only database. Database version and APEX version are compatible. For Argument lets say both are on 19c. 

Now since UI doesn't give detailed information about the error you would need to get to backend oracle database and query view apex_debug_messages.

    select * from apex_debug_messages;

Looking at the results of this view you would get the clue of the error in this case it was:

    ORA-04063 package body APEX_190100.WWV_FLOW_SESSION_RAS has error

This means this package body has problem and looking at the status of this body it's found to be invalid and when you try to compile it get following error

    alter package APEX_050100.WWV_FLOW_SESSION_RAS compile body;

    Warning: Package Body altered with compilation errors.

    show errors

    Errors for PACKAGE BODY APEX_050100.WWV_FLOW_SESSION_RAS:

    LINE/COL ERROR

    -------- -----------------------------------------------------------------

    242/9 PL/SQL: SQL Statement ignored

    244/18 PL/SQL: ORA-00942: table or view does not exist


The problem is with Unpublished Bug 29617728 - DBUA: MISSING GRANTS TO APEX SYS.DBA_XS_DYNAMIC_ROLES/SYS.DBA_TAB_IDENTITY_COLS FOR APEX AFTER 18C UPGD: has been filed.

Workaround for this problem is to grant below missing privileges for the following objects:


    grant select on SYS.DBA_XS_DYNAMIC_ROLES to APEX_050100;

    grant select on SYS.DBA_TAB_IDENTITY_COLS to APEX_050100;


 After granting select on SYS.DBA_XS_DYNAMIC_ROLES and SYS.DBA_TAB_IDENTITY_COLS it was possible to compile the APEX_050100.WWV_FLOW_SESSION_RAS / APEX_050100.WWV_FLOW_WIZARD_API successfully.



Comments

  1. Pablo_Mendoza112 June 2021 at 09:05

    Hello Good morning, I had the same problem and my situation was that I have a link that connects with an oracle database different from the one that oracle apex is hosted on, this link with a public ip so the problem was communication with the checkpoint and the database, change the link to the local ip and the problem disappeared

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more