{"id":1294,"date":"2014-08-01T15:59:26","date_gmt":"2014-08-01T07:59:26","guid":{"rendered":"http:\/\/www.yallonking.com\/?p=1294"},"modified":"2014-08-01T16:00:59","modified_gmt":"2014-08-01T08:00:59","slug":"%e6%b5%85%e6%9e%90undo","status":"publish","type":"post","link":"http:\/\/www.yallonking.com\/?p=1294","title":{"rendered":"\u6d45\u6790undo"},"content":{"rendered":"

\u8be5\u6587\u6863\u4eceinsert\uff0cupdate\uff0cdelete\u6240\u4ea7\u751f\u7684dump\u6587\u4ef6\u8fdb\u884c\u5206\u6790\u5e76\u505a\u7b80\u8981\u8bf4\u660e<\/p>\n

31\u53f7\u4f1a\u8bdd\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\uff08\u672a\u63d0\u4ea4\uff09<\/p>\n

\r\nsys@ora11gr2> select sid from v$mystat where rownum<2;\r\n\r\n       SID\r\n----------\r\n        31\r\n\r\n1 row selected.\r\n\r\nsys@ora11gr2> select * from yallonking.redoundo;\r\n\r\n        ID NAME\r\n---------- --------------------\r\n         1 redoundo1\r\n         2 redoundo2\r\n\r\n2 rows selected.\r\n\r\nsys@ora11gr2> insert into yallonking.redoundo values(3,'redoundo3');\r\n\r\n1 row created.\r\n\r\nsys@ora11gr2> select t.rowid,t.* from yallonking.redoundo t order by 2; \r\n\r\nROWID                      ID NAME\r\n------------------ ---------- --------------------\r\nAAARgrAAFAAAAATAAA          1 redoundo1\r\nAAARgrAAFAAAAATAAB          2 redoundo2\r\nAAARgrAAFAAAAATAAC          3 redoundo3\r\n\r\n3 rows selected.\r\n<\/pre>\n
32\u53f7\u4f1a\u8bdd\u5220\u9664\u4e00\u6761\u8bb0\u5f55\uff08\u672a\u63d0\u4ea4\uff09<\/p>\n
\r\nsys@ora11gr2> select sid from v$mystat where rownum<2;\r\n\r\n       SID\r\n----------\r\n        32\r\n\r\n1 row selected.\r\n\r\nsys@ora11gr2> delete from yallonking.redoundo where id=1;\r\n\r\n1 row deleted.\r\n\r\nsys@ora11gr2> select t.rowid,t.* from yallonking.redoundo t order by 2;\r\n\r\nROWID                      ID NAME\r\n------------------ ---------- --------------------\r\nAAARgrAAFAAAAATAAB          2 redoundo2\r\n\r\n1 row selected.\r\n<\/pre>\n
\u4e0b\u8fb9\u5728\u65b0\u7684\u4f1a\u8bdd\u4e2d\u8fdb\u884c\u5206\u6790<\/p>\n
\r\nsys@ora11gr2> select sid from v$mystat where rownum<2;\r\n\r\n       SID\r\n----------\r\n         1\r\n\r\n1 row selected.\r\n<\/pre>\n
\u67e5\u770b\u6d3b\u52a8\u4e8b\u52a1\u4fe1\u606f<\/p>\n
\r\nsys@ora11gr2> select ADDR,sid,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,a.STATUS,START_SCN \r\n  2  from v$transaction a,v$session b\r\n  3  where a.SES_ADDR=b.SADDR;\r\n\r\nADDR                    SID     XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS            START_SCN\r\n---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------\r\n0000000095A27420         31          7          9        801          3        800        184          1 ACTIVE              1330231\t\/\/\u63d2\u5165\u64cd\u4f5c\u5f00\u59cb\u7684scn\r\n0000000095A367A8         32          4         15        792          3        182        248         18 ACTIVE              1330299\t\/\/\u5220\u9664\u64cd\u4f5c\u5f00\u59cb\u7684scn\r\n\r\n2 rows selected.\r\n\r\n\r\n\r\n\r\nsys@ora11gr2> select * from V$ROLLNAME where USN in (7,4);\r\n\r\n       USN NAME\r\n---------- ------------------------------\r\n         4 _SYSSMU4_3451911709$\r\n         7 _SYSSMU7_774603598$\r\n\r\n2 rows selected.\r\n\r\n<\/pre>\n
\u5bf9\u4e8e\u63d2\u5165\u64cd\u4f5c<\/strong>
\n\u63d2\u5165\u64cd\u4f5c\u7684\u4e8b\u52a1\u4fe1\u606f\u5982\u4e0b\uff1a<\/p>\n
\r\nsys@ora11gr2> alter system dump undo header '_SYSSMU7_774603598$';\r\n\r\nSystem altered.\r\n\r\n<\/pre>\n
dump\u6587\u4ef6\u90e8\u5206\u5982\u4e0b\uff1a<\/p>\n
\r\nindex  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt\r\n  ------------------------------------------------------------------------------------------------\r\n   0x09   10    0x80  0x0321  0x0002  0x0000.00144c37  0x00c00320  0x0000.000.00000000  0x00000001   0x00000000  0\r\n<\/pre>\n
\u67e5\u770b\u63d2\u5165\u64cd\u4f5c\u7684undo\u4fe1\u606f
\ndump\u51fa\u5bf9\u5e94\u7684\u5757\u4fe1\u606f\u90e8\u5206\u5982\u4e0b\uff1a<\/p>\n
\r\n********************************************************************************\r\nUNDO BLK:  \r\nxid: 0x0007.009.00000321  seq: 0xb8  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000\r\n \r\n Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset\r\n---------------------------------------------------------------------------\r\n0x01 0x1f78     \r\n \r\n*-----------------------------\r\n* Rec #0x1  slt: 0x09  objn: 71723(0x0001182b)  objd: 71723  tblspc: 5(0x00000005)\r\n*       Layer:  11 (Row)   opc: 1   rci 0x00   \r\nUndo type:  Regular undo    Begin trans    Last buffer split:  No \r\nTemp Object:  No \r\nTablespace Undo:  No \r\nrdba: 0x00000000Ext idx: 0\r\nflg2: 0\r\n*-----------------------------\r\nuba: 0x00c0031c.00b8.2c ctl max scn: 0x0000.0014413e prv tx scn: 0x0000.00144173\r\ntxn start scn: scn: 0x0000.00144bbe logon user: 0\r\n prev brb: 12583711 prev bcl: 0\r\nKDO undo record:\r\nKTB Redo \r\nop: 0x03  ver: 0x01  \r\ncompat bit: 4 (post-11) padding: 1\r\nop: Z\r\nKDO Op code: DRP row dependencies Disabled\t\t\/\/\u8bf4\u660e\u56de\u6eda\u65f6\u8fdb\u884c\u7684\u662fdelete\u64cd\u4f5c\r\n  xtype: XA flags: 0x00000000  bdba: 0x01400013  hdba: 0x01400012\r\nitli: 2  ispac: 0  maxfr: 4858\r\ntabn: 0 slot: 2(0x2)\t\t\r\n \r\nEnd dump data blocks tsn: 2 file#: 3 minblk 800 maxblk 800\r\n<\/pre>\n
\u4ece\u63d2\u5165\u64cd\u4f5c\u7684undo\u4e2d\u53ef\u4ee5\u627e\u5230\u56de\u6eda\u65f6\u9700\u8981\u5220\u9664\u7684\u884c\u7684rowid\u4fe1\u606f\uff0c\u5982\u4e0b\uff1a
\n\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/<\/p>\n
\r\n\u8bf4\u660e\uff1a\r\nrowid\u7531data_object_id# rfile# block# row#\u7ec4\u6210\r\n\u5360\u752810\u4e2abytes\u7684\u7a7a\u95f4\uff0c 32bit\u7684 data_object_id#,10 bit \u7684 rfile#,22bit \u7684 block#,16 bit \u7684 row#.\r\n\r\n1.objn: 71723(0x0001182b)\t\t        \/\/data_object_id\r\n2.bdba: 0x01400013\t\t\t\t\/\/relative_fno \u548c block_number\r\n3.slot: 2(0x2) \t\t\t\t\t\/\/row_number\r\n<\/pre>\n
\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/<\/p>\n
\r\nsys@ora11gr2> select dbms_utility.data_block_address_file(to_number('01400013','xxxxxxxx')) from dual;\r\n\r\nDBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('01400013','XXXXXXXX'))\r\n----------------------------------------------------------------------\r\n                                                                     5\r\n\r\n1 row selected.\r\n\r\nsys@ora11gr2> select dbms_utility.data_block_address_block(to_number('01400013','xxxxxxxx')) from dual;\r\n\r\nDBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('01400013','XXXXXXXX'))\r\n-----------------------------------------------------------------------\r\n                                                                     19\r\n\r\n1 row selected.\r\n\r\nsys@ora11gr2> select dbms_rowid.rowid_create(1,71723,5,19,2) from dual;\r\n\r\nDBMS_ROWID.ROWID_C\r\n------------------\r\nAAARgrAAFAAAAATAAC\r\n\r\n1 row selected.\r\n\r\n<\/pre>\n
\u5728\u7b2c\u4e00\u4e2a\u63d2\u5165\u4f46\u672a\u63d0\u4ea4\u7684\u4f1a\u8bdd\u4e2d\u67e5\u770b\uff1a<\/p>\n
\r\nsys@ora11gr2> select rowid, \r\n  2  dbms_rowid.rowid_object(rowid) dobj,\r\n  3  dbms_rowid.rowid_relative_fno(rowid) fno,\r\n  4  dbms_rowid.rowid_block_number(rowid) block_number,\r\n  5  dbms_rowid.rowid_row_number(rowid) row_number,\r\n  6  t.* from yallonking.redoundo t;\r\n\r\nROWID                    DOBJ        FNO BLOCK_NUMBER ROW_NUMBER         ID NAME\r\n------------------ ---------- ---------- ------------ ---------- ---------- --------------------\r\nAAARgrAAFAAAAATAAA      71723          5           19          0          1 redoundo1\r\nAAARgrAAFAAAAATAAB      71723          5           19          1          2 redoundo2_p2\r\nAAARgrAAFAAAAATAAC      71723          5           19          2          3 redoundo3\r\n\r\n3 rows selected.\r\n<\/pre>\n
\u5bf9\u4e8e\u5220\u9664\u64cd\u4f5c<\/strong>
\n\u5220\u9664\u64cd\u4f5c\u7684\u4e8b\u52a1\u4fe1\u606f\u5982\u4e0b\uff1a<\/p>\n
\r\nsys@ora11gr2> alter system dump undo header '_SYSSMU4_3451911709$';\r\n\r\nSystem altered.\r\n<\/pre>\n
dump\u6587\u4ef6\u90e8\u5206\u5982\u4e0b\uff1a<\/p>\n
\r\n index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt\r\n  ------------------------------------------------------------------------------------------------\r\n   0x0f   10    0x80  0x0318  0x0000  0x0000.00144c7b  0x00c000b6  0x0000.000.00000000  0x00000001   0x00000000  0\r\n\r\n<\/pre>\n
\u67e5\u770b\u5220\u9664\u64cd\u4f5c\u7684undo\u4fe1\u606f
\ndump\u51fa\u5bf9\u5e94\u7684\u5757\u4fe1\u606f\u90e8\u5206\u5982\u4e0b\uff1a<\/p>\n
\r\n*-----------------------------\r\n* Rec #0x12  slt: 0x0f  objn: 71723(0x0001182b)  objd: 71723  tblspc: 5(0x00000005)\r\n*       Layer:  11 (Row)   opc: 1   rci 0x00\r\nUndo type:  Regular undo    Begin trans    Last buffer split:  No\r\nTemp Object:  No\r\nTablespace Undo:  No\r\nrdba: 0x00000000Ext idx: 0\r\nflg2: 0\r\n*-----------------------------\r\nuba: 0x00c000b6.00f8.0f ctl max scn: 0x0000.0014413f prv tx scn: 0x0000.001441b9\r\ntxn start scn: scn: 0x0000.00144c72 logon user: 0\r\n prev brb: 12583093 prev bcl: 0\r\nKDO undo record:\r\nKTB Redo\r\nop: 0x04  ver: 0x01\r\ncompat bit: 4 (post-11) padding: 1\r\nop: L  itl: xid:  0x0005.004.00000326 uba: 0x00c00173.00ef.2a\r\n                      flg: C---    lkc:  0     scn: 0x0000.00144c37\r\nKDO Op code: IRP row dependencies Disabled\t\t\/\/\u8bf4\u660e\u56de\u6eda\u65f6\u8fdb\u884c\u7684\u662finsert\u64cd\u4f5c\r\n  xtype: XA flags: 0x00000000  bdba: 0x01400013  hdba: 0x01400012\r\nitli: 1  ispac: 0  maxfr: 4858\r\ntabn: 0 slot: 0(0x0) size\/delt: 16\r\nfb: --H-FL-- lb: 0x0  cc: 2\r\nnull: --\r\ncol  0: [ 2]  c1 02\t\t\t\t\t\t\t\/\/\u5220\u9664\u524d\u7684\u503c1\r\ncol  1: [ 9]  72 65 64 6f 75 6e 64 6f 31\t\/\/\u5220\u9664\u524d\u7684\u503credoundo1\r\n\r\nEnd dump data blocks tsn: 2 file#: 3 minblk 182 maxblk 182\r\n<\/pre>\n
\u67e5\u770b\u5177\u4f53\u503c<\/p>\n
\r\nsys@ora11gr2> select dump(1,16) from dual;\r\n\r\nDUMP(1,16)\r\n-----------------\r\nTyp=2 Len=2: c1,2\r\n\r\nsys@ora11gr2> @cast_to_varchar2 7265646f756e646f31\r\n\r\nUTL_RAW.CAST_TO_VARCHAR2('7265646F756E646F31')\r\n----------------------------------------------------------------------------------------------------\r\nredoundo1\r\n\r\n<\/pre>\n
\u4e0b\u8fb9\u5355\u72ec\u8003\u8651update\u7684\u64cd\u4f5c<\/strong>
\n27\u53f7\u4f1a\u8bdd\u4fee\u65392\u6b21\uff08\u672a\u63d0\u4ea4\uff09\u5e76\u8bb0\u5f55\u6bcf\u6b21\u7684savepoint<\/p>\n
\r\nsys@ora11gr2> select sid from v$mystat where rownum<2;\r\n\r\n       SID\r\n----------\r\n        27\r\n\r\n1 row selected.\r\n\r\nsys@ora11gr2> select t.rowid,t.* from yallonking.redoundo t order by 2;\r\n\r\nROWID                      ID NAME\r\n------------------ ---------- --------------------\r\nAAARgrAAFAAAAATAAB          2 redoundo2_p2\r\nAAARgrAAFAAAAATAAC          3 redoundo3\r\n\r\n2 rows selected.\r\n\r\nsys@ora11gr2> savepoint oraking;\r\n\r\nSavepoint created.\r\n\r\nsys@ora11gr2> update yallonking.redoundo set NAME='oraking' where id=2;\r\n\r\n1 row updated.\r\n\r\nsys@ora11gr2> savepoint yallonking;\r\n\r\nSavepoint created.\r\n\r\nsys@ora11gr2> update yallonking.redoundo set NAME='yallonking' where id=2;\r\n\r\n1 row updated.\r\n<\/pre>\n
\u5728\u65b0\u7684\u4f1a\u8bdd\u4e2d\u67e5\u770b\u5f53\u524d\u8fdb\u884c\u4fee\u6539\u7684\u6d3b\u52a8\u4e8b\u52a1\u4fe1\u606f<\/p>\n
\r\nsys@ora11gr2> select sid from v$mystat where rownum<2;\r\n\r\n       SID\r\n----------\r\n        32\r\n\r\n1 row selected.\r\n\r\nsys@ora11gr2> set line 400\r\nsys@ora11gr2> select ADDR,sid,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,a.STATUS,START_SCN \r\n  2  from v$transaction a,v$session b\r\n  3  where a.SES_ADDR=b.SADDR;\r\n\r\nADDR                    SID     XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS            START_SCN\r\n---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------\r\n0000000095A28840         27          9          1        814          3       2235        335         23 ACTIVE              1336130\r\n\r\n1 row selected.\r\n   \r\n<\/pre>\n
\u67e5\u770b\u4fee\u6539\u64cd\u4f5c\u7684undo\u4fe1\u606f
\ndump\u51fa\u5bf9\u5e94\u7684\u5757\u4fe1\u606f\u90e8\u5206\u5982\u4e0b\uff1a<\/p>\n
\r\n*-----------------------------\r\n* Rec #0x16  slt: 0x01  objn: 71723(0x0001182b)  objd: 71723  tblspc: 5(0x00000005)\r\n*       Layer:  11 (Row)   opc: 1   rci 0x00   \/\/\u7ec8\u6b62\u4e8e\u6b64\r\nUndo type:  Regular undo    Begin trans    Last buffer split:  No \r\nTemp Object:  No \r\nTablespace Undo:  No \r\nrdba: 0x00000000Ext idx: 0\r\nflg2: 0\r\n*-----------------------------\r\nuba: 0x00c008bb.014f.15 ctl max scn: 0x0000.00145a16 prv tx scn: 0x0000.00145a2a\r\ntxn start scn: scn: 0x0000.00146342 logon user: 0\r\n prev brb: 12585143 prev bcl: 0\r\nKDO undo record:\r\nKTB Redo \r\nop: 0x04  ver: 0x01  \r\ncompat bit: 4 (post-11) padding: 1\r\nop: L  itl: xid:  0x0009.010.0000032b uba: 0x00c00888.014f.1a\r\n                      flg: C---    lkc:  0     scn: 0x0000.001452e9\r\nKDO Op code: URP row dependencies Disabled\t\t\/\/update\u64cd\u4f5c\r\n  xtype: XA flags: 0x00000000  bdba: 0x01400013  hdba: 0x01400012\r\nitli: 3  ispac: 0  maxfr: 4858\r\ntabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 9\r\nncol: 2 nnew: 1 size: 5\r\ncol  1: [12]  72 65 64 6f 75 6e 64 6f 32 5f 70 32\t\t\/\/redoundo2_p2\r\n \r\n*-----------------------------\r\n* Rec #0x17  slt: 0x01  objn: 71723(0x0001182b)  objd: 71723  tblspc: 5(0x00000005)\r\n*       Layer:  11 (Row)   opc: 1   rci 0x16   \/\/\u7ee7\u7eed\u8fdb\u884cundo\r\nUndo type:  Regular undo   Last buffer split:  No \r\nTemp Object:  No \r\nTablespace Undo:  No \r\nrdba: 0x00000000\r\n*-----------------------------\r\nKDO undo record:\r\nKTB Redo \r\nop: 0x02  ver: 0x01  \r\ncompat bit: 4 (post-11) padding: 1\r\nop: C  uba: 0x00c008bb.014f.16\r\nKDO Op code: URP row dependencies Disabled\t\t\/\/update\u64cd\u4f5c\r\n  xtype: XA flags: 0x00000000  bdba: 0x01400013  hdba: 0x01400012\r\nitli: 3  ispac: 0  maxfr: 4858\r\ntabn: 0 slot: 1(0x1) flag: 0x2c lock: 3 ckix: 9\r\nncol: 2 nnew: 1 size: -3\r\ncol  1: [ 7]  6f 72 61 6b 69 6e 67\t\t\t\t\t\t\/\/oraking\r\n \r\nEnd dump data blocks tsn: 2 file#: 3 minblk 2235 maxblk 2235\r\n\r\n\r\nsys@ora11gr2> @cast_to_varchar2 7265646f756e646f325f7032\r\n\r\nUTL_RAW.CAST_TO_VARCHAR2('7265646F756E646F325F7032')\r\n----------------------------------------------------------------------------------------------------\r\nredoundo2_p2\r\nsys@ora11gr2> @cast_to_varchar2 6f72616b696e67\r\n\r\nUTL_RAW.CAST_TO_VARCHAR2('6F72616B696E67')\r\n----------------------------------------------------------------------------------------------------\r\noraking\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u8be5\u6587\u6863\u4eceinsert\uff0cupdate\uff0cdelete\u6240\u4ea7\u751f\u7684dump\u6587\u4ef6\u8fdb\u884c\u5206\u6790\u5e76\u505a\u7b80\u8981\u8bf4\u660e 31\u53f7\u4f1a\u8bdd\u63d2\u5165\u4e00\u6761\u8bb0 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[87],"tags":[],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1294"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1294"}],"version-history":[{"count":2,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1294\/revisions"}],"predecessor-version":[{"id":1296,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1294\/revisions\/1296"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1294"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}