APPLIES TO:

Oracle Service Contracts - Version 12 and later Oracle Lease and Finance Management - Version 12.0.1 and later Information in this document applies to any platform.

SYMPTOMS

After patching users are seeing "ORA-06508: PL/SQL" errors. This error can be encountered using a variety of navigations, but the "ORA-06508: PL/SQL" error is consistent. The error will have the generic form below. Error: ORA-06508: PL/SQL: could not find program unit being called in package OKX_XXXXX_PUB procedure yyyy_yyyyyy. Here are some actual specific examples of the error: ORA-06508: PL/SQL: could not find program unit being called in package OKS_DEFAULTS_PUB procedure insert_defaults. ORA-06508: PL/SQL: could not find program unit being called in Package OKS_SALES_CREDIT_PUB Procedure Insert_Sales_Credit. ORA-06508: PL/SQL: could not find program unit being called in Package OKC_CONTRACT_PUB Procedure CREATE_CONTRACT_HEADER. ORA-06508: PL/SQL: could not find program unit being called in package OKL_ACCRUAL_RULES_PUB Procedure insert_accrual_rules.

CHANGES

There has been patching on the instance. The database version is 11.X. Patching was done without setting the database parameter _disable_fast_validate to true.

CAUSE

The issue is caused by unpublished bug 7284151, which relates to a new feature introduced in 11g called fast validation. The purpose of fast validation is to speed up recompilation by attempting to detect only those objects which strictly needed to be recompiled. This issue is documented in Note 1192068.1 "Recreating An Existing Package Generates Timestamps Out Of Sync Which Causes ORA-6508". To check if you have timestamp discrepancies on your instance that are causing the error(s) run the following SQL query. alter session set nls_date_format='dd-mon-yy hh24:mi:ss'; select do.name dname, po.name pname, p_timestamp, po.stime p_stime from sys.obj$ do, sys.dependency$ d, sys.obj$ po where p_obj#=po.obj#(+) and d_obj#=do.obj# and do.status=1 /*dependent is valid*/ and po.status=1 /*parent is valid*/ and po.stime!=p_timestamp /*parent timestamp does not match*/ and do.type# not in (28,29,30) /*dependent type is not java*/ and po.type# not in (28,29,30) /*parent type is not java*/ order by 2,1; Check the output of the query for the package name found in your error. If this package is found then this is most likely the cause of the error(s). Another sign of this issue is that the output of the Diagnostic: Apps Check program shows compiled versions of packages that are lower than what is found on the server.

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. Note: One customer reported the above actions took about 8 hours. Manually recompiling the affected files shown in the output of the SQL query should work as well, but this will be a labor intensive process if the list is extensive.