Description of Oracle Block scn/commit scn/cleanout scn

Oracle Block scn/commit scn/cleanout scn description 1. 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. 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 generated (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: the differences between SCN and FSC in ITL dump block ITL information is as follows: Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000e. 007.00000236 0x00000000. 2.16.00c-U-0 scn 0x0000. 005b1f7f0x02 0x000c. 005.000003b4 0x01401727. 0144.13C --- 0 scn 0x0000. 005bbf0b0x03 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 this 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 cleanoutC --- = 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 ii. test 2.1 fast commit cleanoutSYS @ anqing2 (rac2)> create table fcc (idnumber); Table creat Ed. SYS @ anqing2 (rac2)> insert into fccvalues (1); 1 row created. SYS @ anqing2 (rac2)> insert into fccvalues (2); 1 row created. SYS @ anqing2 (rac2)> commit; Commit complete. SYS @ anqing2 (rac2)> histogram (rowid), ora_rowscn from fcc; DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) ORA_ROWSCN limit 305906 7262675 305906 -- the two inserted records are stored in block305906, and ora _ The same is true for rowscn. Update the fcc table: SYS @ anqing2 (rac2)> update fcc set id = 3 where id = 1; 1 row updated. SYS @ anqing2 (rac2)> update fcc set id = 4 where id = 2; 1 row updated. SYS @ anqing2 (rac2)> compute (rowid), ora_rowscn from fcc; DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) ORA_ROWSCN limit 305906 7262675 305906 7262675 -- ora_rowscn unchanged SYS @ anqing2 (rac2)> commit; commit comp Lete. SYS @ anqing2 (rac2)> selectdbms_rowid.rowid_block_number (rowid), ora_rowscn from fcc; DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) ORA_ROWSCN limit 305906 7262794 305906 7262794 -- ora_rowscn has changed, this is explained in the ora_rowscn blog that ora_rowscn reads from the block header SCN. 2.2 delayed block cleanout --. create a small undo tablespace. SYS @ dave2 (db2)> create undo tablespaceundotbs2 datafile '/u01/app/oracle/oradata/dave2/undotbs02.dbf' size 1 M; Tablespace created. SYS @ dave2 (db2)> alter system setundo_tablespace = 'undotbs2 '; System altered. -- create a test table and insertdataSYS @ dave2 (db2)> create Table dbc (idnumber); table created. SYS @ dave2 (db2)> insert into dbcvalues (1); 1 row created. SYS @ dave2 (db2)> insert I Nto dbc values (2); 1 row created. SYS @ dave2 (db2)> commit; Commit complete. SYS @ dave2 (db2)> selectdbms_rowid.rowid_block_number (rowid) block, dbms_rowid.rowid_relative_fno (rowid) fileno, ora_rowscn from dbc; block fileno ORA_ROWSCN ---------- 115346 1 2147768913 115346 1 2147768913 -- update tableSYS @ dave2 (db2)> update dbc set id = 8 where id = 1; 1 row updated. SYS @ dave2 (db2)> update dbc s Et id = 9 where id = 2; 1 row updated. -- Obtain the usage of XIDUSN and XIDSLOT. The Reuse script SYS @ dave2 (db2)> selectxidusn, xidslot, xidsqn from v $ transaction will be released later; xidusn xidslot xidsqn ---------- 16 18 5 -- refresh the buffer cache. If the modified block is flush back to the hard disk before the transaction is committed, delayed block cleanout will occur. SYS @ dave2 (db2)> alter system flush buffer_cache; System altered. SQL> commit; Commit complete -- approximate commit scnSYS @ dave2 (db2)> selecttimestamp_to_scn (systimestamp) from dual; TIMESTAMP_TO_SCN (SYSTIMESTAMP) limit 2147770572 -- use the following script, reuse XIDUSN 16 XIDLOT 18/* Formatted on 15:47:15 (QP5 v5.163.1008.3004) */create table goonAS SELECT * FROMdba_objects WHERE 1 = 2;/* Formatted o N 15:45:12 (QP5 v5.163.1008.3004) */create or replace evaluate (v_XIDUSN NUMBER, v_XIDSLOT NUMBER, v_XIDSQN NUMBER)/* --------------- Description: it's used to maketransaction slot reused random */AS nsid NUMBER; operation is record (xidusn number, xidslot number, xidsqn number); transaction_record transaction_record_type; begin select SYS_CO NTEXT ('userenv', 'sid') INTO nsid from dual; loop insert into goon SELECT * FROMdba_objects where rownum <100; SELECTXIDUSN, XIDSLOT, XIDSQN contains transaction_record FROMv $ transaction, v $ session B WHERE. ADDR = B. taddr and B. SID = nsid; IF (transaction_record.XIDUSN = v_XIDUSN ANDtransaction_record.XIDSLOT = v_XIDSLOT limit> v_XIDSQN) THEN GOTOresue_end; end if; COMMIT; DELETE FROM goon; SELECTXIDUSN, XIDSLOT, XIDSQN contains transaction_record FROMv $ transaction a, v $ session B WHERE. ADDR = B. taddr and B. SID = nsid; IF (region = v_XIDUSN AND transaction_record.XIDSLOT = v_XIDSLOT limit> v_XIDSQN) THEN GOTOresue_end; end if; COMMIT; end loop; <resue_end> COMMIT; END; -- call the script SYS @ dave2 (db2)> execproc_go_break_reuse (16, 18, 5); PL/SQL procedure Successfully completed. -- generates a delay block clearing and records the corresponding scn number SYS @ dave2 (db2)> select * from dbc; ID ---------- 8 9 -- about scnSYS @ dave2 (db2)> selecttimestamp_to_scn (systimestamp) from dual; TIMESTAMP_TO_SCN (SYSTIMESTAMP) when a delayed block is cleared) ---------------------------- 2147771961 -- commit scnSYS @ dave2 (db2)> selectdbms_rowid.rowid_block_number (rowid) block, combine (rowid) fileno, ora_rowscn from dbc on the last itl; Block fileno ORA_ROWSCN ---------- 115346 1 2147771334 115346 1 2147771334 -- dump undo header -- view the used rollback segment SYS @ dave2 (db2)> selectxidusn, xidslot, xidsqn, ubablk, ubafil, ubarec from v $ transaction; xidusn xidslot xidsqn ubablk ubafil ubarec ---------- ------------ bytes ---------- 13 41 7 42 7 6 XIDUSN: rollback IDUBABLK: datafile ID, but here we want to use our undo block at that time, that is, we previously Query XIDUSN = 16 SYS @ dave2 (db2)> select usn, name fromv $ rollname where usn = 16; usn name ---------- ---------------------------- 16 _ SYSSMU16 $ alter system dump undo header '_ SYSSMU13 $'; SYS @ dave2 (db2)> alter system dump undoheader '_ SYSSMU16 $'; System altered. SYS @ dave2 (db2)> oradebug setmypidStatement processed. SYS @ dave2 (db2)> oradebug tracefile_name/u01/app/oracle/admin/dave2/udump/dave2_ora_11079.trc TRN CTL: seq: 0x0003 chd: 0x002a ctl: 0x0029 inc: 0x00000000 nfb: 0x0001 mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x01c0007a. 0003.30 scn: 0x0000. 800464f4 -- control scn sys @ dave2 (db2)> select to_number ('800464f4 ', 'xxxxxxxxxxxx') from dual; TO_NUMBER ('800464f4', 'xxxxxxxxxxxxxxxx') limit 2147771636 Version: 0x01 free block pool: uba: 0x01c0007a. 0003.30 Ext: 0x2 spc: 0x572 uba: 0x00000000. 0000.00 ext: 0x0 spc: 0x0 uba: 0x00000000. 0000.00 ext: 0x0 spc: 0x0 uba: 0x00000000. 0000.00 ext: 0x0 spc: 0x0 uba: 0x00000000. 0000.00 ext: 0x0 spc: 0x0 TRNTBL :: index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt defaults 0x00 9 0x00 0x0007 0x0001 0x0000.80046548 0x01c00068 0x0000. 000.00000000 0x00000001 0x00000000 1312472853 0x01 9 0x00 0x0007 0x0002 0x0000.80046554 0x01c00068 0x0000. 000.00000000 0x00000001 0x00000000 1312472853 0x02 9 0x00 0x0007 0x0003 0x0000.80046560 0x01c00079 0x0000. 000.00000000 0x00000001 0x00000000 1312472853 0x03 9 0x00 0x0007 0x0004 0x0000. 8004656c 0x01c00079 0x0000. 000.00000000 0x00000001 -- dump block 1151_sys @ dave2 (db2)> alte R system dumpdatafile 1 block 115346; System altered. SYS @ dave2 (db2)> oradebug tracefile_name/u01/app/oracle/admin/dave2/udump/dave2_ora_11079.trc Start dump data blocks tsn: 0 file #: 1 minblk 115346 maxblk 115346 buffer tsn: 0 rdba: 0x0041c292 (1/115346) scn: 0x0000.80046634 seq: 0x01 flg: 0x04 tail: 0x66340601 -- This scn is blockscn, and 0x0000. 80046634 convert to a number: SYS @ dave2 (db2)> select to_number ('123', 'xxxxxx Xxx ') from dual; TO_NUMBER ('123', 'xxxxxxxxxxx') ------------------------------- 80046634 this value is similar to the SCN: 2147771956 when the previous delayed block is cleared, therefore, this should be the scn when the delay block is cleared. Frmt: 0x02 chkval: 0x2974 type: 0x06 = transdata... Block header dump: 0x0041c292 Object id on Block? Y seg/obj: 0xdf46 csc: 0x00.80046634 itc: 2 flg: O typ: 1-DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0010. 012.00000005 0x01c0005e. 0001.25 C-U-0 scn 0x0000. 800463c60x02 0x000f. 008.00000005 0x01c00051. 0002.17 C --- 0 scn 0x0000. in 80045e0b, the XID format in ITL is: usn #. slot #. wrap # SYS @ dave2 (db2)> selectto_number ('10', 'xxxxxxxxxxxxx') from dual; TO_NUMBER ('10', 'xxxxxxxxxxxx ')----------------- ------------ 16SYS @ dave2 (db2)> select to_number ('12', 'xxxxxxxxxxxxx') from dual; TO_NUMBER ('12', 'xxxxxxxxxxxx') limit 18SYS @ dave2 (db2)> selectto_number ('20140901', 'xxxxxxxxxxxxxxx') from dual; TO_NUMBER ('20160901', 'xxxxxxxxxxxxxxxx') ----------------------- 5 is consistent with the previous V $ TRANSACTION query. SYS @ dave2 (db2)> selectto_number ('800463c6 ', 'xxxxxxxxxxxxxx') from dual; TO_NUMBER ('800463c6', 'xxxxxxxxxxxxxxxx ') limit 2147771334 -- equal to the last commit scn sys @ dave2 (db2)> select to_number ('80045e0b', 'xxxxxxxxxxxxxx') from dual; TO_NUMBER ('80045e0b', 'xxxxxxxxxxxxxx ') ----------------------------------- 2147769867 Note: As mentioned earlier, when the slot is reused, the scn on the corresponding itl is updated to control scn. However, the dump undo control SCN here is 2147771636. However, the scn of itl is 2147771334, which is the SCN of the last commit. Therefore, this test does not prove this conclusion. The lab steps still need to be improved. The result is sometimes not important. What is important is the analysis process.