ORA-04063 APPS.AD_ZD_ADOP Package Invalid After Applied Database Bundle Patch

ADOP prepare phase was failing after applied the latest database bundle Patch on the RDBMS ORACLE_HOME of an EBS R12.2 database. The APPS.AD_ZD_ADOP Package becomes invalid and due to that all ADOP sessions got failed. See below error details.

[oracle@ebshyd01 ]$ echo $FILE_EDITION
run

[oracle@ebshyd01 ]$ adop phase=prepare

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

Validating credentials.

Initializing.
Run Edition context : /u01/EBSPRDB/fs1/inst/apps/EBSPRDB_ebshyd01/appl/admin/EBSPRDB_ebshyd01.xml
Patch edition context: /u01/EBSPRDB/fs2/inst/apps/EBSPRDB_ebshyd01/appl/admin/EBSPRDB_ebshyd01.xml
Patch file system free space: 125.88 GB

Validating system setup.

[ERROR] Failed to execute SQL statement:
select AD_ZD_ADOP.GET_INVALID_NODES() from dual
[ERROR] Error Message:
[ERROR] ORA-04063: package body "APPS.AD_ZD_ADOP" has errors (DBD ERROR: OCIStmtExecute)
[UNEXPECTED]Error determining whether this is a multi-node instance

[STATEMENT] Please run adopscanlog utility, using the command

"adopscanlog -latest=yes"

to get the list of the log files along with snippet of the error message corresponding to each log file.

adop exiting with status = 2 (Fail)

[oracle@ebshyd01 ]$

And check ADOP status:

[oracle@ebshyd01 ]$ adop -status

Enter the APPS password:

==============================================================
ADOP (C.Delta.7)
Session Id: 45
Command: status
Output: /u01/EBSPRDB/fs_ne/EBSapps/log/status_20171230_112514/adzdshowstatus.out
===================================wo============================

File System Synchronization Type: Full
declare
*
ERROR at line 1:
ORA-04063: package body "APPS.AD_ZD_ADOP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APPS.AD_ZD_ADOP"
ORA-06512: at line 6

[STATEMENT] Please run adopscanlog utility, using the command

"adopscanlog -latest=yes"

to get the list of the log files along with snippet of the error message corresponding to each log file.

adop exiting with status = 1 (Fail)
[oracle@ebshyd01 ]$           

Solution:

First check the status of APPS.AD_ZD_ADOP object:

SQL> select owner, object_name,object_type, status from dba_objects where object_name='AD_ZD_ADOP';

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

SQL> 

See the object APPS.AD_ZD_ADOP package body is INVALID state, so try to compile the APPS.AD_ZD_ADOP package

SQL> alter package AD_ZD_ADOP compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY AD_ZD_ADOP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2686/3 PL/SQL: Statement ignored
2686/7 PLS-00201: identifier 'SYS.DBMS_METADATA_UTIL' must be declared
==================

SQL>          

The above error indicates that, the SYS.DBMS_METADATA_UTIL object needs to be recompiled first before the APPS.AD_ZD_ADOP object can become valid. See the above error looks some privileges issue.

This package became invalid due to incorrect execution of adgrants.sql script. We should always execute the correct version of the adgrants.sql. We should ensure we have applied "adgrants.sql" script from the the applied AD code level.

And here I am just giving the execute privilege to APPS user on SYS.DBMS_METADATA_UTIL. Then compile the APPS.AD_ZD_ADOP Package.

SQL> conn / as sysdba
Connected.

SQL> grant execute on DBMS_METADATA_UTIL to apps;

Grant succeeded.

SQL>
SQL> conn apps
Enter password:
Connected.

SQL> 
SQL> alter package AD_ZD_ADOP compile body;

Package body altered.

SQL> 

SQL> select owner, object_name,object_type, status from dba_objects where object_name='AD_ZD_ADOP';

OWNER       OBJECT_NAME           OBJECT_TYPE             STATUS 
--------------------------------------------------------------------------------
APPS        AD_ZD_ADOP            PACKAGE                 VALID                                                               
APPS        AD_ZD_ADOP            PACKAGE BODY            VALID

SQL>

Rerun the ADOP utilities:

[oracle@ebshyd01 ]$ adop -status

Enter the APPS password:

==============================================================
ADOP (C.Delta.7)
Session Id: 45
Command: status
Output: /u01/EBSPRDB/fs_ne/EBSapps/log/status_20171230_121228/adzdshowstatus.out
===============================================================

File System Synchronization Type: Full

adop exiting with status = 0 (Success)

[oracle@ebshyd01 ]$   

So now run "adop phase=prepare", ADOP cycle will complete without any further issues.

Please give your comment, If this article helps you..

Thanks,
Chowdari