Ver este articulo en EspañolThis really happened to me when trying to learn how the INITRANS
attribute works, was very surprising how easily you can have this
transitory error state.
Replicate the error is very straightforward, you just need one table and
4 transactions. It happened on a 10.2.0.4 database.
CREATE TABLESPACE UNIF
DATAFILE '/u01/MASTER/unif.dbf'
SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;
create table scott.t2 (c1 number, c2 char(10)) tablespace unif;
Table is created with storage defaults, therefore INITRANS is 2 initially.
insert into scott.t2 (c1, c2) values (0,'TEST me!');
commit;
delete from scott.t2 where c1 = 0;
commit;
See this block dump after the DELETE/COMMIT pair... well, that is also
something to think about too: seems that data is logically erased.
Please observe the 2 ITL (Interested Transaction List) slots, which
corresponds to INITRANS=2.
DFAA3E0 00000000 00000000 00000000 0102012C [............,...]
DFAA3F0 45540A80 6D205453 20202165 1CE60601 [..TEST me! ....]
Block header dump: 0x01800017
Object id on Block? Y
seg/obj: 0xd580 csc: 0x00.e1c2e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.013.000001ac 0x0080b037.01c8.17 --U- 1 fsc
0x0000.000e1ce6
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
Now we change the INITRANS attribute for T2, and increment to say 8.
alter table scott.t2 initrans 8;
Another block dump and you can see the new ITL slots are not allocated
right away (a detailed explanation of ITL may be seen at this place
http://advait.wordpress.com/2008/06/16/interested-transaction-list-itl/
and there you can see that this allocation is dynamic).
Now we are going to try something trivial, throw some transactions into
this table and see what happens. For that, we need at least 3 sqlplus
sessions, because ITLs seem to be allocated on a session/block basis
(this to be confirmed on a later article).
Session 1:
insert into scott.t2 (c1, c2) values (1,'TEST y0u!');
commit;
update scott.t2
set c2 = 'TeST You!'
where c1 = 1;
(no commit for the moment, we want all transactions in pending state)
Session 2:
insert into scott.t2 (c1, c2) values (2,'kiSS him!');
(no commit for the moment, same as above session)
Session 3:
insert into scott.t2 (c1, c2) values (4,'TeaCH DeY!');
(no commit for the moment, same as above session)
What our knowledge and logic may tell us, is that up to now the database
should have created a new ITL slot, counting 3.
However this data block dump shows us a distinct reality:
1) The database engine didn't create a new ITL slot
2) It did the 3rd insert on same block as the other transactions
3) A careful look to the dump, shows that the block appears to be
"corrupted"... this is the shocking part.
buffer tsn: 7 rdba: 0x01800016 (6/22)
scn: 0x0000.000e1cad seq: 0x01 flg: 0x04 tail: 0x1cad0601
frmt: 0x02 chkval: 0x89f7 type: 0x06=trans data
Hex dump of corrupt header 3 = CHKVAL
Dump of memory from 0x0DA66400 to 0x0DA66414
DA66400 0000A206 01800016 000E1CAD 04010000 [................]
DA66410 000089F7 [....]
Hex dump of corrupt block <<--
Dump of memory from 0x0DA66414 to 0x0DA683FC
DA66410 00000001 0000D580 000E1C2E [............]
DA66420 00000000 00320002 01800011 0001000A [......2.........]
DA66430 000001BC 00800AB7 001302B1 00000001 [................]
DA66440 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
DA66460 00000000 00010100 0014FFFF 1F731F87 [..............s.]
DA66470 00001F73 1F870001 00000000 00000000 [s...............]
DA66480 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
DA683E0 00000000 00000000 2C000000 C1020201 [...........,....]
DA683F0 65540A05 20484361 21596544 [..TeaCH DeY!]
Dump of memory from 0x0DA66414 to 0x0DA683FC
DA66410 00000001 0000D580 000E1C2E [............]
DA66420 00000000 00320002 01800011 0001000A [......2.........]
DA66430 000001BC 00800AB7 001302B1 00000001 [................]
DA66440 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
DA66460 00000000 00010100 0014FFFF 1F731F87 [..............s.]
DA66470 00001F73 1F870001 00000000 00000000 [s...............]
DA66480 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
DA683E0 00000000 00000000 2C000000 C1020201 [...........,....]
DA683F0 65540A05 20484361 21596544 [..TeaCH DeY!] -- 3rd Trx
buffer tsn: 7 rdba: 0x01800017 (6/23)
scn: 0x0000.000e219d seq: 0x01 flg: 0x00 tail: 0x219d0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DA66400 to 0x0DA68400
DA66400 0000A206 01800017 000E219D 00010000 [.........!......]
DA66410 00000000 00000001 0000D580 000E2163 [............c!..]
DA66420 00000000 00320002 01800011 000E0002 [......2.........]
DA66430 000001E5 0080001E 0009028F 00000001 [................]
DA66440 00000000 00000004 000001B0 008017B6 [................]
DA66450 000701D2 00000001 00000000 00000000 [................]
DA66460 00000000 00030100 00180000 1F5E1F66 [............f.^.]
DA66470 00001F5E FFFF0003 1F661F77 00000000 [^.......w.f.....]
DA66480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
DA683C0 00000000 00000000 022C0000 03C10202 [..........,.....]
DA683D0 53696B0A 69682053 2C20216D C1020201 [.kiSS him! ,....] -- 2nd Transaction
DA683E0 65540A02 59205453 2021756F 0102023C [..TeST You! <...] -- 1st Transaction
DA683F0 45540A80 6D205453 20202165 219D0601 [..TEST me! ...!] -- dead row
Block header dump: 0x01800017
Object id on Block? Y
seg/obj: 0xd580 csc: 0x00.e2163 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00e.000001e5 0x0080001e.028f.09 ---- 1 fsc
0x0000.00000000
0x02 0x0004.000.000001b0 0x008017b6.01d2.07 ---- 1 fsc
0x0000.00000000
data_block_dump,data header at 0xda66464
===============
At this point, we may ask ourselves 'Where is the rdbms keeping track of
ITL for the last transaction?'
... we need to get that answer.
This needed a confirmation which tried to found with
DBMS_REPAIR.CHECK_OBJECT , however everything looked just fine
SQL> set serveroutput on
declare
v_corrupted_blocks number;
begin
dbms_repair.check_object('SCOTT','T2', object_type =>
dbms_repair.table_object,
repair_table_name=>'REPAIR_TABLEAU',corrupt_count=>v_corrupted_blocks);
dbms_output.put_line('Corrupted Blocks = '||v_corrupted_blocks);
end;
/
SQL> 2 3 4 5 6 7
Corrupted Blocks = 0
PL/SQL procedure successfully completed.
Confused I've tried a second block dump for same table, and surprisingly
this time no corruption was present, no new ITL slot was created but
instead database used another block for last transaction.
buffer tsn: 7 rdba: 0x01800016 (6/22)
scn: 0x0000.000e222b seq: 0x01 flg: 0x04 tail: 0x222b0601
frmt: 0x02 chkval: 0x3930 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D26B400 to 0x0D26D400
...
D26B480 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
D26D3E0 00000000 00000000 2C000000 C1020201 [...........,....]
D26D3F0 65540A05 20484361 21596544 222B0601 [..TeaCH DeY!..+"]
Block header dump: 0x01800016
Object id on Block? Y
seg/obj: 0xd580 csc: 0x00.e1c2e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.001.000001bc 0x00800ab7.02b1.13 ---- 1 fsc
0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
data_block_dump,data header at 0xd26b464
===============buffer tsn: 7 rdba: 0x01800017 (6/23)
scn: 0x0000.000e219d seq: 0x01 flg: 0x04 tail: 0x219d0601
frmt: 0x02 chkval: 0x5eaa type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D26B400 to 0x0D26D400
...
D26B480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
D26D3C0 00000000 00000000 022C0000 03C10202 [..........,.....]
D26D3D0 53696B0A 69682053 2C20216D C1020201 [.kiSS him! ,....]
D26D3E0 65540A02 59205453 2021756F 0102023C [..TeST You! <...]
D26D3F0 45540A80 6D205453 20202165 219D0601 [..TEST me! ...!]
Block header dump: 0x01800017
Object id on Block? Y
seg/obj: 0xd580 csc: 0x00.e2163 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00e.000001e5 0x0080001e.028f.09 ---- 1 fsc
0x0000.00000000
0x02 0x0004.000.000001b0 0x008017b6.01d2.07 ---- 1 fsc
0x0000.00000000
data_block_dump,data header at 0xd26b464
===============
Try to replicate this on your environments and share with us your
release and behavior. Note: I tried this on 11g2 and at least no block
corruption showed up.
Thank you for your time, and see you next time
Subscribe to Oracle Database Disected by Email
Bookmark this on Delicious
4 comments:
on 10.2.0.4
I could not get any corruption on HP/UX by the way I needed to dump 3 block with min max syntax for exactly same test case
By the way
"However this data block dump shows us a distinct reality:
1) The database engine didn't create a new ITL slot"
DB engine creates slot for the operations on the rows that already in the block.
When you insert from different sessions Oracle most of the time uses different block you need to do update operation to see more ITL slots.
you can check this behaviour by running the select below to see block distributions for the rowids you newly inserted but not commited
select dbms_rowid.rowid_relative_fno(rowid) file_no,dbms_rowid.rowid_block_number(rowid) block_no,c1,c2 from scott.t2
Not able to test on HP/UX, but I'll as soon as one gets near... You're right, twice:
1) more than one block is required to dump
2) UPDATE increases ITL slots
I've replicated error on 11.1.0.5 (beta) and 11.1.0.6 for Linux x86.
Post a Comment