Issue:
After applying Exadata or Super Cluster Bundle Patch on the RDBMS HOME of an EBS R12.2 database, the APPS.AD_ZD_ADOP Package becomes invalid and all ADOP sessions fail.
Error:
Check ADOP status:
applprod@ebsapp01:~$ adop -status
Enter the APPS password:
==============================================================
ADOP (C.Delta.7)
Session Id: 55
Command: status
Output: /u01/PROD/fs_ne/EBSapps/log/status_20161115_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)
=====================
Run ADOP Prepare Phase:
applprod@ebsapp01:~$ adop phase=prepare
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Validating credentials.
Initializing.
Run Edition context : /u01/PROD/fs1/inst/apps/PPD_ebsapp01/appl/admin/PPD_ebsapp01.xml
Patch edition context: /u01/PROD/fs2/inst/apps/PPD_ebsapp01/appl/admin/PPD_ebsapp01.xml
Patch file system free space: 83.83 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)
Cause:
The SYS.DBMS_METADATA_UTIL object needs to be recompiled first before the APPS.AD_ZD_ADOP object can become valid.
Compile the APPS.AD_ZD_ADOP Package.
Solution:
Check the status of the object APPS.AD_ZD_ADOP
SQL> select owner,object_name,object_type from dba_objects where status='INVALID' and object_name like'%ADOP%';
OWNER OBJECT_NAME OBJECT_TYPE
------ ----------- -----------
APPS AD_ZD_ADOP PACKAGE BODY
Try to compile the 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> grant execute on DBMS_METADATA_UTIL to apps;
Grant succeeded.
SQL> alter package APPS.AD_ZD_ADOP compile body;
Package body altered.
SQL> show error
No errors.
Verify the Status of APPS.AD_ZD_ADOP package is valid.
Rerun the ADOP utility.
applprod@ebsapp01:~$ adop -status
Enter the APPS password:
==============================================================
ADOP (C.Delta.7)
Session Id: 55
Command: status
Output: /u01/PROD/fs_ne/EBSapps/log/status_20161115_121228/adzdshowstatus.out
===============================================================
File System Synchronization Type: Full
adop exiting with status = 0 (Success)
applprod@ebsapp01:~$