How to recover the Expired Password (or) Disable Password Checking feature in Oracle DB

 Now, you have reached  PASSWORD_LIFE_TIME value and getting below error.

$ sqlplus remote_dba/remote_dba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 11 03:01:11 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 ERROR:
 ORA-28001: the password has expired

Or you are getting below error.

SQL> connect remote_dba/remote_dba 

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

In both of the above case, remote DBA doesn't have any option other than changing password of users. Now think about situation 

1. when you need to change this at 100 places in window based application if you change the password.

2. you are not allowed to change password, but you are bound to change the password from Database side. Now this has become a challenging situation for a DBA.

Here, you are giving new password as an existing password to keep same password and hitting below error.

  

$ sqlplus remote_dba/remote_dba

ERROR:
ORA-28001: the password has expired

Changing password for remote_dba  Old password: remote_dba 

New password: remote_dba  Retype new password: remote_dba 

ERROR:  ORA-28003: password verification for the specified password failed   

ORA-20002: Password length less than 10 

Password unchanged 

 Enter user-name:  

Solution:

To make remote DBA life easy, below is the solution for all these problems using which you
can keep same Password for Oracle User when Expiry Time is Reached.

1. Remove the verify function from the profile assigned to the user, To find profile assigned to user use below query:

SQL> SELECT USERNAME, PROFILE from dba_users where username like 'REMOTE_DBA%';

USERNAME                       PROFILE
------------------------------ ------------------------------
REMOTE_DBA                  DEFAULT

In this case use has default profile. Suppose, use has profile_test then use below command:

SQL> alter profile profile_test limit password_verify_function null;

If user has default profile then remove the verify function from DEFAULT profile

SQL> alter profile DEFAULT limit password_verify_function null;

Profile altered.

This will disables the password checking feature.

2.What Limits Set with a Profile?

We are only interested in “DEFAULT” profile and resource of PASSWORD type.  To query all sorts of limits imposed with “DEFAULT” profile, you do the following query:

SQL> select resource_name, limit from dba_profiles where profile=’DEFAULT’ and resource_type=’PASSWORD';
RESOURCE_NAME                    LIMIT
——————————– —————————————-
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               180
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7

As shown above, all our OAM and OIM user accounts will expire in 180 days.  However, we would like to set it to never expire.

3.How to Set User Password to Never Expire?

Here is the alter statement that you can use:

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

The above command has set password life time associated with “DEFAULT” profile to be unlimited.  You can verify the setting by:

SQL> select resource_name, limit from dba_profiles where profile=’DEFAULT’ and resource_type=’PASSWORD';

RESOURCE_NAME                    LIMIT

——————————– —————————————-

FAILED_LOGIN_ATTEMPTS            10

PASSWORD_LIFE_TIME               UNLIMITED

PASSWORD_REUSE_TIME              UNLIMITED

PASSWORD_REUSE_MAX               UNLIMITED

PASSWORD_VERIFY_FUNCTION         NULL

PASSWORD_LOCK_TIME               1

PASSWORD_GRACE_TIME              7

7 rows selected.

4. Now change the password by keeping the same value.

SQL> alter user remote_dba identified by remote_dba;

If you forgot user password, you can user below query.

SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;  
Enter value for username: remote_dba
old   3: where d.username = upper('&&username')
new   3: where d.username = upper('remote_dba')

C
--------------------------------------------------------------------------------
alter user "REMOTE_DBA" identified by values 'F894844C34402B67';

SQL> alter user "REMOTE_DBA" identified by values 'F894844C34402B67';