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

Overview of ADOP Phase=Prepare

Oracle E-Business Suite Release 12.2 introduces Online Patching (OP), a new feature that greatly reduces the downtime that was needed in previous releases for application of Release Update Packs (RUPs), Critical Patch Updates (CPUs), and other patches and bug fixes of various kinds.

In Release 12.2, all patching operations are carried out while the applications are in use and users are online. Patching is performed using the new adop (AD Online Patching) utility.

ADOP – Regular Patching Cycle:

The online patching cycle consists of a number of phases:

 1) Prepare - Creates a new patch edition in the database.

 2) Apply - Executes patch drivers to update patch edition.

 3) Finalize - This phase is used to perform the final operations that can be executed while the application is online.
    a) Compiles invalid objects.
    b) Generates derived objects.

 4) Cutover - Configures patch edition of database to be the new run edition. Restarts application tier services.

 5) Cleanup - Delete obsolete code and seed data to recover space.

Overview of adop phase=prepare:

During the prepare phase, adop performs the following steps:

1) Checks whether to perform a cleanup, which will be needed if the user failed to invoke cleanup after the cutover phase of a previous online patching cycle.

2) Validates system configuration to ensure that the system is ready to start an online patching cycle.

3) Performing Sanity Checks to see if the database is prepared for online patching:

   a) Checks if the database user is edition-enabled. If not, adop immediately exits with an error.

   b) Checks to see if the patch service (ebs_patch) has been created. ADOP requires that a special database service exists for the purpose of connecting to the patch edition.
   
   c) Checks to see if logon trigger exists and is enabled. If the logon trigger is missing or the patch service (ebs_patch) has not been created, adop will automatically try to fix the issue so that it can proceed. If it cannot fix the issue, it will exit with an error message.

   d) Checks the integrity of the database data dictionary. If any corruption is found, adop exits with an error.
 
   e) Checks to see if enough space is available in the database (SYSTEM tablespace should have minimum of 25 GB of free space and APPS_TS_SEED tablespace should have minimum of 5 GB of free space)
 
   f) Checks that the E-Business Suite Technology Codelevel Checker (ETCC) has has been run, to verify that all required patches have been applied to the database.
 
4) Online Patching tool would submit (if its not already submitted) a concurrent request (ADZDPATCH), which would make the incompatible concurrent programs not to run, instead, they would be enqueued and would be picked-up after the patching cycle is complete. If any concurrent program is already running, waits until it finishes.

 5) The next stage depends on whether the concurrent managers are running:

       i) If the concurrent managers are all down, the prepare phase continues, with ADZDPATCH entering a status of pending (with the highest priority) until the managers are started.

      ii) If the concurrent managers are partially up, but there is no manager defined that can run ADZDPATCH, then the prepare phase will exit with an error.

     iii) If the concurrent managers are up, and there is one defined that can run ADZDPATCH, processing will loop until ADZDPATCH changes status from pending to running. The prepare phase then continues.

   Note: ADZDPATCH is cancelled when the cutover phase is complete.

  6) Invokes the TXK script $AD_TOP/patch/115/bin/txkADOPPreparePhaseSynchronize.pl to synchronize the patches which have been applied to the run APPL_TOP, but not the patch APPL_TOP. The script depends on the adop repository for patches that have been applied on the run APPL_TOP but not the patch APPL_TOP.

  7) Checks the database for the existence of a patch edition, and creates one if it does not find one.
 
  8) Calls the $AD_TOP/patch/115/bin/txkADOPPreparePhaseSanityCheck.pl script again to confirm that the database connection to the patch edition is working.
 
  9) If any of the above checks fail, adop will exit with an error.
 
10) The patch file system APPL_TOP is synchronized with the run file system APPL_TOP
 
      i) The APPLY phase of online patching tool would maintain the patches which are applied to the RUN environment (in previous patching cycle) in Online Patching tool repository.

     ii) The synchronization process would pickup these patches for synchronization of Patch file system. The patches are applied to perform only file system actions (copy or generate actions).

11) If any patch is applied previously using adop, then during prepare phase, it would be applied with nodatabaseportion on patch file system. Also, patch_file_stystem_base column would be updated in ad_adop_session_patches.

12) If patch is applied with nocopyportion,nodatabaseportion, then the value of patch_file_system_base would be ‘skippatch’ and adop will not do any synchronization as there’s really no need.

Thats it. Hope this will help you. :)

Thanks,
Chowdari