|Subject:||Snapshot too old and delayed block cleanout|
Good question. I'll force the error here after setting an event to take a processstate dump as follows ...
alter session set events '1555 trace name processstate forever, level 10';From the trace file, here is the header of the block that I was trying to do a delayed block cleanout on ...
Block header dump: 0x00c0a067 Object id on Block? Y seg/obj: 0xf36 csc: 0x00.13b8ee itc: 1 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0002.00b.000021c5 uba: 0x00812a3e.0109.0b ---- 21 fsc 0x0000.00000000Note that the C flag is not yet set and the transaction still has 21 row-level locks in the block. This is the current mode image of the block, and it is left exactly like this (in need of cleanout) when the ORA-1555 error hits. Now here are some extracts from the dump of the rollback segment header block that we were trying to use to do the cleanout ...
BH #3804 (0x331f0ac) file#: 2 rdba: 0x00800802 (2/2050) class 15 ba: 0x417e000 set: 1, dbwrid: 0 hash: [32d5cd4,333684c], lru: [32bd8f4,32efa9c] LRU flags: ckptq: [NULL] fileq: [NULL] use: [3325d9c,3325d9c], wait: [NULL] st: CR, md: EXCL, rsop: 0x0, tch: 0 cr:[[scn: 0x0000.00164999],[xid: 0x0000.000.00000000],[uba: 0x00000000.0000.00], sfl: 0x0] ... TRN CTL:: seq: 0x013f chd: 0x0008 ctl: 0x0013 inc: 0x00000000 nfb: 0x0001 mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2048 (0x00000800) uba: 0x008128d1.0137.0d scn: 0x0000.0016497f ... index state cflags wrap# uel scn dba parent-xid nub ------------------------------------------------------------------------------------------------ ... 0x0b 9 0x00 0x3c6c 0x0004 0x0000.00164988 0x00000000 0x0000.000.00000000 0x00000000This shows that the slot 0x0b was last used by a transaction with wrap number 0x3c6c whereas the wrap number of the transaction that we need to cleanout was 0x21c5. Therefore we can conclude that the transaction did commit, but until we know when it committed, whether before or after the snapshot SCN for our query, we don't know whether this block needs to be just cleaned out, or cleaned out and rolled back.
To find out when the transaction committed, we need to roll back this rollback segment header block to recover the old contents of slot 0x0b containing the commit SCN for the old transaction. This is done using a special chain of undo linking blocks in the rollback segment that contain changes to the rollback segment header. The head of this chain is pointed to by the control uba in the transaction control section of the rollback segment header block.
The picture that we have here is actually a CR version of the block that has already been rolled back as far as possible (scn 0x0000.00164999) which is not as far as necessary (scn 0x0000.0013b1ed), thus the ORA-1555 error.
I then visited the block again in a separate query with a more recent snapshot SCN, and then dumped the block again. Here is the ITL ...
Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0002.00b.000021c5 uba: 0x00812a3e.0109.0b C-U- 0 scn 0x0000.0016497fNote that the transaction is now marked as committed (C flag) and has 0 row-level locks in the block. The U flag shows that the commit SCN recorded here is not the true commit SCN for the query but an "upper bound" for it. The number came from the control SCN in the transaction control section of the rollback segment header block once it had been rolled back as far as possible.
The inability to fully rollback the rollback segment header block did not result in an ORA-01555 error because the new query had a more recent snapshot SCN than the rollback segment's control SCN.
Could you explain what happens to the ITL information when a delayed block cleanout fails due to snapshot too old on the RBS header block. How and when these blocks are cleaned?