Blog

Symptoms:

While executing ADZDREG.sql as part of ADZDPSUM.sql recommendation to register custom schemas before applying online enablement patch during Rel 12.2 upgrade, ADZDREG.sql completes with warnings as mentioned below

$ sqlplus apps @$AD_TOP/patch/115/sql/ADZDREG.sql <systempassword> <apps username> DELPHI

SQL*Plus: Release 10.1.0.5.0 – Production on Wed Apr 5 13:58:33 2023

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Connected.

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

ERROR: Installing APPS_DDL packages failed. Run the sql files adaddls.pls, adaaddls.pls, adaddlb.pls, adaaddlb.pls from $AD_TOP/patch/115/sql directory

Commit complete.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

prodapp(oracle):/dodiapps/prod/prodapps/fs1/inst/apps/PROD_prodapp/logs/appl/op

$

Solution

1. Run the following to verify if the custom schema is registered with READ_ONLY FLAG ‘B’ in FND_ORACLE_USERID table:

select READ_ONLY_FLAG from fnd_oracle_userid where oracle_username=’DELPHI’;

2. Are the following file versions at (or higher than):
ad_apply_patch.xml 120.4.12020000.2
adaddlb.pls 120.1.12020000.2
adaddls.pls 120.1.12020000.2
aidafo.lpc 120.13.12020000.4
adppdep.lpc 120.10.12020000.7

If both #1 and #2 above are YES, then the following error can safely be ignored:

Metalink Reference

Error Running ADZDREG.sql For adaddls.pls, adaaddls.pls, adaddlb.pls, adaaddlb.pls ( Doc ID 2018020.1 )

Symptoms

SQL> select object_name,object_type, status from all_objects where object_name like ‘AD%’ and status like ‘INVALID’;

OBJECT_NAME

——————————————————————————–

OBJECT_TYPE                            STATUS

———————– ——-

AD_PATCH_ANALYSIS_ENGINE

PACKAGE BODY                         INVALID

SQL>

SQL> alter package AD_PATCH_ANALYSIS_ENGINE compile body;

Warning: Package Body altered with compilation errors.

SQL> sho errors

Errors for PACKAGE BODY AD_PATCH_ANALYSIS_ENGINE:

LINE/COL ERROR

——– —————————————————————–

54/12          PLS-00323: subprogram or cursor ‘GETGLOBALVIEWSNAPSHOTID’ is

    declared in a package specification and must be defined in the

    package body

SQL>

Solution:

1. Ignore the invalid PACKAGE BODY AD_PATCH_ANALYSIS_ENGINE at this time.
2. Apply the latest RUP 12.2.10 (30399999), RUP 12.2.11 (31856789) or above. The FUNCTION getGlobalViewSnapshotID is declared in both the Package Specification and the Package Body (adpaengb.pls) with below file versions, and will become VALID.
   
   <Patch30399999> R12.2.10 RUP
     adpaengs.pls 120.2.12020000.5
     adpaengb.pls 120.13.12020000.10

<Patch31856789> R12.2.11 RUP
  adpaengs.pls 120.2.12020000.5
  adpaengb.pls 120.13.12020000.10

Note: The AD_PATCH_ANALYSIS_ENGINE package is only used by Patch Wizard.

Metalink Reference:

AD_PATCH_ANALYSIS_ENGINE PACKAGE BODY Becomes Invalid with Error PLS-00323 When Upgrading to R12.2.0. (Doc ID 2876753.1)

Observed Error:

There is already an ACTIVE ADOP CYCLE with session id : SP2-0640: Not connected adpreclone cannot be run with pending ADOP session

Fix:

As per the cause mentioned in Adpreclone.pl Fails with an SP2-0640 Error on 12.2 Application Tier ( Doc ID 2139174.1 ) – “Sqlplus permits user variable lengths up to 240 characters.”, there seems to be a restriction at OS level that is causing only script to fail only on PROD and not non-PROD.

1. Verified and Patch 22271970 was already applied.
2. Modified the script to assign apps and weblogic passwords directly into the variables.

Symptom:

ATTENTION: All workers either have failed or are waiting:

               FAILED: file ap_undo_event_log.xdf on worker  1.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

Worker log showed below error

Table Name is AP_UNDO_EVENT_LOG

Table exists in the target database

Checking for differences

Number of columns for the table in the xml file is 7

The table in the Xml file and in the target database match

Executing grants on AP_UNDO_EVENT_LOG to APPS

GRANT ALL ON CLE.AP_UNDO_EVENT_LOG TO APPS WITH GRANT OPTION

Creating synonym :CREATE SYNONYM AP_UNDO_EVENT_LOG FOR CLE.AP_UNDO_EVENT_LOG

Exception occured  in  createSynonym

ORA-00955: name is already used by an existing object

Fix:

Follow below Action plan to rename the table in APPS Schema and proceed with patch application. This way the table will not be checked and later if needed the data can be moved to the new table.

1. Rename the table

sqlplus apps   

rename AP_UNDO_EVENT_LOG to AP_UNDO_EVENT_LOG_R1213;

2. Restart Failed work using adctrl Option 2: /dodiapps/rgp6/rgp6apps/fs1/EBSapps/appl/admin/RGP6/log/adctrl_18712060_ap_undo_event_log.log

3. Restore the data after patch application is successful.

sqlplus apps   

insert into cle.ap_undo_event_log (select * from ap_undo_event_log_r1213);
commit;

SQL> insert into cle.ap_undo_event_log (select * from ap_undo_event_log_r1213);

116 rows created.

SQL> commit;

Commit complete.

SQL>

Metalink Reference: EMEA Add-On Localization Upgrade Patch 18712060 Failed with Error “ORA-00955: name is already used by an existing object” (Doc ID 2883489.1)

Time when worker started job: Fri Mar 31 2023 16:32:49

Loading data using  FNDLOAD function.

FNDLOAD APPS/***** 0 Y UPLOAD @JTF:patch/115/import/jtfamvit.lct @IBE:patch/115/import/US/ibeitems.ldt –

Connecting to APPS……Connected successfully.

Calling FNDLOAD function.

Returned from FNDLOAD function.

Log file: /dodiapps/rgp6/rgp6apps/fs1/EBSapps/appl/admin/RGP6/log/US_ibeitems_ldt.log

Error calling FNDLOAD function.

Time when worker failed: Fri Mar 31 2023 16:32:50

rgp6app(oracle):/dodiapps/rgp6/rgp6apps/fs1/EBSapps/appl

Error found in /dodiapps/rgp6/rgp6apps/fs1/EBSapps/appl/admin/RGP6/log/US_ibeitems_ldt.log

  Error loading seed data for JTF_AMV_ITEM:  ITEM_ID = 8126,  ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

  Error loading seed data for JTF_AMV_ITEM:  ITEM_ID = 8128,  ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

  Error loading seed data for JTF_AMV_ITEM:  ITEM_ID = 8130,  ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Fix:

To implement the solution, please execute the following steps:

1. Connect to Mid-tier node to directory: $JTF_TOP/patch/115/sql

2. sqlplus apps/apps_pwd

@jtfiimt.sql jtf icybay

@jtfiaitd.sql jtf icybay

@jtfiaitn.sql jtf icybay

@jtfiaitt.sql jtf icybay

@jtfiappr.sql apps

@jtfiaibu.sql jtf icybay apps

@jtfntsci.sql jtf icybay

@jtftkimi.sql jtf icybay

3. Verify all status columns are with VALID status

select index_name, domidx_status, domidx_opstatus, funcidx_status, status from dba_indexes where index_type=’DOMAIN’ and index_name like ‘JTF_AMV_ITEMS%’;

Metalink Reference: Upgrade Driver Failed Error loading seed data for JTF_AMV_ITEM – ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE (Doc ID 2435119.1)