ADZDEXRPT.sql Fails 'AD_ZD_PREP' Invalid or with With Error: ORA-06508: PL/SQL: could not find APPS.AD_ZD_PREPAD_ZD_PREP Due To Missing Package 'xdb_migrateschema' or PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS'

Issue:

AD_ZD_PREP Invalid while running script: $AD_TOP/sql/ADZDEXRPT.sql as part of EBS 12.2 upgrade

SQL> select owner,object_name,object_type,status from dba_objects where object_name like 'AD_ZD%' and status ='INVALID';

OWNER           OBJECT_NAME                    OBJECT_TYPE         STATUS
--------------- ------------------------------ ------------------- -------
APPS            AD_ZD_PREP                     PACKAGE BODY        INVALID

SQL> alter package AD_ZD_PREP compile body;

Warning: Package Body altered with compilation errors.

SQL> sho error
Errors for PACKAGE BODY AD_ZD_PREP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
297/6    PL/SQL: Statement ignored
297/6    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

633/4    PL/SQL: Statement ignored
633/4    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

715/6    PL/SQL: Statement ignored
715/6    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

1081/4   PL/SQL: Statement ignored
1081/17  PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared
       
Solution:

I.
Check if the latest R12.TXK.C.Delta patch applied on instance.
If yes, Please run the adgrants from the latest R12.TXK.C.Delta patch to grant privilages to APPS user
If not, Please apply the patch and check.

Recompile the package and check.

II.
For issue: Error: PL/SQL: ORA-00942: table or view does not exist
sys.xdb$moveSchemaTab;

Sol:

1. Confirm if the package 'xdb_migrateschema' exists using the following select statement:

select owner, object_name from dba_objects where object_name like upper ('xdb_migrateschema');

If it does NOT exist, then use the following scripts to create it and confirm results again using the above query:

conn / as sysdba

@?/rdbms/admin/dbmsxdbschmig.sql
@?/rdbms/admin/prvtxdbschmig.plb

2. Run adgrants.sql.
3. Recompile the package AD_ZD_PREP and ensure it compiles successfully.
4. Continue with the upgrade and confirm online patching is successfully enabled.

III.

If you see the issue with the package: XDB_MIGRATESCHEMA invalid.

Sol:

Connect database sys as sysdba user

SQL> @?/md/admin/catnomgdidcode.sql

and

SQL> @?/md/admin/catmgdidcode.sql

Run adgrants.sql

Compile package : XDB_MIGRATESCHEMA
Compile package : AD_ZD_PREP

IV.

If you see the issue with the package: SYS.DBMS_OBJECTS_APPS_UTILS not exist

SQL> select owner, object_name, object_type from dba_objects where object_name='DBMS_OBJECTS_APPS_UTILS';
no rows selected

1. Please refer to Note 1594274.1 (Oracle E-Business Suite Release @ 12.2: Consolidated List of Patches and Technology Bug Fixes) to find the corresponding patch that delivers the missing bug fixes.

2. If an overlay patch is needed for any particular patch listed, the footnote for that patch also applies to the overlay patch.

3. At a minimum ensure that the following bug fixes are not missing in your instance:

17421340
17487358
12749597
14692386

4. If the above patches are not applied, apply them in a test environment with guidance from Note 1594274.1.

5. Retest the issue.


OR

Connect DB as sys user and execute below.

cd $ORACLE_HOME/rdbms/admin/

SQL> @dbmsobj.sql

select owner, object_name, object_type from dba_objects where object_name='DBMS_OBJECTS_APPS_UTILS';

Compile Invalid packages and test.

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