Oracle EBS 12.2 - ADOP ad_zd_prep.create_patch_service exceptions

Oracle EBS 12.2 - ADOP ad_zd_prep.create_patch_service exceptions

Please note , if you get ADOP issues on PROD please read the logs and understand the problem before executing any commands.



There are cases where you might have to create a patch service manually. So usually after cloning or fresh install , add node , mass patching , upgrade this crazy error will pop up.

SQL> exec ad_zd_prep.create_patch_service;
BEGIN ad_zd_prep.create_patch_service; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.AD_ZD_PREP", line 342
ORA-06512: at "APPS.AD_ZD_PREP", line 378
ORA-06512: at line 1

Check the state of the package

SQL> select owner, object_name,status,object_type from dba_objects where object_name like upper ('AD_ZD_PREP');

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


SQL> alter package apps.ad_zd_prep compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY APPS.AD_ZD_PREP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
463/3    PL/SQL: SQL Statement ignored
463/19   PL/SQL: ORA-00942: table or view does not exist
467/5    PL/SQL: SQL Statement ignored
467/21   PL/SQL: ORA-00942: table or view does not exist
501/5    PL/SQL: SQL Statement ignored
501/21   PL/SQL: ORA-00942: table or view does not exist


Fix :  follow this note
12.2 E-Business Suite DBA Upgrade Script ADZDEXRPT.sql Fails On Invalid Object 'AD_ZD_PREP' Or Patch 13543062 Fails With Error: ORA-06508: PL/SQL: could not find APPS.AD_ZD_PREPAD_ZD_PREP Due To Missing Package 'xdb_migrateschema' (Doc ID 2066607.1)

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.

Move the adgrants.sql from MT to DB before running it.



exec ad_zd_prep.create_patch_service  -- this should succeed.