Search This Blog

Wednesday, February 6, 2013

ORA-04068: existing state of packages () has been discarded in Oracle Advanced Benefits

Goal

How does one resolve the below errors occurring when attempting to process life events after applying an upgrade?
ORA-04068/ORA-04065/ORA-06508/ORA-06512 error on package "APPS.BEN_PTNL_LER_FOR_PER_API" after RUP5 upgrade.
When trying process life events "New Hire", "Termination", etc. - we get error which says:
ORA-04068: existing state of packages () has been discarded
ORA-04065: not executed, altered or dropped stored procedure "APPS.BEN_PTNL_LER_FOR_PER_API"
ORA-06508: PL/SQL: could not find program unit being called:"APPS.BEN_PTNL_LER_FOR_PER_API"
ORA-06512: at "APPS.BEN_ON_LINE_LF_EVT", line 2097

Fix

Issue apparently occurs where valid packages are generating "invalid package" errors.
Clear out system as much as possible, including bouncing the database, application server processes, workflow processes, concurrent managers, etc.
Shut down the application server processes, clear the Java cache, shutdown the database, shutdown the database listener, restart the database listener, and restart the database.
No errors occur after following these steps

Metalink Reference - How to Resolve Several ORA Errors Occurring After RUP5 When Attempting to Process Life Events [ID 1511181.1]

Solution:

Perform the following as SYSDBA:
1. ALTER SYSTEM set _disable_fast_validate=TRUE scope=spfile;
2. Shut down the database:
   SHUTDOWN IMMEDIATE
3. Start the database in upgrade mode:
   STARTUP UPGRADE
4. Oracle provides scripts in $ORACLE_HOME/rdbms/admin that, when run as sys, will invalidate and revalidate all PL/SQL objects so any timestamp mismatches should be resolved. The script utlirp.sql invalidates all PL/SQL based objects, recreates STANDARD and DBMS_STANDARD, invalidates all views and synonyms dependent on now invalid objects and then does some clean up. The script utlrp.sql calls UTL_RECOMP.RECOMP_PARALLEL which performs dependency based recompilation, in parallel where resources allow. Please use these two scripts:
 a) Invalidate all the objects - utlirp.sql
 b) Recompile all the objects - utlrp.sql
5. ALTER SYSTEM set _disable_fast_validate=FALSE scope=spfile;
6. Shut down the database:
   SHUTDOWN IMMEDIATE
7. Start the database:
   STARTUP
8. Retest the issue.
9. Check with dba if you have this 11.1.0.7 patch 7284151 on your instance ?
reference : note 7284151.8 Bug 7284151 - Dependency timestamp mismatch after recompiling invalid packages (Doc ID 7284151.8)



 

No comments:

Post a Comment