From:Steve Adams
Date:24-Mar-2001 22:44
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.00000000
Note 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  0x00000000
This 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.0016497f
Note 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?