Solution Oracle 11g password expiration problem

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

[Problem description]
Recently, the use of sqlplus in short message Oracle (Oracle training) database that &ldquo ORA-28000: account is locked;”.
[Cause / trigger factors]
Determination of Oracle11g is due to default &ldquo settings in the default profile; FAILED_LOGIN_ATTEMPTS=10, ” when the input password error number reaches the setting will cause this problem.
[Effects and risks]
Influence
Account is locked, the database connection abnormal business process, business use.
The frequency of occurrence problem
Database account has been locked, business process once the restart will prompt the connection failed.
[The solution]
To operate according to the following steps:
1, What is the user proifle, generally is default:
sql>SELECT username,PROFILE FROM dba_users;
2, The specified profile (such as default) password set the validity period:
sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='FAILED_LOGIN_ATTEMPTS';
3, Will try to login failed times by default 10 revisions to “ no limit”:
sql>ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
4, After modification, has not been suggested that ORA-28000 warning users don't get the same prompt;
Has been locked accounts must be unlocked, for example:
$sqlplus / as sysdba
sql> alter user smsc identified by oracle account unlock;
5, The modified default profile should be as follows:
sql> select * from dba_profiles WHERE dba_profiles.profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
After the modification does not need to restart the database, will come into force immediately.
Hope the above scheme can solve all the Oracle 11g password expiration problem.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Abel at November 29, 2013 - 10:59 AM