ORA-04063: Package Body "APPS.AD_ZD_ADOP" Has Errors when running adop

While running adop prepare,  the error is encountered on APPS.AD_ZD_ADOP . 

When checking further the package body is in invalid state.

Error:

When we try to compile manually, we observe below error.

SQL> alter package APPS.AD_ZD_ADOP compile body;
Warning: Package Body altered with compilation errors.

SQL> show err
Errors for PACKAGE BODY APPS.AD_ZD_ADOP:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2686/3 PL/SQL: Statement ignored
2686/7 PLS-00201: identifier 'SYS.DBMS_METADATA_UTIL' must be declared

Solution:

1. Connect as sysdba and 

   grant execute on DBMS_METADATA_UTIL to apps;
2. alter package APPS.AD_ZD_ADOP compile body;

The package should be compiled normally now.

ORA-29548: Java system class reported

 After applying PSU to 12.1.0.2.0, the application gets following error

[Error] Execution (1: 1): ORA-29548: Java system class reported: release of classes.bin in the database does not match that of the oracle executable
ORA-06512: at “MDSYS.SDO_JAVA_STP”, line 370
ORA-06512: at “MDSYS.SDO_UTIL”, line 3188
ORA-06512: at “MDSYS.SDO_UTIL”, line 3211

When I checked the alert log file, it puts following

joxcsys: release mismatch 12.1.0.2.160719 1.6 in database (classes.bin) vs 12.1.0.2.0 1.7 in executable

I checked any INVALID objects. But everything was OK. I did a small test about Java.

SQL> select dbms_java.get_jdk_version() from dual
*
ERROR at line 1:
ORA-29548: Java system class reported: release of classes.bin in the database
does not match that of the oracle executable

There was a problem with my java. Don’t worry about that there is a solution. Please run below script as sysdba.

SQL> @?/javavm/install/update_javavm_db.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> alter session set “_ORACLE_SCRIPT”=true;

Session altered.

SQL>
SQL> — If Java is installed, do CJS.
SQL>
SQL> — If CJS can deal with the SROs inconsistent with the new JDK,
SQL> — the drop_sros() call here can be removed.
SQL> call initjvmaux.drop_sros();

Call completed.

SQL>
SQL> create or replace java system;
2 /

Java created.

SQL>
SQL> update dependency$
2 set p_timestamp=(select stime from obj$ where obj#=p_obj#)
3 where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and
4 (select type# from obj$ where obj#=p_obj#)=29 and
5 (select owner# from obj$ where obj#=p_obj#)=0;

0 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter session set “_ORACLE_SCRIPT”=false;

Session altered.

SQL>

Now, let’s do a test again.

SQL> select dbms_java.get_jdk_version() from dual;

DBMS_JAVA.GET_JDK_VERSION()
——————————————————————————–
1.7.0_51

1 row selected.

SQL> select dbms_java.longname(‘TEST’) from dual
2 ;

DBMS_JAVA.LONGNAME(‘TEST’)
——————————————————————————–
TEST

1 row selected.

SQL>    

How to find the error when nothing is logged in the logs in R12.2

 "You have encountered an unexpected error. Please contact the System Administrator for assistance"

We can trace the error from the database as well. Please do the following to figure out the cause from tables.  

A. 

To Enable FND debug: 

Navigate Responsibility: System Administrator > Profile => System 

a. Find the FND profile options for User who is submitting the process to debug. 

User: User submitting the process 

Profile: FND:%Debug% 

b. Set the following Profile values at the user level 

FND: Debug Log Enabled :Yes 

FND: Debug Log Filename : NULL 

FND: Debug Log Level : STATEMENT 

FND: Debug Log Module : % 

c. Save. 

B. 

Get the current log sequence in the FND table. 

select max(log_sequence) from fnd_log_messages ; -- start 

C. 

Reproduce the issue 

D. Get the sequence in the FND log 

select max(log_sequence) from fnd_log_messages ; -- end 

E. Spool the output of the following query to an excel file which gives FND debug log: 

Run this script and replace start and end with the sequences you obtained from steps B and D. 

select module, message_text 

from fnd_log_messages 

where log_sequence between <start> and <end>;

ORA-01422: exact fetch returns more than requested number of rows

Error Message:

 *******FATAL ERROR*******

AutoConfig could not successfully execute the following scripts:

Directory: <EBS_BASE>/fs#/FMW_Home/webtier/perl/bin/perl -I <EBS_BASE>/fs#/FMW_Home/webtier/perl/lib/5.10.0 -I <EBS_BASE>/fs#/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I <EBS_BASE>/fs#/EBSapps/appl/au/12.0.0/perl -I <EBS_BASE>/fs#/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /<EBS_BASE>/fs#/inst/apps/<SID_hostname>/admin/install

txkGenADOPWrapper.pl INSTE8_APPLY 1

SQL> SQL> Connected.
SQL> SELECT ad_zd_adop.get_node_type('<hostname>') FROM DUAL
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.AD_ZD_ADOP", line 3049

Resolution:

First, check the following query returns just one row. If it will return more than one row, then you are hitting the issue described in this note.

 

 

 

 COL node_name FORMAT A20

    COL note_type FORMAT A10   

 

    SELECT avn.node_name,

    DECODE(EXTRACTVALUE(XMLType(focf.text),'//APPL_TOP_NAME'),

    admin_node.appl_top, DECODE(avn.node_name,admin_node.node_name, 1,3),

    DECODE(ROW_NUMBER() OVER (PARTITION BY EXTRACTVALUE(XMLType(focf.text),'//APPL_TOP_NAME')

    ORDER BY avn.node_name),

    1,2,4)) node_type

    FROM

    fnd_oam_context_files focf,

    adop_valid_nodes avn,

    (SELECT node_name,

    EXTRACTVALUE(XMLType(text),'//APPL_TOP_NAME') appl_top

    FROM fnd_oam_context_files

    WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')

    AND CTX_TYPE='A'

    AND (status IS NULL or UPPER(status) IN ('S','F'))

    AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'run'

    AND EXTRACTVALUE(XMLType(text),'//oa_service_group_status[@oa_var=''s_web_admin_status'']')='enabled'

    AND EXTRACTVALUE(XMLType(text),'//oa_service_list/oa_service[@type=''admin_server'']/oa_service_status')='enabled') admin_node

    WHERE focf.name not IN ('TEMPLATE','METADATA','config.txt')

    AND focf.CTX_TYPE='A'

    AND (focf.status is null

    or upper(focf.status) IN ('S','F'))

    AND EXTRACTVALUE(XMLType(focf.text),'//file_edition_type') = 'run'

    AND upper(focf.node_name)=upper(avn.node_name);

 

 

 

 

 

 

 

 1. A full backup should be taken before any testing begins.

 

 2. Stop Application services

 

 3. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables, and if on AD/TXK 8 or higher; ad_nodes_config_status in the EBS env nodes:

 

 Linux#> sqlplus applsys/<pwd>

 

      SQLPlus#> create table fnd_oam_context_files_bkp as select * from fnd_oam_context_files;

 

      SQLPlus#> create table fnd_nodes_bk as select * from fnd_nodes;

 

        SQLPlus#> create table adop_valid_nodes_bk as select * from adop_valid_nodes;

 

      If on AD/TXK 8 or higher:

 

      SQLPlus#> create table ad_nodes_config_status_bk as select * from ad_nodes_config_status;

               

               

 4. Truncate the following tables (Continue from step 3):

 

 SQLPlus#> truncate table fnd_oam_context_files;

 

      SQLPlus#> truncate table fnd_nodes;

 

      SQLPlus#> truncate table adop_valid_nodes;

 

      If on AD/TXK 8 or higher:

 

      SQLPlus#> truncate table ad_nodes_config_status;

               

               

5.  Run AutoConfig on the DB tier:

 

Source the <RDBMS_ORACLE_HOME> home.

 

Linux#> cd <RDBMS_ORACLE_HOME>/appsutil/scripts/<SID>_<HOSTNAME>/

 

Linux#> ./adautocfg.sh

 

              ... Confirm Autoconfig completes successfully.

 

              ... If RAC, Repeat step 3 on all RAC nodes.

 

 

 

6.  Run Autoconfig on the run file system.

 

Linux#> source <EBS_BASE>/EBSapps.env run

 

Linux#> cd $ADMIN_SCRIPTS_HOME

 

Linux#> ./adautocfg.sh

 

      ... Confirm Autoconfig completes successfully.

 

      ... If more than one EBS node, repeat step 4 on all EBS nodes.

 

 

 

7. Run Autoconfig on patch file system

 

NOTE:

 

Before running Autoconfig on the patch file system the ebs_login trigger MUST be disabled

... After the successful completion of Autoconfig the ebs_login trigger MUST be re-enabled.

 

 

 

a. Disable the ebs_login trigger using the following SQL.

 

 

sqlplus system/xxxxxx

 

 

SQL> alter trigger ebs_logon disable;

 

 

b. At this time Run autoconfig with the patch env sourced .      

 

Linux#> source <EBS_BASE>/EBSapps.env patch

 

Linux#> cd $ADMIN_SCRIPTS_HOME

 

Linux#> ./adautocfg.sh

 

... Confirm Autoconfig completes successfully

 

... If more than one EBS node, repeat step 5.b on all EBS nodes.

 

 

c. Enable the ebs_login trigger using the following SQL.

 

Linux#> sqlplus system/<pwd>

 

SQLPlus#> alter trigger ebs_logon enable;

 

 

After Autoconfig has been run successfully on all nodes, run the following two (2) queries in order to verify the tables have been correctly populated:

 

 

 

 

 

        set pagesize 200

 

        set linesize 200

 

        col node_name format a15

 

        col server_id format a8

 

        col server_address format a15

 

        col platform_code format a4

 

        col webhost format a25

 

        col domain format a20

 

        col virtual_ip format a18

 

 

 

        select node_id, platform_code, support_db D, support_cp C, support_admin A, support_forms F, support_web W, node_name, server_id,

 

               server_address, domain, webhost, virtual_ip, status

 

          from fnd_nodes order by node_name;

 

 

                              

 

              set pagesize 200

 

        set linesize 200

 

        col NAME format A20

 

        col VERSION format A22

 

        col PATH format A86

 

        col STATUS format A10

 

 

 

        select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;

How to run autoconfig on PATCH filesystem.

 Running autoconfig on PATCH filesystem

NOTE:

Before running Autoconfig on the patch file system the ebs_login trigger MUST be disabled
... After the successful completion of Autoconfig the ebs_login trigger MUST be re-enabled.

a. Disable the ebs_login trigger using the following SQL.

sqlplus system/xxxxxx

SQL> alter trigger ebs_logon disable;

b. At this time Run autoconfig with the patch env sourced .       

Linux#> source <EBS_BASE>/EBSapps.env patch

Linux#> cd $ADMIN_SCRIPTS_HOME

Linux#> ./adautocfg.sh

... Confirm Autoconfig completes successfully

... If more than one EBS node, repeat step 5.b on all EBS nodes.

c. Enable the ebs_login trigger using the following SQL.

Linux#> sqlplus system/<pwd>

SQLPlus#> alter trigger ebs_logon enable;

After Autoconfig has been run successfully on all nodes, verify the tables have been correctly populated

  After Autoconfig has been run successfully on all nodes, run the following two (2) queries in order to verify the tables have been correctly populated:

 

        set pagesize 200

        set linesize 200

        col node_name format a15

        col server_id format a8

        col server_address format a15

        col platform_code format a4

        col webhost format a25

        col domain format a20

        col virtual_ip format a18

        select node_id, platform_code, support_db D, support_cp C, support_admin A, support_forms F, support_web W, node_name, server_id,

               server_address, domain, webhost, virtual_ip, status

          from fnd_nodes order by node_name;

  

  

  

  

set pagesize 200

        set linesize 200

        col NAME format A20

        col VERSION format A22

        col PATH format A86

        col STATUS format A10

        select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;