[20210316] Lck 3.txt of MSSM table space block ITL
–//Previous tests, link: http://blog.itpub.net/267265/viewspace-2564734/= >[20190125] lck.txt of MSSM table space block ITL
–//Ora-04000 the sum of pctused and pctfree cannot exceed100, link: http://blog.itpub.net/267265/viewspace-2762819/
–//Think about whether it is related to this problem? This is also the difference between MSSM and ASSM. I decided to test it.
1. Environment:
[email protected]> @ ver1
PORT_STRING VERSION BANNER
—————————— ————– ——————————————————————————–
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
CREATE TABLESPACE MSSM DATAFILE
‘/mnt/ramdisk/book/mssm01.dbf’ SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
2. Test:
[email protected]> create table t tablespace mssm pctfree 99 pctused 1 as select level id, rpad(level, 3500, ‘X’) vc from dual connect by level <= 1;
Table created.
–//Note: the default is pctfree = 10, pctused = 40
[email protected]> @ desc t
Name Null? Type
—– ——– —————————-
ID NUMBER
VC VARCHAR2(4000)
[email protected]> select rowid ,id,substr(vc,1,2) from t;
ROWID ID SUBS
—————— ———- —-
AAAWMkAAHAAAACBAAA 1 1X
[email protected]> @ rowid AAAWMkAAHAAAACBAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
———- ———- ———- ———- ——————– ——————– —————————————-
90916 7 129 0 0x1C00081 7,129 alter system dump datafile 7 block 129 ;
[email protected]> delete from t where id=1;
1 row deleted.
[email protected]> commit ;
Commit complete.
[email protected]> alter system flush buffer_cache ;
System altered.
[email protected]> alter system dump datafile 7 block 129 ;
System altered.
–//Check the dump and find that:
Block header dump: 0x01c00081
Object id on Block? Y
seg/obj: 0x16324 csc: 0x03.177d9219 itc: 3 flg: O typ: 1 – DATA
fsl: 2 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.177d9219
0x02 0x000a.00b.00005d86 0x00c00b60.11db.0b –U- 2 fsc 0x0db3.177d9231
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
–//In fact, one record is deleted, while the Lck record with ITL = 0x02 is 2. This is the case I encountered before.
3. Continue testing:
–//drop table t purge;
[email protected]> create table t tablespace mssm pctfree 40 pctused 1 as select level id, rpad(‘X’, 2000, ‘X’) vc from dual connect by level <= 4;
Table created.
[email protected]> @ desc t
Name Null? Type
—– ——– —————————-
ID NUMBER
VC VARCHAR2(2000)
[email protected]> select rowid ,id,substr(vc,1,2) from t;
ROWID ID SUBS
—————— ———- —-
AAAWMnAAHAAAACBAAA 1 XX
AAAWMnAAHAAAACBAAB 2 XX
AAAWMnAAHAAAACCAAA 3 XX
AAAWMnAAHAAAACCAAB 4 XX
[email protected]> delete from t where id=1;
1 row deleted.
[email protected]> commit ;
Commit complete.
[email protected]> alter system flush buffer_cache ;
System altered.
[email protected]> alter system dump datafile 7 block 129 ;
System altered.
–//Check the dump and find that:
Block header dump: 0x01c00081
Object id on Block? Y
seg/obj: 0x16327 csc: 0x03.177d9893 itc: 3 flg: – typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.177d9893
0x02 0x000a.00f.00005d89 0x00c00b6c.11db.0a –U- 1 fsc 0x07d7.177d98a6
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
–//Notice the underline Lck = 1 instead of 2
[email protected]> delete from t where id=2;
1 row deleted.
[email protected]> commit ;
Commit complete.
[email protected]> alter system flush buffer_cache ;
System altered.
[email protected]> alter system dump datafile 7 block 129 ;
System altered.
–//Check the dump and find that:
Block header dump: 0x01c00081
Object id on Block? Y
seg/obj: 0x16327 csc: 0x03.177d9893 itc: 3 flg: O typ: 1 – DATA
fsl: 3 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.177d9893
0x02 0x000a.00f.00005d89 0x00c00b6c.11db.0a –U- 1 fsc 0x07d7.177d98a6
0x03 0x000a.011.00005cd6 0x00c00b6b.11db.1e –U- 2 fsc 0x07d7.177d9977
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
–//It can be found that this deletion is the Lck of the corresponding transaction slot = 2. That is, the Lck generated by multiple transactions is related to the size of pctuser.
–//When I delete a record, it does not satisfy the condition of pctused = 1. When I delete a record again, there is no record in the block, so it must satisfy the condition of pctused = 1.
–//In this way, I can identify that the block can be put into use again. I’m not very clear about some details. I still don’t know where to reflect such an extra transaction.
4. My inference can be verified as follows:
–//drop table t purge;
[email protected]> create table t tablespace mssm pctfree 40 pctused 60 as select level id, rpad(‘X’, 2000, ‘X’) vc from dual connect by level <= 4;
Table created.
–//Note: pctfree = 40, pctused = 60. In this way, even if I delete a record, I will meet the trigger condition (pctused = 60)
[email protected]> select rowid ,id,substr(vc,1,2) from t;
ROWID ID SUBS
—————— ———- —-
AAAWMoAAHAAAACBAAA 1 XX
AAAWMoAAHAAAACBAAB 2 XX
AAAWMoAAHAAAACCAAA 3 XX
AAAWMoAAHAAAACCAAB 4 XX
[email protected]> delete from t where id=2;
1 row deleted.
[email protected]> @ xid
XIDUSN_XIDSLOT_XIDSQN
——————————
6.11.2062
[email protected]> commit ;
Commit complete.
[email protected]> alter system flush buffer_cache ;
System altered.
[email protected]> alter system dump datafile 7 block 129 ;
System altered.
–//Check the dump and find that:
Block header dump: 0x01c00081
Object id on Block? Y
seg/obj: 0x16328 csc: 0x03.177d9bae itc: 3 flg: O typ: 1 – DATA
fsl: 2 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.177d9bae
0x02 0x0006.00b.0000080e 0x00c009bb.03c4.14 –U- 2 fsc 0x07d7.177d9c1f
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
–//Continue to delete:
[email protected]> delete from t where id=1;
1 row deleted.
[email protected]> commit ;
Commit complete.
[email protected]> alter system checkpoint ;
System altered.
[email protected]> alter system dump datafile 7 block 129 ;
System altered.
–//Check the dump and find that:
Block header dump: 0x01c00081
Object id on Block? Y
seg/obj: 0x16328 csc: 0x03.177d9bae itc: 3 flg: O typ: 1 – DATA
fsl: 2 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.177d9bae
0x02 0x0006.00b.0000080e 0x00c009bb.03c4.14 –U- 2 fsc 0x07d7.177d9c1f
0x03 0x000a.007.00005d7c 0x00c00b6f.11db.0c –U- 1 fsc 0x07d7.177d9c92
–//That is to say, one test. That’s what happened in my previous test.
5. The number of Lck can be controlled by controlling the size of pctused.
Otherwise, if the DML of the transaction does not meet the pctused condition, the Lck will not be increased by 1 on the basis of the original transaction.
[email protected]> alter table t pctused 10;
Table altered.
[email protected]> delete from t where id=3;
1 row deleted.
[email protected]> @ xid
XIDUSN_XIDSLOT_XIDSQN
——————————
5.2.1925
[email protected]> commit ;
Commit complete.
[email protected]> alter system checkpoint ;
System altered.
[email protected]> alter system dump datafile 7 block 130 ;
System altered.
–//Check the dump and find that:
Block header dump: 0x01c00082
Object id on Block? Y
seg/obj: 0x16328 csc: 0x03.177d9bae itc: 3 flg: – typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.177d9bae
0x02 0x0005.002.00000785 0x00c000c7.0547.19 –U- 1 fsc 0x07d7.177d9f0c
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
–//This also verifies my judgment again, but there is one thing I have never understood. How does Oracle know there is another transaction? For example, I delete id = 4
–//At this time, the corresponding ITL slot Lck record must be 2
[email protected]> delete from t where id=4;
1 row deleted.
[email protected]> commit ;
Commit complete.
[email protected]> alter system checkpoint ;
System altered.
[email protected]> alter system dump datafile 7 block 130 ;
System altered.
–//Check the dump and find that:
Block header dump: 0x01c00082
Object id on Block? Y
seg/obj: 0x16328 csc: 0x03.177d9bae itc: 3 flg: O typ: 1 – DATA
fsl: 3 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.177d9bae
0x02 0x0005.002.00000785 0x00c000c7.0547.19 –U- 1 fsc 0x07d7.177d9f0c
0x03 0x000a.010.00005d8d 0x00c00b70.11db.0c –U- 2 fsc 0x07d7.177da008
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
6. What happens if you modify the ITL slot information through bbed?
BBED> p dba 7,130 ktbbh.ktbbhitl[2]
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x000a
ub2 kxidslt @94 0x0010
ub4 kxidsqn @96 0x00005d8d
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00c00b70
ub2 kubaseq @104 0x11db
ub1 kubarec @106 0x0c
ub2 ktbitflg @108 0x2002 (KTBFUPB)
union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 2007
ub2 _ktbitwrp @110 0x07d7
ub4 ktbitbas @112 0x177da008
BBED> assign dba 7,130 ktbbh.ktbbhitl[2].ktbitflg=0x2001
ub2 ktbitflg @108 0x2001 (KTBFUPB)
–//Note: it is equivalent to modifying Lck = 1
BBED> sum apply
Check value for File 7, Block 130:
current = 0xfae4, required = 0xfae4
BBED> verify
DBVERIFY – Verification starting
FILE = /mnt/ramdisk/book/mssm01.dbf
BLOCK = 130
Block Checking: DBA = 29360258, Block Type = KTB-managed data block
data header at 0x1f41e74
kdbchk: xaction header lock count mismatch
trans=3 ilk=1 nlo=2
Block 130 failed with check code 6108
–//You can find that the check is wrong. How can Oracle check that Lck should be 2? What or identifier in the block reflects such a change?
–//Is it at DBA = 7128.
BBED> p dba 7,128 ktsfs_txn[0]
struct ktsfs_txn[0], 20 bytes @4176
ub2 ktsfsflg @4176 0x0001 (KTSUSED)
struct ktsfsxid, 8 bytes @4180
ub2 kxidusn @4180 0x0006
ub2 kxidslt @4182 0x000b
ub4 kxidsqn @4184 0x0000080e
ub4 ktsfslhd @4188 0x01c00081
ub4 ktsfsltl @4192 0x01c00081
–//0x02 0x0006.00b.0000080e 0x00c009bb.03c4.14 –U- 2 fsc 0x07d7.177d9c1f
–//Note: the transaction slot records of kxidusn, kxidslt and kxidsqn are consistent.
BBED> p dba 7,128 ktsfs_txn[1]
struct ktsfs_txn[1], 20 bytes @4196
ub2 ktsfsflg @4196 0x0001 (KTSUSED)
struct ktsfsxid, 8 bytes @4200
ub2 kxidusn @4200 0x000a
ub2 kxidslt @4202 0x0010
ub4 kxidsqn @4204 0x00005d8d
ub4 ktsfslhd @4208 0x01c00082
ub4 ktsfsltl @4212 0x01c00082
–//0x01c00082 = set dba 7,130 = alter system dump datafile 7 block 130 = 29360258
–//0x03 0x000a.010.00005d8d 0x00c00b70.11db.0c –U- 2 fsc 0x07d7.177da008
–//Note: the transaction slot records of kxidusn, kxidslt and kxidsqn are consistent.
–//Follow up the implementation of bbed
$ ps -ef | grep bbe[d]
oracle 55921 20345 0 Mar15 pts/5 00:00:00 /usr/local/bin/rlwrap -s 9999 -c -r -i -f /usr/local/share/rlwrap/bbed /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/bbed parfile=bbed.par cmdfile=cmd.par
oracle 55922 55921 0 Mar15 pts/10 00:00:00 bbed app/oracle/product/11.2.0.4/dbhome_1/bin/bbed parfile=bbed.par cmdfile=cmd.par
$ ls -l /proc/55922/fd
total 0
lrwx—— 1 oracle oinstall 64 2021-03-16 10:17:51 0 -> /dev/pts/10
lrwx—— 1 oracle oinstall 64 2021-03-16 10:17:51 1 -> /dev/pts/10
lr-x—— 1 oracle oinstall 64 2021-03-16 10:17:51 10 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/mesg/bbedus.msb
lrwx—— 1 oracle oinstall 64 2021-03-16 08:42:11 2 -> /dev/pts/10
lr-x—— 1 oracle oinstall 64 2021-03-16 10:17:51 3 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/mesg/bbedus.msb
lr-x—— 1 oracle oinstall 64 2021-03-16 10:17:51 4 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/oracore/mesg/lrmus.msb
lr-x—— 1 oracle oinstall 64 2021-03-16 10:17:51 5 -> /home/oracle/bbed/filelist.txt
lrwx—— 1 oracle oinstall 64 2021-03-16 10:17:51 6 -> /home/oracle/bbed/bifile.bbd
l-wx—— 1 oracle oinstall 64 2021-03-16 10:17:51 7 -> /home/oracle/bbed/log.bbd
lr-x—— 1 oracle oinstall 64 2021-03-16 10:17:51 8 -> /home/oracle/bbed/cmd.par
lrwx—— 1 oracle oinstall 64 2021-03-16 10:17:51 9 -> /mnt/ramdisk/book/mssm01.dbf
–//File handle 9 corresponds to / MNT / ramdisk / book / mssm01.dbf.
$ strace -f -p 55922 -e read,lseek -o /tmp/bbed.txt
Process 55922 attached – interrupt to quit
^CProcess 55922 detached
$ egrep “lseek.9” /tmp/bbed.txt
55922 lseek(9, 1064960, SEEK_SET) = 1064960
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
55922 lseek(9, 1065984, SEEK_SET) = 1065984
55922 lseek(9, 1067008, SEEK_SET) = 1067008
55922 lseek(9, 1068032, SEEK_SET) = 1068032
55922 lseek(9, 1069056, SEEK_SET) = 1069056
55922 lseek(9, 1070080, SEEK_SET) = 1070080
55922 lseek(9, 1071104, SEEK_SET) = 1071104
55922 lseek(9, 1072128, SEEK_SET) = 1072128
55922 lseek(9, 1064960, SEEK_SET) = 1064960
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
55922 lseek(9, 1065984, SEEK_SET) = 1065984
55922 lseek(9, 1067008, SEEK_SET) = 1067008
55922 lseek(9, 1068032, SEEK_SET) = 1068032
55922 lseek(9, 1069056, SEEK_SET) = 1069056
55922 lseek(9, 1070080, SEEK_SET) = 1070080
55922 lseek(9, 1071104, SEEK_SET) = 1071104
55922 lseek(9, 1072128, SEEK_SET) = 1072128
55922 lseek(9, 1064960, SEEK_SET) = 1064960
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
55922 lseek(9, 1065984, SEEK_SET) = 1065984
55922 lseek(9, 1067008, SEEK_SET) = 1067008
55922 lseek(9, 1068032, SEEK_SET) = 1068032
55922 lseek(9, 1069056, SEEK_SET) = 1069056
55922 lseek(9, 1070080, SEEK_SET) = 1070080
55922 lseek(9, 1071104, SEEK_SET) = 1071104
55922 lseek(9, 1072128, SEEK_SET) = 1072128
–//130*8192 = 1064960, scan DBA = 7130 blocks three times, and do not scan DBA = 7128, how does Oracle check?
–//It was found that only ktbbh.ktbbhflg, ktbbh.ktbbhfsl were possible
BBED> p dba 7,130 ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00016328
ub4 ktbbhod1 @24 0x00016328
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x177d9bae
ub2 kscnwrp @32 0x0003
sb2 ktbbhict @36 3
ub1 ktbbhflg @38 0x03 (KTBFONFL) –>0 = ON THE FREELIST
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub1 ktbbhfsl @39 0x03 –ITL TX FREELIST SLOT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub4 ktbbhfnx @40 0x00000000
BBED> assign dba 7,130 ktbbh.ktbbhfsl=0x0
ub1 ktbbhfsl @39 0x00
BBED> assign dba 7,130 ktbbh.ktbbhflg=0x02
ub1 ktbbhflg @38 0x02 (NONE)
BBED> sum apply
Check value for File 7, Block 130:
current = 0xf9e5, required = 0xf9e5
BBED> verify
DBVERIFY – Verification starting
FILE = /mnt/ramdisk/book/mssm01.dbf
BLOCK = 130
–//Of course, there is no need to modify this way. You only know that there is one more transaction here. You can also do a verification:
BBED> assign dba 7,130 ktbbh.ktbbhitl[1].ktbitflg=0x2002
ub2 ktbitflg @84 0x2002 (KTBFUPB)
BBED> assign dba 7,130 ktbbh.ktbbhflg=0x03
ub1 ktbbhflg @38 0x03 (KTBFONFL)
BBED> assign dba 7,130 ktbbh.ktbbhfsl=0x02
ub1 ktbbhfsl @39 0x02
–//Point to the corresponding ITL slot (counting from 1).
BBED> sum apply
Check value for File 7, Block 130:
current = 0xfbe7, required = 0xfbe7
BBED> verify
DBVERIFY – Verification starting
FILE = /mnt/ramdisk/book/mssm01.dbf
BLOCK = 130
7. Summary:
–//The inconsistency of DML in the Lck of the ITSM table space block is caused by the trigger check pctused, which modifies the ktbbh.ktbbhfsl, ktbbh.ktbbhflg identifiers.
–//This is related to the pctused attribute of MSSM table space. In fact, when ora-04000 appeared yesterday, the first reaction in the brain was not how to solve this problem, but before
–//This is the problem we have. In other words, if we could think about the difference between MSSM and ASSM, maybe we would have known the answer.
–//Itpub is too short of relevant people to discuss such problems. Let me retrieve this question I have posted on itpub
–//http://www.itpub.net/thread-2108112-1-1.html
–//It’s even more impossible within our team
–//I’ve wasted another morning. From 8:30 to 11:30 in the morning, my buttocks hurt. I really want to stand up and walk.