[20231103]rename IDL_UB1$后使用bbed的恢复3.txt

[20231103]rename IDL_UB1$后使用bbed的恢复3.txt

--//上午解决rename IDL_UB1$后使用bbed的恢复问题,就是涉及到的5个索引4个需要修改,其中一个因为NULL值的缘故,不需要修改。
--//主要原因是rename是delete再insert obj$,反过来思考,如果修改时长度等长,我仅仅需要name等于原来的字符串就可以,当然有
--//一个前提,rename后在几个索引的位置不能发生变动。不然这样的改动量也是很大的,这样修复索引仅仅是修复i_obj2,i_obj5,
--//name等于原来的字符串就ok了,这样改动仅仅涉及3个块,自己还是测试验证看看。

1.环境:
--//先从冷备份恢复数据库,步骤略。
SYS@book> @ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1\$'| awk '{printf "%6d %6d %20s\n",int($1/8192), $1-int($1/8192)*8192, $2}'
   243   6325             IDL_UB1$
   351   5692             IDL_UB1$
   375   5692             IDL_UB1$
 17086   4802             IDL_UB1$
 34025   5594               SELECT
 34029   5278        COMMIT"SELECT
 36154   5278        COMMIT"SELECT
 95512   3371            IDL_UB1$l

--//注:显示的第一个字段对应数据块号,第二个字段对应相应数据块的偏移.
--//前面的测试我已经探察对应数据块,主要修改数据块243,351,375对应对象是obj$以及对应索引i_obj2,i_obj5.
--//注:前面的恢复测试说明我的疏漏,实际上rename是先delete,在insert,这样涉及到几个索引都要修改,因为rowid已经发生了变化,并
--//且记录在索引里面.参考前面的测试:[20231103]rename IDL_UB1$后使用bbed的恢复的后遗症.txt"

2.开始测试:
SYS@book> rename IDL_UB1$ to IDL_UB1Y;
Table renamed.
--//注:修改前后长度一样,我仅仅将$ -> Y,这样在两个索引段的位置即使变化,也在同一个数据块中,前后位置的差别。

SYS@book> alter system checkpoint;
System altered.

$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1Y'| awk '{printf "%6d %6d %20s\n",int($1/8192), $1-int($1/8192)*8192, $2}'
   351   3983             IDL_UB1Y
   375   3983             IDL_UB1Y
 86182   4750             IDL_UB1Y
--//仅仅涉及3个数据块.我仅仅修改里面的Y=>$,这样等于索引改动还在同一块中,修改的工作量也大大减少.
--//我仅仅需要修改数据块86182以及索引块(351,375)指向的行IDL_UB1Y => IDL_UB1$就ok了。
--//也就是 Y (ascii 0x59) 变成 $ (ascii 0x24).

3.使用bbed观察与修复:
--//dba = 1,86182

BBED> x  /rnnncncntttnccnxnnncct dba 1,86182 *kdbr[18]
rowdata[0]                                  @4736
----------
flag@4736: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4737: 0x02
cols@4738:   18

col    0[3] @4739: 225
col    1[3] @4743: 225
col    2[1] @4747: 0
col    3[8] @4749: IDL_UB1Y
col    4[2] @4758: 1
col    5[0] @4761: *NULL*
col    6[2] @4762: 2
col    7[7] @4765: 2013-08-24 11:37:39
col    8[7] @4773: 2023-11-07 08:45:45
col    9[7] @4781: 2023-11-07 08:45:45
col   10[2] @4789: 1
col   11[0] @4792: *NULL*
col   12[0] @4793: *NULL*
col   13[1] @4794: 0
col   14[0] @4796: *NULL*
col   15[1] @4797: 0
col   16[2] @4799: 2
~~~~~~~~~~~~~~~~~~~
col   17[1] @4802: 0

SYS@book> @bbvi 1 86182
BVI_COMMAND
----------------------------------------------------------------------------------------------------
bvi -b 706002944 -s 8192 /mnt/ramdisk/book/system01.dbf
xxd -c16 -g 2 -s 706002944 -l 8192 /mnt/ramdisk/book/system01.dbf
dd if=/mnt/ramdisk/book/system01.dbf bs=8192 skip=86182 count=1 of=1_86182.dd conv=notrunc 2>/dev/null
od -j 706002944 -N 8192 -t x1 -v /mnt/ramdisk/book/system01.dbf
hexdump -s 706002944 -n 8192 -C -v /mnt/ramdisk/book/system01.dbf
alter system dump datafile '/mnt/ramdisk/book/system01.dbf' block 86182;
6 rows selected.
--//使用bvi修改比较简单.

--//dba = 1,351
BBED> x /rncncccnnn dba 1,351 *kd_off[37]
rowdata[4]                                  @3972
----------
flag@3972:     0x00 (NONE)
lock@3973:     0x02
keydata[6]:    0x00  0x41  0x50  0xa6  0x00  0x12

data key:
col    0[1] @3981: 0
col    1[8] @3983: IDL_UB1Y
col    2[2] @3992: 1
col    3[0] @3995: *NULL*
col    4[0] @3996: *NULL*
col    5[0] @3997: *NULL*
col    6[2] @3998: 2
col    7[1] @4001: 0
col    8[3] @4003: 225

SYS@book> @bbvi 1 351
BVI_COMMAND
-----------------------------------------------------
bvi -b 2875392 -s 8192 /mnt/ramdisk/book/system01.dbf

--//dba = 1,375
BBED> x /rncnnnnccn dba 1,375 *kd_off[37]
rowdata[4]                                  @3972
----------
flag@3972:     0x00 (NONE)
lock@3973:     0x02
keydata[6]:    0x00  0x41  0x50  0xa6  0x00  0x12

data key:
col    0[1] @3981: 0
col    1[8] @3983: IDL_UB1Y
col    2[2] @3992: 1
col    3[2] @3995: 2
col    4[1] @3998: 0
col    5[0] @4000: *NULL*
col    6[0] @4001: *NULL*
col    7[0] @4002: *NULL*
col    8[3] @4003: 225

SYS@book> @bbvi 1 375
BVI_COMMAND
------------------------------------------------------
bvi -b 3072000 -s 8192 /mnt/ramdisk/book/system01.dbf

BBED> sum apply dba 1,86182
Check value for File 1, Block 86182:
current = 0x3af3, required = 0x3af3

BBED> sum apply dba 1,351
Check value for File 1, Block 351:
current = 0x9706, required = 0x9706

BBED> sum apply dba 1,375
Check value for File 1, Block 375:
current = 0x2f2d, required = 0x2f2d
--//使用完成后,使用x命令检查过程略.

verify dba 1,86182
verify dba 1,351
verify dba 1,375

4.验证是否正确:

SYS@book> alter system flush buffer_cache;
System altered.

SYS@book> alter system flush shared_pool;
System altered.

SYS@book> select rowid from sys.obj$ where name='IDL_UB1$';
ROWID
------------------
AAAAASAABAAAVCmAAS

SYS@book> @ rowid AAAAASAABAAAVCmAAS
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
        18          1      86182         18   0x4150A6           1,86182              alter system dump datafile 1 block 86182

--//注:在测试过程遇到1个小问题,alter system flush buffer_cache; dba=1,86182块被重写还原(不知代为什么).后面我关闭数据库修改OK.

SYS@book> analyze table idl_ub1$ validate structure cascade;
Table analyzed.

SYS@book> analyze table obj$ validate structure cascade;
analyze table obj$ validate structure cascade
*
ERROR at line 1:
ORA-08100: index is not valid - see trace file for diagnostics

$ oerr ora 8100
08100, 00000, "index is not valid - see trace file for diagnostics"
// *Cause:  Validate Index detected an inconsistency in its argument index
// *Action:  Send trace file to your customer support representative

--//还是不行!!
--//最后我发现连删除的索引也要按照顺序排序.

BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1713]                               @5681
-------------
flag@5681:     0x01 (KDXRDEL)
lock@5682:     0x02
keydata[6]:    0x00  0x40  0x00  0xf3  0x00  0x17

data key:
col    0[1] @5690: 0
col    1[8] @5692: IDL_UB1$
col    2[2] @5701: 1
col    3[0] @5704: *NULL*
col    4[0] @5705: *NULL*
col    5[0] @5706: *NULL*
col    6[2] @5707: 2
col    7[1] @5710: 0
col    8[3] @5712: 225

BBED> x /rncncccnnn dba 1,351 *kd_off[37]
rowdata[4]                                  @3972
----------
flag@3972:     0x00 (NONE)
lock@3973:     0x02
keydata[6]:    0x00  0x41  0x50  0xa6  0x00  0x12

data key:
col    0[1] @3981: 0
col    1[8] @3983: IDL_UB1$
col    2[2] @3992: 1
col    3[0] @3995: *NULL*
col    4[0] @3996: *NULL*
col    5[0] @3997: *NULL*
col    6[2] @3998: 2
col    7[1] @4001: 0
col    8[3] @4003: 225

--//kd_off[36]指向的删除的记录,kd_off[37]指向正常的记录.两个除了flag,keydata[6]不同外,其它一样.
--//$ ascii=24,ascii 23 对应字符 #.
--//我必须把指向kd_off[36]的记录的col    1[8] @5692: IDL_UB1$修改为col    1[8] @5692: IDL_UB1#.这样保证顺序一致.

--//修改后的样子如下:
BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1713]                               @5681
-------------
flag@5681:     0x01 (KDXRDEL)
lock@5682:     0x02
keydata[6]:    0x00  0x40  0x00  0xf3  0x00  0x17

data key:
col    0[1] @5690: 0
col    1[8] @5692: IDL_UB1#
col    2[2] @5701: 1
col    3[0] @5704: *NULL*
col    4[0] @5705: *NULL*
col    5[0] @5706: *NULL*
col    6[2] @5707: 2
col    7[1] @5710: 0
col    8[3] @5712: 225

--//dba = 1,375 也是一样,不然 analyze table obj$ validate structure cascade;还会报错.
BBED> x /rncnnnnccn dba 1,375 *kd_off[36]
rowdata[1713]                               @5681
-------------
flag@5681:     0x01 (KDXRDEL)
lock@5682:     0x02
keydata[6]:    0x00  0x40  0x00  0xf3  0x00  0x17

data key:
col    0[1] @5690: 0
col    1[8] @5692: IDL_UB1#
col    2[2] @5701: 1
col    3[2] @5704: 2
col    4[1] @5707: 0
col    5[0] @5709: *NULL*
col    6[0] @5710: *NULL*
col    7[0] @5711: *NULL*
col    8[3] @5712: 225

SYS@book> alter system flush shared_pool;
System altered.

SYS@book> alter system flush buffer_cache;
System altered.

SYS@book> analyze table obj$ validate structure cascade;
Table analyzed.

$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 7 09:48:34 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 97280
Total Pages Processed (Data) : 64260
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 13456
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4206
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 15358
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 392040706 (3.392040706)
--//这样算下来工作量也不小.

总结:
--//1.修改对应数据块(86182))以及索引块(351,375)的name=IDL_UB1$.
--//2.修改删除索引对应数据块原来的IDL_UB1$->IDL_UB1#,保证顺序一致.这样analyze table obj$ validate structure cascade;ok.
--//3.我觉得第2步可以不做,这样就无法通过analyze table obj$ validate structure cascade;罢了,不会影响使用.
--//4.不建议采用这样的方式恢复,直接修改数据字段是最高效的方法。
作者:lfree原文地址:https://www.cnblogs.com/lfree/p/17824965.html

%s 个评论

要回复文章请先登录注册