ORA-28000: the account is locked user locking troubleshooting

ORA-28000: the account is locked user locking troubleshooting

Today, my colleague reflected a problem, a test database modified the password, and changed the password used by the relevant application, still appear after a while the account is locked, reported ORA-28000: the account is locked.
 
Check process:
1. view the resource limit effective parameters
SQL> show parameter resource
NAME TYPE VALUE
-----------------------------------------------------------------------------
Resource_limit boolean FALSE
FALSE indicates that the resource limit is not enabled.
 
2. view the PROFILE used by the user
SQL> select resource_name, limit from dba_profiles where profile = 'default ';
RESOURCE_NAME LIMIT
------------------------------------------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
FAILED_LOGIN_ATTEMPTS indicates the number of consecutive logon failures. Here, the user is locked if 10 consecutive logon failures occur.
 
3. unlock alter user pss3 account unlock; then observe the phenomenon
SQL> select name, lcount from user $ where name = 'ss3 ';
NAME LCOUNT
----------------------------------------
PSS3 10
In less than one minute, the number of Logon failures will reach 10.
 
Preliminary conclusion:
Some applications may still log on with the old password. If the login fails, repeat the attempt until 10 times.
 
But the question is:
1. FAILED_LOGIN_ATTEMPTS is set to 10 times, but resource_limit is not started. Why is it restricted by 10 times?
2. How do I know which other applications cause ORA errors due to unchanged password?
 
Question 1: FAILED_LOGIN_ATTEMPTS is set to 10 times, but resource_limit is not started. Why is it restricted by 10 times?
This MOS Article 160528.1 (Profile Limits (Resource Parameter (s) Are Not Enforced/Do Not Work) describes some of the following:
After creating a new profile or altering an old one to limit the following profile resources there is no change:
SESSIONS_PER_USER
CPU_PER_SESSION
CPU_PER_CALL
CONNECT_TIME
IDLE_TIME
LOGICAL_READS_PER_SESSION
COMPOSITE_LIMIT
PRIVATE_SGA
The resource usage limits are not enforced and the users that are assigned the profile continue to use resources beyond profile's limits.
CAUSE
 
The initialization parameter RESOURCE_LIMIT is set to FALSE (default ).
 
Because RESOURCE_LIMIT is not set to TRUE, the above variable does not take effect after modification.
 
FAILED_LOGIN_ATTEMPTS is not mentioned here. In other words, variables such as FAILED_LOGIN_ATTEMPTS are not restricted by the RESOURCE_LIMIT parameter. Then, the variables such as limit belong to user password management, and those variables belong to resource management, it is suggested that the RESOURCE_LIMIT parameter must be set to TRUE for Oracle's resource management restrictions. The restrictions on password management are not affected by RESOURCE_LIMIT.
 
"Profiles are a useful way of managing passwords and resources but can really only apply in an environment where every application user has their own database user account. "Note that he divided the profile into two categories: password management and resource management. Although it is not clearly stated, the above two sections are used for reference and the problems encountered in the above two sections, I believe that the restrictions on password management are not affected by the RESOURCE_LIMIT parameter.
 
Question 2: How do I know which other applications cause ORA errors due to unchanged password?
After the UNLOCK account was attempted, the LCOUNT logon failure times in less than one minute reached 10, which indicates that the application frequently retried the password during this time. Further, if we can find the IP address used to access the database during this time, then, filter out the possible IP address and password modification applications and find the "culprit ".
To find the IP address of the database, you can set the listening log to find the IP address.
Listener logs are similar to alert logs. The default log Path is $ ORACLE_HOME/network/log/listener. log, but I used 11 GB. I don't know if I have modified it. I didn't find this directory. As for how to find it, we will talk about it later.
Follow the @ secooler tutorial to enable listener logs in two ways:
1. You can set the log_status parameter to off without restarting the listener.
2. Add the LOGGING _ <listener_name> = OFF parameter to the listener. ora file, and restart the listener to implement
You can select different methods based on your needs.
Here I select the first type. Execute lsnrctl and then execute set log_status on. Then you need to find the Log Path:
Ora11g @ vm-kvm-ora $ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0-Production on 20-AUG-2014 11:56:27
Copyright (c) 1991,201 1, Oracle. All rights reserved.
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0-Production
Start Date 30-APR-2014 15:22:19
Uptime 111 days 20 hr. 34 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/oracle/ora11g/product/11.2.0/network/admin/listener. ora
Listener Log File/oracle/ora11g/diag/tnslsnr/vm-kvm-ora/listener/alert/log. xml
Here we can see a message Listener Log File, followed by the corresponding Log Path and Log File name.
The xml format of log. xml is used in the 11g to record listening logs.
The content is similar:
<Txt> 20-AUG-2014 10:07:30 * (CONNECT_DATA = (SERVICE_NAME = pss3) (CID = (PROGRAM = dcs_data_sync) (HOST = v490h4-tux-t) (USER = dcsopen ))) * (ADDRESS = (PROTOCOL = tcp) (HOST = x. x. x. x) (PORT = 37339) * establish * pss3 * 0
</Txt>
Therefore, you only need to find the IP address that is still logged on after unlocking the user, and then filter out the possible applications.
 
Here is another knowledge point, that is, whether the FAILED_LOGIN_ATTEMPTS sets the number of consecutive logon failures or the cumulative number of Logon failures?
FAILED_LOGIN_ATTEMPTS indicates the number of consecutive logon failures.

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian