Oracle 11g Database Password Expiration ORA-28002 Problem Solution

Oracle 11g Database Password Expiration ORA-28002 Problem Solution

Problem description and analysis: the user returns an error ORA-28002 upon logon, for example, prompting that the data will expire in 1 day. This is because the 11g password lifecycle is controlled, the control time is 180 days. The modification of the time can solve this problem. If the error is not reported, the user will be judged based on the modified time. the user with a warning needs to modify the password, can be determined based on the new time.

Sqlplus connection:

ORA-28002: the password will expire within 1 days

28002. 00000-"the password will expire within % s days"

* Cause: The user's account is about to expire and the password

Needs to be changed

* Action: change the password or contact the DBA

Supplier Code 28002

Oracle SQL developer connection:

Pl/SQL connection:

Solution:

1. Connect to the database and check the password_life_time parameter in the default profile file.

[Oracle @ tora01 diag] $ sqlplus/as sysdba

SQL * Plus: Release 11.2.0.4.0 Production on Mon May 25 13:17:57 2015

Copyright (c) 1982,201 3, Oracle. All rights reserved.

Connected:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from dba_profiles s where s. profile = 'default' and resource_name = 'password _ LIFE_TIME ';

PROFILE RESOURCE_NAME RESOURCE

----------------------------------------------------------------------

LIMIT

----------------------------------------

DEFAULT PASSWORD_LIFE_TIME PASSWORD

180

2. Modify the password_life_time parameter to unlimited (this parameter is changed as required. If you are afraid that it will expire again later, you can directly unlimited it)

SQL> alter profile default limit password_life_time unlimited;

Profile altered.

3. Check this parameter again

SQL> select * from dba_profiles s where s. profile = 'default' and resource_name = 'password _ LIFE_TIME ';

PROFILE RESOURCE_NAME RESOURCE

----------------------------------------------------------------------

LIMIT

----------------------------------------

DEFAULT PASSWORD_LIFE_TIME PASSWORD

UNLIMITED

4. Modify the password of the alert user

SQL> alter user testauchan identified by oracle;

User altered.

5. Verify that the logon is normal

[Oracle @ tora01 ~] $ Sqlplus testauchan/oracle

SQL * Plus: Release 11.2.0.4.0 Production on Mon May 25 13:41:58 2015

Copyright (c) 1982,201 3, Oracle. All rights reserved.

Connected:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Summary: this parameter is set by oracle for security purposes. Therefore, with sufficient DBA control, you can change it to unlimited. Otherwise, we recommend that you set a limit on the number of days. This makes it easy to change the password security.