Dear all!
Welcome to our community!
The post describes how to deal with the problem that Oracle DB password become due result CMS log in failed.
Issue Description
The customer site, when log in CMS web page, display below information: "A database exception occurred when an agent logs in".
eSpace CC:V200R001C50, Oracle11G
Handling Process
First, log in Oracle DB and check its running status:
use sqlplus connect to Oracle (for example ICD user), display below information:
ERROR:
ORA-28001: the password has expired
1. Use oracle user log in DB server
2. Connect to DB.
$ sqlplus / as sysdba
3. Check the user profile, usually, it is by default.
sql>select username,PROFILE FROM dba_users;
USERNAME PROFILE
------------------------------ ------------------------------
SYS DEFAULT
ICD DEFAULT
...... ......
4. Check the profile password period of validity configuration, it is 180 days limited.
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
5. Change the password period of validity to unlimited
sql> alter PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
6. Check the new configuration if take effect
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
7. After that, the user not prompt ORA-28001/ ORA-28002 alarm will not get the same prompt; but the user already gets the prompt need to change the password to make the password policy take effect, or else it will still display alarm. the method to change the password is as below:
sql> alter user username identified by original_password
"username" is the user name you want to change password;
"original_passwd" is the password you define, you can use old password.
8. When Oracle DB user connects to DB reach the maximum number of times, the system will prompt "the account is locked". the method to unlock DB user is as below:
alter user username account unlock;
Root Cause
Oracle11G create user by default profile, the password will become due in 180 days. if customer does not change password in 180 days, then the DB user can not log in. for this issue, the customer can cancel the password policy of 180 days and change to unlimited.
before the password is due, when use sqlplus connect to Oracle by ICD user, the system will display:
ERROR:
ORA-28002 : the password will expire within 5 days
Solution
As explained in Procedure.
Suggestions
Oracle11G create user by default profile, the password will become due in 180 days. if customer does not change password in 180 days, then the DB user can not log in. for this issue, the customer can cancel the password policy of 180 days and change to unlimited.
This is all, thanks for reading!
