ITPub博客

首页 > 数据库 > Oracle > 上次失败,继续使用bbed 提交事物

上次失败,继续使用bbed 提交事物

原创 Oracle 作者:desert_xu 时间:2015-09-02 10:34:58 0 删除 编辑

bbed 提交事物(续)

2015/09/02

desert_xu


  上次使用bbed 提交事物失败,导致undo header 块损坏,导致重启,出现 ORA-00600: 内部错误代码, 参数: [kddummy_blkchk], [2], [41], [38504], [], [], [], [] 的错误。
    俗话说 失败是成功之母,总结上次失败了的原因,主要是修改的地方遗漏了很多了,我只把简单的事物状态 state、cflags 改成 0900
  0x1c   10    0x80  0x0027  0x0002  0x0000.000e06bc  0x018004bf  0x0000.000.00000000
看了好多大牛的写的blog,但是自己还是看不明白,但是大概的思路对了,通过find /x  查处相关的数据,不知道要找哪些数据修改哦,还是比较迷茫,只能dump了 大量的undo header trace 看看了,了解 undo header 结构,不过bbed对非SYSTEM undo header 是不能 print 相关的数据结构信息了,只能用dump命令查看然后结合猜着了。
  

点击(此处)折叠或打开

  1. BBED> map
  2.  File: /export/home/oracle/oradata/ora10g/system01.dbf (1)
  3.  Block: 9 Dba:0x00400009
  4. ------------------------------------------------------------
  5.  Unlimited Undo Segment Header

  6.  struct kcbh, 20 bytes @0 通用文件头
  7.  struct ktech, 72 bytes @20 undo extent control header
  8.  struct ktemh, 16 bytes @92 undo extent map header
  9.  struct ktetb[6], 48 bytes @108 extent table
  10.  struct ktuxc, 104 bytes @4148 extent control
  11.  struct ktuxe[255], 10200 bytes @4252 undo extent table
  12.  ub4 tailchk @8188

  13. BBED> map/v
  14.  File: /export/home/oracle/oradata/ora10g/system01.dbf (1)
  15.  Block: 9 Dba:0x00400009
  16. ------------------------------------------------------------
  17.  Unlimited Undo Segment Header

  18.  struct kcbh, 20 bytes @0
  19.     ub1 type_kcbh @0
  20.     ub1 frmt_kcbh @1
  21.     ub1 spare1_kcbh @2
  22.     ub1 spare2_kcbh @3
  23.     ub4 rdba_kcbh @4
  24.     ub4 bas_kcbh @8
  25.     ub2 wrp_kcbh @12
  26.     ub1 seq_kcbh @14
  27.     ub1 flg_kcbh @15
  28.     ub2 chkval_kcbh @16
  29.     ub2 spare3_kcbh @18

  30.  struct ktech, 72 bytes @20 extent control header
  31.     ub4 spare1_ktech @20
  32.     word tsn_ktech @24
  33.     ub4 lastmap_ktech @28
  34.     ub4 mapcount_ktech @32
  35.     ub4 extents_ktech @36
  36.     ub4 blocks_ktech @40
  37.     ub2 mapend_ktech @44
  38.     struct hwmark_ktech, 32 bytes @48
  39.     struct locker_ktech, 8 bytes @80
  40.     ub4 flag_ktech @88

  41.  struct ktemh, 16 bytes @92 extent map header
  42.     ub4 count_ktemh @92
  43.     ub4 next_ktemh @96
  44.     ub4 obj_ktemh @100
  45.     ub4 flag_ktemh @104

  46.  struct ktetb[6], 48 bytes @108 extent table
  47.     ub4 ktetbdba @108
  48.     ub4 ktetbnbk @112

  49.  struct ktuxc, 104 bytes @4148 事物控制头
  50.     struct ktuxcscn, 8 bytes @4148
  51.     struct ktuxcuba, 8 bytes @4156
  52.     sb2 ktuxcflg @4164
  53.     ub2 ktuxcseq @4166
  54.     sb2 ktuxcnfb @4168
  55.     ub4 ktuxcinc @4172
  56.     sb2 ktuxcchd @4176
  57.     sb2 ktuxcctl @4178
  58.     ub2 ktuxcmgc @4180
  59.     ub4 ktuxcopt @4188
  60.     struct ktuxcfbp[5], 60 bytes @4192

  61.  struct ktuxe[255], 10200 bytes @4252 事物表
  62.     ub4 ktuxexid @4252
  63.     ub4 ktuxebrb @4256
  64.     struct ktuxescn, 8 bytes @4260
  65.     sb4 ktuxesta @4268
  66.     ub1 ktuxecfl @4269
  67.     sb2 ktuxeuel @4270

  68.  ub4 tailchk @8188


上面是system undo header 结构,从非system undo header dump trace 文件和 system undo header trace 文件 结构都一样,当然 offset 位置信息是不一样的哦。上面只能参考参考了。

后来我的想到了通过dump trace 文件来比对 oracle 在commit 之后做了哪些动作,改了哪些东西。

一下是实验
先俩个事物
SQL> insert into t1 values( 1,'AAAAA');
先不提交 然后 dump undo header
sys@ORA10G@159> SELECT xidusn,xidslot,xidsqn,ubablk,ubafil,ubasqn,ubarec FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
        17         28         39       1215          6         25         37
------------------------------------------------------------------------------------------------------------------------------------------------
selec name from v$rollname where usn=17;
-----------------------------------------------------------------------------
sys@ORA10G@159> alter system dump undo header '_SYSSMU17$';

系统已更改。

sys@ORA10G@159> oradebug setmypid
已处理的语句
sys@ORA10G@159> oradebug tracefile_name
/export/home/oracle/admin/ora10g/udump/ora10g_ora_14766.trc
sys@ORA10G@159> /export/home/oracle/admin/ora10g/udump/ora10g_ora_14766.trc
然后提交在dump undo 文件

点击(此处)折叠或打开

  1. ---
  2. > Unix process pid: 14780, image: oracle@solaris10 (TNS V1-V3)
  3. 15,17c15,16
  4. < *** 2015-09-01 18:21:53.172
  5. < *** SERVICE NAME:(SYS$USERS) 2015-09-01 18:21:53.172
  6. < *** SESSION ID:(159.9) 2015-09-01 18:21:53.172
  7. ---
  8. > *** SERVICE NAME:(SYS$USERS) 2015-09-01 18:24:50.837
  9. > *** SESSION ID:(159.11) 2015-09-01 18:24:50.837
  10. 44c43
  11. < TRN CTL:: seq: 0x0019 chd: 0x001f ctl: 0x0019 inc: 0x00000000 nfb: 0x0001
  12. ---
  13. > TRN CTL:: seq: 0x0019 chd: 0x001f ctl: 0x001c inc: 0x00000000 nfb: 0x0002
  14. 49c48
  15. < uba: 0x00000000.0019.24 ext: 0x2 spc: 0xbfa
  16. ---
  17. > uba: 0x018004bf.0019.25 ext: 0x2 spc: 0xb74
  18. 83c82
  19. < 0x19 9 0x00 0x0027 0xffff 0x0000.000e06ad 0x018004bf 0x0000.000.00000000
  20.   0x00000001 0x00000000 1441102692
  21. ---
  22. > 0x19 9 0x00 0x0027 0x001c 0x0000.000e06ad 0x018004bf 0x0000.000.00000000
  23.   0x00000001 0x00000000 1441102692
  24. 86c85
  25. < 0x1c 10 0x80 0x0027 0x0002 0x0000.000e06bc 0x018004bf 0x0000.000.00000000
  26.   0x00000001 0x00000000 0
  27. ---
  28. > 0x1c 9 0x00 0x0027 0xffff 0x0000.000e0744 0x018004bf 0x0000.000.00000000
  29.   0x00000001 0x00000000 1441103043

这里要修改的数据 大概有四个地方

 1、事物控制头部TRN CTL ctl:0019 ==>001c
2、最近一次commit的事物 uel 0xffff ==> 当前事物的slot#,trn tb 里的 index索引号(xidslot#) 0xffff=>001c
3、当前事物的state\cflags  0a80 ==> 0900
4、当前事物的uel 改成 ==> 无穷大 也就是 0xffff

未commit 的trace文件 部分
   TRN CTL:: seq: 0x0019 chd: 0x001f ctl: 0x0019 inc: 0x00000000 nfb: 0x0001  --上次使用这个回滚段的事物的槽号(slot)
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x018004bf.0019.25 scn: 0x0000.000e00f2

 index   state cflag   wrap#    uel        scn                      uba               
 0x19    9    0x00  0x0027  0xffff  0x0000.000e06ad  0x018004bf  0x0000.000.00000000  0x00000001   0x00000000  1441102692   ----在本回滚段上次上次事物相关信息
   0x1a    9    0x00  0x0027  0x0015  0x0000.000e0658  0x018004bf  0x0000.000.00000000  0x00000001   0x00000000  1441102663
   0x1b    9    0x00  0x0026  0x0023  0x0000.000e018b  0x018004bd  0x0000.000.00000000  0x00000001   0x00000000  1441100372
   0x1c   10    0x80  0x0027  0x0002  0x0000.000e06bc  0x018004bf  0x0000.000.00000000  0x00000001   0x00000000  0
   0x1d    9    0x00  0x0026  0x002f  0x0000.000e032c  0x018004b8  0x0000.000.00000000  0x00000001   0x00000000  1441101312
   0x1e    9    0x00  0x0026  0x0021  0x0000.000e012e  0x018004bd  0x0000.000.00000000  0x00000001   0x00000000  1441100169
   0x1f    9    0x00  0x0026  0x001e  0x0000.000e0125  0x018004bd  0x0000.000.00000000  0x00000002   0x00000000  1441100169

然后再做实验 再插入一条数据

insert into t1 valus (1,'aaaaa');
不提交,dump trace 文件
sys@ORA10G@159> alter system flush buffer_cache;

系统已更改。

sys@ORA10G@159> alter system checkpoint;

系统已更改。

sys@ORA10G@159>  SELECT xidusn,xidslot,xidsqn,ubablk,ubafil,ubasqn,ubarec FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
        14         29         44       1523          6         25         39

sys@ORA10G@159> ^[[A^[[A
                    " - 其余行忽略。
sys@ORA10G@159> ^[[A^[[A
sys@ORA10G@159> 令 "
sys@ORA10G@159> select name from v$rollname where usn=14;

NAME
------------------------------
_SYSSMU14$

sys@ORA10G@159> _SYSSMU14$
SP2-0042: 未知命令 "_SYSSMU14$" - 其余行忽略。
sys@ORA10G@159> alter system dump undo header '_SYSSMU14$';

系统已更改。

sys@ORA10G@159> oradebug setmypid
已处理的语句
sys@ORA10G@159> oradebug tracefile_name
/export/home/oracle/admin/ora10g/udump/ora10g_ora_14780.trc
sys@ORA10G@159> /export/home/oracle/admin/ora10g/udump/ora10g_ora_14780.trc
SP2-0734: 未知的命令开头 "/export/ho..." - 忽略了剩余的行。
sys@ORA10G@159> 
sys@ORA10G@159> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on 星期二 9月 1 21:50:26 2015

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

连接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORA10G@159> alter system dump undo header '_SYSSMU14$';

系统已更改。

sys@ORA10G@159> oradebug setmypid
已处理的语句
sys@ORA10G@159> oradebug tracefile_name
/export/home/oracle/admin/ora10g/udump/ora10g_ora_15202.trc
sys@ORA10G@159> /export/home/oracle/admin/ora10g/udump/ora10g_ora_15202.trc
SP2-0734: 未知的命令开头 "/export/ho..." - 忽略了剩余的行。
sys@ORA10G@159> 
sys@ORA10G@159> _SYSSMU14
SP2-0042: 未知命令 "_SYSSMU14" - 其余行忽略。
sys@ORA10G@159> select header_file,header_block from dba_segments where segment_name='_SYSSMU14$';

HEADER_FILE HEADER_BLOCK
----------- ------------
          6           57

sys@ORA10G@159> SELECT xidusn,xidslot,xidsqn,ubablk,ubafil,ubasqn,ubarec FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
        14         29         44       1523          6         25         39

一下是操作过程,需要细心、看仔细,不然出错就问题大了

点击(此处)折叠或打开

  1. BBED> find /x ffff top
  2.  File: /export/home/oracle/oradata/udotbs02.dbf (6)
  3.  Block: 121 Offsets: 6137 to 6236 Dba:0x01800079
  4. ------------------------------------------------------------------------
  5.  ffff7f00 0000001f 00040001 00ca1d82 0080011f 00140001 004e1500 00000015
  6.  0004000b 007e1d00 00000000 00000000 00000000 00000000 00000000 00000020
  7.  00000081 00800182 d40d0000 00a18709 00060000 00000000 00000000 00000001
  8.  00000010

  9.  <32 bytes per line>

  10. BBED> f
  11.  File: /export/home/oracle/oradata/udotbs02.dbf (6)
  12.  Block: 121 Offsets: 7058 to 7157 Dba:0x01800079
  13. ------------------------------------------------------------------------
  14.  ffff0000 00000000 00000000 00000100 0000551f e5551f00 00008000 8001a0d0
  15.  0d000000 a1870900 18000000 00000000 00000000 00000100 00000413 e5551f00
  16.  00008000 8001f2d0 0d000000 2f080900 19000000 00000000 00000000 00000100
  17.  00008e13

  18.  <32 bytes per line>

  19. BBED> d offset 7040 count 100
  20.  File: /export/home/oracle/oradata/udotbs02.dbf (6)
  21.  Block: 121 Offsets: 7040 to 7139 Dba:0x01800079
  22. ------------------------------------------------------------------------
  23.  20000000 83008001 aed60d00 0000a187 0900ffff 00000000 00000000 00000000
  24.  01000000 551fe555 1f000000 80008001 a0d00d00 0000a187 09001800 00000000
  25.  00000000 00000000 01000000 0413e555 1f000000 80008001 f2d00d00 00002f08
  26.  09001900

  27.  <32 bytes per line>

  28. BBED> set file 1 block 9
  29.         FILE# 1
  30.         BLOCK# 9

  31. BBED> map
  32.  File: /export/home/oracle/oradata/ora10g/system01.dbf (1)
  33.  Block: 9 Dba:0x00400009
  34. ------------------------------------------------------------
  35.  Unlimited Undo Segment Header

  36.  struct kcbh, 20 bytes @0

  37.  struct ktech, 72 bytes @20

  38.  struct ktemh, 16 bytes @92

  39.  struct ktetb[6], 48 bytes @108

  40.  struct ktuxc, 104 bytes @4148

  41.  struct ktuxe[255], 10200 bytes @4252

  42.  ub4 tailchk @8188


  43. BBED> map/v
  44.  File: /export/home/oracle/oradata/ora10g/system01.dbf (1)
  45.  Block: 9 Dba:0x00400009
  46. ------------------------------------------------------------
  47.  Unlimited Undo Segment Header

  48.  struct kcbh, 20 bytes @0
  49.     ub1 type_kcbh @0
  50.     ub1 frmt_kcbh @1
  51.     ub1 spare1_kcbh @2
  52.     ub1 spare2_kcbh @3
  53.     ub4 rdba_kcbh @4
  54.     ub4 bas_kcbh @8
  55.     ub2 wrp_kcbh @12
  56.     ub1 seq_kcbh @14
  57.     ub1 flg_kcbh @15
  58.     ub2 chkval_kcbh @16
  59.     ub2 spare3_kcbh @18

  60.  struct ktech, 72 bytes @20
  61.     ub4 spare1_ktech @20
  62.     word tsn_ktech @24
  63.     ub4 lastmap_ktech @28
  64.     ub4 mapcount_ktech @32
  65.     ub4 extents_ktech @36
  66.     ub4 blocks_ktech @40
  67.     ub2 mapend_ktech @44
  68.     struct hwmark_ktech, 32 bytes @48
  69.     struct locker_ktech, 8 bytes @80
  70.     ub4 flag_ktech @88

  71.  struct ktemh, 16 bytes @92
  72.     ub4 count_ktemh @92
  73.     ub4 next_ktemh @96
  74.     ub4 obj_ktemh @100
  75.     ub4 flag_ktemh @104

  76.  struct ktetb[6], 48 bytes @108
  77.     ub4 ktetbdba @108
  78.     ub4 ktetbnbk @112

  79.  struct ktuxc, 104 bytes @4148
  80.     struct ktuxcscn, 8 bytes @4148
  81.     struct ktuxcuba, 8 bytes @4156
  82.     sb2 ktuxcflg @4164
  83.     ub2 ktuxcseq @4166
  84.     sb2 ktuxcnfb @4168
  85.     ub4 ktuxcinc @4172
  86.     sb2 ktuxcchd @4176
  87.     sb2 ktuxcctl @4178
  88.     ub2 ktuxcmgc @4180
  89.     ub4 ktuxcopt @4188
  90.     struct ktuxcfbp[5], 60 bytes @4192

  91.  struct ktuxe[255], 10200 bytes @4252
  92.     ub4 ktuxexid @4252
  93.     ub4 ktuxebrb @4256
  94.     struct ktuxescn, 8 bytes @4260
  95.     sb4 ktuxesta @4268
  96.     ub1 ktuxecfl @4269
  97.     sb2 ktuxeuel @4270

  98.  ub4 tailchk @8188


  99. BBED> p ktuxe
  100. struct ktuxe[0], 40 bytes @4252
  101.    ub4 ktuxexid @4252 0x0000002e
  102.    ub4 ktuxebrb @4256 0x0040018f
  103.    struct ktuxescn, 8 bytes @4260
  104.       ub4 kscnbas @4260 0x000d1f80
  105.       ub2 kscnwrp @4264 0x0000
  106.    sb4 ktuxesta @4268 6225929 (NONE)
  107.    ub1 ktuxecfl @4269 0x00
  108.    sb2 ktuxeuel @4270 95
  109. struct ktuxe[1], 40 bytes @4292
  110.    ub4 ktuxexid @4292 0x0000002f
  111.    ub4 ktuxebrb @4296 0x00400198
  112.    struct ktuxescn, 8 bytes @4300
  113.       ub4 kscnbas @4300 0x000dd663
  114.       ub2 kscnwrp @4304 0x0000
  115.    sb4 ktuxesta @4308 917513 (NONE)
  116.    ub1 ktuxecfl @4309 0x00
  117.    sb2 ktuxeuel @4310 14
  118. struct ktuxe[2], 40 bytes @4332
  119.    ub4 ktuxexid @4332 0x0000002f
  120.    ub4 ktuxebrb @4336 0x00400198
  121.    struct ktuxescn, 8 bytes @4340
  122.       ub4 kscnbas @4340 0x000dd661
  123.       ub2 kscnwrp @4344 0x0000
  124.    sb4 ktuxesta @4348 65545 (NONE)
  125.    ub1 ktuxecfl @4349 0x00
  126.    sb2 ktuxeuel @4350 1
  127. struct ktuxe[3], 40 bytes @4372
  128.    ub4 ktuxexid @4372 0x0000002e
  129.    ub4 ktuxebrb @4376 0x0040018f
  130.    struct ktuxescn, 8 bytes @4380
  131.       ub4 kscnbas @4380 0x000d1f70
  132.       ub2 kscnwrp @4384 0x0000
  133.    sb4 ktuxesta @4388 5898249 (NONE)
  134.    ub1 ktuxecfl @4389 0x00
  135.    sb2 ktuxeuel @4390 90
  136. struct ktuxe[4], 40 bytes @4412
  137.    ub4 ktuxexid @4412 0x0000002f
  138.    ub4 ktuxebrb @4416 0x00400197
  139.    struct ktuxescn, 8 bytes @4420
  140.       ub4 kscnbas @4420 0x000d4f82
  141.       ub2 kscnwrp @4424 0x0000
  142.    sb4 ktuxesta @4428 4718601 (NONE)
  143.    ub1 ktuxecfl @4429 0x00
  144.    sb2 ktuxeuel @4430 72
  145. struct ktuxe[5], 40 bytes @4452
  146.    ub4 ktuxexid @4452 0x0000002f
  147.    ub4 ktuxebrb @4456 0x00400198
  148.    struct ktuxescn, 8 bytes @4460
  149.       ub4 kscnbas @4460 0x000dd655
  150.       ub2 kscnwrp @4464 0x0000
  151.    sb4 ktuxesta @4468 5308425 (NONE)
  152.    ub1 ktuxecfl @4469 0x00
  153.    sb2 ktuxeuel @4470 81
  154. struct ktuxe[6], 40 bytes @4492
  155.    ub4 ktuxexid @4492 0x0000002e
  156.    ub4 ktuxebrb @4496 0x0040018e
  157.    struct ktuxescn, 8 bytes @4500
  158.       ub4 kscnbas @4500 0x000d1f6a
  159.       ub2 kscnwrp @4504 0x0000
  160.    sb4 ktuxesta @4508 1507337 (NONE)
  161.    ub1 ktuxecfl @4509 0x00
  162.    sb2 ktuxeuel @4510 23
  163. struct ktuxe[7

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21980353/viewspace-1789189/,如需转载,请注明出处,否则将追究法律责任。

下一篇: bbed
请登录后发表评论 登录
全部评论

注册时间:2013-10-23

  • 博文量
    79
  • 访问量
    241118