Pages

Friday, December 12, 2014

Data Dictionary Curruption error in R12.2 adop

For Seeded table mismatch in any phase
==============================
Example

[apsid@host001 <RUN> ]$ adop phase=finalize

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

 Please wait. Validating credentials...


RUN file system context file: /sid/inst/fs2/inst/apps/sid_host001/appl/admin/sid_host001.xml
PATCH file system context file: /sid/inst/fs1/inst/apps/sid_host001/appl/admin/sid_host001.xml
Execute SYSTEM command : df /sid/applmgr/fs1

************* Start of  session *************
 version: 12.2.0
 started at: Wed Oct 08 2014 11:33:11

APPL_TOP is set to /sid/applmgr/fs2/EBSapps/appl
  There is already a session which is incomplete. Details are:
        Session Id            :   47
        Prepare phase status  :   COMPLETED
        Apply phase status    :   ATLEAST ONE PATCH IS ALREADY APPLIED
        Cutover  phase status :   NOT COMPLETED
        Abort phase status    :   NOT COMPLETED
        Session status        :   FAILED
  Will continue with previous session
yes
  [START 2014/10/08 11:34:32] adzdoptl.pl run
    ADOP Session ID: 47
    Phase: finalize
    Log file: /sid/applmgr/fs_ne/EBSapps/log/adop/47/adop_20141008_113227.log
    [START 2014/10/08 11:34:42] finalize phase
      [UNEXPECTED]Dictionary Corrupted:
      [START 2014/10/08 11:34:56] Data Dictionary Curruption Details
        APPS            INV_MWB_CG_TRANSFER            V_20140912_2110 APPS            INV_MWB_CG_TRANSFER            V_20131106_0451 TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00
      [END   2014/10/08 11:34:57] Data Dictionary Curruption Details
      NOTE: Please contact Oracle Support and request them to open a bug against
            Oracle Application Install(166), Component Online Patching(OP).
      [UNEXPECTED]Finalize phase completed with errors/warnings. Please check logfiles
      Log file: /sid/applmgr/fs_ne/EBSapps/log/adop/47/adop_20141008_113227.log

adop exiting with status = 1 (Fail)


D_OWNER         D_NAME                         D_EDITION                 P_OWNER         P_NAME                         P_EDITION                 REASON
--------------- ------------------------------ ------------------------- --------------- ------------------------------ ------------------------- --------------------------------------------------
APPS            INV_MWB_CG_TRANSFER            V_20140912_2110           APPS            INV_MWB_CG_TRANSFER            V_20131106_0451           TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00

Elapsed: 00:00:12.00




Solution
======

SQL> select du.name d_owner, d.name d_name, d.defining_edition d_edition,
       pu.name p_owner, p.name p_name, p.defining_edition p_edition,
  2    3        case
  4           when p.status not in (1, 2, 4) then 'P Status: ' || to_char(p.status)
  5        else 'TS mismatch: ' ||
  6             to_char(dep.p_timestamp, 'DD-MON-YY HH24:MI:SS') || ' ' ||
  7             to_char(p.stime, 'DD-MON-YY HH24:MI:SS')
  8        end reason
  9  from sys."_ACTUAL_EDITION_OBJ" d,
 10       sys.user$ du,
 11       sys.dependency$ dep,
 12       sys."_ACTUAL_EDITION_OBJ" p,
 13       sys.user$ pu
 14  where d.obj# = dep.d_obj#
 15    and d.owner# = du.user#
 16    and p.obj# = dep.p_obj#
 17    and p.owner# = pu.user#
 18    and d.status = 1                                    -- Valid dependent
  and bitand(dep.property, 1) = 1                     -- Hard dependency
 19   20    and d.subname is null                               -- !Old type version
 21    and not(p.type# = 32 and d.type# = 1)               -- Index to indextype
 22    and not(p.type# = 29 and d.type# = 5)               -- Synonym to Java
 23    and not(p.type# in(5, 13) and d.type# in (2, 55))   -- TABL/XDBS to TYPE
 24    and (p.status not in (1, 2, 4) or p.stime <> dep.p_timestamp)
 25  /

D_OWNER                        D_NAME
------------------------------ ------------------------------
D_EDITION                      P_OWNER
------------------------------ ------------------------------
P_NAME                         P_EDITION
------------------------------ ------------------------------
REASON
--------------------------------------------------------------------
APPS                           INV_MWB_CG_TRANSFER
V_20140912_2110                APPS
INV_MWB_CG_TRANSFER            V_20131106_0451
TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00

SQL> @$AD_TOP/patch/115/sql/adzddtsfix.sql
exec dbms_utility.invalidate(794147,NULL,0);
exec sys.utl_recomp.recomp_parallel


SQL> exec dbms_utility.invalidate(794147,NULL,0);

PL/SQL procedure successfully completed.

SQL> exec sys.utl_recomp.recomp_parallel

PL/SQL procedure successfully completed.

SQL> select du.name d_owner, d.name d_name, d.defining_edition d_edition,
       pu.name p_owner, p.name p_name, p.defining_edition p_edition,
  2    3        case
  4           when p.status not in (1, 2, 4) then 'P Status: ' || to_char(p.status)
  5        else 'TS mismatch: ' ||
  6             to_char(dep.p_timestamp, 'DD-MON-YY HH24:MI:SS') || ' ' ||
  7             to_char(p.stime, 'DD-MON-YY HH24:MI:SS')
      end reason
  8    9  from sys."_ACTUAL_EDITION_OBJ" d,
 10       sys.user$ du,
 11       sys.dependency$ dep,
 12       sys."_ACTUAL_EDITION_OBJ" p,
 13       sys.user$ pu
 14  where d.obj# = dep.d_obj#
  and d.owner# = du.user#
 15   16    and p.obj# = dep.p_obj#
 17    and p.owner# = pu.user#
 18    and d.status = 1                                    -- Valid dependent
 19    and bitand(dep.property, 1) = 1                     -- Hard dependency
 20    and d.subname is null                               -- !Old type version
 21    and not(p.type# = 32 and d.type# = 1)               -- Index to indextype
 22    and not(p.type# = 29 and d.type# = 5)               -- Synonym to Java
 23    and not(p.type# in(5, 13) and d.type# in (2, 55))   -- TABL/XDBS to TYPE
 24    and (p.status not in (1, 2, 4) or p.stime <> dep.p_timestamp)
 25  /

no rows selected


For Custom Objects
==================
Inform to custom schema owner to check and drop those objects