EXTREME FAILED LOGINS ATTEMPTS CAUSE HIGH LIBRARY CACHE LOCK WAIT TIME
HOW TO CREATE SIMPLE JAVA PROGRAM FOR QUERING AN ORACLE DATABASE
WHO IS LOCKING MY ORACLE ACCOUNT?
You are observing too high Concurrency wait class. LIBRARY CACHE LOCK event is almost 100% of Concurrency wait class
For instance, ASH viewer is showing the following during the problem
One of the reasons of high concurrency wait class is that a user with a wrong password try to login to the database continuously. Resource name FAILED_LOGIN_ATTEMPTS of a user profile of such a user is set to UNLIMITED limit.
It is related to a database of version 12.1.0.2. Possible other versions are affected as well. But in 19c database the issue does not exist.
SQL> SELECT BANNER FROM V$VERSION WHERE BANNER LIKE '%Database%';
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
1 Create a user for the demo
-- A user profile
CREATE PROFILE DEMO_PROFILE LIMIT
SESSIONS_PER_USER UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED;
-- A user
CREATE USER DEMO IDENTIFIED BY DEMO PROFILE DEMO_PROFILE;
GRANT CONNECT TO DEMO;
-- Profile limits
SET LINES 300
SET PAGES 999
COL PROFILE FOR A20
COL RESOURCE_NAME FOR A25
COL LIMIT FOR A20
SELECT * FROM DBA_PROFILES WHERE PROFILE='DEMO_PROFILE'
ORDER BY LIMIT;
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT COMMON
-------------------- ------------------------- ------------------------ -------------------- ---------
DEMO_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT NO
DEMO_PROFILE CONNECT_TIME KERNEL DEFAULT NO
DEMO_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT NO
DEMO_PROFILE CPU_PER_SESSION KERNEL DEFAULT NO
DEMO_PROFILE CPU_PER_CALL KERNEL DEFAULT NO
DEMO_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT NO
DEMO_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT NO
DEMO_PROFILE IDLE_TIME KERNEL DEFAULT NO
DEMO_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT NO
DEMO_PROFILE PRIVATE_SGA KERNEL DEFAULT NO
DEMO_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT NO
DEMO_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT NO
DEMO_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT NO
DEMO_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT NO
DEMO_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED NO
DEMO_PROFILE SESSIONS_PER_USER KERNEL UNLIMITED NO
16 rows selected.
2 Create a dummy application that will perform continuously logins to a database with a wrong password. See HOW TO CREATE SIMPLE JAVA PROGRAM FOR QUERING AN ORACLE DATABASE for details
// DummyApplication.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;
class DummyApplication
{
public static void main (String args []) throws SQLException
{
OracleDataSource ods = null;
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
// Create DataSource and connect to a local database
ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@//localhost:1521/tst12cr1.vmware.local");
ods.setUser("DEMO");
ods.setPassword("DEMO1");
for (int i = 1; i <= 1000; i++) {
try {
conn = ods.getConnection();
if(conn!=null) conn.close();
} catch(Exception e){ }
}
}
}
Modify the following lines to meet your database connection parameters
ods.setURL("jdbc:oracle:thin:@//hostname:port/service_name");
ods.setUser("YOUR_USERNAME");
ods.setPassword("YOUR_PASSWORD");
3 Compile a program and then run it in parallel
It simulates a situation when there are enormous users with failed logins attempts to a database
## Current value of ORACLE_HOME variable
echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0.2
## All required variables are based on ORACLE_HOME variable
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar:/home/oracle/java_demo
export PATH=$ORACLE_HOME/jdk/bin:$PATH
## Compile a dummy program
javac DummyApplication.java
## Run a dummy program in parallel
for i in {1..30}; do
java DummyApplication &
done
You will have 30 background jobs running a dummy application. To Kill all background jobs run the following command in the same terminal
kill $(jobs -p)
4 In a minute confirm that concurrency wait class has become the top one by the following query
BREAK ON SNAP_TIME
SET LINES 300
SET PAGES 999
COL SNAP_TIME FOR A20
COL AVERAGE_WAITER_COUNT FOR 999990D0
COL PCT_OF_DBTIME FOR 990D0
COL WAIT_CLASS FOR A20
COL AVG_TIME_WAITED_MS FOR 999990D0
WITH WAIT_CLASSES AS (
SELECT DISTINCT WAIT_CLASS,WAIT_CLASS# FROM V$EVENT_NAME
)
SELECT TO_CHAR(WCM.END_TIME,'DD-MON-YYYY HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') SNAP_TIME,
WCS.WAIT_CLASS,
ROUND(WCM.AVERAGE_WAITER_COUNT,1) AVERAGE_WAITER_COUNT,
ROUND(WCM.DBTIME_IN_WAIT,1) PCT_OF_DBTIME,
ROUND(WCM.TIME_WAITED/100) TIME_WAITED_S
FROM
V$WAITCLASSMETRIC WCM,
WAIT_CLASSES WCS
WHERE
WCM.WAIT_CLASS#=WCS.WAIT_CLASS# AND
WCS.WAIT_CLASS != 'Idle'
ORDER BY PCT_OF_DBTIME DESC;
SNAP_TIME WAIT_CLASS AVERAGE_WAITER_COUNT PCT_OF_DBTIME TIME_WAITED_S
-------------------- -------------------- -------------------- ------------- -------------
29-APR-2023 12:55:14 Concurrency 28.6 96.1 1700
Other 1.0 3.4 59
System I/O 0.0 0.1 1
Queueing 0.0 0.0 0
Network 0.0 0.0 0
User I/O 0.0 0.0 0
Scheduler 0.0 0.0 0
Cluster 0.0 0.0 0
Administrative 0.0 0.0 0
Configuration 0.0 0.0 0
Application 0.0 0.0 0
Commit 0.0 0.0 0
12 rows selected.
Some time later you can observe in ASH viewer something like depicted in following pictures
5 If you have auditing enabled then confirm existence of failed logins attempts to a database. See WHO IS LOCKING MY ORACLE ACCOUNT? to know how to enable auditing.
-- This will display all failed logins attempts for the last hour
SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL OS_USERNAME FOR A11
COL DB_USERNAME FOR A10
COL CONNECTION_FROM FOR A30
COL ACTION_NAME FOR A10
COL OS_PROCESS FOR A10
COL TIMESTAMP FOR A20
SELECT INSTANCE_NUMBER INUM,
OS_USERNAME,
USERNAME DB_USERNAME,
USERHOST CONNECTION_FROM,
RETURNCODE,
COUNT(RETURNCODE) FAILED_ATTEMPTS#
FROM
DBA_AUDIT_SESSION
WHERE
ACTION_NAME = 'LOGON' AND
RETURNCODE IN (1017, 28000) AND
EXTENDED_TIMESTAMP > SYSDATE - 1/24
GROUP BY
INSTANCE_NUMBER,
OS_USERNAME,
USERNAME,
USERHOST,
RETURNCODE; INUM OS_USERNAME DB_USERNAM CONNECTION_FROM RETURNCODE FAILED_ATTEMPTS#
----- ----------- ---------- ------------------------------ ---------- ----------------
0 oracle DEMO dbpilot.vmware.local 1017 2132
That's it. By enormous failed logins attempts we've simulated a situation that causes high concurrency wait class in a database. Library cache lock event is top consumer of the concurrency wait class in this case.
6 Drop DEMO user and remove created files
## kill $(jobs -p)
-- DROP USER DEMO CASCADE;
## rm DummyApplication.java DummyApplication.class
