ORA-28002: the password will expire within 1 days

A user complained that his password was to expire in 1 day, so I changed the "PASSWORD_LIFE_TIME" to be "unlimted", but the user still got following message when logged into his account:


SQL> conn  user1/passwd@db1
C:\Users\pc1>sqlplus user1/passsd@db1


SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 1 16:22:54 2011


Copyright (c) 1982, 2010, Oracle.  All rights reserved.


ERROR:
ORA-28002: the password will expire within 1 days


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


I was puzzled by this error message. I thought the "PASSWORD_LIFE_TIME" value changed to "unlimited" would resolve this problem. After searching Oracle Support, I found this note which explains why it still happens even after "PASSWORD_LIFE_TIME" value changed to "unlimited":

ORA-28002 On User Connection Immediately After PASSWORD_LIFE_TIME Changed [ID 162818.1]


"The PASSWORD_LIFE_TIME limit of a profile is measured from the last time an account's password was changed or the account creation time if the password has never been changed. These are the dates USER$.PTIME and USER$.CTIME respectively.

It is NOT measured from the current time, as may be initially thought.
Therefore, any accounts affected by the changed profile whose last password change time was more than PASSWORD_LIFE_TIME days ago immediately expire and enter their grace period on their next connection, issuing the ORA-28002 warning."

SQL> ALTER PROFILE test LIMIT PASSWORD_LIFE_TIME UNLIMITED;



This change only affects accounts who have not entered their grace period (and started getting ORA-28002 on connection). Accounts that have entered their grace period will have to change their passwords."


Here is what happend in one of my 11202 RAC database:
1 Check user account status:

SQL> select username, profile,account_status,expiry_date, created 
from dba_users 
where default_tablespace  not in ('SYSAUX','SYSTEM') 
--and expiry_date is not null 
and account_status not like '%LOCKED' order by expiry_date;

2 Change profile
SQL> alter profile default LIMIT PASSWORD_LIFE_TIME UNLIMITED;


3 Re-check account status

SQL> select username, profile,account_status,expiry_date, created 
from dba_users 
where default_tablespace  not in ('SYSAUX','SYSTEM') 
--and expiry_date is not null 
and account_status not like '%LOCKED' order by expiry_date;

Related Notes:

1. Oracle Password Management Policy [ID 114930.1]
2. BUG:1231172 - ORA-28003 WHEN CHANGING PASSWORD FOR A USER
3. NOTE:1079860.6 - ORA-28011 Password Expiry Date is Reached But Reset to NULL
4. NOTE:1083889.6 - ORA-00931: missing identifier when PASSWORD_VERIFY_FUNCTION = UNLIMITED
5. NOTE:124648.1 - ORA-28003, ORA-20001, ORA-20002, ORA-20003, ORA-20004 After Running utlpwdmg.sql
6. NOTE:139676.1 - ORA-28007: the password cannot be reused
   NOTE:139748.1 - Demonstrates the use of the new Oracle OCI8 OCIPasswordChange function (SCR 631)
7. NOTE:162818.1 - ORA-28002 On User Connection Immediately After PASSWORD_LIFE_TIME Changed
8. NOTE:260111.1 - How to Interpret the ACCOUNT_STATUS Column in DBA_USERS
9. NOTE:98481.1 - How to Keep the Same Password when Expiry Time is Reached and Change is Required