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.
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.
