签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c Last Successful Logon Time (LSLT) 特性说明

2024-02-04 14:45 340 0 原创 Oracle 19c
作者: dave

1 LSLT特性说明

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

http://www.cndba.cn/dave/article/131510
http://www.cndba.cn/dave/article/131510
http://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

[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:

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

bug 的描述可以参考官网的说明:

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 特性来解决问题。

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

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/131510http://www.cndba.cn/dave/article/131510

http://www.cndba.cn/dave/article/131510
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

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

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2237
    原创
  • 2
    翻译
  • 535
    转载
  • 185
    评论
  • 访问:6531568次
  • 积分:4229
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ