oracle redo和undo系列一
oracle undo與redo系列(一)
1,測試目的：
1,進一步理解undo和redo相關概念
2,把undo和redo統一聯絡起來；
2,準備工作
1,轉儲日誌檔案的命令:
alter system dump logfile '處於current狀態的日誌檔案組名稱';
2,日誌檔案的構成要素:
重作記錄
重作向量
重作記錄與重作向量的關係
2,轉儲undo segment block的命令:
alter system dump datafile x block y;
或者：
--由v$rollstat之usn關聯v$transaction獲取
alter system dump undo header '事務所佔用的回滾段';
3,轉儲undo block命令：
--x,y由undo header block可知或v$transaction可知
alter system dump datafile x block y;
3,開始測試
/*****************************構建測試表*******************/
SQL> create table t_redo(a int);
Table created
SQL> select object_id from dba_objects where object_name='T_REDO';
OBJECT_ID
----------
70020
/***********10046 trace,為了觀察到單塊讀,flush buffer_cache************************/
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> insert into t_redo values(1);
1 row inserted
SQL> alter session set events '10046 trace name context off';
Session altered
/******************檢視生成的trace檔案*****************************/
SQL> select * from v$diag_info where name like '%Default Trace File%';
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
1 Default Trace File d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace\orcl_s001_4500.trc
/*******************生成的trace檔案**********************************/
WAIT #1: nam='db file sequential read' ela= 527 file#=10 block#=276633 blocks=1 obj#=70020 tim=126724965619
WAIT #1: nam='db file sequential read' ela= 26638 file#=3 block#=139965 blocks=1 obj#=0 tim=126724992359
/*********************檢視錶的物件號，檔案號，資料塊號********************/
SQL> select dbms_rowid.rowid_object(rowid) as object_id,dbms_rowid.rowid_relative_fno(rowid) as file_id,dbms_rowid.rowid_block_number(rowid) as block_id from t_redo;
OBJECT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
70020 10 276638
/*********************檢視錶的段頭塊*********************/
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='T_REDO';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------------------------------------------------------------- ----------- ------------
T_REDO 10 276634
/***************事務所涉及的回滾段*********************/
SQL> select * from v$rollname where usn=( select xidusn from v$transaction);
USN NAME
---------- ------------------------------
51 _SYSSMU51_1137245832$
/**************事務所涉及的撤消檔案編號及撤消塊編號*******從trace知道第二次讀了此塊****/
SQL> select ubafil,ubablk from v$transaction;
UBAFIL UBABLK
---------- ----------
3 139965
/*************表分配的區相關情況**********從trace先讀取了表的第2個塊***/
SQL> select segment_name,extent_id,block_id,blocks from dba_extents where segment_name='T_REDO';
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ---------- ----------
T_REDO 0 276632 8
/**********************開始依次dump上述10046的的塊******************************/
WAIT #1: nam='db file sequential read' ela= 527 file#=10 block#=276633 blocks=1 obj#=70020 tim=126724965619
/****************alter system dump datafile 10 block 276633**************************/
--可知先讀取是二級點陣圖管理塊
frmt: 0x02 chkval: 0xac2c type: 0x21=SECOND LEVEL BITMAP BLOCK
Dump of Second Level Bitmap Block
/***********下述的pdba經轉換即段頭塊地址（select to_number('00284389a','xxxxxxxxxxx') from dual;*******************
select dbms_utility.data_block_address_file(42219674),dbms_utility.data_block_address_block(42219674) from dual;
***************************************************************************/
number: 1 nfree: 1 ffree: 0 pdba: 0x0284389a
Inc #: 0 Objd: 70020
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x02843898 Free: 5 Inst: 1 --這個free我理解是一個區extent未使用過的塊個數
(小結：說明oracle其實先是讀取了header block,然後才讀取level 2 bitmap block)
dbwrid: 0 obj: 70020 objn: 70020 tsn: 8 afn: 10 hint: f --hint即v$bh的cachehint即在buffer cache命中的次數
/***************繼續dump10046第二個單塊讀******************/
/*************WAIT #1: nam='db file sequential read' ela= 26638 file#=3 block#=139965 blocks=1 obj#=0 tim=126724992359************/
/*****************alter system dump 3 block 139965;******************/
SQL> select name from v$rollname where usn=(select xidusn from v$transaction);
NAME
------------------------------
_SYSSMU51_1137245832$
SQL> select segment_name,owner,tablespace_name,segment_id,file_id,block_id from dba_rollback_segs where segment_name='_SYSSMU51_1137245832$';
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID
------------------------------ ------ ------------------------------ ---------- ---------- ----------
_SYSSMU51_1137245832$ PUBLIC UNDOTBS1 51 3 7104
/*******看來第二個單塊讀不是讀取的事務回滾段的段頭塊**************/
/************不管怎麼樣我們先trace下事務讀取的回滾塊****************/
alter system dump datafile 3 block 139965;
scn: 0x0000.00922f1d seq: 0x01 flg: 0x04 tail: 0x2f1d0201
frmt: 0x02 chkval: 0x8be0 type: 0x02=KTU UNDO BLOCK --可知是具體的undo block,而非undo header block
UNDO BLK:
xid: 0x0033.01a.00000085 seq: 0x17a cnt: 0x21 irb: 0x21 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f18 0x02 0x1e6c 0x03 0x1dc4 0x04 0x1d0c 0x05 0x1cac
0x06 0x1c5c 0x07 0x1bb0 0x08 0x1b08 0x09 0x1a78 0x0a 0x19d0
0x0b 0x1928 0x0c 0x1880 0x0d 0x17fc 0x0e 0x17a4 0x0f 0x1738
0x10 0x168c 0x11 0x15e0 0x12 0x1548 0x13 0x14c0 0x14 0x1450
0x15 0x13a4 0x16 0x12ac 0x17 0x1204 0x18 0x1158 0x19 0x10d0
0x1a 0x1060 0x1b 0x0fb8 0x1c 0x0f0c 0x1d 0x0ea0 0x1e 0x0e38
0x1f 0x0dc0 0x20 0x0d58 0x21 0x0cec
*-----------------------------
* Rec #0x21 slt: 0x1a objn: 70020(0x00011184) objd: 70020 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c222bd.017a.1c ctl max scn: 0x0000.00915f03 prv tx scn: 0x0000.00916782 --之前事務的scn
txn start scn: scn: 0x0000.00922f1d logon user: 61 --事務開始的scn
prev brb: 12682835 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0284389e hdba: 0x0284389a
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) --因為這裡是insert未提交,故無前映象
/************************再trace下undo header block******************************/
共同2種方式
alter system dump undo header '_SYSSMU51_1137245832$';
alter system dump datafile 3 block 7104;
/*********先看第一種方式結果*************/
*** 2013-03-22 21:23:04.903
*** SESSION ID:(157.203) 2013-03-22 21:23:04.903
********************************************************************************
Undo Segment: _SYSSMU51_1137245832$ (51) ---undo header block名稱
********************************************************************************
Extent Control Header
/****此sql參考查詢回滾段的區分配情況************/
SQL> select owner,segment_name,segment_type,tablespace_name,extent_id,block_id,blocks from dba_extents where segment_name='_SYSSMU51_1137245832$';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS
------------------------------ -------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ---------- ----------
SYS _SYSSMU51_1137245832$ TYPE2 UNDO UNDOTBS1 0 7104 8
SYS _SYSSMU51_1137245832$ TYPE2 UNDO UNDOTBS1 1 720 8
SYS _SYSSMU51_1137245832$ TYPE2 UNDO UNDOTBS1 2 139904 128
同上sql知回滾段共有3個區,共佔用資料塊個數143,還少一個資料塊
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c01bc2 ext#: 0 blk#: 1 ext size: 7
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000
/**********同上理，查詢回滾段區分配******************/
SQL> select block_id,to_char(block_id,'xxxxxxxxxxxxxxxxx'),blocks from dba_extents where segment_name='_SYSSMU51_1137245832$';
BLOCK_ID TO_CHAR(BLOCK_ID,'XXXXXXXXXXXX BLOCKS
---------- ------------------------------ ----------
7104 1bc0 8
720 2d0 8
139904 22280 12
Extent Map
-----------------------------------------------------------------
0x00c01bc1 length: 7 --和上述sql匹配，每個區的首塊；這個首塊為何加1,因為有個undo block header block佔用了
0x00c002d0 length: 8
0x00c22280 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1363958134 --每個區extent commit時間
Extent Number:1 Commit Time: 1363939587
Extent Number:2 Commit Time: 1363958134
TRN CTL:: seq: 0x017b chd: 0x0012 ctl: 0x0014 inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c01bc2.017b.01 scn: 0x0000.009171f4 --uba即具體的undo block,老方法轉算為具體的undo block,這樣undo header block就和undo block聯絡上了
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c01bc2.017b.01 ext: 0x0 spc: 0x1f84
uba: 0x00000000.017a.43 ext: 0x7 spc: 0xaec
uba: 0x00000000.0170.08 ext: 0x8 spc: 0x356
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
/********這就是大名鼎鼎的事務表*************其實就是一個表格一樣的東東*************/
/***********************插入的查詢sql,查詢測試表的資料塊,經查即與如下事務表的dba 0x1a條目相對應，其state=10,表示未提交,9表示提交**********************************
/********從事務表可看到對一個資料塊多次修改會形成多條條目*************/
SQL> select to_number('00c222bd','xxxxxxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('00C222BD','XXXXXXXX
------------------------------
12722877
SQL> select dbms_utility.data_block_address_file(12722877),dbms_utility.data_block_address_block(12722877) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
3 139965
**************************************************************/
TRN TBL::--縮略詞即transaction table就是事務表
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0087 0x0005 0x0000.0091e754 0x00c1ec6a 0x0000.000.00000000 0x00000053 0x00000000 1363940433
0x01 9 0x00 0x0089 0x001f 0x0000.009206ad 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363941256
0x02 9 0x00 0x0088 0x000e 0x0000.00921cd2 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363949100
0x03 9 0x00 0x0087 0x000d 0x0000.0091a40b 0x00c19d76 0x0000.000.00000000 0x00000053 0x00000000 1363940321
0x04 9 0x00 0x0088 0x0017 0x0000.009220f9 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363950607
0x05 9 0x00 0x0087 0x001d 0x0000.0091ea63 0x00c1ec6a 0x0000.000.00000000 0x00000001 0x00000000 1363940437
0x06 9 0x00 0x0087 0x0000 0x0000.0091d4b7 0x00c1ec18 0x0000.000.00000000 0x00000052 0x00000000 1363940406
0x07 9 0x00 0x0088 0x000f 0x0000.009229e7 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363953616
0x08 9 0x00 0x0089 0x0016 0x0000.009218f9 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363947658
0x09 9 0x00 0x0086 0x0020 0x0000.00920aad 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363942735
0x0a 9 0x00 0x0086 0x0019 0x0000.009213ba 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363945846
0x0b 9 0x00 0x0086 0x0014 0x0000.009237eb 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1363958134
0x0c 9 0x00 0x0088 0x0007 0x0000.0092282c 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363953012
0x0d 9 0x00 0x0086 0x0006 0x0000.0091bb1d 0x00c1c8c7 0x0000.000.00000000 0x00000052 0x00000000 1363940372
0x0e 9 0x00 0x0089 0x0004 0x0000.00921ec3 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363949896
0x0f 9 0x00 0x0085 0x0018 0x0000.00922b35 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363953917
0x10 9 0x00 0x0086 0x0003 0x0000.00918bfe 0x00c19d24 0x0000.000.00000000 0x00000001 0x00000000 1363940230
0x11 9 0x00 0x0089 0x0021 0x0000.009224ee 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363951812
0x12 9 0x00 0x0085 0x0010 0x0000.00917fac 0x00c19d24 0x0000.000.00000000 0x00000052 0x00000000 1363940201
0x13 9 0x00 0x0081 0x0001 0x0000.009205c2 0x00c222bc 0x0000.000.00000000 0x00000002 0x00000000 1363941035
0x14 9 0x00 0x0087 0xffff 0x0000.009237ee 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1363958134
0x15 9 0x00 0x0086 0x001c 0x0000.00920e68 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363944036
0x16 9 0x00 0x0086 0x0002 0x0000.009219d8 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363947954
0x17 9 0x00 0x0089 0x0011 0x0000.009222c8 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363951203
0x18 9 0x00 0x0089 0x000b 0x0000.00922d3a 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363954819
0x19 9 0x00 0x0088 0x0008 0x0000.009216b6 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363947046
0x1a 10 0x80 0x0085 0x0002 0x0000.00922f1d 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 0 ---這個就是測試表insert未提交在undo header block插入的事務表條目
0x1b 9 0x00 0x0086 0x000a 0x0000.0092115f 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363944945
0x1c 9 0x00 0x0087 0x001b 0x0000.009210a6 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363944647
0x1d 9 0x00 0x0087 0x001e 0x0000.0091fddc 0x00c222bb 0x0000.000.00000000 0x00000052 0x00000000 1363940461
0x1e 9 0x00 0x0087 0x0013 0x0000.009205c1 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363941035
0x1f 9 0x00 0x0086 0x0009 0x0000.0092091b 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363942234
0x20 9 0x00 0x0087 0x0015 0x0000.00920c42 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363943135
0x21 9 0x00 0x0086 0x000c 0x0000.009225dd 0x00c222bd 0x0000.000.00000000 0x00000001 0x00000000 1363952111
/********************擴充套件事務表***************/
EXT TRN CTL::
usn: 51 --這個好v$transaction中的usn,就是我們測試的事務
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000 --sp字首不知是什麼
sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
--我理解這個擴充套件事務表即是對事務表的增強，這個還要研究
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
0x03 0x00000000 0x00000000 0x00000000 0x00000000
0x04 0x00000000 0x00000000 0x00000000 0x00000000
0x05 0x00000000 0x00000000 0x00000000 0x00000000
0x06 0x00000000 0x00000000 0x00000000 0x00000000
0x07 0x00000000 0x00000000 0x00000000 0x00000000
0x08 0x00000000 0x00000000 0x00000000 0x00000000
0x09 0x00000000 0x00000000 0x00000000 0x00000000
0x0a 0x00000000 0x00000000 0x00000000 0x00000000
0x0b 0x00000000 0x00000000 0x00000000 0x00000000
0x0c 0x00000000 0x00000000 0x00000000 0x00000000
0x0d 0x00000000 0x00000000 0x00000000 0x00000000
0x0e 0x00000000 0x00000000 0x00000000 0x00000000
0x0f 0x00000000 0x00000000 0x00000000 0x00000000
0x10 0x00000000 0x00000000 0x00000000 0x00000000
0x11 0x00000000 0x00000000 0x00000000 0x00000000
0x12 0x00000000 0x00000000 0x00000000 0x00000000
0x13 0x00000000 0x00000000 0x00000000 0x00000000
0x14 0x00000000 0x00000000 0x00000000 0x00000000
0x15 0x00000000 0x00000000 0x00000000 0x00000000
0x16 0x00000000 0x00000000 0x00000000 0x00000000
0x17 0x00000000 0x00000000 0x00000000 0x00000000
0x18 0x00000000 0x00000000 0x00000000 0x00000000
0x19 0x00000000 0x00000000 0x00000000 0x00000000
0x1a 0x00000000 0x00000000 0x00000000 0x00000000
0x1b 0x00000000 0x00000000 0x00000000 0x00000000
0x1c 0x00000000 0x00000000 0x00000000 0x00000000
0x1d 0x00000000 0x00000000 0x00000000 0x00000000
0x1e 0x00000000 0x00000000 0x00000000 0x00000000
0x1f 0x00000000 0x00000000 0x00000000 0x00000000
0x20 0x00000000 0x00000000 0x00000000 0x00000000
0x21 0x00000000 0x00000000 0x00000000 0x00000000
/******再看來看redo的情況****************/
/********找到當前日誌並trace******************/
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
3 1 689 52428800 512 1 NO INACTIVE 9537783 2013/3/22 1 9539502 2013/3/22 1
4 1 690 209715200 512 1 NO INACTIVE 9539502 2013/3/22 1 9544823 2013/3/22 1
5 1 691 209715200 512 1 NO INACTIVE 9544823 2013/3/22 1 9551076 2013/3/22 1
6 1 692 209715200 512 1 NO INACTIVE 9551076 2013/3/22 1 9557772 2013/3/22 1
7 1 693 209715200 512 1 NO INACTIVE 9557772 2013/3/22 1 9564180 2013/3/22 1
8 1 694 209715200 512 1 NO INACTIVE 9564180 2013/3/22 1 9577485 2013/3/22 1
9 1 696 209715200 512 1 NO CURRENT 9583525 2013/3/22 2 281474976710
10 1 695 20971520 512 1 NO INACTIVE 9577485 2013/3/22 1 9583525 2013/3/22 2
8 rows selected
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
7 ONLINE D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO07.LOG NO
8 ONLINE D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO08.LOG NO
3 ONLINE D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO03.LOG NO
4 ONLINE D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO04.LOG NO
5 ONLINE D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO05.LOG NO
6 ONLINE D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO06_1.LOG NO
9 ONLINE D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO09.LOG NO
10 ONLINE D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO10.LOG NO
8 rows selected
/*************************dump線上日誌********************/
SQL> alter system dump logfile 'D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO09.LOG';
System altered
DUMP OF REDO FROM FILE 'D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO09.LOG'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff --rba唯一標記每個重作日誌條目
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=1331266241=0x4f5982c1, Db Name='ORCL'
Activation ID=1331289025=0x4f59dbc1
Control Seq=19901=0x4dbd, File size=409600=0x64000 --日誌檔案大小
File Number=9, Blksiz=512, File Type=2 LOG --日誌檔案編號
descrip:"Thread 0001, Seq# 0000000696, SCN 0x000000923ba5-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x000002b8 hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x2fe43741 scn: 0x0000.00000001 (1)
prev resetlogs count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.00923ba5 (9583525) 03/22/2013 21:30:37 --最低的scn
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 ---最高的scn
Enabled scn: 0x0000.00000001 (1) 12/31/2012 14:25:10
Thread closed scn: 0x0000.00923ba5 (9583525) 03/22/2013 21:30:37
Disk cksum: 0x3704 Calc cksum: 0x3704
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
--具體的重作條目或叫 重作記錄，日誌檔案有許多條這樣的結構
REDO RECORD - Thread:1 RBA: 0x0002b8.00000002.0010 LEN: 0x0070 VLD: 0x06 --rba，rba有3個部分，與v$log對應
SCN: 0x0000.00923bf0 SUBSCN: 1 03/22/2013 21:34:13 --也有scn
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 3 rdba: 0x00c019c8 BFT:(1024,12589512) non-BFT:(3,6600)
scn: 0x0000.00923ac9 seq: 0x01 flg:0x04
REDO RECORD - Thread:1 RBA: 0x0002b8.00000004.0010 LEN: 0x00e0 VLD: 0x06
SCN: 0x0000.00923c0d SUBSCN: 1 03/22/2013 21:35:41
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 2 rdba: 0x00801798 BFT:(1024,8394648) non-BFT:(2,6040) --寫入了資料塊
scn: 0x0000.00923af3 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x00801799 BFT:(1024,8394649) non-BFT:(2,6041)
scn: 0x0000.00923af2 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x0080179a BFT:(1024,8394650) non-BFT:(2,6042)
scn: 0x0000.00923af2 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x0080179b BFT:(1024,8394651) non-BFT:(2,6043)
scn: 0x0000.00923af4 seq: 0x02 flg:0x06
Block Written - afn: 2 rdba: 0x0080179c BFT:(1024,8394652) non-BFT:(2,6044)
scn: 0x0000.00923b78 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x0080179d BFT:(1024,8394653) non-BFT:(2,6045)
scn: 0x0000.00923af2 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x0080179e BFT:(1024,8394654) non-BFT:(2,6046)
scn: 0x0000.00923af2 seq: 0x01 flg:0x04
Block Written - afn: 2 rdba: 0x0080179f BFT:(1024,8394655) non-BFT:(2,6047)
scn: 0x0000.00923b7d seq: 0x01 flg:0x04
REDO RECORD - Thread:1 RBA: 0x0002b8.00000006.01ac LEN: 0x0044 VLD: 0x02
SCN: 0x0000.00923c0d SUBSCN: 1 03/22/2013 21:35:41
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 2 rdba: 0x00810954 BFT:(1024,8456532) non-BFT:(2,67924)
scn: 0x0000.00923b0c seq: 0x01 flg:0x06
REDO RECORD - Thread:1 RBA: 0x0002b8.00000862.0050 LEN: 0x0108 VLD: 0x01
SCN: 0x0000.00923d74 SUBSCN: 51 03/22/2013 21:40:37
CHANGE #1 TYP:0 CLS:124 AFN:3 DBA:0x00c01c0f OBJ:4294967295 SCN:0x0000.00923d74 SEQ:50 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 100 spc: 3958 flg: 0x0022 seq: 0x0196 rec: 0x33 --說明日誌檔案對undo也有redo記錄
xid: 0x0036.00c.00000089
ktubu redo: slt: 12 rci: 50 opc: 10.22 objn: 6216 objd: 6216 tsn: 1
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: C uba: 0x00c01c0f.0196.31
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x8010c2 block=0x0080dec0
(kdxlpu): purge leaf row
key :(32):
06 c5 0e 20 1b 3f 2a 0d 64 64 74 31 70 73 35 78 62 64 6e 33 64 06 c5 26 4d
03 14 5d 03 c2 02 07
CHANGE #2 TYP:0 CLS:1 AFN:2 DBA:0x0080dec0 OBJ:6216 SCN:0x0000.00923d74 SEQ:25 OP:10.2 ENC:0 RBL:0 --每條重作條目由多個change向量，也叫重作向量組成;即對資料塊一次更改產生一個redo vector
index redo (kdxlin): insert leaf row
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: C uba: 0x00c01c0f.0196.33
REDO: SINGLE / -- / --
itl: 2, sno: 45, row size 42
insert key: (32):
06 c5 0e 20 1b 3f 2a 0d 64 64 74 31 70 73 35 78 62 64 6e 33 64 06 c5 26 4d
03 14 5d 03 c2 02 07
keydata: (6): 00 81 0d f0 00 15
---在日誌檔案中可看到塊清除記錄
Block cleanout record, scn: 0x0000.00923f2e ver: 0x01 opt: 0x02, entries follow...
itli: 3 flg: 2 scn: 0x0000.0092039f
itli: 4 flg: 2 scn: 0x0000.009203a4
itli: 5 flg: 2 scn: 0x0000.00923f2e
4,小結
1,對redo這裡內部結構還有些暈,把undo和redo未聯絡起來
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-756904/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Redo and UndoOracle Redo
- 【REDO】Oracle redo undo 學習Oracle Redo
- Oracle的redo 和undo的區別Oracle
- 關於Oracle的redo和undo的理解Oracle
- Redo 和 Undo 概念解析
- redo和undo的區別
- oracle體系結構梳理---redo和undo解析1Oracle
- Oracle redo undo commit rollback剖析Oracle RedoMIT
- Oracle中undo 如何產生RedoOracle
- 深入理解MySQL系列之redo log、undo log和binlogMySql
- oracle體系結構梳理---redo和undo檔案解析Oracle
- oracle undo系列(三)Oracle
- oracle undo系列(二)Oracle
- SQLServer的檢查點、redo和undoSQLServer
- MySQL中的redo log和undo logMySql
- MySQL Undo Log和Redo Log介紹MySql
- UNDO REDO 區別
- redo與undo的一點點思考
- 【Oracle】Current online Redo 和 Undo 損壞的處理方法Oracle
- 深入淺出-redo和undo記載01
- 深入淺出-redo和undo記載02
- 深入淺出redo和undo記載03
- MySQL 日誌 undo | redoMySql
- oracle undo一Oracle
- 深入理解MYSQL undo redoMySql
- 【開發篇sql】 基礎概述（二） undo和redoSQL
- 從Undo, Redo, DataFile看Oracle中的事務過程Oracle
- MySQL redo與undo日誌解析MySql
- LMT更新file header bitmap不產生redo和undo ?Header
- Redo Log之一：理解Oracle redo logOracle Redo
- oracle undo segment header 事務表transaction table系列一OracleHeader
- Sqlserver沒有單獨的undo檔案，使用tempdb和redo log來存放undo資料SQLServer
- Flink Table & SQL API--動態表與Redo和UndoSQLAPI
- Undo和Current Online Redo損壞的處理方法
- Current online Redo 和 Undo 損壞的處理方法
- Undo表空間與redo日誌
- 《Undo, Redo, DataFile看Oracle中的事務過程》學習筆記Oracle筆記
- 基於Redo Log和Undo Log的MySQL崩潰恢復流程MySql