EBS 12.2 -- ORA-06508 + ORA-04065 for APPS.AD_ZD / 6508 trace name errorstack level 3 and all that

Yesterday, I worked on a weird case... The case was on a production EBS 12.2 instance. The problem appeared after recreating the APPS.AD_ZD package body along with its spec.. 

Actually, this kind of a compilation should not be done manually (and suddenly:), but it seems some "consultant" (yes in quotation marks! :) recommended such an action to fix some ADOP problems and then the environment ended up with lots of ORA-06508 errors, thus lots of OAF and Forms pages could not function. 

Lots of ORA-06508 blocking lots of EBS operations (and as you may guess)-> preventing the Business.. 

So it is needless to say it was a Severity 1... Well.. At the end of the story, I have been contacted to fix the problem ASAP.

You know, I like to work on the details/on these weird cases, because those tiny little gaps/problems/errors/unexpected things that we see in those details/in such cases, have the potential to reveal lots information..

Actually, I was already familiar with these kinds of errors and I even wrote some blog post about them .. (https://ermanarslan.blogspot.com/2015/03/ebs-r12-app-fnd-01926-ora-06508-ora.html) 

But! this one was different. 

Following is an example of an ORA-06508 error encountered on a standard EBS form;

Normally, firstly, I suggested the following action plan;

1)Shutdown apps tier services on all apps nodes. Ensure there are no apps-related OS processes left running there.

2)Run utlrp.sql - sqlplus “/as sysdba” @$ORACLE_HOME/rdbms/admin/urltp.sql

3)Run apps schema compile using adadmin.

4)Run -> Alter package AD_ZD compile

5)Alter package AD_ZD compile body

6)Repeat step 2&3

7)Restart the EBS database

8)Start Apps Tier and retest

Note that, there were no invalid objects in the database and AD_ZD was already valid and seemed healthy..

Well, this action plan didn't help and then I saw a grant on the AD_ZD table.. An exectute grant was given manually.. Remember we have EBR (Edition Based Redefinition) in EBS 12.2, so we need to pay attention to that..  So I told the admin to revoke that grant and use AD_ZD.grant_privs to grant it back. (of course, I told -> take those necessary compilation actions in case you see an increase in the count of invalid objects)

Example: exec AD_ZD.GRANT_PRIVS ('EXECUTE','AD_ZD','OAM_MONITOR_ROLE');

This was a good move but it didn't solve the issue either. -- Still, alesson learned there..(for the admin at least)

Then I decided the check the call stack.. So I made the admin set the following event in the database and reproduce the error; 

ALTER SYSTEM SET EVENTS '6508 trace name errorstack level 3’;

After setting the event and reproducing the error, we checked the event trace and the call stack was there;

----- Error Stack Dump -----

ORA-04065: package body "APPS.AD_ZD" 

ORA-06508: PL/SQL: APPS.AD_ZD"

----- SQL Statement (None) -----

Current SQL information unavailable - no cursor.

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

object line object

handle number name

327fccceb8 1074 package body APPS.WF_CORE

253df749c0 489 package body APPS.WF_EVENT

253df749c0 2141 package body APPS.WF_EVENT

253df749c0 3021 package body APPS.WF_EVENT

319d17c378 190 package body APPS.JTF_TASK_WF_EVENTS_PVT

30ccddb468 2137 package body APPS.JTF_TASK_ASSIGNMENTS_PVT

30ccddb468 1213 package body APPS.JTF_TASK_ASSIGNMENTS_PVT

31e85eb340 4080 package body APPS.JTF_TASKS_PVT

2efd1fc9d0 4477 package body APPS.JTF_TASKS_PUB

It was cearly seen that the code in 1074th line of WF_CORE package was failing..
We had a call to ad_zd.get_edition() function there, in that line.. 

However; AD_ZD package was already valid and when we tried to execute the same function via sqlplus or/ Toad (using an APPS session), we could succeed without any problem..

This was one of my AHA moments actually.. Just after seeing the contents of the trace and ensuring the APPZ_ZD.GET_EDITION can be executed by an Apps session, I started to think that there was a kind of a timestamp discrepancy exist for the objects that we see in the call stack of the event trace.

At the end of the story, I suggested compiling WF_CORE,WF_EVENT and all the other packages listed above in the call stack.. Of course these kinds of compilations would make some dependent objects invalid and that's why I also suggested to run utlrp.sql and apps schema compile as the post actions..The compilations saved the day, resolved the timestamp discrepancies and the issue was solved! 

It was a risky move (compiling a bunch of standard packages), but it was necessary in this case..

a reminder -> Don't forget to disable the event '6508 trace name errorstack level 3' after reproducing the error and catching the call stack :) Those events are just for diagnosing purposes, so they should not be  left enabled.