Description of Oracle block SCN/commit SCN/cleanout SCN

 

I. Description

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

Http://blog.csdn.net/tianlesoftware/article/details/6654786

 

Oraceitl (interestedtransaction list) Description

Http://blog.csdn.net/tianlesoftware/article/details/6573988

 

 

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

Http://blog.csdn.net/tianlesoftware/article/details/6658529

 

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.

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, the exact SCN submitted by the transaction can be retrieved. If the slot has been overwritten, The undo
The control SCN in the 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

 

 

2. Test 2.1 fast commit cleanout.

Sys @ anqing2 (rac2)> Create Table FCC (idnumber );

Table created.

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)> selectdbms_rowid.rowid_block_number (rowid), ora_rowscn from FCC;

Dbms_rowid.rowid_block_number (rowid) ora_rowscn

----------------------------------------------

305906 7262675

305906 7262675

 

-- The two records just inserted are stored in block305906, and the same is true for ora_rowscn.

 

Update an 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)> selectdbms_rowid.rowid_block_number (rowid), ora_rowscn from FCC;

Dbms_rowid.rowid_block_number (rowid) ora_rowscn

----------------------------------------------

305906 7262675

305906 7262675

-- Ora_rowscn does not change

 

Sys @ anqing2 (rac2)> commit;

Commit complete.

Sys @ anqing2 (rac2)> selectdbms_rowid.rowid_block_number (rowid), ora_rowscn from FCC;

Dbms_rowid.rowid_block_number (rowid) ora_rowscn

----------------------------------------------

305906 7262794

305906 7262794

-- Ora_rowscn has changed. This is explained in the blog of ora_rowscn. This ora_rowscn is read 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 insertdata

Sys @ dave2 (DB2)> Create Table DBC (idnumber );

Table created.

Sys @ dave2 (DB2)> insert into dbcvalues (1 );

1 row created.

Sys @ dave2 (DB2)> insert into 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 table

Sys @ dave2 (DB2)> Update DBC Set ID = 8 where id = 1;

1 row updated.

Sys @ dave2 (DB2)> Update DBC Set ID = 9 where id = 2;

1 row updated.

 

-- Obtain the usage of xidusn and xidslot, and release the reuse script later.

Sys @ dave2 (DB2)> selectxidusn, xidslot, xidsqn from V $ transaction;

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 submitted, delayed block cleanout will occur.

Sys @ dave2 (DB2)> alter system flush buffer_cache;

System altered.
SQL> commit;
Commit complete

-- Approximate commit SCN

Sys @ dave2 (DB2)> selecttimestamp_to_scn (systimestamp) from dual;

Timestamp_to_scn (systimestamp)

------------------------------

2147770572

 

-- Use the following script to reuse xidusn 16 xidlot 18

/* Formatted on 15:47:15 (qp5 v5.163.1008.3004 )*/

Create Table goon

As

Select *

Fromdba_objects

Where 1 = 2;

 

/* Formatted on 15:45:12 (qp5 v5.163.1008.3004 )*/

Create or replace procedureproc_go_break_reuse (v_xidusn number,

V_xidslot number,

V_xidsqn number)

/*-----------------

Description: It's used to maketransaction slot Reused

-----------------*/

As

Nsid number;

 

Typetransaction_record_type is record

(

Xidusn number,

Xidslot number,

Xidsqn number

);

 

Transaction_record transaction_record_type;

Begin

Select sys_context ('userenv', 'sid') into nsid from dual;

 

Loop

Insert into goon

Select *

Fromdba_objects

Where rownum: <100;

 

Selectxidusn, xidslot, xidsqn

Intotransaction_record

Fromv $ transaction A, V $ session B

Where a. ADDR = B. taddr and B. Sid = nsid;

 

If (transaction_record.xidusn = v_xidusn

Andtransaction_record.xidslot = v_xidslot

Andtransaction_record.xidsqn> v_xidsqn)

Then

Gotoresue_end;

End if;

 

Commit;

 

Delete from Goon;

 

Selectxidusn, xidslot, xidsqn

Intotransaction_record

Fromv $ transaction A, V $ session B

Where a. ADDR = B. taddr and B. Sid = nsid;

 

If (transaction_record.xidusn = v_xidusn

And transaction_record.xidslot = v_xidslot

Andtransaction_record.xidsqn> 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

 

-- Approximate SCN in case of delayed block clearing

Sys @ dave2 (DB2)> selecttimestamp_to_scn (systimestamp) from dual;

Timestamp_to_scn (systimestamp)

------------------------------

2147771961

 

-- Commit SCN on the last itl

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 2147771334

115346 1 2147771334

 

-- Dump undo Header

-- View the rollback segments in use

Sys @ dave2 (DB2)> selectxidusn, xidslot, xidsqn, ubablk, ubafil, ubarec from V $ transaction;

Xidusn xidslot xidsqn ubablk ubafil ubarec

------------------------------------------------------------

13 41 7 42 7 6

 

Xidusn: rollback ID

Ubablk: datafile ID

 

But here we want to use our undo block at that time, that is, the xidusn we previously queried is 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 setmypid

Statement processed.

Sys @ dave2 (DB2)> oradebug tracefile_name

/U01/APP/Oracle/admin/dave2/udump/dave2_ora_11079.trc

 

 

Trnctl: 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 ', 'xxxxxxxxxxxxx') from dual;

To_number ('800464f4 ', 'xxxxxxxxxxxxxx ')

-----------------------------------

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

Bytes ------------------------------------------------------------------------------------------------

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 115346

Sys @ dave2 (DB2)> alter system dumpdatafile 1 blocks 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, which converts 0x0000. 80046634 to a number:

Sys @ dave2 (DB2)> select to_number ('123', 'xxxxxxxxxxx') from dual;

To_number ('123', 'xxxxxxxxxxx ')

---------------------------------

2147771956

This value is similar to the SCN: 2147771961 when the delayed block is cleared. Therefore, the SCN is used when the delayed 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/FSC

0x01 0x0010. 012.00000005 0x01c0005e. 0001.25 C-U-0 SCN 0x0000. 800463c6

0x02 0x000f. 008.00000005 0x01c00051. 0002.17 C --- 0 SCN 0x0000. 80045e0b

 

The XId format in ITL is USN #. Slot #. Wrap #

Sys @ dave2 (DB2)> selectto_number ('10', 'xxxxxxxxxxxxx') from dual;

To_number ('10', 'xxxxxxxxxxxxx ')

-----------------------------

16

Sys @ dave2 (DB2)> select to_number ('12', 'xxxxxxxxxxxxx') from dual;

To_number ('12', 'xxxxxxxxxxxxx ')

-----------------------------

18

Sys @ dave2 (DB2)> selectto_number ('123', 'xxxxxxxxxxxxx') from dual;

To_number ('123', 'xxxxxxxxxxxxx ')

-----------------------------------

5

It is consistent with the previous v $ transaction query.

 

Sys @ dave2 (DB2)> selectto_number ('800463c6 ', 'xxxxxxxxxxxxx') from dual;

To_number ('800463c6 ', 'xxxxxxxxxxxxxx ')

-----------------------------------

2147771334

-- Equals to the SCN of the last commit

 

Sys @ dave2 (DB2)> select to_number ('80045e0b', 'xxxxxxxxxxxxx') from dual;

To_number ('80045e0b', 'xxxxxxxxxxxxx ')

-----------------------------------

2147769867

 

Note:

As mentioned above, 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.

 

 

Reference: http://www.easyora.net/blog/scn_block_scn.html

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 (full) dba5 group: 142216823 (full)

Dba6 group: 158654907 (full) chat group: 40132017 (full) chat group 2: 69087192 (full)

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.