I will be updating this post as my situation progresses, but some of these items could be critical so I am getting the information out as soon as possible.
TLDR: After adding APEX schemas to EBS, using the SYSTEM option of FNDCPASS corrupted passwords, broke FNDCPASS, and broke forms. In the course of this, I also found 12.2.14 broke AFPASSWD. There is a patch and a workaround for AFPASSWD. For the rest see the bottom of the post for my current workaround.
Background
Well this past week has been interesting to say the least. I have been working through Extending Oracle E-Business Suite Release 12.2 Using Oracle APEX with reasonable success. However when I decided to go ahead and register the Apex schema in production so that it would move down as I cloned, things took a decided turn for the worse. I run a nightly support and reporting clone. After the clone completed, we noticed that the REST APIs we have did not change their URLs, then we noticed that forms would not launch because of an authentication error.
Triage
In any situation like this, you need to try and find the root cause. First step was to manually try to complete the scripted steps. No luck in reconfiguring ISG, but discovered that we had an interesting java error, where it was unable to create a pool object. Manually changing the password for apps in WLS did not fix the forms issue. Reran the clone, and had the same issues. FNDCPASS failed with the following error in the log Error in password verification for APPS, and AFPASSWD failed with that as well as some strange errors. At this point, an SR was opened. While waiting on a response to the SR, I started searching for any known solutions.
AFPASSWD and FNDCPASS
I verified that AFPASSWD was failing in an environment that I had registered the Apex schemas in as well so that got me looking directly at AFPASSWD. This led me to note AFPASSWD ORA-00942 Error and CORE DUMP after 12.2.14 RUP (Doc ID 3064561.1). If you have upgraded to 12.2.14, make sure you install patch 3768396.
However, this patch did not solve the password issue, it just left me with only Error in password verification for APPS message. I found two other notes that seemed to apply, Error In Password Verification For APPS With FNDCPASS (Doc ID 3055934.1) — the patch in this note was superseded, but some searches on the new patch led me to Unable To Change Password With Hard To Guess Profile (Doc ID 3056638.1). Patches 36842768 and 36961988 seem like a good idea to apply in any environment (you definitely should install 36961988 if you have enabled Hard To Guess passwords).
Note: if you are on the AD/TXK delta 16 patch level, you are running the most current adgrants.sql ignore the instructions to run adgrants.sql from the patch 3768396–the version in the patch is not EBS_SYSTEM compliant.
As you can probably guess, while these patches did fix other issues, such as being able to use AFPASSWD at all, they did not fix the issue with changing the apps password.
Workaround
My workaround in the clones is to run
UPDATE FND_ORACLE_USERID
SET READ_ONLY_FLAG='A'
WHERE READ_ONLY_FLAG='K';followed by running FNDCPASS ALLORACLE to set all the oracle passwords to a known value. Following this, you can use FNDCPASS SYSTEM to change the apps password.
I do not want E-Business Suite to be in control of my Apex database accounts, so in practice I would not use the all oracle options with FNDCPASS or AFPASSWD. Also, if you are running from the command line, please use AFPASSWD. If you are scripting, FNDCPASS is much easier since you put it all on the command line and do not need to deal with prompts.
At this time, I am using the workaround in clones before changing the apps password and taking regular backups of FND_ORACLE_USERID and FND_USER in production so that I can manually recover if the passwords get corrupted.