Ora 28000 the account is locked как снять

ORA-28000: The Account Is Locked When Log In As SYS User Remotely While SYS User Was Locked (Doc ID 1601360.1)

In Oracle RDBMS 11.2.0.4.0, when attempting to connect remotely using sysdba the following may occur:

Changes

Upgrade from an earlier version to patchset version 11.2.0.4.0.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. � Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

How to Resolve ORA-28000: The account is locked

ORA-28000 means that the account that you tried to use is locked because some privileged user disabled the ability of connection, or attempt connections with wrong passwords exceeded the limit.

SQL> conn hr/hr
ERROR:
ORA-28000: The account is locked.

Please note that, schema objects and data of the user can still be used by others, even though the account is locked. In some cases, it could be a good tactic for sharing accounts which do not need logging.

Solution

You can revert the status by unlocking the account. First of all, logging as a privileged user.

SQL> conn / as sysdba
Connected.

And then we can check the status of the account.

SQL> column account_status forma a20;
SQL> select account_status, lock_date from dba_users where username = ‘HR’;

There’re some ways to solve ORA-28000.

ALTER USER ACCOUNT UNLOCK

A simple unlocking can solve ORA-28000 by privileged users. If you can’t do it, please ask DBA to do that.

SQL> alter user hr account unlock;

In reality, DBA may lock some inactive accounts for a period of time before actually dropping them. Therefore, if your account is locked, please inform your DBA as soon as possible.

PASSWORD_LOCK_TIME

PASSWORD_LOCK_TIME means that it is allowable to unlock the account automatically after a period of time, if the account lock was triggered by FAILED_LOGIN_ATTEMPTS .

In such situation, you may set a shorter PASSWORD_LOCK_TIME to release the account lock automatically, say 5 minutes.

SQL> alter profile default limit password_lock_time 5/1440;

In this case, we set the lock time to a shorter period, 5 minutes.

By the way, a locked account which is waiting for automatic unlock has ASTATUS 4.

FAILED_LOGIN_ATTEMPTS

FAILED_LOGIN_ATTEMPTS means that how many times of failed login attempts are allowed before locking the account. Yes, the account will be locked if the number of consecutive failed attempts meets the value.

When ORA-28000 becomes too often, you may also consider to remove the restriction of FAILED_LOGIN_ATTEMPTS by setting it to UNLIMITED .

SQL> alter profile default limit failed_login_attempts unlimited;

Please note that, some native accounts are initially locked by the database to protect them from unauthorized access. This is designed scheme.

For expired password of accounts, you shall see ORA-28001: the password has expired.

ORA-28000: the account is locked error getting frequently

Is this a DB Issue ? Whenever I unlock the user account using the alter SQL query, that is ALTER USER username ACCOUNT UNLOCK , it will be temporarily OK.

Then after sometime the same account gets locked again. The database is using oracle XE version. Does anybody else have the same issue?

8 Answers 8

One of the reasons of your problem could be the password policy you are using.

And if there is no such policy of yours then check your settings for the password properties in the DEFAULT profile with the following query:

And If required, you just need to change the PASSWORD_LIFE_TIME to unlimited with the following query:

How to solve ORA-28000 the account is locked

We often come across the error ORA-28000 the account is locked in day to day activities. This can happened with developers and DBA both.

Reasons

This can happen due to multiple reasons

a. Oracle DBA has purposely locked the account

b. Wrong password has been attempted many times resulting in locking. The number of FAILED_LOGIN_ATTEMPTS can be found using the below query

We can also found where all failed login attempt happened by enabling auditing

Solution

In case this is not happened purposely , we can solve the problem using the below approach accordingly

(1) Unlock the account using below command

Before executing above query make sure reason for the error is sorted out.Otherwise again the error will happen.

(2) If the reason for error is unknown and Till the issue is resolved, we can create another profile with unlimited FAILED_LOGIN_ATTEMPTS

And Assign it to the user which is experiencing the issue

Once the cause is established and we know the source from where invalid password is coming, we can change user profile to previous one and dropped the newly created profile

Hope you like this post on ORA-28000 the account is locked and it will help in your day to day activities. Please do provide feedback on it