标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,679)
- DB2 (22)
- MySQL (73)
- Oracle (1,541)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (563)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
标签归档:enq: TX – allocate ITL entry
模拟enq: TX – allocate ITL entry等待
今天在分析一份awr中发现了较为明显的enq: TX – allocate ITL entry等待,这里通过试验详细重现了enq: TX – allocate ITL entry等待
创建测试对象
SQL> create table t_xifenfei (name char(2000)) pctfree 0 initrans 1; Table created. SQL> insert into t_xifenfei select object_name from all_objects where rownum < 5; 4 rows created. SQL> commit; Commit complete. SQL> alter system flush buffer_cache; System altered. SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) block# from t_xifenfei; FILE# BLOCK# ---------- ---------- 4 32
bbed查看block
BBED> set block 32 BLOCK# 32 BBED> map File: /u01/oracle/oradata/XFF/users01.dbf (0) Block: 32 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @100 struct kdbt[1], 4 bytes @114 sb2 kdbr[4] @118 ub1 freespace[38] @126 --该block空闲空间为38byte ub1 rowdata[8024] @164 ub4 tailchk @8188 BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000d318 ub4 ktbbhod1 @24 0x0000d318 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0xc0320e3b ub2 kscnwrp @32 0x0b2c b2 ktbbhict @36 2 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01000019 struct ktbbhitl[0], 24 bytes @44 --1个itl slot为24byte struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0015 ub2 kxidslt @46 0x0019 ub4 kxidsqn @48 0x00000005 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x0080009d ub2 kubaseq @56 0x0002 ub1 kubarec @58 0x28 ub2 ktbitflg @60 0x2004 (KTBFUPB) union _ktbitun, 2 bytes @62 b2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0xc0320e4e struct ktbbhitl[1], 24 bytes @68 --有两个itl slot struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0000 ub2 kxidslt @70 0x0000 ub4 kxidsqn @72 0x00000000 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00000000 ub2 kubaseq @80 0x0000 ub1 kubarec @82 0x00 ub2 ktbitflg @84 0x0000 (NONE) union _ktbitun, 2 bytes @86 b2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000
通过bbed我们可以得出如下结论:
1.该block剩余38 byte 空闲空间可以用来存放数据
2.该block 初始化 itl 为2(和我们设置的1不相符)
3.一个itl slot为24byte
更新表记录
--session 1 SQL> select trim(name) from t_xifenfei; TRIM(NAME) -------------------------------------------------------------------------------- ICOL$ I_USER1 CON$ UNDO$ SQL> update t_xifenfei set name='WWW.XIFENFEI.COM' WHERE name='ICOL$'; 1 row updated. --session 2 SQL> update t_xifenfei set name='www.orasos.com' where name='UNDO$'; 1 row updated. --session 3 SQL> update t_xifenfei set name='www.xifenfei.com' where name='CON$'; 1 row updated. --session 4 SQL> update t_xifenfei set name='www.xifenfei.com' where name='I_USER1'; --hang住 --session 5 SQL> select event from v$session where event like 'enq%'; EVENT ---------------------------------------------------------------- enq: TX - allocate ITL entry
通过这里可以看到我们模拟了4个update 该block操作(均未提交),前面三个可以正常的update操作,第四个出现了enq: TX – allocate ITL entry等待,根据我们知识分析(未提交事务的itl不能覆盖,一个dml操作需要一个itl),这里使用了3个itl slot,而我们已经知道一个itl 需要24byte,该block初始化有2个itl,现在这里有3个dml操作成功,即占用了3个itl,所以该block的剩余空间只有38-24=14 byte<24byte,因此无法分配第四个itl slot从而出现了enq: TX - allocate ITL entry等待
bbed验证上述分析
BBED> map File: /u01/oracle/oradata/XFF/users01.dbf (0) Block: 32 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 96 bytes @20 struct kdbh, 14 bytes @124 struct kdbt[1], 4 bytes @138 sb2 kdbr[4] @142 ub1 freespace[14] @150 ub1 rowdata[8024] @164 ub4 tailchk @8188 BBED> p ktbbh struct ktbbh, 96 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000d318 ub4 ktbbhod1 @24 0x0000d318 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0xc0320eb0 ub2 kscnwrp @32 0x0b2c b2 ktbbhict @36 3 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01000019 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0003 ub2 kxidslt @46 0x001f ub4 kxidsqn @48 0x00000208 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00800027 ub2 kubaseq @56 0x0414 ub1 kubarec @58 0x01 ub2 ktbitflg @60 0x0001 (NONE) union _ktbitun, 2 bytes @62 b2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00000000 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x000a ub2 kxidslt @70 0x000f ub4 kxidsqn @72 0x00000185 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x0080008a ub2 kubaseq @80 0x01a6 ub1 kubarec @82 0x0c ub2 ktbitflg @84 0x0001 (NONE) union _ktbitun, 2 bytes @86 b2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000 struct ktbbhitl[2], 24 bytes @92 struct ktbitxid, 8 bytes @92 ub2 kxidusn @92 0x0008 ub2 kxidslt @94 0x002a ub4 kxidsqn @96 0x00000217 struct ktbituba, 8 bytes @100 ub4 kubadba @100 0x008000cc ub2 kubaseq @104 0x0291 ub1 kubarec @106 0x12 ub2 ktbitflg @108 0x0001 (NONE) union _ktbitun, 2 bytes @110 b2 _ktbitfsc @110 0 ub2 _ktbitwrp @110 0x0000 ub4 ktbitbas @112 0x00000000
可以看到剩余空间为14byte,事务槽为3个,因此上述分析为正确。
提交会话测试
--session 1 SQL> commit; Commit complete. --session 4 SQL> update t_xifenfei set name='www.xifenfei.com' where name='I_USER1'; 1 row updated.
证明commit掉事务后,itl slot可以重利用
总结说明
enq: TX – allocate ITL entry为分配ITL条目的等待,因为PCTFREE不足,BLOCK中没有足够空间分配ITL,ORACLE只能重用ITL,但是这个时候由于没有COMMIT,无法重用ITL,所以会出现allocate ITL等待事件。要解决此类问题,我们可以考虑增加PCTFREE和initrans大小,需要注意该修改只能对于新block生效,已经存放数据的block不会发生改变.另外可以考虑修改业务逻辑,减少频繁访问