[20210316] Lck 3.txt of MSSM table space block ITL

Time:2021-6-11

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

Recommended Today

OC basis

IOS development interview essential skills chart.png What are objects and what are the objects in OC? An object is an instance of a class; Is an instance created through a class, which is generally called an instance object; Common objects in OC include instance objects, class objects, and metaclass objects; What is a class? What […]