Oracle ORA-28002

ORA-28002 & ORA-28011

Let's see how to troubleshoot/understand the issue which is related to account profile settings.  Introduction When I was trying to login to Oracle database 11g, I encountered the below issue. Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation.  All rights reserved. C:\Users\Pavan>sqlplus scott/tiger@orcl SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 9 12:39:11 2011 Copyright (c) 1982, 2010, Oracle.  All rights reserved. ERROR: ORA-28002: the password will expire within 4 days Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Systematic Approach Refer to Doc : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm PASSWORD_LIFE_TIME  Specify the number of days the same password can be used for authentication. If you also set a value for PASSWORD_GRACE_TIME, the password expires if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely. PASSWORD_GRACE_TIME  Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires. let's query the details from data dictionary C:\Users\Pavan>sqlplus /@orcl as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 9 12:45:46 2011 Copyright (c) 1982, 2010, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select profile from dba_users where username='SCOTT'; PROFILE ------------------------------ DEFAULT SQL> select LIMIT, RESOURCE_NAME from dba_profiles   2  where  PROFILE = 'DEFAULT'   3  ; LIMIT                                    RESOURCE_NAME ---------------------------------------- -------------------------------- UNLIMITED                                COMPOSITE_LIMIT UNLIMITED                                SESSIONS_PER_USER UNLIMITED                                CPU_PER_SESSION UNLIMITED                                CPU_PER_CALL UNLIMITED                                LOGICAL_READS_PER_SESSION UNLIMITED                                LOGICAL_READS_PER_CALL UNLIMITED                                IDLE_TIME UNLIMITED                                CONNECT_TIME UNLIMITED                                PRIVATE_SGA 10                                       FAILED_LOGIN_ATTEMPTS 180                                      PASSWORD_LIFE_TIME LIMIT                                    RESOURCE_NAME ---------------------------------------- -------------------------------- UNLIMITED                                PASSWORD_REUSE_TIME UNLIMITED                                PASSWORD_REUSE_MAX NULL                                     PASSWORD_VERIFY_FUNCTION 1                                        PASSWORD_LOCK_TIME 7                                        PASSWORD_GRACE_TIME 16 rows selected. SQL> select LIMIT, RESOURCE_NAME from dba_profiles   2  where  PROFILE = 'DEFAULT'   3  and  RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME'); LIMIT                                    RESOURCE_NAME ---------------------------------------- -------------------------------- 180                                      PASSWORD_LIFE_TIME UNLIMITED                                PASSWORD_REUSE_TIME UNLIMITED                                PASSWORD_REUSE_MAX 7                                        PASSWORD_GRACE_TIME If we observer carefully, we can notice that PASSWORD_LIFE_TIME is 180 days and PASSWORD_GRACE_TIME is 7 days (Total 187 days). To find an account's last password change time SQL> select ptime,sysdate, floor(sysdate- ptime) from sys.user$ where name='SCOTT'; PTIME     SYSDATE   FLOOR(SYSDATE-PTIME) --------- --------- -------------------- 10-AUG-10 09-FEB-11                  183 183 - out of 187 - 4 days in Grace period, suggested by Oracle warning The account creation time can be obtained using SQL> SELECT CREATED FROM DBA_USERS WHERE USERNAME = 'SCOTT'; CREATED --------- 02-APR-10 SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited; Profile altered. SQL> select LIMIT, RESOURCE_NAME from dba_profiles   2  where  PROFILE = 'DEFAULT'   3  and  RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME'); LIMIT                                    RESOURCE_NAME ---------------------------------------- -------------------------------- UNLIMITED                                PASSWORD_LIFE_TIME UNLIMITED                                PASSWORD_REUSE_TIME UNLIMITED                                PASSWORD_REUSE_MAX 7                                        PASSWORD_GRACE_TIME SQL> disconnect; Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn scott/tiger@orcl ERROR: ORA-28002: the password will expire within 4 days What's happening after modifying the limits of password, still facing issue. since, "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." So, before changing the profile limits just check with other users who are getting effected too. In order to resolve the issue on temporary and further in order to continue with password limits, we must change the password. If I try to disconnect and reconnect the Oracle warning message is changed in different way, since my profile settings of Password Life Time Changed. SQL> disconnect Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn scott/tiger@orcl ERROR: ORA-28011: the account will expire soon; change your password now Let's change the password. C:\Users\Pavan>sqlplus scott@orcl SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 9 13:04:54 2011 Copyright (c) 1982, 2010, Oracle.  All rights reserved. Enter password: ERROR: ORA-28011: the account will expire soon; change your password now Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> password Changing password for SCOTT Old password: New password: Retype new password: Password changed SQL> disconnect Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn scott/tiger@orcl Connected. SQL>