Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, March 10, 2014

R12.2 Dictionary Corrupted error during adop phase=prepare

Do not follow the document 1576086.1.  I followed it and increased my invalid counts from 1000 to 100,000 and wasted 10 hours to get back to where I started.  Instead follow these instructions:


Reference : Ora-04023 Reported while Accessing Valid Objects (Doc ID 1610514.1)
Dictionary Corrupted:
Details are as follows:
OWBSYS          HAB_RT_EXEC_PROC_RUN_METRICS                   OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          WB_RTI_OBJECT_CATALOG                          OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          UAB_RT_EXEC_PROCESS                            OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          UAB_RT_EXEC_PROCESS_RUNS                       OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          UAB_RT_EXEC_PROC_RUN_COUNTS                    OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          UAB_RT_EXEC_PROC_RUN_METRICS                   OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

PUBLIC          WB_OLAP_AW_PRECOMPUTE                          OWBSYS          WB_OLAP_AW_PRECOMPUTE                          TS mismatch: 23-SEP-10 05:17:34 26-NOV-12 18:48:25

OWBSYS          ALL_RT_INSTALLATIONS                           OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          RAB_RT_INSTALLATIONS                           OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          HAB_RT_EXEC_PROCESS                            OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          HAB_RT_EXEC_PROCESS_RUNS                       OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19

OWBSYS          HAB_RT_EXEC_PROC_RUN_COUNTS                    OWBSYS          WB_RT_VERSION                                  TS mismatch: 16-SEP-10 09:13:44 26-NOV-12 18:48:19


please contact Oracle Support and request them to open a bug against Oracle Application Install(166), Component Online Patching(OP)
        [ERROR]     Prepare phase completed with errors/warnings. Please check logfiles
        Log file: /erpodpa1/erpapp/fs_ne/EBSapps/log/adop/3/adop_20140306_005850.log

select owner,object_type,object_name
from dba_objects
where object_name in
(
'HAB_RT_EXEC_PROC_RUN_METRICS',
'WB_RTI_OBJECT_CATALOG',
'UAB_RT_EXEC_PROCESS',
'UAB_RT_EXEC_PROCESS_RUNS',
'UAB_RT_EXEC_PROC_RUN_COUNTS',
'UAB_RT_EXEC_PROC_RUN_METRICS',
'WB_OLAP_AW_PRECOMPUTE',
'ALL_RT_INSTALLATIONS',
'RAB_RT_INSTALLATIONS',
'HAB_RT_EXEC_PROCESS',
'HAB_RT_EXEC_PROCESS_RUNS',
'HAB_RT_EXEC_PROC_RUN_COUNTS'
);


Select against a view  fails with following error.

SQL> Select * from . ;

ORA-04023: could not be validated or authorized

This is issue is due to a timestamp inconsistency in the data dictionary. This timestamp inconsistency will lead to the problem when we  populate the relevant cached objects and later try to select from the affected views.

Run following script to find out the objects which are having timestamp discrepencies.

Connect as / as sysdba

SQL> @?/rdbms/admin/utldtchk.sql

This script is available from 11g onwards.

Recompile all the objects manually returned by this script.
Conn owbsys/owbsys
alter view ALL_RT_INSTALLATIONS compile;
alter view RAB_RT_INSTALLATIONS compile;
alter view HAB_RT_EXEC_PROCESS compile;
alter view HAB_RT_EXEC_PROCESS_RUNS compile;
alter view HAB_RT_EXEC_PROC_RUN_COUNTS compile;
alter view HAB_RT_EXEC_PROC_RUN_METRICS compile;
alter view UAB_RT_EXEC_PROCESS compile;
alter view UAB_RT_EXEC_PROCESS_RUNS compile;
alter view UAB_RT_EXEC_PROC_RUN_COUNTS compile;
alter view UAB_RT_EXEC_PROC_RUN_METRICS compile;
alter function WB_OLAP_AW_PRECOMPUTE compile;
drop public synonym WB_OLAP_AW_PRECOMPUTE;
CREATE PUBLIC SYNONYM WB_OLAP_AW_PRECOMPUTE
FOR OWBSYS.WB_OLAP_AW_PRECOMPUTE;

It will remove the timestamp inconsistency in data dictionary and resolve this issue.

Verify by re-running the script, it should return zero rows.

SQL> @?/rdbms/admin/utldtchk.sql

If you still see occurances, bounce the database and rerun the above script.

2 comments:

PRAD said...

IF YOU ARE UNABLE TO COMPILE THESE OBJECTS. try flushing the shared_pool and try compiling. Remember to flush the shared_pool each view/package.

djay said...

Hi Vikram,

Im applying patch first time on my R12.2.2 instance and while adop prepare phase facing dictionary corrupted error for for following objects. Tried the above action plan with no success. Could you please help me out . . .

Dictionary Corrupted:
Details are as follows:
APPS CS_SR_LINK_VALID_OBJ_PKG ORA$BASE APPS CS_SR_LINK_VALID_OBJ_PKG ORA$BASE TS mismatch: 04-SEP-14 13:38:21 31-DEC-10 14:04:15

APPS IGI_SLS_14_TRG ORA$BASE AR RA_INTERFACE_LINES_ALL# ORA$BASE TS mismatch: 12-MAR-14 10:45:16 01-DEC-12 12:37:35

APPS IGI_SLS_13_TRG ORA$BASE AR RA_INTERFACE_DISTRIBUTIONS_AL# ORA$BASE TS mismatch: 12-MAR-14 10:45:55 01-DEC-12 12:36:48