Thursday, August 7, 2008

"alter profile ..." could shut down entire 11i applications

Co-worker misunderstood the concept of database password management when tried to set the length of 11i Application password expiration, and ran two lines as sysdba:

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 1;
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 2;

That action expired APPS password, and even SYSTEM password:
SQL> conn system/xxx@tns
ERROR:ORA-28002: the password will expire within 0 days
Connected.

and so the entire Oracle EBS instance went down.

To fix the issue, tried following steps:

1. Run the below sql's as sysdba to change the profile back.

1) ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited;
2) ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

2. Then try to connect as apps user in SQL*Plus. If it is requesting for a password change, cancel it.

3. Use normal approach of changing the apps password.
1) Stop MT services
2) Change APPS password using FNDCPASS
3) Change the password in wdbsvr.app and cgicmd.dat
4) Execute cmclean.sql connected to the database as APPS
5) Start MT services.

But, FNDCPASS keep getting error:
$ FNDCPASS apps/xxxxxx 0 Y system/xxxxxx SYSTEM APPLSYS xxxxxxx
APP-FND-01564: ORACLE error 28001 in AFPCOA
Cause: AFPCOA failed due to ORA-28001: the password has expired

Even, after APPS account is unlocked by SQL*Plus,
$ FNDCPASS apps/*** 0 Y system/*** system APPLSYS ***
fails with these error messages in logfile:
FNDCPASS was not able to decrypt password for ANONYMOUS during applsys password change.
FNDCPASS was not able to decrypt password for AUTOINSTALL during applsys password change.
FNDCPASS was not able to decrypt password for CONCURRENT MANAGER during applsys password change.
... 500 lines generated.

Metalink note 459601.1 suggests to change password manually for 500 users, which doesn't sound like a good solution.

Finally, we have to refresh the instance by using another instance.

No comments: