Blog

Symptoms

While applying 12.2.11 patch, adop fails with below error

$ adop phase=apply apply_mode=downtime patches=31856789 restart=yes abandon=no patchtop=/mnt/install_images/oracle/EBS122Patches/EBS12211/12211 workers=16 wait_on_failed_job=yes

Enter the APPS password:
Enter the EBS_SYSTEM password:
Enter the WLSADMIN password:

Validating credentials.

Initializing.
Run Edition context : /dodiapps/prod/prodapps/fs1/inst/apps/PROD_prodapp/appl/admin/PROD_prodapp.xml
Patch edition context: /dodiapps/prod/prodapps/fs2/inst/apps/PROD_prodapp/appl/admin/PROD_prodapp.xml
*******FATAL ERROR*******
PROGRAM : (/dodiapps/prod/prodapps/fs1/EBSapps/appl/ad/12.0.0/bin/adzdoptl.pl)
TIME : Wed May 17 15:14:16 2023
FUNCTION: ADOP::GlobalVars::_GetMandatoryArgs [ Level 1 ]
ERRORMSG: Cannot identify any valid application tier nodes in
ADOP_VALID_NODES table. Ensure AutoConfig has been run on all nodes.


[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 = 255 (Fail)
prodapp(oracle):/home/oracle
$

Fix:

1. Inconsistency observed in dbhost of Apps context File and DB context File

Application Context File

$ grep -i s_dbhost /dodiapps/prod/prodapps/fs1/inst/apps/PROD_prodapp/appl/admin/PROD_prodapp.xml
<dbhost oa_var=”s_dbhost”>PRODDB</dbhost>
prodapp(oracle):/dodiapps/prod/prodapps/fs1/EBSapps/appl

DB context File

$ grep -i s_dbhost PROD_proddb.xml
<dbhost oa_var=”s_dbhost”>proddb-int</dbhost>
proddb(oracle):/dodiapps/prod/proddb/19c/appsutil

2. Change s_dbhost on DB tier to match with Apps Tier (i.e. proddb).

3. Execute autoconfig on DB tier to ensure it executed successfully.

4. Run the ADZDDMDIAG.SQL script again to verify that all critical ADOP tables are in sync.
SQL> @$AD_TOP/sql/ADZDDMDIAG.sql

5. If any tables show out of sync in 4, Run the ADZDDMFIXER.SQL script to correct the tables that are displayed as not in sync in adzdshowdmdiag.out
SQL> @$AD_TOP/sql/ADZDDMFIXER.sql

6. Run the ADZDDMDIAG.SQL script again to verify that all critical ADOP tables are in sync.
SQL> @$AD_TOP/sql/ADZDDMDIAG.sql

7. Restarted the patch with restart=yes and bandaon=no option.

adop phase=apply apply_mode=downtime patches=31856789 restart=yes abandon=no patchtop=/mnt/install_images/oracle/EBS122Patches/EBS12211/12211 workers=16 wait_on_failed_job=yes

adadminsrvctl.sh start shows warnings

Symptoms

adadminsrvctl.sh start and adop commands shows warnings after Online Enablement Patching and before applying AD/TXK14 patch during 12.2 upgrade

sh: which: line 1: syntax error: unexpected end of file

sh: error importing function definition for `which’

sh: module: line 1: syntax error: unexpected end of file

sh: error importing function definition for `module’

sh: _module_raw: line 1: syntax error: unexpected end of file

sh: error importing function definition for `_module_raw’

sh: switchml: line 1: syntax error: unexpected end of file

sh: error importing function definition for `switchml’

sh: scl: line 1: syntax error: unexpected end of file

sh: error importing function definition for `scl’

sh: ml: line 1: syntax error: unexpected end of file

sh: error importing function definition for `ml’

/bin/sh: switchml: line 1: syntax error: unexpected end of file

/bin/sh: error importing function definition for `switchml’

/bin/sh: which: line 1: syntax error: unexpected end of file

/bin/sh: error importing function definition for `which’

/bin/sh: ml: line 1: syntax error: unexpected end of file

/bin/sh: error importing function definition for `ml’

/bin/sh: _module_raw: line 1: syntax error: unexpected end of file

/bin/sh: error importing function definition for `_module_raw’

/bin/sh: scl: line 1: syntax error: unexpected end of file

/bin/sh: error importing function definition for `scl’

/bin/sh: module: line 1: syntax error: unexpected end of file

/bin/sh: error importing function definition for `module’

/bin/sh: switchml: line 1: syntax error: unexpected end of file

/bin/sh: error importing function definition for `switchml’

Solution:

These messages should be harmless and is related to a bug on OEL 8. Implement below changes as a workaround for next coommands until few adop cutover cycles for a permanent fix.

As a root user:

cd /etc/profile.d

mv which2.sh which2.shbkp

mv scl-init.sh scl-init.shbkp

mv modules.sh modules.shbkp

As oracle user, execute below unset commands along with the 12.2 environment file

unset -f which

unset -f scl

unset -f ml

unset -f module

unset -f switchml

unset -f _module_raw

Metalink Reference:

R12.2 Errors Running adadminsrvctl.sh with ‘sh: module: line 1: Syntax Error: Unexpected End Of File’ or ‘Importing Function Definition for `BASH_FUNC_module” On Oracle Linux 6 64-bit (Doc ID 1595052.1)

Symptoms

US_czsdrule_ldt.log shows below error

CURRENT SESSION_ID is : 670165893
CURRENT EDITION_NAME is : ORA$BASE

Uploading from the data file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsdrule.ldt
Altering database NLS_LANGUAGE environment to AMERICAN
Dumping from LCT/LDT files (/dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/czsdrule.lct(120.6.12020000.1), /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsdrule.ldt) to staging tables
Dumping LCT file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/czsdrule.lct(120.6.12020000.1) into FND_SEED_STAGE_CONFIG
Dumping LDT file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsdrule.ldt into FND_SEED_STAGE_ENTITY
Dumped the batch (CZ_RULES 1 , CZ_RULES 999 ) into FND_SEED_STAGE_ENTITY
Uploading from staging tables
Error during uploading. ORA-06550: line 431, column 15:
PL/SQL: ORA-04098: trigger ‘APPS.CZ_RULES_T2′ is invalid and failed re-validation

US_czsdxpnd_ldt.log shows below error CURRENT SESSION_ID is : 670165896
CURRENT EDITION_NAME is : ORA$BASE

Uploading from the data file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsdxpnd.ldt
Altering database NLS_LANGUAGE environment to AMERICAN
Dumping from LCT/LDT files (/dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/czsdxpnd.lct(120.2.12020000.1), /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsdxpnd.ldt) to staging tables
Dumping LCT file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/czsdxpnd.lct(120.2.12020000.1) into FND_SEED_STAGE_CONFIG
Dumping LDT file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsdxpnd.ldt into FND_SEED_STAGE_ENTITY
Dumped the batch (CZ_EXPRESSION_NODES 16 , CZ_EXPRESSION_NODES 502 ) into FND_SEED_STAGE_ENTITY
Uploading from staging tables
Error during uploading. ORA-06550: line 358, column 24:
PL/SQL: ORA-04098: trigger ‘APPS.CZ_EXPRESSION_NODES_T2′ is invalid and failed re-validation

US_czsduitl_ldt shows below error.

CURRENT SESSION_ID is : 670165894

CURRENT EDITION_NAME is : ORA$BASE

Uploading from the data file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsduitl.ldt

Altering database NLS_LANGUAGE environment to AMERICAN

Dumping from LCT/LDT files (/dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/czsduitl.lct(120.3.12020000.1), /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsduitl.ldt) to staging tables

Dumping LCT file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/czsduitl.lct(120.3.12020000.1) into FND_SEED_STAGE_CONFIG

Dumping LDT file /dodiapps/prod/prodapps/fs1/EBSapps/appl/cz/12.0.0/patch/115/import/US/czsduitl.ldt into FND_SEED_STAGE_ENTITY

Dumped the batch (CZ_UI_TEMPLATES 1 0 , CZ_UI_TEMPLATES 200101 0 ) into FND_SEED_STAGE_ENTITY

Uploading from staging tables

  Error during uploading. ORA-06550: line 257, column 15:  PL/SQL: ORA-04098: trigger ‘APPS.CZ_UI_CONT_TYPE_TEMPLS_T2′ is invalid and failed re-validation

Solution:

1.Execute below commands

sqlplus apps

SQL> drop trigger CZ_RULES_T2;

SQL> drop trigger CZ_EXPRESSION_NODES_T2;

SQL> drop trigger CZ_UI_CONT_TYPE_TEMPLS_T2;

            SQL> @$CZ_TOP/patch/115/sql/cztrigs2.sql

2. Validate if above triggers are valid:

SQL>select OWNER,OBJECT_NAME,OBJECT_TYPE,status from all_objects where OBJECT_NAME like ‘&a’;

               CZ_RULES_T2

                CZ_UI_CONT_TYPE_TEMPLS_T2

                CZ_EXPRESSION_NODES_T2

3. Restart failed workers using adctrl Option 2 Tell worker to restart a failed job

Metalink Reference:

Patch 31856789 fails at czsdxpnd.ldt, czsdrule.ldt and czsduitl.ldt, Unable to Compile CZ% Invalid Objects After Database Upgrade ( Doc ID 2930404.1 )

Symptoms:

The enablement patch application may take several hours to finish. You can monitor its progress at any time by running the DDL Status Report  (ADZDSHOWDDLS.sql) as follows

$ cd $LOG_HOME/appl/op

$ mv adzdshowddls.out adzdshowddls.outbkp

$ sqlplus apps @$AD_TOP/sql/ADZDSHOWDDLS.sql

This report lists a count of the DDL statements that are required to EBR enable your environment. The report is organized by outcome of execution: ‘Successfully Executed’, ‘Not Executed’, and ‘Failed Execution’. At the end of the patch application the report should have a zero count in the sections: ‘Not Executed’ and  ‘Failed Execution’. If the report contains ‘Not Executed’ and ‘Failed Execution’ items, then report these failures to Oracle Support. You will be asked to provide the output from the report, the patch log and all worker logs, and the online patching enablement status report output. If there is any worker failure during the Online Enablement patch, you should not ignore or skip the failed error. The issue should be addressed before restarting the worker. Ignoring or skipping the error can cause database corruption.

Note: In addition to the above outcomes, some DDL statements may complete with a ‘Warning’ status.

DOC>#

Phase                     Count Status

——————– ——- ——————————

DROP_UNUSED_OBJECT         24 S – Successfully Executed

COPY_TYPE                   45 S – Successfully Executed

COMPILE_TYPE                         1 S – Successfully Executed

COPY_EVOLVED_TYPE              1 S – Successfully Executed

FIX_COLUMN                2 S – Successfully Executed

FIX_TYPE                      23 S – Successfully Executed

FIX_PUBLIC_SYNONYM        6730 S – Successfully Executed

RECREATE_AQ_OBJECT           13 S – Successfully Executed

DROP_OBJECT                           9 S – Successfully Executed

ENABLE_EDITIONING           175 S – Successfully Executed

UPGRADE_TABLE              20025 S – Successfully Executed

                                       2 E – Error

UPGRADE_SEED                     2681 S – Successfully Executed

COLLECT_STATS                     2681 S – Successfully Executed

UPGRADE_MVIEW                 113 S – Successfully Executed

                                       1 E – Error

CLEANUP                       1 N – Not Executed

17 rows selected.

DOC>

$ grep -i failed adzdshowddls.out

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

$

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

$ grep -i not adzdshowddls.out

CLEANUP                    1 N – Not Executed

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

$

Solution:

No technical changes are required, as it is the intended functionality for hotpatch to skip Cleanup and Cutover phases.

Users should verify the ADOP enablement Patch 13543062 completes successfully without any errors or skipped workers.

If no issues are found then users may proceed with the upgrade.

The CLEANUP and CUTOVER will show as “Not Executed”, and this informational and can be ignored.

Metalink Reference:

12.2 Applications DBA Upgrade ADOP Online Enablement Patch 13543062 Completes Successfully But Script ADZDSHOWDDLS.sql Shows CUTOVER And CLEANUP Stages As ‘Not Executed’ (Doc ID 2352746.1)

Symptoms:

Online Enablement Patch log and worker log shows below

ATTENTION: All workers either have failed or are waiting:

           FAILED: file ADZDPREP.sql on worker  1.

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

Current time is: Thu Apr 06 2023 11:55:08

prodapp(oracle):/dodiapps/prod/prodapps/fs1/EBSapps/appl/admin/PROD/log

$

Worker log says below

Commit complete.

begin

*

ERROR at line 1:

ORA-44303: service name exists

ORA-06512: at “SYS.DBMS_SERVICE_ERR”, line 21

ORA-06512: at “SYS.DBMS_SERVICE”, line 316

ORA-06512: at “APPS.AD_ZD_PREP”, line 392

ORA-01403: no data found

ORA-06512: at “APPS.AD_ZD_PREP”, line 386

ORA-06512: at line 4

Time when worker failed: Thu Apr 06 2023 11:55:08

However, there was no ebs_patch observed at PDB level

SQL> select SERVICE_ID,NAME from all_services;

SERVICE_ID NAME
———- —————————————————————-
2 ebs_PROD
3 PROD
1 PROD

SQL>

Solution:

1. Verify if query return ebs_patch:

SQL> select c.name from cdb_services c, service$ s where s.deletion_date is null and s.name = c.name;

2. Execute below commands at CDB level

exec dbms_service.stop_service(‘ebs_patch’);

exec dbms_service.delete_service(‘ebs_patch’);

3. Verify if query return ebs_patch:

SQL> select c.name from cdb_services c, service$ s where s.deletion_date is null and s.name = c.name;

This should not list ebs_patch.

4. If not then proceed for restarting the patch from adctrl option-2

Metalink Reference:

Known Issue: FAILED: File ADZDPREP.sql In Online Patching with ORA-44303: service name exists (Doc ID 2060543.1).