Diagnosis and handling of frequent lock (ORA-28000) for Oracle10g users

First, I tried to log on to PLSQL. I want to query the Compilation Time in the table below the HOEGH user and check the specific time for executing the imp operation. Surprisingly, when using PLSQL to log on

First, I tried to log on to PLSQL. I want to query the Compilation Time in the table below the HOEGH user and check the specific time for executing the imp operation. Surprisingly, when using PLSQL to log on

My colleague asked me for help this morning, saying that the data of a user (we assume the user name is HOEGH) is not up-to-date. It is strange that I used the exp and imp scripts to update related user data last Friday. It must include HOEGH users. My colleague started the application and pointed to the program interface to tell me that the data is definitely not up-to-date. Well, didn't I miss the HOEGH user when I updated the data?

First, I tried to log on to PLSQL. I want to query the Compilation Time in the table below the HOEGH user and check the specific time for executing the imp operation. Unexpectedly, an error is reported when you log on using PLSQL, prompting "ORA-28000: the account is locked ".

A little dizzy. Who moved the HOEGH user?

I log on to the sys user and use the following SQL statement to query the account status and lock time of the HOEGH user.

SQL> select username, account_status, to_char (lock_date, 'yyyymmdd hh24: mi: ss') from dba_users where username = 'hoegh ';

USERNAME ACCOUNT_STATUS TO_CHAR (LOCK_DATE

-------------------------------------------------------------------------------

Hoegh locked 20150817 08:57:54

SQL>

From the query results, the HOEGH user is indeed locked, and the lock operation takes place a few minutes ago.

Although I haven't figured out what's going on, I should solve the problem first. I used the "alter user HOEGH account unlock" statement to unlock the HOEGH user, and then logged on to PLSQL to query the Compilation Time of the table below the HOEGH user. The result shows that no HOEGH user is missing when I perform data update on Friday. The compilation time for all tables is last Friday.

I suspect that the version of the program just started is incorrect. As a result, I asked my colleagues to re-run the application. The displayed interface is still not the latest data, and the problem persists.

Okay, it's important to solve the problem! I re-run the import script. The script first truncate all the tables and then execute imp to import data. Did not expect an error again when executing the imp operation, prompting "ORA-28000: the account is locked ".

The HOEGH user is locked, again!

I don't want to understand.

Use the "alter user HOEGH account unlock" statement again to unlock the HOEGH user and re-execute the import script. The data is successfully imported this time.

It should be okay this time. Let colleagues start the application for the third time. the problem persists !!!

Generally, the program will not go wrong and there must be other problems.

Sure enough, after a while, my colleague told me that the password was accidentally incorrect and changed to OK.

I did not think of this reason. I just figured out the problem. Everyone has a nap.

But why are HOEGH users locked one by one? It is estimated that it is also related to the wrong password. The most direct is the security policy of the Oracle database. Therefore, I execute the following statement to query:

SQL>

SQL> select * from dba_profiles where resource_name like 'failed _ LOGIN_ATTEMPTS % ';

PROFILE RESOURCE_NAME RESOURCE LIMIT

------------------------------------------------------------------------------------------------------

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

SQL>

Among them, the FAILED_LOGIN_ATTEMPTS parameter is set to 10, that is, when the wrong password is entered more than 10 times, the user will be automatically locked. Based on our application design philosophy, it regularly connects to the database to monitor the database connection status. The cause of this error is that the client does not use the correct password, the number of connections exceeds the size of the FAILED_LOGIN_ATTEMPTS parameter defined in the default security policy of the database. As to why old version data is always displayed, it is because the Oracle database cannot be connected and local data cannot be updated. The interface shows that the local data of the old version has to be read.
In addition, in Oracle10g, the default value of the FAILED_LOGIN_ATTEMPTS parameter is 10. This setting is actually risky. If a user keeps trying wrong passwords, the user will be locked. If you want to restore without restrictions, you can use one command to solve the problem:
Alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

The following are some inspirations for resolving this fault today:
1. be sure to be careful and seize all the resources available for trouble-shooting; because our application interface prompts the database connection status (small words ), at that time, I ignored it;
2. Standardize project development and management to reduce the probability of errors in similar configuration files;

This article permanently updates the link address: