Unlimit the failed login attempts in ORACLE, SAPSR3: ORA-28000: the account is locked

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Symptom

You cannot log on to the Oracle database and the program returns the message:

ORA-28000: the account is locked

Other Terms

Reason and Prerequisites

ORA-28000 is usually triggered because someone has previously attempted to log on several times using an incorrect password, which causes the logon to fail and returns ORA-01017. The number of allowed logon attempts is defined by the resource FAILED_LOGIN_ATTEMPTS in the profile. You can determine the current value of this parameter using the following query:

SELECT LIMIT FROM DBA_PROFILES
WHERE
  PROFILE = 'DEFAULT' AND
  RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS';

Until Oracle 10.1, this parameter had the default value UNLIMITED, so that no ORA-28000 messages would normally occur. As of Oracle 10.2, this standard value has been set to 10.

A further option in the case of a locked user is an explicit lock as follows:

ALTER USER <username> ACCOUNT LOCK;

Solution

To prevent ORA-28000 errors, and avoid similar problems in future, be aware of the following:

• If you use Oracle 9i or earlier, do not manually restrict the authorizations of the default profile (also refer to Note 700548 (16)).

• If FAILED_LOGIN_ATTEMPTS is not set to UNLIMITED, you can adjust it as follows:
  
  ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

           This is also possible for Oracle 10. 1 and earlier and Oracle 10.2 and later.

• If a user is already locked, you can unlock it as follows:
  
  ALTER USER <username> ACCOUNT UNLOCK;

           If the ORA-28000 error occurred due to a large number of failed logon attempts, you must check why these logon attempts were unsuccessful and returned the ORA-01017 error. The cause is often an incorrect configuration of the OPS$ mechanism. In this case, refer to Note 400241.

  Thanks Yoonus