How to Synchronize FND_NODES, ADOP_VALID_NODES, and FND_OAM_CONTEXT_FILES in 12.2 When ADOP Phase=Prepare Fails with Error

How to Synchronize FND_NODES, ADOP_VALID_NODES, and FND_OAM_CONTEXT_FILES in 12.2 When ADOP Phase=Prepare Fails with Error

After a recent change or addition to the 12.2 E-Business Suite application tiers, adop phase=prepare fails with the following error:

Error
$ adop phase=prepare

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

Validating credentials...
Initializing.
Run Edition context : /u01/app/fs1/inst/apps/SID_erpnode/appl/admin/SID_erpnode.xml
Patch edition context: /u01/app/fs2/inst/apps/ SID_erpnode /appl/admin/SID_erpnode.xml
Patch file system free space: 26.63 GB

Validating system setup.
Node registry is valid.

Checking for existing adop sessions.
Continuing with existing session [Session ID: 10].
Session Id : 10
Prepare phase status : NOT COMPLETED
Apply phase status : NOT COMPLETED
Cutover phase status : NOT COMPLETED
Abort phase status : NOT COMPLETED
Session status : FAILED

===========================================================================
ADOP (C.Delta.9)
Session ID: 10
Node: erpnode
Phase: prepare
Log: /u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/adop.log
===========================================================================

Validating configuration on node: [erpnode].
Log: /u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/prepare/validate/erpnode
[WARNING]: There could be issues while validating the ports used for E-Business Suite instance against ports used in /etc/services. Refer the log file for more details.
[WARNING]: Found invalid cross references in FS config files.
[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.
[UNEXPECTED]Error occurred running "perl /u01/app/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl -contextfile=/u01/app/fs1/inst/apps/SID_erpnode/appl/admin/SID_erpt_erpnode.xml -phase=prepare -logloc=/u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/prepare/validate/erpnode -promptmsg=hide"
[UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on erpnode

[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.

Cause
There is a synchronization error between fnd_nodes, adop_valid_nodes, and fnd_oam_context_files.

Solution
Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is EXPECTED / REQUIRED that the Applications have been shutdown.
The only thing running should be the Database Tier.
Note: A full backup should also be taken before any testing begins.

Test the following steps in a development instance, and then migrate accordingly once the desired result is confirmed:

1. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables in the EBS env:

 sqlplus applsys/pwd

 create table fnd_oam_context_files_bkp as select * from fnd_oam_context_files;
create table fnd_nodes_bk as select * from fnd_nodes;
create table adop_valid_nodes_bk as select * from adop_valid_nodes;

2. Truncate the following tables:

truncate table fnd_oam_context_files;

truncate table fnd_nodes;

truncate table adop_valid_nodes;

3. Run AutoConfig on the DB tier
Confirm Autoconfig completes successfully

4. Run Autoconfig on the run file system.
    Source the Environment File to switch to run file system
    . /u01/app/EBSapps.env RUN

Confirm Autoconfig completes successfully

5. Run Autoconfig on the patch file system
   Source the Environment File to switch to patch file system
   . /u01/app/EBSapps.env patch

 Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is           EXPECTED / REQUIRED that the Applications have been shutdown.
 The only thing running should be the Database Tier.
 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.
 This needs to be done as the SYSTEM schema user.
   a. Disable the ebs_login trigger using the following SQL.
     SQL> alter trigger ebs_logon disable;
  At this time Run autoconfig with the patch env sourced.
  Make sure Autoconfig completes ok
   b. Enable the ebs_login trigger using the following SQL.
  SQL> alter trigger ebs_logon enable;

6. 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:
SQL> 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_id;

SQL> select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;

Check adop with prepare phase. This time it should complete successfully.
$ adop phase=prepare

The prepare phase completed successfully.
adop exiting with status = 0 (Success)

Reference: Doc ID 2064223.1