Autoconfig Error on AppsTier after Cloning : ORA-01422

ERROR:

Executing script in InstantiateFile:

/olvtestt1_olvtst_cls/apps/fs1/FMW_Home/webtier/perl/bin/perl -I /olvtestt1_olvtst_cls/apps/fs1/FMW_Home/webtier/perl/lib/5.10.0 -I /olvtestt1_olvtst_cls/apps/fs1/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I /olvtestt1_olvtst_cls/apps/fs1/EBSapps/appl/au/12.0.0/perl -I /olvtestt1_olvtst_cls/apps/fs1/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /olvtestt1_olvtst_cls/apps/fs1/inst/apps/olvtst_olvtestt1ap01/admin/install/txkGenADOPWrapper.pl

script returned:

****************************************************

Refer log file /olvtestt1_olvtst_cls/apps/fs1/inst/apps/olvtst_olvtestt1ap01/logs/appl/rgf/TXK/txkGenADOPWrapper_Mon_Oct_16_15_53_03_2023/txkGenADOPWrapper_Mon_Oct_16_15_53_03_2023.log for details

Script Name : txkGenADOPWrapper.pl

Script Version : 120.0.12020000.7

ERROR DESCRIPTION:

(*******FATAL ERROR*******

PROGRAM : (/olvtestt1_olvtst_cls/apps/fs1/inst/apps/olvtst_olvtestt1ap01/admin/install/txkGenADOPWrapper.pl)

TIME : Mon Oct 16 15:53:04 2023

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

SQLPLUS error: buffer=

SQL*Plus: Release 10.1.0.5.0 – Production on Mon Oct 16 15:53:04 2023

SQL> SQL> Connected.

SQL> SELECT ad_zd_adop.get_node_type(‘cloned-node-name-001’) 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 3072

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

CHANGE:

The cloned-node-name-001 node has been recently cloned from primary node.

CAUSE :

The underlying query used by the ad_zd_adop.get_node_type() function is returning two rows (or more) when it should be returning ONLY ONE ROW.

SQL> SELECT ad_zd_adop.get_node_type(‘cloned-node-name-001’) 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 3053

The ad_zd_adop.get_node_type() function is expected to return only one row with information of node enabled for the service “s_web_admin_status”. The internal query used by the function is returning the following:

Node_name Node_Type

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

cloned-node-name-001 1

cloned-node-name-001 4

The cloned node (cloned-node-name) is also enabled with this service, which should have been disabled as soon as the clone completed.

SOLUTION :

This solution only applies to a clone performed from a multinode environment, it will not work on a clone from a single-node environment.

To solve this issue, Please do the following:

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);

The meaning of various return values are:

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

1 – Admin Node

2 – Non-Shared master node

3 – Admin Shared slave node

4 – Shared slave node

If the above query returns more than one row, do the following:

Disable the “s_web_admin_status” parameter on the application context file of the recently cloned node and
Rerun autoconfig and confirm the issue is solved.

Hemanth Kumar