Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Wednesday, September 5, 2007

When you can't change your APPS password thru FNDCPASS

I got an SOS call in the morning today. Some newbie DBA had changed the APPS password by giving command ALTER USER APPS identified by newpassword; When he realized his mistake he tried various things with FNDCPASS and other R&D, because of which changing the APPS password back to original through ALTER USER command did not suffice. FNDCPASS still failed to change APPS password:

APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password.

There is a very easy solution to this. Oracle stores all application users in FND_USER and all application related database user in FND_ORACLE_USERID. To correct the problem in the cloned instance this is what I suggested:

export FND_USER and FND_ORACLE_USERID tables from production.
create a temporary schema in the clone instance.
import FND_USER and FND_ORACLE_USERID from the export file into this temporary schema.
Take a backup of your current FND_USER and FND_ORACLE_USERID by using CTAS
create table fnd_user_ as select * from fnd_user;
create table fnd_oracle_userid_ as select * from fnd_oracle_userid;
delete fnd_user;
delete fnd_oracleuser_id;
insert into fnd_user (select * from temporary.fnd_user);
insert into fnd_oracle_userid (select * from temporary.fnd_oracle_userid);
commit;

Once you do the above, you are back to the stage before the newbie DBA did any change.
This works with all 11i versions. I have not yet tested this in R12.

No comments: