编程知识 cdmana.com

Oracle block SCN / commit SCN / cleanup SCN description

Oracle In a data block of SCN There are three kinds of , They are big SCN, CSC (cleanout SCN) and ITL Medium commit SCN. About block The details in , You can put block dump come out , To view the . This is before me blog There is sorting in the :

       Oracle datafile block Format explain

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

 

       OraceITL(InterestedTransaction List) explain

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

 

 

       Block Of cache header part , Recording a block scn, It is the timestamp of the last change of the current block , To be exact , This update does not mean itl Upper scn The latest update of , Next delayed block cleanout Under the slot In case of reuse , You can see blockscn Is not the same as itl Last updated on scn. Can pass dump get block scn/last itl scn And release ora_rowscn Gets the last itl scn.

 

Oracle ORA_ROWSCN Pseudo column explain

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.

 

       Release transaction after , Before submitting ,block scn It won't change , Corresponding itl Nor does it scn Record .Block scn Changes , Not exactly Publishing commit Time of day ( Because there is delayed block cleanout The situation exists ), But in transaction Corresponding itl get commit scn Time of day .

 

       cleanout It is divided into 2 The clock , One is fast commit cleanout, The other is delayed block cleanout.

       oracle There is one modified block list structure , To record each transaction Changed block, Every transaction It's about to record 10% buffer cache That's a lot modified block. This part block It's when it happens commit When ,oracle According to modified block list Locate those blocks and do fast commit cleanout. If one transaction The modified block exceeds 10% buffer cache, Then more blocks are executed delayed block cleanout.

       treat as fast commit cleanout when ,oracle Will not clean up Row locks lb Sign a ,ITL lck Sign a .

       The other case is delayed block cleanout, When transaction Has not yet commit or rollback when modified block Has been written back to disk , Happen when commit when oracle It doesn't mean that block Read in again and do cleanout, It's too expensive , But the cleanout Save it for the next time dml Time to complete . When delayed cleanout Time if undo segment header Of transaction table slot Not covered yet , Then you can retrieve the exact scn, If slot It's covered , Then it will use undo segment header Medium control scn Do it as upper bound scn.

 

       Happen when fast commit cleanout, The system will transaction At the time of submission scn As commit scn, to update block On itl and undo segment header Of Transaction table Of slot Upper scn, And modify it block scn, The three are consistent .

        happen delayed block cleanout When , Previous transaction commit The update is just Transaction table, Instead of doing block Processing on , Wait for the next time you use this block When , to update block scn and itl state .block scn and itl The update is divided into 2 In this case :

       1) When there is no slot When it comes to reuse (ITL Don't reuse ), delayed block cleanout when , according to Transaction table The message inside , to update block scn and itl Upper Scn/Fsc by transaction Once submitted scn.
       2) When there is slot When it comes to reuse ( reusing ITL), Update correspondence itl On scn by control scn, and block scn by delayed block cleanout The moment of occurrence scn.

 

explain :ITL in SCN and FSC The difference between

dump block Of ITL The information 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

 

       there SCN and FSC That's what it is ITL When the corresponding transaction is committed SCN, So the largest of all the slots here SCN The sign means this BLOCK When it was last updated SCN. Each transaction corresponds to a itl Record . If the transaction does not involve delayed block clearing , So the display FSC. In case of delay block clearing (delayed block cleanout), So what it shows is SCN.

 

        stay ITL There is a displayed... In the message Flag The state of ,FLAG stay block To occupy 1 Byte size . Different flag The meaning of the mark 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

 

 

Two . 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

 

-- Just inserted 2 All records are stored in block305906 in , also ora_rowscn It's the same thing .

 

Yes fcc table update

SYS@anqing2(rac2)> update fcc set id=3where id=1;

1 row updated.

SYS@anqing2(rac2)> update fcc set id=4where 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 There is no 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 been changed , This is in ora_rowscn That article blog There's a description in , This ora_rowscn It's from block header SCN Read from .

 

  

2.2 delayed block cleanout

--. Create a small undo Table space .

SYS@dave2(db2)> create undo tablespaceundotbs2 datafile '/u01/app/oracle/oradata/dave2/undotbs02.dbf' size 1M;

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=8where id=1;

1 row updated.

SYS@dave2(db2)> update dbc set id=9where id=2;

1 row updated.

 

-- obtain XIDUSN,XIDSLOT Usage situation , Reuse scripts for subsequent releases

SYS@dave2(db2)> selectxidusn,xidslot,xidsqn from v$transaction;

   XIDUSN    XIDSLOT     XIDSQN

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

       16         18          5

 

-- Refresh buffer cache, If there is a problem before the transaction is submitted modified block I was flush Go back to the hard disk , Then it will happen delayed block cleanout.

SYS@dave2(db2)> alter system flush buffer_cache;

System altered.
SQL> 
commit;
Commit complete


-- About the commit scn

SYS@dave2(db2)> selecttimestamp_to_scn(systimestamp) from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP)

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

  2147770572

 

-- Use the following script , reusing XIDUSN 16 XIDLOT 18

/* Formatted on 2011/8/4 15:47:15(QP5 v5.163.1008.3004) */

CREATE TABLE goon

AS

   SELECT *

     FROMdba_objects

    WHERE 1 = 2;

 

/* Formatted on 2011/8/4 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 script

SYS@dave2(db2)> execproc_go_break_reuse(16,18,5);

PL/SQL procedure successfully completed.

 

-- Generate delay block clear , Record accordingly scn Number

SYS@dave2(db2)> select * from dbc;

       ID

----------

        8

        9

 

-- The approximate time when the delayed block clearing occurs scn

SYS@dave2(db2)> selecttimestamp_to_scn(systimestamp) from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP)

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

                    2147771961

 

-- The last time itl Upper commit scn

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 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're going to use our current undo block , That is, what we queried before 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 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','xxxxxxxxxxx')from dual;

TO_NUMBER('800464F4','XXXXXXXXXXX')

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

                        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

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

  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 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#: 1minblk 115346 maxblk 115346

buffer tsn: 0 rdba: 0x0041c292 (1/115346)

scn: 0x0000.80046634 seq:0x01 flg: 0x04 tail: 0x66340601

 

-- This scn Namely blockscn, take 0x0000.80046634 Turn to numbers :

SYS@dave2(db2)> select to_number('80046634','xxxxxxxxx')from dual;

TO_NUMBER('80046634','XXXXXXXXX')

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

                       2147771956

        This value is the same as when the previous delay block is cleared SCN:2147771961 almost , So this should be when the delay block is cleared scn.

 

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

 

among ITL Medium XID The format is :usn#.slot#.wrap#

SYS@dave2(db2)> selectto_number('10','xxxxxxxxxxx') from dual;

TO_NUMBER('10','XXXXXXXXXXX')

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

                      16

SYS@dave2(db2)> select to_number('12','xxxxxxxxxxx')from dual;

TO_NUMBER('12','XXXXXXXXXXX')

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

                   18

SYS@dave2(db2)> selectto_number('00000005','xxxxxxxxxxx') from dual;

TO_NUMBER('00000005','XXXXXXXXXXX')

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

                    5

And before V$TRANSACTION Consistency of query .

 

SYS@dave2(db2)> selectto_number('800463c6','xxxxxxxxxxx') from dual;

TO_NUMBER('800463C6','XXXXXXXXXXX')

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

                         2147771334

-- Equal to the last time commit Of SCN

 

SYS@dave2(db2)> select to_number('80045e0b','xxxxxxxxxxx')from dual;

TO_NUMBER('80045E0B','XXXXXXXXXXX')

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

                         2147769867

 

explain :

       Mentioned earlier , When slot When reusing , Update correspondence itl On scn by control scn. But I'm here dump undo Of control SCN by 2147771636. but itl Of scn by 2147771334, It's the last time commit Of SCN. So this test does not prove this conclusion . The experimental steps need to be improved .

版权声明
本文为[wx5caecf2ed0645]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/08/20210809181600773u.html

Scroll to Top