3836mysql数据库应用基础_UNDO$ – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜分...

指定undo$对应数据块(11g 1_225,10g 1_106)[具体时候需要查询]

BBED> set file 1 block 225

FILE# 1

BLOCK# 225

查询数据内容,主要关注kdbr

BBED> map

File: /u01/oracle/oradata/ora11g/system01.dbf (1)

Block: 225 Dba:0x004000e1

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

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 48 bytes @20

struct kdbh, 14 bytes @68

struct kdbt[1], 4 bytes @82

sb2 kdbr[31] @86 <===表示31条记录,从0开始

ub1 freespace[3644] @148

ub1 rowdata[4396] @3792

ub4 tailchk @8188

查看30号回滚段内容(列举其中一个)

BBED> p *kdbr[30]

rowdata[0]

----------

ub1 rowdata[0] @3792 0x2c

BBED> x /rncnnnnnnn

rowdata[0] @3792

----------

flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@3793: 0x01

cols@3794: 17

col 0[2] @3795: 30

col 1[21] @3798: _SYSSMU30_3379578723$

col 2[2] @3820: 1

col 3[2] @3823: 3

col 4[3] @3826: 432

col 5[1] @3830: 0

col 6[1] @3832: 0

col 7[1] @3834: 0

col 8[1] @3836: 0

col 9[1] @3838: 0

col 10[2] @3840: 5 <===修改前对应值undo$.status$

col 11[2] @3843: 6

col 12[0] @3846: *NULL*

col 13[0] @3847: *NULL*

col 14[0] @3848: *NULL*

col 15[0] @3849: *NULL*

col 16[2] @3850: 2

BBED> x /r

rowdata[0] @3792

----------

flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@3793: 0x01

cols@3794: 17

col 0[2] @3795: 0xc1 0x1f

col 1[21] @3798: 0x5f 0x53 0x59 0x53 0x53 0x4d 0x55 0x33 0x30 0x5f

0x33 0x33 0x37 0x39 0x35 0x37 0x38 0x37 0x32 0x33 0x24

col 2[2] @3820: 0xc1 0x02

col 3[2] @3823: 0xc1 0x04

col 4[3] @3826: 0xc2 0x05 0x21

col 5[1] @3830: 0x80

col 6[1] @3832: 0x80

col 7[1] @3834: 0x80

col 8[1] @3836: 0x80

col 9[1] @3838: 0x80

col 10[2] @3840: 0xc1 0x06 <===修改前16进制值

col 11[2] @3843: 0xc1 0x07

col 12[0] @3846: *NULL*

col 13[0] @3847: *NULL*

col 14[0] @3848: *NULL*

col 15[0] @3849: *NULL*

col 16[2] @3850: 0xc1 0x03

修改回滚段状态

BBED> m /x 02 offset 3842 <===注意修改为02

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /u01/oracle/oradata/ora11g/system01.dbf (1)

Block: 225 Offsets: 3842 to 4353 Dba:0x004000e1

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

0202c107 ffffffff 02c1032c 001102c1

<32 bytes per line>

BBED> p *kdbr[30]

rowdata[0]

----------

ub1 rowdata[0] @3792 0x2c

BBED> x /r

rowdata[0] @3792

----------

flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@3793: 0x01

cols@3794: 17

col 0[2] @3795: 0xc1 0x1f

col 1[21] @3798: 0x5f 0x53 0x59 0x53 0x53 0x4d 0x55 0x33 0x30 0x5f

0x33 0x33 0x37 0x39 0x35 0x37 0x38 0x37 0x32 0x33 0x24

col 2[2] @3820: 0xc1 0x02

col 3[2] @3823: 0xc1 0x04

col 4[3] @3826: 0xc2 0x05 0x21

col 5[1] @3830: 0x80

col 6[1] @3832: 0x80

col 7[1] @3834: 0x80

col 8[1] @3836: 0x80

col 9[1] @3838: 0x80

col 10[2] @3840: 0xc1 0x02 <===修改后16进制值

col 11[2] @3843: 0xc1 0x07

col 12[0] @3846: *NULL*

col 13[0] @3847: *NULL*

col 14[0] @3848: *NULL*

col 15[0] @3849: *NULL*

col 16[2] @3850: 0xc1 0x03

BBED> x /rncnnnnnnn

rowdata[0] @3792

----------

flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@3793: 0x01

cols@3794: 17

col 0[2] @3795: 30

col 1[21] @3798: _SYSSMU30_3379578723$

col 2[2] @3820: 1

col 3[2] @3823: 3

col 4[3] @3826: 432

col 5[1] @3830: 0

col 6[1] @3832: 0

col 7[1] @3834: 0

col 8[1] @3836: 0

col 9[1] @3838: 0

col 10[2] @3840: 1 <===实际展示值undo$.status$

col 11[2] @3843: 6

col 12[0] @3846: *NULL*

col 13[0] @3847: *NULL*

col 14[0] @3848: *NULL*

col 15[0] @3849: *NULL*

col 16[2] @3850: 2

1.修改为02(表示的实际值比修改值大1[1:DELETE,2:OFFLINE, 3:ONLINE,4:UNDEFINED,5:NEEDS RECOVERY,6:PARTLY AVAILABLE,其他表示:UNDEFINED])

2.Offset需要在10列漂移量上+2(或者11列偏移量-1)

修改验证值

BBED> sum apply

Check value for File 1, Block 225:

current = 0x9708, required = 0x9708

补充说明

1)通过dbms_metadata.get_ddl分析DBA_ROLLBACK_SEGS可以知道seg$.status$表示含义

2, 'OFFLINE'

3, 'ONLINE',

4, 'UNDEFINED'

5, 'NEEDS RECOVERY',

6, 'PARTLY AVAILABLE'

other, 'UNDEFINED'

2)该方法一般出现在使用隐含参数屏蔽回滚段无效之后使用,一般不到最后,不建议使用该方法,可能引起不可预知的后果

3)使用该方法启动数据库后,需要手工删除seg$中被bbed处理过的回滚段