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

ORA-1166 file number 101 is larger than MAXDATAFILES (100)

I am in the process of upgrading EBS R12 DB from 11.2.0.3 to 11.2.0.4 using DBUA. The end of the upgrade, DBUA fails with following error message.

During the DBUA, I have selected option 'move the database files location'.

ORA-1503: CREATE CONTROLFILE failed
ORA-1166: file number 101 is larger than MAXDATAFILES (100)
ORA-1110: data file 101: '/u01/EBS/data/opmor01.dbf'

So then I have verified oracle documentation and support docs. There is a bug in DBUA, the problem is Database Upgrade Assistant (DBUA) tries to re-create the control file with MAXDATAFILES set to 100 instead of using the value from the old control file.

To fix this issue one of the following workarounds should be used:

1) Upgrade the database using DBUA (without moving the database files option) and If you are using ASM, after completed the upgrade use RMAN to move the database files to ASM.
2) Manually upgrade the database and manually re-create the control file (setting MAXDATAFILES to a higher value).

Thats it..Hope this will helps you..:)

Thanks,
Chowdari.

ORA-02030 How to grant on v$ views

Today I have faced an issue while giving GRANT permission on a v$ view. Received 'ORA-02030: can only select from fixed tables/views' error.

SQL> grant select on v$instance to apps;
grant select on v$instance to apps
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL>

Solution:

Here the problem is caused because of trying to give select privilege on a synonym, Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and we can't give any privilege on a synonym.

If you want to give permission to a V$ views, Follow below way,,

SQL> grant select on v_$instance to apps;

Grant succeeded.

SQL>

Thats it,, hope this will help you. :)

Regards,
Chowdari

XML Clob data type 'Value Error' while creating Materialized views

Today I have faced an issue like after creating materialized view we got <Value Error> for XML Clob data types.

I tried in many ways finally I get to know that its a bug in 11.2.0.3. while creating XML clob data type column in MView use the "xmltype.getclobval(COLUMN)" syntax.

Then I have dropped and recreted the materialized view with xmltype.getclobval option.

Followed below process:

DROP MATERIALIZED VIEW EMP.CHECKLISTMASTER;
CREATE MATERIALIZED VIEW EMP.CHECKLISTMASTER
REFRESH force on demand
AS
SELECT labsubdepartmentid,
processid,
checklistid,
xmltype.getclobval(checklistdetails),
status,
createddate,
createdby,
reasonforupdate,
updateddate,
updatedby,
showinreport,
checklistcode,
checklistname,
approvedby,
approved,
approvalremarks FROM EMP.CHECKLISTMASTER@DBLINK where rownum > 0;

Hope this will help you... :)

Best Regards,