1 LSLT特性说明

Oracle 从 12c 开始引入了Last Successful Logon Time (LSLT) 特性， 该特性就是记录用户最后一次成功登录的时间。可以通过dba_users 表的last_login 列查看这个时间。 http://www.cndba.cn/dave/article/131510http://www.cndba.cn/dave/article/131510

How to find the Last login date time for Database users ? (Doc ID 2185763.1)

http://www.cndba.cn/dave/article/131510

http://www.cndba.cn/dave/article/131510

[dave@www.cndba.cn ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 4 14:27:33 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

SQL> col username for a30
SQL> col last_login for a40
SQL> set lines 150
SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';
Session altered.

SQL> select username,last_login from dba_users where last_login is not null order by last_login;

USERNAME                       LAST_LOGIN
------------------------------ ----------------------------------------
JJ                             21.11.2019 08:52:10
AAA                            05.12.2019 08:38:27
EXPORT                         30.06.2021 04:58:34
ALTER_SALE                     10.01.2022 14:57:05
ICDREAD                        29.12.2023 17:04:52
EMPUSER                        01.01.2024 07:08:29
AUDIT_DEV                      24.01.2024 09:00:50
ELITE_HR                       29.01.2024 08:40:21
ELITE_READ                     29.01.2024 09:06:03
PAASDBA                        04.02.2024 08:30:01
SYSTEM                         04.02.2024 09:00:38
ELITE_IFLY                     04.02.2024 14:30:56
ELITE                          04.02.2024 14:31:10
DBSNMP                         04.02.2024 14:31:11

Oracle 从 12.1 开始引入该特性，但是直到 19.16 版本，一直都存在一个 bug：http://www.cndba.cn/dave/article/131510

Bug 33121934 Library cache lock / load lock / mutex x during connection storm due to update user$

http://www.cndba.cn/dave/article/131510

http://www.cndba.cn/dave/article/131510

bug 的描述可以参考官网的说明：http://www.cndba.cn/dave/article/131510http://www.cndba.cn/dave/article/131510

Concurrent logons for user may be affected due to Last Successful Logon Time (LSLT) update:

update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
(sql_id = 9zg9qd9bm4spu)

REDISCOVERY INFORMATION:
 If the concurrent logons for a user may be slow when the system is under
 heavy load and/or logon storm happens, all waiting for the Last Successful
 Logon Time (LSLT) update to happen, this bug may have been rediscovered.

During a connection storm you may see wait events like:
  - library cache: mutex X
  - library cache load lock
  - library cache lock
  - gc buffer busy acquire

The fix introduces the parameter _disable_last_successful_login_time.
 Set it to TRUE to turn off LSLT.

2 关闭LSLT特性

所以如果是 Oracle 19.16 以下的版本，在并发较高的情况，如果触发了该 bug，那么可以通过关闭 LSLT 特性来解决问题。

SQL> alter system set "_disable_last_successful_login_time"=TRUE scope=spfile;
System altered.
SQL> col PARAMNAME for a50
SQL> col SESSIONVALUE for a15
SQL> col INSTANCEVALUE for a15
SQL> set lines 190
SQL> select paramName,SessionValue,InstanceValue from hparams where paramname='_disable_last_successful_login_time';

PARAMNAME                                          SESSIONVALUE    INSTANCEVALUE
-------------------------------------------------- --------------- ---------------
_disable_last_successful_login_time                FALSE           FALSE

在 19.22 中是可以动态修改的，其他版本如果不能动态修改，可以先修改 spfile，在重启 DB 生效：http://www.cndba.cn/dave/article/131510

SQL> alter system set "_disable_last_successful_login_time"=TRUE scope=both;
System altered.
SQL> select paramName,SessionValue,InstanceValue from hparams where paramname='_disable_last_successful_login_time';

PARAMNAME                                          SESSIONVALUE    INSTANCEVALUE
-------------------------------------------------- --------------- ---------------
_disable_last_successful_login_time                TRUE            TRUE

版权声明：本文为博主原创文章，未经博主允许不得转载。