ORA-04065: not executed, altered or dropped stored procedure "PUBLIC.PLITBLM"

There was one more offshoot of the problem described in my previous post. After applying patches, when autoconfig was running it gave these errors:


Uploading Metadata file $AD_TOP/admin/template/adctxinf.tmp
Metadata file $AD_TOP/admin/template/adctxinf.tmp upload failed.
Check log file created by FNDCPUCF program.
ERRORCODE = 1 ERRORCODE_END
.end std out.
Oracle error -6508: ORA-04065: not executed, altered or dropped stored procedure
"PUBLIC.PLITBLM"
ORA-06508: PL/SQL: could not find program unit being called: "PUBLIC.PLITBLM"
has been detected in FND_GLOBAL.INITIALIZE.
Log filename : Lxxxxxx.log

OR jtfictx.sh started at ....

SQL*Plus: Release 8.0.6.0.0 - Production on ...

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected.
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "PUBLIC.PLITBLM"
ORA-06508: PL/SQL: could not find program unit being called: "PUBLIC.PLITBLM"
ORA-06512: at "CTXSYS.DRVUTL", line 51
ORA-06512: at "CTXSYS.CTX_DDL", line 53
ORA-06512: at line 60


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
jtfictx.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
.end std out.

.end err out.

This error is well described in metalink notes
370137.1
392470.1
469792.1

However it is Metalink Note 443706.1 which tells us that this is a bug and we need to apply a DB patch to fix it.

Cause

The issue is related to unpublished Bug 4882839.

The root-cause is a mismatch in the timestamp of depended objects.
In the error situation on PLITBLM, the following query will reported rows of objects which
have a mismatch.

SQL> select do.obj# d_obj,do.name d_name,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MM-YYYY HH24:MI:SS') p_timestamp,
to_char(po.stime ,'DD-MM-YYYY HH24:MI:SS') stime,
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X,
do.type#,po.type#
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 not match*/
order by 2,1;

Solution

Unpublished Bug 4882839 is fixed in Oracle11g and 10.2.0.4 and a one-off patches are available for 10.2.0.2 and 10.2.0.3

An upgrade is recommended, but the following procedure helped as well :

1. Download the patch for unpublished Bug 4882839
and install it according the README

2. Shutdown and restart the instance and run utlip Running 'utlip' is required for 'activating' the fix.

SQL> shutdown immediate
startup migrate



SQL> select do.obj# d_obj,do.name d_name,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MM-YYYY HH24:MI:SS') p_timestamp,
to_char(po.stime ,'DD-MM-YYYY HH24:MI:SS') stime,
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X,
do.type#,po.type#
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 not match*/
order by 2,1;

No rows is a good value.

After doing the above you need to

sqlplus /nolog
connect / as sysdba
@?/rdbms/admin/utlip.sql (This will invalidate all objects)
alter session set job_queue_processes=24 (or the no. of processors on the server)
@?/rdmbs/admin/utlrp.sql


The cause of this error is