There are three types of SCN in a data block of Oracle, namely the block header SCN, CSC (cleanout SCN) and commit SCN in ITL. You can dump the block to view the specific information in the block. This is organized in my previous blog:
Oracle datafile block format description
Effece ITL (InterestedTransaction List) Description
The cache header part of the Block records a block scn, which is the timestamp of the last change of the current Block. To be exact, this update does not refer to the latest update of the scn on the itl, when the slot under delayed block cleanout is reused, we can see that blockscn is not equal to the last updated scn on itl. You can use dump to obtain the block scn/last itl scn and publish the ora_rowscn statement to obtain the last itl scn.
Description of Oracle ORA_ROWSCN pseudo Columns
Eachdata block in a datafile contains an SCN, which is the SCN at which the lastchange was made to the block. during an incremental backup, RMAN reads the SCNof each data block in the input file and compares it to the checkpoint SCN ofthe parent incremental backup. RMAN reads the entire file every time whether ornot the blocks have been used.
After the transaction is published, block scn will not change before it is submitted, and no scn record is made in the corresponding itl. The change of Block scn is not exactly when the commit is released (because of the existence of delayed block cleanout), but when the itl corresponding to the transaction obtains the commit scn.
Cleanout is divided into two minutes. One is fast commit cleanout, and the other is delayed block cleanout.
Oracle has a modified block list structure to record the modified blocks of each transaction. Each transaction can record the modified block of about 10% buffer cache. When a commit occurs, oracle can locate the Blocks Based on the modified block list and execute fast commit cleanout. If a transaction modifies more than 10% buffer cache blocks, delayed block cleanout is executed for the blocks that exceed the cache limit.
When used as fast commit cleanout, oracle will not clear the Row locks lb flag and the ITL lck flag.
Another case is delayed block cleanout. when the transaction is not commit or rollback, the modified block has been written back to the disk. When a commit occurs, oracle does not read the block again for cleanout, in this way, the cost is too high, but cleanout is reserved for the next dml of this block. When delayed cleanout, if the transaction table slot of the undo segment header is not overwritten, you can retrieve the exact scn submitted by the transaction. If the slot has been overwritten, the control scn In the undo segment header is used as the upper bound scn.
When fast commit cleanout occurs,The system uses the scn of the transaction commit time as the commit scn, updates the scn on the Transaction table slot of the itl and undo segment headers on the block, and modifies the block scn. The three are consistent.
When delayed block cleanout occurs, the previous transaction commit updates only the Transaction table, but does not process the block. Wait until the next time you use this block, update the block scn and itl statuses. Block scn and itl are updated in two situations:
(1) When no slot is reused (ITL is not reused) and delayed block cleanout, according to the information in the Transaction table, update the scn/Fsc on block Scn and itl to the scn when transaction was submitted.
(2) When the slot is reused (ITL is reused), the scn on the corresponding itl is updated to control scn, and the block scn is the scn at the time when the delayed block cleanout occurs.
Note: differences between SCN and FSC in ITL
The information of the dump block ITL is as follows:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e. 007.00000236 0x00000000. 0000.00C-U-0 scn 0x0000. 005b1f7f
0x02 0x000c. 005.000003b4 0x01401727. 0144.13C --- 0 scn 0x0000. 005bbf0b
0x03 0x0011. 007.00000406 0x0140015b. 00c7. 57--U-483 fsc 0x0000. 005bdee1
The SCN and FSC here are actually the SCN of the transaction commit corresponding to the ITL, the largest SCN number in all slots indicates the SCN of the BLOCK when it is last updated. Each transaction corresponds to an itl record.If the transaction does not involve the clearing of delayed blocks, the FSC is displayed. If delayed block cleanout is used, the SCN is displayed.
There is a Flag status in the ITL information, and the FLAG occupies 1 byte in the block. The significance of different flag labels is as follows:
---- = Transaction is active, or committedpending cleanout
C --- = transaction has been committed andlocks cleaned out
-B -- = this undo record contains the undofor this ITL entry
-- U-= transaction committed (maybe longago); SCN is an upper bound
--- T = transaction was still active atblock cleanout SCN