[20211115]12c以上版本Last Login Time 引發的故障.txt
[20211115]12c以上版本Last Login Time 引發的故障.txt
--//昨天看了連結 12c New Feature: Last Login Time 引發的故障.
--//我一直認為oracle這個功能是不好的設計，大量密集的使用者登陸會導致buffer busy waits。
--//對方遇到的問題就是登陸出現阻塞，原因是某個使用者登陸後修改user$表，但是機器掛了，導致後續的使用者無法登陸，出現library
--//cache lock。我在測試環境模擬看看，順便測試wcx.sql指令碼。
1.環境：
XXXXX> @ ver1
XXXXX> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 18.0.0.0.0
BANNER : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.測試：
--//注意修改後不提交：
XXXXX> select user#,name,SPARE6 from user$ where name='TTT';
USER# NAME SPARE6
---------- -------------------- --------------------
105 TTT 2021-11-11 09:01:59
--//9zg9qd9bm4spu
XXXXX> @ tpt/sql_id 9zg9qd9bm4spu %
Show SQL text, child cursors and execution stats for SQLID 9zg9qd9bm4spu child %
HASH_VALUE CH# SQL_TEXT
---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
1462919866 0 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
CH# PARENT_HANDLE OBJECT_HANDLE PLAN_HASH PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED CPU_MS ELA_MS LIOS PIOS SORTS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
0 0000000069ABDA38 0000000108D6A758 2447725225 2 2 2 0 2 5.534 16.897 13 1 0 0
XXXXX> update user$ set spare6=DECODE(to_char(sysdate, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), sysdate) where user#=105;
1 row updated.
--//其他使用者登陸:
$ seq 10 | xargs -P 10 -IQ bash -c "sqlplus -s -l ttt/oracle@orcl <<<exit"
XXXXXcdb> @ wcx sysdate-1/1440 sysdate
-- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS #Blkrs WAIT_CHAIN FIRST_SEEN LAST_SEEN
------ ---------- ---- ------ -------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- --------------------
10% 9 0 1 -> 38,20451,@1=>294,10284,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
10% 9 0 1 -> 38,20451,@1=>35,46402,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
10% 9 0 1 -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
10% 9 0 1 -> 38,20451,@1=>285,21962,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
10% 9 0 1 -> 38,20451,@1=>409,35152,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
10% 9 0 1 -> 38,20451,@1=>286,53111,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
10% 9 0 1 -> 38,20451,@1=>164,47506,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
10% 9 0 1 -> 38,20451,@1=>27,25128,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
10% 9 0 1 -> 38,20451,@1=>151,9063,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
10% 9 0 1 -> 38,20451,@1=>282,44344,@1=>library cache lock -> 276,37682,@1=>38,20451,@1=>enq: TX - row lock contention -> [idle blocker 1,276,37682 (sqlplus.exe)] 2021-11-15 09:43:57 2021-11-15 09:44:05
2% 2 0 1 -> ,,@=>421,12408,@1=> 2021-11-15 09:43:09 2021-11-15 09:43:24
1% 1 0 1 -> ,,@=>131,65094,@1=>control file parallel write 2021-11-15 09:43:51 2021-11-15 09:43:51
12 rows selected.
--//一樣可以看見library cache lock。阻塞是sid＝276.
XXXXXcdb> @ sid 276
sid = 276
SPID PID SID SERIAL# CLIENT_INFO PNAME TRACEFILE PROGRAM TERMINAL SQL_ID STATUS C50
------ ------- ---------- ---------- -------------------- ------ ----------------------------------------------------------------- ----------- ------------ ------ ---------------- --------------------------------------------------
14690 70 276 37682 /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_1469 sqlplus.exe XXXXXBXX INACTIVE alter system kill session '276,37682' immediate;
0.trc
XXXXXcdb> alter system kill session '276,37682' immediate;
System altered.
XXXXXcdb> @ wcx sysdate-0.1/1440 sysdate
-- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com )
no rows selected
--//問題解決。
XXXXXcdb> @ tpt/sql_id 9zg9qd9bm4spu %
Show SQL text, child cursors and execution stats for SQLID 9zg9qd9bm4spu child %
HASH_VALUE CH# SQL_TEXT
---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
1462919866 0 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
1462919866 1 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
CH# PARENT_HANDLE OBJECT_HANDLE PLAN_HASH PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED CPU_MS ELA_MS LIOS PIOS SORTS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
0 0000000069ABDA38 0000000108D6A758 2447725225 4 2 4 0 4 8.374 18.955 21 1 0 0
1 0000000069ABDA38 0000000101C7AD80 2447725225 21 1 21 0 21 28.689 298151.332 88 0 0 0
$ seq 10 | xargs -P 10 -IQ bash -c "sqlplus -s -l ttt/oracle@orcl <<<exit"
--//OK，問題消失。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2842130/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 12c新特性：Last Successful login timeAST
- [20211013]19C 關於LAST SUCCESSFUL LOGIN TIME.txtAST
- [20190107]12c以上版本配置dg注意.txt
- [20210421]12c以上版本增加欄位與預設值.txt
- [20230308]12c以上版本模糊查詢問題.txt
- 由於版本升級引發的SQL語句故障SQL
- 由於版本升級引發的SQL語句故障（續）SQL
- 【RAC】Oracle 12c以及以上版本的diagsnap是什麼? (Doc ID 2469643.1)Oracle
- 同步故障Last_Errno:1051AST
- remote_listener引發的故障分析REM
- [20230124]12c訪問login.sql指令碼.txtSQL指令碼
- .Net版本引發的血案
- 同步故障Last_Errno:1061AST
- 同步故障Last_Errno: 1091AST
- library cache: mutex X引發的故障Mutex
- SYN flooding引發的網路故障
- 關於DBA_OBJECTS的LAST_DDL_TIMEObjectAST
- PG: Estimate last modified or read time of a tableAST
- 由OGG引發的資料庫故障資料庫
- hp vg引發的資料庫故障(zt)資料庫
- oracle 12C以上 版本資料庫訪問 ORA-28040 ORA-03134Oracle資料庫
- 【故障公告】部落格系統升級到 .NET 5.0 引發的故障
- Oracle 12C新特性-資料泵新引數(LOGTIME)Oracle
- oracle集合型別的first、next、prior、last方法.TXTOracle型別AST
- REMOTE_LOGIN_ PASSWORDFILE的引數設定REM
- Oracle in子句過多的硬編碼引發的故障Oracle
- Remote_login_passwordfile引數探究REM
- mysqldump --login-path引數使用MySql
- 新發布GoldenGate 12c版本中的主要特性Go
- Maven依賴版本號引發的血案Maven
- [20191106]12c DCD SQLNET.EXPIRE_TIME.txtSQL
- flask_login模組中user_loader裝飾器引發的思考Flask
- Oracle DBLink bug引發的故障（Session Hang Memory leak）OracleSession
- v$session中LAST_CALL_ET引數的理解SessionAST
- 關於REMOTE_LOGIN_PASSWORDFILE引數REM
- 【故障公告】資料庫伺服器 CPU 100% 引發全站故障資料庫伺服器
- 【故障公告】redis 伺服器當機引發部落格站點故障Redis伺服器
- 12C SQL Translation Framework.txtSQLFramework