學(xué)用Oracle也已近三年時間了新

1、 A Simple Experiment for Oracle Transaction1. Preface(學(xué)用 Oracle 也已近三年時間了,證書也考下來了,但心中一直對 Oracle 底層的運作懷有無比的好奇心和疑惑.就比如 SG 上說更新數(shù)據(jù)前會先寫 undo ,而 undo 的 rolback transaction table,rollback data block發(fā)生變化時，又寫 redo,等等。在此嚴(yán)謹(jǐn)?shù)囊?guī)則背后,Oracle究竟是如何實現(xiàn)的?這些實現(xiàn)和 Oracle 底層的各數(shù)據(jù)文件,控制文件,日志文件又是何關(guān)系. 在出現(xiàn)異常時（比如Instance crash 后，Orac

2、le 做 instance recovery 的 rollforward/rollback），這時都使用到哪些文件中的哪些數(shù)據(jù),這些內(nèi)容是復(fù)雜的,需要掌握很多 Oracle 低層原理,做很多的實驗才能逐漸將它們清晰化,才能掌握它們,并在實際工作中運用它們來解決問題.就這篇文章而言,并不能夠解決所有問題,因為本文只是 update 一張表的一條記錄,然后再 rollback的操作實驗過程,很簡單,因為只是開始.讀此文章請注意:A. 紅顏色的字都是本人加入的注釋或可稱為廢話,可能與需注釋的信息在同一行或換新行,且都是用雙#號封閉起來了.B. 藍(lán)顏色的字都與 REDO LOG中的 change ve

3、ctor 相關(guān).C. 黑體字(文章及段落標(biāo)題除外)都為偶所希望描述的或需特別注意的內(nèi)容.D. 背景為灰色部分全都是執(zhí)行 SQL 后 SQLPLUS 的輸出結(jié)果或 DUMP 文件中的內(nèi)容(因部分 DUMP 文件特別大,故只取了與本實驗相關(guān)的部分)且以上 ABCD 四種屬性可能相互組合:).2. Experiment ProcessStep2.0 connect to oracle2.0.1 connect as sysdbaconnect /as sysdbaselect * from v$version;BANNERProductionNLSRTL Version 9.2.0.6.0 Prod

4、uction#在 Linux 下先做了第一遍,未能在一個工作日內(nèi)將全部內(nèi)容串起來,只能回來在 Windows2000 Professional 上做了.# 2.0.2 query dba_tablespaces to show their characterselecttablespace_name,block_size,status,contents,extent_management,segment_space_managementfrom dba_tablespaces;EXTENT_MAN SEGMENT_SPACSYSTEMUNDOTBS1TEMP8192 ONLINE8192 ON

5、LINE8192 ONLINE8192 ONLINE8192 ONLINE8192 ONLINETEMPORARYPERMANENTPERMANENTPERMANENTINDXTOOLSUSERSLOCALLOCALAUTOAUTO#Tools 表空間是 LTM/ASSM 的#Step2.1 initiate a demotab table2.1.1 create table demotab in TOOLS tablespaceDrop table demotab;create table demotab(id varchar2(20),name varchar2(20) tablespac

6、e tools;2.1.2 query demotab segmentselect owner,segment_name,tablespace_name,header_file,header_blockfrom dba_segmentswhere owner=SYS and segment_name=DEMOTAB;OWNER SYS DEMOTAB TOOLSSEGMENT_NAMETABLESPACEHEADER_FILEHEADER_BLOCK1142.1.3 query dba_extent to show demotab segments characterselect owner,

7、segment_name,tablespace_name,extent_id,file_id,block_idfrom dba_extentswhere owner=SYS and segment_name=DEMOTAB;04#Block 10 is SECOND LEVEL BITMAP BLOCK.# #Block 11 is PAGETABLE SEGMENT HEADER,and its HWM will point to the next block: block 12.# 之所以查以上兩個 dictionary view,是因原以為數(shù)據(jù)必定寫入 block 13,但實際卻不是,最

8、終根據(jù) ROWID 發(fā)現(xiàn)數(shù)據(jù)是寫到 block15 中去的.#2.1.4 append three records into demotab tableinsert into demotab(id,name) values(hijklmn,11);insert into demotab(id,name) values(123, 22);insert into demotab(id,name) values(test,33);commit;select rowid,id,name from demotab;ROWIDIDNAME AAABfTAAEAAAAAPAAA hijklmnAAABfTA

9、AEAAAAAPAAB 123AAABfTAAEAAAAAPAAC test112233# 先查出 ROWID, 再調(diào)用 sys.dbms_rowid.rowid_info 獲得 file id,block i等d信息#exec show_rowid(AAABfTAAEAAAAAPAAA);rowid_type1block_number15row_number 0#Now we know those inserted rows are storing in file 4 and block 15 .#2.1.5 dump original data blockbefore being upda

10、tedalter system dump datafile 4 block 15;scn: 0 x0000.0001f2a1seq: 0 x01 flg: 0 x00 tail: 0 xf2a10601frmt: 0 x02 chkval: 0 x0000 type: 0 x06=trans dataBlock header dump:Object id on Block?seg/obj: 0 x17d30 x0100000fcsc: 0 x00.1cad2itc: 2 flg: E typ: 1 - DATAbrn: 0 bdba: 0 x1000009 ver: 0 x01inc: 0 e

11、xflg: 0#ITL entries#ItlXidUba0 x0009.020.000000363 fsc 0 x0000.0001c9dd nrow=3frre=-1fsbo=0 x18fseo=0 x1f75avsp=0 x1f5dtosp=0 x1f5d0 xe:pti0nrow=3offs=0tab 0, row 0, 0 x1f8atab 0, row 1, 0 x1f80#The second row which we just inserted. Id= 123 name= 22#col 1: 2 32 3275tab 0, row 2, 0 x1fend_of_block_d

12、umpStep 2.2 update demotab table2.2.1 update the tableupdate demotab set id=555 where id = 123;#Attention: I should let this session idle untSitlep2.4.#2.2.2 dump data blockafter updating(before rollback)alter system dump datafile 4 block 15; itc: 2 flg: E typ: 1 - DATAXidFlag LckScn/FscC0 scn 0 x00

13、00.0001c9dd1 fsc 0 x0000.00000000#Xid0,Lck=1 #nrow=3frre=-1fsbo=0 x18fseo=0 x1f75avsp=0 x1f5dtosp=0 x1f5d0 xe:pti0nrow=3offs=0tab 0, row 0, 0 x1f8a555 #lb:0 x2 means this row using transaction entry with order 0 x2(active transaction).#col 1: 2 32 32 end_of_block_dumpStep 2.3 get transaction informa

14、tion2.3.1 query v$transaction to get transaction informationafter updating(before rollback)select XIDUSN,XIDSLOT ,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;XIDUSN XIDSLOT XIDSQNUBAFIL UBABLK UBASQN UBAREC02#UNDO File/Block/Rec: 2/94/16- 0 x0080005e:0 x10#Step 2.4 get undo data2.4.

15、1 query v$rollback to get rollback segment nameselect * from v$rollname;after updating(before rollback)USN NAME6 _SYSSMU6$7 _SYSSMU7$8 _SYSSMU8$9 _SYSSMU9$10 _SYSSMU10$#This rollback segment is current being used.#select owner,segment_name,tablespace_name,header_file,header_blockfrom dba_segmentswhe

16、re owner=SYS and segment_name=_SYSSMU6$;TABLESPACE_NAME SYS2#Transaction table is in this block(rollback segments header block)#select owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocksfrom dba_extentswhere owner=SYS and segment_name=_SYSSMU6$ ;SYSSYS_SYSSMU6$_SYSSMU6$UNDOTBS1UNDOT

17、BS10122909778select usn,xacts from v$rollstat;010000001000234567891002.4.2 dump undo segment header block to get transaction tablealter system dump datafile 2 block 89;after updating(before rollback)alter system dump undo header _SYSSMU6$;#this command is similar to the command upper.#Start dump dat

18、a blocks tsn: 1 file#: 2 minblk 89 maxblk 89buffer tsn: 1rdba: 0 x00800059 (2/89)scn: 0 x0000.0001cad2seq: 0 x01 flg: 0 x04 tail: 0 xcad22601#Attention: This block changed at this SCN,finnally I found it in REDO LOG DUMP FILE.#frmt: 0 x02 chkval: 0 xdb80 type: 0 x26=KTU SMU HEADER BLOCKExtent Contro

19、l Headerspare2: 0#maps: 0blk#: 4#blocks: 150 x00000000UnlockedMap Header: nextExtent Map0 x00000000 0 x0080005a0 x00800061length: 7length: 8FREE BLOCK POOL:spc: 0 x0index state cflags wrap#uelscndbaparent-xidnubstmt_num0 x00100 x800 x00340 x00000 x0000.0001cad20 x0080005e0 x0000.000.000000000 x00000

20、0010 x00000000#index-v$transaction.slot 0 x00=00,state=10 (active)#90 x000 x00340 x00060 x0000.000153eb0 x0080005b0 x0000.000.000000000 x0000000090 x000 x00340 x00260 x0000.0001be110 x0080005b0 x0000.000.000000000 x000000010 x00000000#Transaction tables from 0 x03 to 0 x2e were erased.#0 x2f90 x000

21、x00330 x00020 x0000.0001bd370 x0080005b0 x0000.000.000000000 x000000010 x00000000End dump data blocks tsn: 1 file#: 2 minblk 89 maxblk 892.4.3 dump undo data block to get before image dataafter updating(before rollback)alter system dump datafile 2 block 94;* #xid:Undo segment no.Slotno.sequence no #

22、irb: 0 x10 : transactions begin here.#Rec Offset0 x04 0 x1e2c0 x09 0 x1c2c0 x0e 0 x197cobjd: 9 tblspc: 0(0 x00000000)*No*uba: 0 x0080005d.0016.01 ctl max scn: 0 x0000.0000e7c2 prv tx scn: 0 x0000.0000ebb3op: 0 x04 ver: 0 x01op: L itl: xid:0 x0009.019.00000036 uba: 0 x00800091.0017.08flg: Clkc: 0scn:

23、 0 x0000.0001c63bkey :(9): 02 c1 07 02 c1 07 02 c1 0a* Rec #0 x10slt: 0 x00 objn: 6099(0 x000017d3)Layer: 11 (Row) opc: 1 rci 0 x00Begin trans Last buffer split: Noobjd: 6099 tblspc: 4(0 x00000004)*No*uba: 0 x0080005e.0016.0b ctl max scn: 0 x0000.0000f9b2 prv tx scn: 0 x0000.0000fae6KDO undo record:

24、KTB Redotabn: 0 slot: 1(0 x1) flag: 0 x2c lock: 0 ckix: 0 End dump data blocks tsn: 1 file#: 2 minblk 94 maxblk 94Step 2.5 dump logfile to detect redo generation根據(jù)已有的知識,Oracle是在更改一個 data block 過程中,會做以下動作:A.B.C.申請 rollback segment 中的 transaction table slot這, 需對這個數(shù)據(jù)塊(rollback segment header block) 作更改

25、,這會先產(chǎn)生 redo log,才能改 transaction table.將before image( 在本例中是id=123 )寫入到rollback data block之中去,這又會先寫redo log再更改rollback datablock.最后要做 data block 更新前,還是需先將 after image(在本例中是 id=555 )寫入 redo log,最后才更新 buffer cache中的 data block.也就是說對于 A,B,C,Oracle 都當(dāng)作普通的 data block,在它們被更改前,都需要先將所更改數(shù)據(jù)的 before image 寫入 red

26、o log,才開始更改正式的 data block 內(nèi)容.再看 dump 出來的 redo log file其, 中含有很多的 REDO RECORD東就該是偶們要找的.將 update 之后,未提交 前 dump 出來的 data block 4/15 中,偶們所更新行所對應(yīng)的 ITL 之中的xid(0 x0006.000.00000034 )取出來,到 redo log dump file之中去查找,真的找到了.記錄(Change Vectors), 查看到其中的 xid列 :),這個東2.5.1 query current redo log fileafter updating(befo

27、re rollback)select group#,thread#,sequence#,members,status,first_change# from v$log;GROUP# THREAD# SEQUENCE#MEMBERS STATUSFIRST_CHANGE# 121192 INACTIVE2 CURRENT5370411164910archive log list;非存檔模式禁用存檔終點c:oracleora92RDBMS最早的概要日志序列當(dāng)前日志序列910select group#,status,type,member from v$logfile where group#=2;

28、GROUP# STATUSTYPEMEMBER 22ONLINEONLINED:ORADATAPUBERREDO2A.LOGD:ORADATAPUBERREDO2B.LOG2.5.2 dump current redo log fileafter updating(before rollback)alter system dump logfile “d:oradatapuberREDO2A.log”; DBAs: (file # 0, block # 0) thru (file # 65534, block # 4194303)RBAs: 0 x000000.00000000.0000 thr

29、u 0 xffffffff.ffffffff.ffffSCNs scn: 0 x0000.00000000 thru scn: 0 xffff.ffffffffTimes: creation thru eternityLargest LWN: 0 blocksEnd-of-redo stream : NoUnprotected modeMiscellaneous flags: 0 x0#Other REDO RECORDs were erased.# 以 xid(0 x0006.000.00000034),# 在這個 REDO RECORD中總共看到三個 CHANGE. #REDO RECOR

30、D - Thread:1RBA: 0 x00000a.000004a3.00101 01/15/2005 15:31:48LEN: 0 x01f0 VLD: 0 x01SCN: 0 x0000.0001cad2 SUBSCN:CHANGE #1TYP:0 CLS:27 AFN:2 DBA:0 x00800059SCN:0 x0000.0001ca5f SEQ:1 OP:5.2ktudh redo: slt: 0 x0000 sqn: 0 x00000034 flg: 0 x0012 siz: 104 fbi: 0uba: 0 x0080005e.0016.10 pxid: 0 x0000.00

31、0.00000000中出現(xiàn)了 transaction table所在 block 的 DBA(DBA 0 x00800059).# 因為 REDO LOG 是按順序?qū)懙?正如偶們之前所學(xué)的,Oracle在更新數(shù)據(jù)過程中,的確是最先寫入的對 transaction table所在塊的 REDO 數(shù)據(jù).同時 uba(uba:0 x0080005e.0016.10) 也對應(yīng)于 transaction table lot之中的 uba.#CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0 x0080005e SCN:0 x0000.0001ca5e SEQ: 5 OP:5.1#CHA

32、NGE #1ktudb redo: siz: 104 spc: 6336 flg: 0 x0012 seq: 0 x0016 rec: 0 x10 xid: 0 x0006.000.00000034ktubl redo: slt: 0 rci: 0 opc: 11.1 objn: 6099 objd: 6099 tsn: 4Tablespace Undo:0 x00000000prev ctl max cmt scn: 0 x0000.0000f9b2Noprev tx cmt scn: 0 x0000.0000fae6 op: 0 x03 ver: 0 x01col 0: 3 31 32 3

33、3TYP:2 CLS: 1 AFN:4 DBA:0 x0100000fSCN:0 x0000.0001c9dd SEQ:1 OP:11.5op: 0 x11 ver: 0 x01op: F xid: 0 x0006.000.00000034uba: 0 x0080005e.0016.10col 0: 3 35 35 35#This is after image (id=555 ) what we changed, so CHANGE #3 is the redo data for data block.#DBA(DBA:0 x0100000f) is the dba of block cont

34、aining the row(s) being changed .#0 OP:5.19serial number = 7current username = SYSlogin username = SYSclient info =OS process id = 1512:632OS program name= sqlplusw.exetransaction name =Step 2.6 rollback the transaction2.6.1 rollback the transaction&query v$transactionrollback;回退已完成 select * from v$

35、transaction;未選定行Step 2.7 data block after being rolled back2.7.1 dump data block after being rolled backalter system dump datafile 4 block 15;scn: 0 x0000.0001f2a1seq: 0 x01 flg: 0 x00 tail: 0 xf2a10601Object id on Block? Yseg/obj: 0 x17d3 csc: 0 x00.1cad2 itc: 2 flg: E typ: 1 - DATAbrn: 0 bdba: 0 x

36、1000009 ver: 0 x01inc: 0 exflg: 0ItlXidUbaFlag LckScn/Fsc0 x01 0 x0009.020.000000360 x02 0 x0000.000.00000000#no active transactions#C 0 scn 0 x0000.0001c9dd 0 fsc 0 x0000.00000000#Flag:-U- = transaction committed (maybe long ago); SCN is an upper bound#hsiz: 0 x18flag=ntab=1nrow=3frre=-1fsbo=0 x18f

37、seo=0 x1f75avsp=0 x1f5dtosp=0 x1f5d0 xe:pti0 nrow=3 offs=00 x12:pri0 offs=0 x1f8a0 x14:pri1 offs=0 x1f800 x16:pri2 offs=0 x1f75block_row_dump:tab 0, row 0, 0 x1f8a col 0: 4 74 65 73 74col 1: 2 33 33end_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 15 maxblk 15# 我將 update 之前和先 update 再 rol

38、lback后的塊作了比較,已將變化的內(nèi)容在以上標(biāo)識為黃色.# 對于 ITL 中 Flag 的各種狀態(tài),ITPUB 第九期的雜志中有介紹,偶看得懂那些詞,但不明意.#Step 2.8 undo after rollback2.8.1 dump undo segment header block to get transaction tableafter rollbackalter system dump datafile 2 block 89;scn: 0 x0000.0001f2a2seq: 0 x01 flg: 0 x04 tail: 0 xf2a22601spare2: 0#blocks:

39、 15mapblk 0 x00000000 offset: 0Unlockedobj#: 0flag: 0 x400000000 x0080005a length: 7Extent Number:0 Commit Time: 1105199203Extent Number:1 Commit Time: 1105106949 spc: 0 x0index state cflags wrap#uelscndbaparent-xid nubstmt_num0 x009 0 x00 0 x0034 0 xffff 0 x0000.0001f2a2 0 x00000000 0 x0000.000.000

40、000000 x0000000090 x00 0 x00340 x00060 x0000.000153eb0 x0080005b0 x0000.000.000000000 x000000010 x00000000#Contents from Rec#0 x02 to Rec#0 x2f were erased. #End dump data blocks tsn: 1 file#: 2 minblk 89 maxblk 892.8.2 dump undo data blockafter rollbackalter system dump datafile 2 block 94;#undo da

41、ta block 沒有任何改變#xid: 0 x0006.000.00000034seq: 0 x16 cnt: 0 x10 irb: 0 x10 icl: 0 x0 flg: 0 x0000Rec OffsetRec OffsetRec OffsetRec OffsetRec Offset0 x04 0 x1e2c0 x09 0 x1c2c0 x0e 0 x197c* Rec #0 x1 slt: 0 x10 objn: 9(0 x00000009) objd: 9 tblspc: 0(0 x00000000) *Undo type: Regular undoTemp Object: NoT

42、ablespace Undo: Nordba: 0 x00000000op: 0 x04 ver: 0 x01op: L itl: xid:0 x0009.019.00000036 uba: 0 x00800091.0017.08flg: C lkc: 0scn: 0 x0000.0001c63bkey :(9): 02 c1 07 02 c1 07 02 c1 0a#Contents from Rec#0 x02 to Rec#0 x2f were erased. #*objd: 6099 tblspc: 4(0 x00000004)*Begin trans Last buffer split: NoTablespace Undo: Nordba: 0 x00000000*uba: 0 x0080005e.0016.0b ctl max scn: 0 x0000.0000f9b2 prv tx scn: 0 x0000.0000fae6KDO undo record:KTB Redoop: 0 x03 ver: 0 x01op: Zcol 0: 3 31 32 33End dump d